473,699 Members | 3,186 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Q: Drop table and check if table exists

Hi

I'm trying to drop a table by using:

Dim cmd As New OleDbCommand("D ROP TABLE IF EXISTS books", myconnection)

cmd.ExecuteNonQ uery()

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 22757
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**********@p lanet.nl> wrote in message
news:uS******** ******@TK2MSFTN GP10.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************* ************@TK 2MSFTNGP11.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**********@p lanet.nl> wrote in message
news:uy******** ******@TK2MSFTN GP11.phx.gbl...
Geoff,

See this message from Ken for your first question
http://www.google.com/gr************* ************@TK 2MSFTNGP11.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.Ro ws
If dr("TABLE_NAME" ).ToString = "MyTable" Then
cmd.CommandText = "DROP TABLE MyTable"
cmd.ExecuteNonQ uery()
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("D ROP TABLE IF EXISTS books", myconnection)
cmd.ExecuteNonQ uery()

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

Geoff

"Cor Ligthert" <no**********@p lanet.nl> wrote in message
news:eu******** ******@TK2MSFTN GP09.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.Ro ws
If dr("TABLE_NAME" ).ToString = "MyTable" Then
cmd.CommandText = "DROP TABLE MyTable"
cmd.ExecuteNonQ uery()
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.microsof t.com/microsoft.publi c.dotnet.framew ork.adonet>

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

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("D ROP TABLE IF EXISTS books", myconnection)
cmd.ExecuteNonQ uery()

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

Geoff

"Cor Ligthert" <no**********@p lanet.nl> wrote in message
news:eu******** ******@TK2MSFTN GP09.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.Ro ws
If dr("TABLE_NAME" ).ToString = "MyTable" Then
cmd.CommandText = "DROP TABLE MyTable"
cmd.ExecuteNonQ uery()
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**********@p lanet.nl> wrote in message
news:%2******** ********@tk2msf tngp13.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.microsof t.com/microsoft.publi c.dotnet.framew ork.adonet>

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

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("D ROP TABLE IF EXISTS books", myconnection)
cmd.ExecuteNonQ uery()

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

Geoff

"Cor Ligthert" <no**********@p lanet.nl> wrote in message
news:eu******** ******@TK2MSFTN GP09.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.Ro ws
If dr("TABLE_NAME" ).ToString = "MyTable" Then
cmd.CommandText = "DROP TABLE MyTable"
cmd.ExecuteNonQ uery()
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***@NODAMNSP AM.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("D ROP TABLE IF EXISTS books", myconnection)
cmd.ExecuteNonQ uery()

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 GetOleDbSchemaT able:

Dim DatabaseConnect ion As New System.Data.Ole Db.OleDbConnect ion
Dim SchemaTable As DataTable

DatabaseConnect ion.ConnectionS tring = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=E:\My Documents\db1.m db"

DatabaseConnect ion.Open()

SchemaTable =
DatabaseConnect ion.GetOleDbSch emaTable(System .Data.OleDb.Ole DbSchemaGuid.Co lumns, _
New Object() {Nothing, Nothing, "books"})

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

DatabaseConnect ion.Close()
Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
11856
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 error message and stop the process every time someone loads the script after the table is created because that would mean the page could only ever run once which of course not the solution I was looking for. I simply want to know how I can check...
3
545
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 'snorkTable' already exists. I suspect my syntax is wrong - can anyone help?
2
6934
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 Alter table Drop con.... It gives no table exist error.
2
6431
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 simple enough, except that the other side is CREATE TABLESPACE FOO IN BARx
1
3576
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 pp_fisk only if it exists, is there a way to do something similar in Postgres? Tnx in advance. Dragan
2
2463
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 click on the Unplatted Radio button, the rest isn't functional, yet. Any ideas how to make these drop down in FF. This works in IE. Here is the code: <HTML> <HEAD> <TITLE>Door County Register of Deeds - Tract Inquiry</TITLE>
4
46018
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
6631
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 (functions...triggers...views) that depends on that table? A.M.
5
13782
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 drag&drop on the same page (which was not possible). Now ive a new concept of the script, more object oriented. Ive also commented the whole code so you guys can easier understand it engine. What exactly we need when trying to make a column drag &...
0
8704
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
8623
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9187
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9053
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8936
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8894
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3071
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2360
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.