473,387 Members | 1,899 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

How can I check if a Database Table exists?

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
2 11830
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
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...
7
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 =...
1
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...
2
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...
10
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...
1
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...
2
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
5
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...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
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...
0
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
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
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...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.