I am looking for a simple way to check if a database table exists. I
keep getting advice to use "Try.. Catch" and other error handling
methods, but I obviously don't want to have to display an error
message and stop the process every time someone loads the script after
the table is created because that would mean the page could only ever
run once which of course not the solution I was looking for. I simply
want to know how I can check (using ASP code or an SQL query) to
create a table if it doesn't already exist, and once it does, it
doesn't try anymore. With PHP, I've just been able instruct it to
create the table and it just ignores the command if the table already
exists (at least with MySQL). With ASP, however, I've tried the
following:
(1) I simply instruct the database program to create a table, but it
returns an error if the table already exists (it looks like it's an
ASP error that's caught before even trying to query the database). To
do so, I used this code:
connectionToDatabase.Execute("CREATE TABLE members(UserName CHAR(200),
Password CHAR(200), Email CHAR(200), Session CHAR(200))")
(2) Then I thought I was clever, so I just adjusted for the error in
the SQL syntax rather than trying to figure out how to find out if the
table exists using ASP:
connectionToDatabase.Execute("CREATE TABLE IF EXISTS members(UserName
CHAR(200), Password CHAR(200), Email CHAR(200), Session CHAR(200))")
Again, no luck. Now it tells me that my CREATE TABLE syntax is wrong
(but it's not... I use it with PHP and MySQL all the time)
(3) Finally, I tried figuring out how to handle errors, but since all
of the strategies I find stop the page from processing and just print
out a more attractive error message, it's obviously not going to solve
the problem.
So my question is: Isn't there some simple way to check for the
existence of a table in ASP? I mean isn't it a pretty common thing to
want to do? And if not, then is there some SQL command that it WILL
accept that will not return an error if the table already exists? I'm
using Access this time around if that has anything to do with it.
I greatly appreciate any help/insight/comments.
Thanks
Jonathan 2 11834
Be aware that you don't have to stop the code if an error occurs.
If you put "On Error Resume Next" at the top of your page (a bit dodgy if
the code could produce unexpected errors) and then at the end put
"response.write err.number" then you can get the error number that relates
to "That table already exists". Once you get this number you can put in a
connectionToDatabase.Execute("Select * from tblTableName")
If err.number = 21294032 then
'table creation code here
end if
Otherwise, a SQL query like this could help:
if not exists (select * from tblTableName) create table........
M
"Jonathan" <th***************@yahoo.com> wrote in message
news:67**************************@posting.google.c om... I am looking for a simple way to check if a database table exists. I keep getting advice to use "Try.. Catch" and other error handling methods, but I obviously don't want to have to display an error message and stop the process every time someone loads the script after the table is created because that would mean the page could only ever run once which of course not the solution I was looking for. I simply want to know how I can check (using ASP code or an SQL query) to create a table if it doesn't already exist, and once it does, it doesn't try anymore. With PHP, I've just been able instruct it to create the table and it just ignores the command if the table already exists (at least with MySQL). With ASP, however, I've tried the following:
(1) I simply instruct the database program to create a table, but it returns an error if the table already exists (it looks like it's an ASP error that's caught before even trying to query the database). To do so, I used this code:
connectionToDatabase.Execute("CREATE TABLE members(UserName CHAR(200), Password CHAR(200), Email CHAR(200), Session CHAR(200))")
(2) Then I thought I was clever, so I just adjusted for the error in the SQL syntax rather than trying to figure out how to find out if the table exists using ASP:
connectionToDatabase.Execute("CREATE TABLE IF EXISTS members(UserName CHAR(200), Password CHAR(200), Email CHAR(200), Session CHAR(200))")
Again, no luck. Now it tells me that my CREATE TABLE syntax is wrong (but it's not... I use it with PHP and MySQL all the time)
(3) Finally, I tried figuring out how to handle errors, but since all of the strategies I find stop the page from processing and just print out a more attractive error message, it's obviously not going to solve the problem.
So my question is: Isn't there some simple way to check for the existence of a table in ASP? I mean isn't it a pretty common thing to want to do? And if not, then is there some SQL command that it WILL accept that will not return an error if the table already exists? I'm using Access this time around if that has anything to do with it.
I greatly appreciate any help/insight/comments.
Thanks
Jonathan
Jonathan wrote: So my question is: Isn't there some simple way to check for the existence of a table in ASP? I mean isn't it a pretty common thing to want to do? And if not, then is there some SQL command that it WILL accept that will not return an error if the table already exists? I'm using Access this time around if that has anything to do with it.
It depends on your database:
Access: http://www.aspfaq.com/show.asp?id=2350
SQL Server: http://www.aspfaq.com/show.asp?id=2458
MySQL:
See the MySQL documentation
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Fire Juggler |
last post by:
On my website, the user can create tables, but how do i check if the table
exists, and if it doesn't it creates the table?
Thanks
--
Kathryn (Fire Juggler)
http://www.firejugglers.34sp.com...
|
by: Simon Wigzell |
last post by:
You'd think this would be the most basic sql query in the world but noooooo!
I've tried this:
on error resume next
strsql = "SELECT * FROM " & session("TablePrefix") & CurrentTable
SET rs =...
|
by: G G |
last post by:
I need to check if a table exists in Access using ADO externally. I
tried
"Select Name from MSysObjects Where Name = 'myTable'"
with the ADO command object, but I got an error than I don't...
|
by: Pawel Wrobel |
last post by:
Hi!
I have a folowing situation - in my Access database I have a table that is
linked with SPS. I put some data into this table to present it no SPS. The
thing is that when I have no connection...
|
by: Geoff Jones |
last post by:
Hi
I'm trying to drop a table by using:
Dim cmd As New OleDbCommand("DROP TABLE IF EXISTS books", myconnection)
cmd.ExecuteNonQuery()
but I get a syntax error: "Syntax error in DROP TABLE...
|
by: jaya_rath |
last post by:
Check table's existance
--------------------------------------------------------------------------------
How can I check if a table exists in an MS SQL database using asp.plz help me . advance...
|
by: Dan2kx |
last post by:
Is there a line of code i could use to check if a table exists (so that i can delete it if it does)
im thinking
If table1 = true then
etc
thanks
Dan
|
by: munkee |
last post by:
Hi all,
Pretty simple as stated in the topic, I want to check the fron t end of my datyabase to see if a temporary table exists, if it does then I will carry on executing myc ode if it does not I...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |