How can I check if a Database Table exists?

[Jonathan]

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

 

[Matt Foster]

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

 

[Bob Barrows]

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