472,791 Members | 1,733 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,791 software developers and data experts.

Q: Drop table and check if table exists

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
10 22583
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
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
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
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
> 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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jonathan | last post by:
I am looking for a simple way to check if a database table exists. I keep getting advice to use "Try.. Catch" and other error handling methods, but I obviously don't want to have to display an...
3
by: Snorkerz | last post by:
Can anyone help, I'm trying to change some code on an asp page, this line: mySQL="drop table snorkTable" does not seem to close the table. Net time the page is refreshed I'm told that Table...
2
by: T.S.Negi | last post by:
Hi there, I have created a hash table. After using it, somehow the primary key constraint of this hash table still exist in database. Which cause error. When I delete this constraint with...
2
by: robert | last post by:
the real DBAs aren't around for a few days, so rather than shoot myself in a tender spot, i thought i'd ask first. what docs i can find say that the syntax is: DROP TABLESPACE FOO seems...
1
by: Dragan Matic | last post by:
if exists (select * from sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table . GO For instance, this is a valid script in Ms SQL, it will drop table...
2
by: tpaulson | last post by:
I have a couple of DIV's that I hide/display based on radio buttons. On the DIV's, I have multiple drop down boxes. The source shows that they are populated, but I can't make them drop down. Only...
4
by: Gregor Kovač | last post by:
Hi! Is it possible do to something like this: DROP TABLE MY_TABLE ONLY IF EXISTS and the same for views, indexes, procedures, .... ? Best regards, Kovi --...
8
by: alessandro menchini | last post by:
Hi all, When i try to drop a table, and this table have (for example) some function related...db2 don't allow to drop the table how can i enter a command to drop the table AND all the objects...
5
by: Romulo NF | last post by:
Greetings, Im back here to show the new version of the drag & drop table columns (original script ). Ive found some issues with the old script, specially when trying to use 2 tables with...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.