Q: EXISTS????

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

Geoff Jones

Hi

I've posted a similar question to the VB.net group but others suggested that
I may have more success here.

I'm trying to drop a table, called books, 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"

All I'm trying to do is to check whether the table exists before I drop it.
I have discovered a work round by simply including the command in a Try
Catch block and ignoring the exception if no table exists. However, this
feels very messy. Surely there must be a cleaner way to do this.

Can anybody help?

Thanks in advance

Geoff
 
Hi Geoff,

The actual syntax depends on your database.
Since you are using OleDb you might invoke
OleDbConnection.GetOleDbSchemaTable method to find out if your table exists
within the database and then just invoke DROP TABLE sql command.
 
Miha,

Geoff has aked this in the language.vb newsgroup and I provided him the
method to see if the table exist (The same as you sugested).

That is not the problem. However I am as curious as he if the IF Exists
exist in Adonet (especially OleDB) because I do not see any documentation
about it related to Adonet.

Therefore I suggested him to ask it here.

Cor
 
Hi Miha

Interesting. So if the table does not exist, then GetOleDbSchemaTable will
return NULL? Or would I use it in another way? Could you supply some example
code?

Geoff

Miha Markic said:
Hi Geoff,

The actual syntax depends on your database.
Since you are using OleDb you might invoke
OleDbConnection.GetOleDbSchemaTable method to find out if your table exists
within the database and then just invoke DROP TABLE sql command.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Geoff Jones said:
Hi

I've posted a similar question to the VB.net group but others suggested that
I may have more success here.

I'm trying to drop a table, called books, 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"

All I'm trying to do is to check whether the table exists before I drop it.
I have discovered a work round by simply including the command in a Try
Catch block and ignoring the exception if no table exists. However, this
feels very messy. Surely there must be a cleaner way to do this.

Can anybody help?

Thanks in advance

Geoff
Click to expand...
Click to expand...
 
Hi Geoff,

There is an example in the help file on how to retrieve the table metadata
from database.
If you look at parameters you'll see an object array passed to method.
Fields in those array corresponds to DataTable returned from the method.
You set a field to filter the column before retrieving it.

HTH,
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Geoff Jones said:
Hi Miha

Interesting. So if the table does not exist, then GetOleDbSchemaTable will
return NULL? Or would I use it in another way? Could you supply some example
code?

Geoff

Miha Markic said:
Hi Geoff,

The actual syntax depends on your database.
Since you are using OleDb you might invoke
OleDbConnection.GetOleDbSchemaTable method to find out if your table exists
within the database and then just invoke DROP TABLE sql command.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Geoff Jones said:
Hi

I've posted a similar question to the VB.net group but others
Click to expand...
suggested
that
I may have more success here.

I'm trying to drop a table, called books, 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"

All I'm trying to do is to check whether the table exists before I
Click to expand...
drop
it.
I have discovered a work round by simply including the command in a Try
Catch block and ignoring the exception if no table exists. However, this
feels very messy. Surely there must be a cleaner way to do this.

Can anybody help?

Thanks in advance

Geoff
Click to expand...
Click to expand...
Click to expand...
 
Hi Cor,

Cor Ligthert said:
Miha,

Geoff has aked this in the language.vb newsgroup and I provided him the
method to see if the table exist (The same as you sugested).
Click to expand...

I hope he'll use it now :-)
That is not the problem. However I am as curious as he if the IF Exists
exist in Adonet (especially OleDB) because I do not see any documentation
about it related to Adonet.
Click to expand...

The command as any other Sql command is provider/database specific.
Ado.net just forwards them to the provider.
 
Miha,

Could have known that nock nock nock nock, against my head.

(I hate SQL you know that)

Thanks anyway.

:-)

Cor
 
To make the thread complete again,

\\\
Dim dtTableNames As DataTable
dtTableNames = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim dr As DataRow
For Each dr In dtTableNames.Rows
If dr("TABLE_NAME").ToString = "Persons" Then
dim cmd as new Oledb.OledbCommand("DROP TABLE Persons",conn)
cmd.ExecuteNonQuery()
End If
Next
///

Cor
 
What database are you connecting to? With Oracle, I could just query the
data dictionary to determine if a table exists.

You could definately leave your code as-is. Either a table exists to be
dropped, or it doesn't exist- why trace an error that doesn't matter? I'd
imagine you'd be spending more time across the wire (and on your processor)
trying to determine a table's existence.

I'm basing this on a common "upgrade schema" SQL script. Say client "A" has
version 1.0 of your product, and client "B" has version 1.0.2, which
includes a special stored procedure. Maybe that procedure's a good idea,
and you include it in your brand new version 1.5 release. Both clients want
to upgrade to 1.5 because you changed the background color of a data grid.
(laughs) Anyway, you would write a single script that would first try to
drop the procedure if it existed- for client "B", the old proc will drop.
For client "A", an error will just appear on the screen. Next, you'd
recreate the procedure. Your upgrade script wouldn't care that it got an
error for client "A". But you definately wouldn't want to write TWO
scripts, one to upgrade a client with version 1.0, and one to upgrade a
client with version 1.0.2, for something this simple.

Good luck,

-Thomas

But, don't ignore the exception; first check it to be sure that it's the
exception that you expect to receive.
 
Back
Top