Q: Drop table and check if table exists

[Geoff Jones]

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 or DROP INDEX"

Can anybody tell me how to check whether a table exists before deleting or
dropping it?

Also, could somebody tell me how to create a new table with the same
structure as an existing table but not the data within it (within VB.net
that is - I can do it in Access but I need to do it in VB code).

Thanks in advance

Geoff

 

[Cor Ligthert]

Hi Geoff,

Did I not send that code in a message of yours?

However when that drops works than you do not need that in my opinion.

Cor

 

[Geoff Jones]

Hi Cor

No, you did send a link with details about DROP TABLE but that didn't cover
any details as to whether the table existed.

Geoff

 

[Cor Ligthert]

Geoff,

See this message from Ken for your first question
http://www.google.com/[email protected]

And this from me for your second
http://www.google.com/[email protected]

I hope this helps?

Cor

 

[Geoff Jones]

Sorry Cor, I don't follow you. The link you give is to create a table rather
than a way to find out if a table already exists.

Am I missing something obvious?

Geoff

 

[Cor Ligthert]

Sorry Cor, I don't follow you. The link you give is to create a table
rather

than a way to find out if a table already exists.

Am I missing something obvious?

Using the code from Ken (the first link) you can see if a table exist.
Where is that debug.write(.....)
You can make someting as

For Each dr In dtTableNames.Rows
If dr("TABLE_NAME").ToString = "MyTable" Then
cmd.CommandText = "DROP TABLE MyTable"
cmd.ExecuteNonQuery()
exit for
End If
Next

I hope this helps?

Cor

 

[Geoff Jones]

Hi Cor

Ah, I see what you mean. However, do I take it that there is no IF EXISTS
command that I could use in my original idea i.e.

' Drop the table books if it exists
Dim cmd As New OleDbCommand("DROP TABLE IF EXISTS books", myconnection)
cmd.ExecuteNonQuery()

As far as I can see this is a valid SQL statement.

Geoff

 

[Cor Ligthert]

Hi Geoff,

I see not any documentation to it related to AdoNet, however it gives with
me as well errors

You can try it maybe in the AdoNet newsgroup, you cannot be the first one
with this problem. I always try to avoid SQL because I hate that always
errors giving scripting language.

Adonet
<
Web interface:
<http://communities2.microsoft.com/communities/newsgroups/en-us/?dg=microsof
t.public.dotnet.framework.adonet>

Cor

 

[Geoff Jones]

Hi Cor

Many thanks again for your help.

Yes, this is a bit strange. I would have thought it an essential command.
However, as with so many things, it doesn't appear to be.

Thanks again for your help. I'll try a post to the ADO.NET group as you
suggest.

Geoff

 

[Paul Clement]

¤ Hi Cor
¤
¤ Ah, I see what you mean. However, do I take it that there is no IF EXISTS
¤ command that I could use in my original idea i.e.
¤
¤ ' Drop the table books if it exists
¤ Dim cmd As New OleDbCommand("DROP TABLE IF EXISTS books", myconnection)
¤ cmd.ExecuteNonQuery()
¤
¤ As far as I can see this is a valid SQL statement.

No, the SQL statement EXISTS will not work in this manner. You could either trap the exception that
occurs when executing the DROP statement or use GetOleDbSchemaTable:

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"

DatabaseConnection.Open()

SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "books"})

If SchemaTable.Rows.Count <> 0 Then
'table exists
End If

DatabaseConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Geoff Jones]

Many thanks Paul

¤ Hi Cor
¤
¤ Ah, I see what you mean. However, do I take it that there is no IF EXISTS
¤ command that I could use in my original idea i.e.
¤
¤ ' Drop the table books if it exists
¤ Dim cmd As New OleDbCommand("DROP TABLE IF EXISTS books", myconnection)
¤ cmd.ExecuteNonQuery()
¤
¤ As far as I can see this is a valid SQL statement.

No, the SQL statement EXISTS will not work in this manner. You could either trap the exception that
occurs when executing the DROP statement or use GetOleDbSchemaTable:

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString =

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=E:\My Documents\db1.mdb"

DatabaseConnection.Open()

SchemaTable =

DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Col
umns, _