By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Q: Drop table and check if table exists

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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

Nov 20 '05 #2

P: n/a
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" <no**********@planet.nl> wrote in message
news:uS**************@TK2MSFTNGP10.phx.gbl...
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

Nov 20 '05 #3

P: n/a
Geoff,

See this message from Ken for your first question
http://www.google.com/gr*************************@TK2MSFTNGP11.phx.gbl

And this from me for your second
http://www.google.com/gr**************************************@news.dial .pipex.com

I hope this helps?

Cor

Nov 20 '05 #4

P: n/a
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" <no**********@planet.nl> wrote in message
news:uy**************@TK2MSFTNGP11.phx.gbl...
Geoff,

See this message from Ken for your first question
http://www.google.com/gr*************************@TK2MSFTNGP11.phx.gbl

And this from me for your second
http://www.google.com/gr**************************************@news.dial .pipex.com
I hope this helps?

Cor

Nov 20 '05 #5

P: n/a
> 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
Nov 20 '05 #6

P: n/a
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" <no**********@planet.nl> wrote in message
news:eu**************@TK2MSFTNGP09.phx.gbl...
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

Nov 20 '05 #7

P: n/a
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
<news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:
<http://communities2.microsoft.com/co...s/?dg=microsof
t.public.dotnet.framework.adonet>

Cor
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" <no**********@planet.nl> wrote in message
news:eu**************@TK2MSFTNGP09.phx.gbl...
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


Nov 20 '05 #8

P: n/a
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

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
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
<news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:
<http://communities2.microsoft.com/co...s/?dg=microsof t.public.dotnet.framework.adonet>

Cor
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" <no**********@planet.nl> wrote in message
news:eu**************@TK2MSFTNGP09.phx.gbl...
> 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



Nov 20 '05 #9

P: n/a
On Fri, 30 Jul 2004 10:29:20 +0100, "Geoff Jones" <ge***@NODAMNSPAM.com> wrote:

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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #10

P: n/a
Many thanks Paul

"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:3n********************************@4ax.com...
On Fri, 30 Jul 2004 10:29:20 +0100, "Geoff Jones" <ge***@NODAMNSPAM.com> wrote:
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, _ New Object() {Nothing, Nothing, "books"})

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

DatabaseConnection.Close()
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

Nov 20 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.