How can I check if a Database Table exists?

Discussion in 'ASP General' started by Jonathan, Apr 4, 2004.

  1. Jonathan

    Jonathan Guest

    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, Apr 4, 2004
    #1
    1. Advertising

  2. Jonathan

    Matt Foster Guest

    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" <> wrote in message
    news:...
    > 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, Apr 4, 2004
    #2
    1. Advertising

  3. Jonathan

    Bob Barrows Guest

    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"
    Bob Barrows, Apr 4, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Thorsten Meininger
    Replies:
    1
    Views:
    450
    Sudsy
    Oct 13, 2004
  2. Totan
    Replies:
    0
    Views:
    914
    Totan
    Apr 17, 2006
  3. Jason Stacy
    Replies:
    2
    Views:
    4,647
    Roedy Green
    Apr 27, 2008
  4. Ulf Meinhardt
    Replies:
    8
    Views:
    6,108
  5. Replies:
    1
    Views:
    442
    Adrienne Boswell
    May 20, 2009
Loading...

Share This Page