Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > How to tell if a database table exists?

Reply
Thread Tools

How to tell if a database table exists?

 
 
Simon Wigzell
Guest
Posts: n/a
 
      06-17-2004
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. 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!


 
Reply With Quote
 
 
 
 
Simon Wigzell
Guest
Posts: n/a
 
      06-17-2004
<snip>

I have found and implemented this solution from the internet:

<!-- #include file="adovbs.inc"-->
<%
Set Conn = Server.CreateObject ("ADODB.Connection")
Conn.Open "DSN=Library"
Set Rs = Conn.OpenSchema (adSchemaTables)

tableExists = 0

Do While Not Rs.EOF
if (rs("Table_Name") = CurrentTable) then
tableExists = 1
end if
Rs.MoveNext
Loop

Rs.Close
Set Rs = Nothing

Conn.Close
Set Conn = Nothing
%>
You must find on your system the include file adovbs.inc and copy it to the
directory where you ASP code is!

I'm just astonished and shaking my head in utter disbelief that there isn't
a simpler way of determining if a table exists! UGH!


 
Reply With Quote
 
 
 
 
CJM
Guest
Posts: n/a
 
      06-17-2004
http://www.aspfaq.com/show.asp?id=2112

Chris


 
Reply With Quote
 
Yan Roosens
Guest
Posts: n/a
 
      06-17-2004
Hi Simon,

Simon Wigzell wrote:

> But it doesn't return an error if the table doesn't exist. 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!


With one line of SQL, I don't know, but with a few lines of vbscript....

set Cat = CreateObject("ADOX.Catalog")
Cat.activeConnection = conn
for each table in Cat.tables
if table.type="TABLE" then
if table.name = session("TablePrefix") & CurrentTable then
' do your thing....
end if
end if
next
set Cat = Nothing


HTH
Yan


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-17-2004
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.


 
Reply With Quote
 
PW
Guest
Posts: n/a
 
      06-17-2004

"Simon Wigzell" <(E-Mail Removed)> wrote in message
news:cIfAc.778765$oR5.348143@pd7tw3no...
> You'd think this would be the most basic sql query in the world but

noooooo!


If you're using Access, try this ...


myTableName = "BLAH"
myFileExists = FALSE
mySQL = "SELECT name FROM MSysObjects WHERE type in (1, 4)"
rs6.open mySQL,mydsn
Do While NOT rs6.EOF
if rs6("name") = myTableName then
myFileExists = TRUE
end if
rs6.MoveNext
Loop


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-17-2004
PW wrote:
> "Simon Wigzell" <(E-Mail Removed)> wrote in message
> news:cIfAc.778765$oR5.348143@pd7tw3no...
>> You'd think this would be the most basic sql query in the world but
>> noooooo!

>
>
> If you're using Access, try this ...
>
>
> myTableName = "BLAH"
> myFileExists = FALSE
> mySQL = "SELECT name FROM MSysObjects WHERE type in (1, 4)"
> rs6.open mySQL,mydsn



This part is just silly:
*******************************
> Do While NOT rs6.EOF
> if rs6("name") = myTableName then
> myFileExists = TRUE
> end if
> rs6.MoveNext
> Loop

*******************************

Change your sql to (in practice, I would use a saved parameter query instead
of dynamic sql):
mySQL = "SELECT count(*) FROM MSysObjects " & _
"WHERE type in (1, 4) AND [name] = '" & myTableName & "'"

Now, open rs6 and simply check whether or not rs6(0) contains 0.

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.


 
Reply With Quote
 
PW
Guest
Posts: n/a
 
      06-17-2004

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:eYvQ%(E-Mail Removed)...
>
> This part is just silly:
>



Done ...


myTableName = "BLAH"
mySQL = "SELECT count(*) FROM MSysObjects WHERE type in (1, 4) AND [name] =
'" & myTableName & "'"
rs6.open mySQL,mydsn
if rs6(0) = 0 then
myFileExists = FALSE
else
myFileExists = TRUE
end if


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Some people tell that at present, most web hosting servers supportall kinds of programming language, some people tell me that many web hostingserver don't support Java, What is the truth? Erwin Moller Java 3 05-07-2008 05:09 PM
CAN any one tell it whats the code tell it yogesh C++ 1 03-14-2007 01:12 PM



Advertisments