Re: How to tell if a database table exists?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 06/17/04 

Date: Thu, 17 Jun 2004 09:40:16 -0400

Simon Wigzell wrote:
> 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 = conn.execute(strsql)
>
> tableExists = 0
>
> if (Err.number = 0) then
> tableExists = 1
> end if
>
> But it doesn't return an error if the table doesn't exist.

Yes it does. Your problem is that you've executed a statement that does not
raise an error between the statement that raises an error (the Execute
statement) and the statement that tests the Err object for the existence of
an error. When a statement executes with no error, the Err object is
cleared. Move the "tableExists = 0" line to before the Execute statement.
That will allow you to see the error.

Better yet, check the connection object's Errors collection, which will not
be affected by the execution of subsequent vbscript statements.

> I'm
> searching on the internet and hitting these long complicatred
> solutions involving the database "shema". Surely there is a simple
> way of telling with one line of sql if a table exists or not???
> Thanks!

It's possible, but the implementation depends on the database you are using.
Jet databases have a MSysObjects table which can be queried for the database
schema. SQL Server has INFORMATION_SCHEMA views which can also be queried
for this information. I suspect Oracle databases have similar structures.
See:
http://www.aspfaq.com/show.asp?id=2112

HTH,
Bob Barrows 

-- 
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Relevant Pages

  • Re: Business objects, subset of collection
    ... SQL only works when the statements are ... all items (invoices) are subscribing for events. ... all items and all items has to execute the criteria evaluation, ... The features of a OO database is basically the same as of a network ...
    (comp.object)
  • Re: Multiple Database Security - How to handle
    ... There is no 'execute as' in SQL Server but you can simplify security ... Assuming the DM database contains tables that are accessed only by ...
    (microsoft.public.sqlserver.security)
  • Re: Add record to database
    ... > I think im missing the line to execute the sql in the asp code. ... You still can't execute it without submitting from a form. ... All three of these options are off-topic for this newsgroup, ... Dim newclass, sSQL, dbConn ...
    (microsoft.public.inetserver.asp.db)
  • Re: PHP/Oracle - Pulling data into array
    ... MySQL has the flaw that you have to stuff values into SQL statements, ... DATA with SQL. ... Depending on what interface and database you're using. ... time it comes across a new statement, it works out the best way to execute it. ...
    (comp.lang.php)
  • Re: New to C# - DB question
    ... Firstly, you are interested in the System.Data namespace, also known as ... In there you will find a few sub namespaces for specific database ... is your sql statement. ... The former allows you to just execute some SQL, ...
    (microsoft.public.dotnet.languages.csharp)