Testing a SQL Statement

Results 1 to 5 of 5

Thread: Testing a SQL Statement

  1. #1
    scopeout Guest

    Default Testing a SQL Statement

    Is there anyway to test and see if a table exist in a db, instead of getting this message:<BR><BR>Error Type:<BR>Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)<BR>[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query &#039;fever&#039;. Make sure it exists and that its name is spelled correctly.<BR><BR>I was thinking that the sql statement might return a true or false, but it didn&#039;t work...<BR>

  2. #2
    Joel N Guest

    Default RE: Testing a SQL Statement

    I expect using ADOX you could check for the existence of a table but your question raises another one... do you mean to tell us that someone is asking you to code against a database where you don&#039;t know the SCHEMA????? WOW! You must have really ticked off your boss!<BR><BR>ADOX link:<BR>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscadoapireference.asp?frame=true<BR><BR>HTH

  3. #3
    Scopeout Guest

    Default RE: Testing a SQL Statement

    I&#039;m allowing some employees to create their own tables and field names. You create the table, then you add your field names, but I work with some that will try to create the table and it already exist, or they&#039;ll try to add field names to a table don&#039;t exist. That&#039;s why I was wanting to test and see if the table already exist.

  4. #4
    Joel N Guest

    Default RE: Testing a SQL Statement

    Empowering the peasant class, huh? <BR><BR>OK, here&#039;s some rather UNTESTED sample code...<BR><BR>&#060;%<BR>Dim cat <BR>Set cat = Server.CreateObject("ADOX.Catalog")<BR>Dim tbl <BR>&#039;Using Access in example. Change to appropriate connection<BR>&#039;info if using something else<BR>cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:pathdatabase.mdb;"<BR><BR>For Each tbl In cat.Tables<BR> If UCase(tbl.Name) = UCase(TableNameTheUserIsTryingToQuery) Then<BR> &#039;Do what they want<BR> Else<BR> &#039;Do what you would do if the table doesn&#039;t exist<BR> End If<BR>Next<BR>%&#062;<BR><BR>If you have a BUNCH of tables this could be lengthy. Views will show up as tables if your backend DB is SQL Server so that could bloat the time it takes this code to run also. What would be more efficient, if your backend supports stored procs, is to fire a stored proc that tests for the existence of a table and then returns a true or false to your script. That way you don&#039;t have to iterate through what could be a lot of tables.<BR><BR><BR>Good Luck

  5. #5
    Joel N Guest

    Default Oops!

    Before everyone rags on me........<BR><BR>If the test condition is met you should do an "Exit For"<BR><BR>If UCase(tbl.Name) = UCase(TableNameTheUserIsTryingToQuery) Then<BR>&#039;Do what they want<BR>Exit For &#039;&#060;-- Forgot this bad boy. My fault.<BR>Else<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts