By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

How can I check if a Database Table exists?

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Jul 19 '05 #2

P: n/a
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"
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.