Q: EXISTS????

[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

 

[Miha Markic [MVP C#]]

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.

 

[Cor Ligthert]

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

 

[Geoff Jones]

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

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

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

 

[Miha Markic [MVP C#]]

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

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

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

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

 

[Miha Markic [MVP C#]]

Hi Cor,

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).

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.

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

 

[Cor Ligthert]

Miha,

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

(I hate SQL you know that)

Thanks anyway.



Cor

 

[Cor Ligthert]

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

 

[Thomas H]

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.

 

[Geoff Jones]

Thanks Cor & Miha

Geoff