Q: Drop table and check if table exists

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

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

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

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

> 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

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

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

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

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)

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)