CODEWITHSUNDEEP
sqlvb.net
Gutanoth
Gutanoth

Reputation: 842

Dropping a table in vb.net

I want to create a table with an SQL query using vb.net. I use the following code:

dBaseConnection.Open()
sql = "DROP TABLE TagSubs;" &
      "CREATE TABLE TagSubs (TAGNAME varchar(79), POLLTIME varchar(6), SCALEMODE varchar(8), DEADBAND varchar(15))"
Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(sql, dBaseConnection)
dBaseCommand.ExecuteNonQuery()
dBaseCommand = Nothing
dBaseConnection.Close()

For some reason, if i omit the line:

 "DROP TABLE TagSubs;"

The application creates the table just fine, the first time. But the second time I execute it, it tells me the table already exists and throws up an error. That is why I added the drop table part.

Unfortunatly for me, this gives me a syntax error that applies to "DROP TABLE or DROP INDEX"

What am I doing wrong ?

Edit 1:

The error message I get is the following(roughly translated from dutch):

The instruction DROP TABLE or DROP INDEX contains a syntaxerror

And the only help that is given by visual studio is: 

Get general help for this exception

Wich is not really any help at all!

Edit 2:

After the comments from Steve and Mitch, I have changed the code to:

sql = "DROP TABLE IF EXISTS TagSubs"
dBaseCommand = New System.Data.OleDb.OleDbCommand(sql, dBaseConnection)
dBaseCommand.ExecuteNonQuery()
dBaseCommand = Nothing

sql = "CREATE TABLE TagSubs (TAGNAME varchar(79), POLLTIME varchar(6), SCALEMODE varchar(8), DEADBAND varchar(15))"
dBaseCommand = New System.Data.OleDb.OleDbCommand(sql, dBaseConnection)
dBaseCommand.ExecuteNonQuery()
dBaseCommand = Nothing
dBaseConnection.Close()

However, this gives the error:

"An invalid SQL instruction was found. DELETE,INSERT, PROCEDURE, SELECT or UPDATE was expected"

Upvotes: 2

Views: 6710

Answers (2)

Robert Aria
Robert Aria

Reputation: 1

Try this one and put on your procedure to drop:

Dim rsSchema As ADODB.Recordset
rsSchema = New ADODB.Recordset

rsSchema = conn.OpenSchema(ADODB.SchemaEnum.adSchemaColumns, New Object() {Nothing, Nothing, **tablename**, Nothing})
'MsgBox(rsSchema)

If rsSchema.BOF And rsSchema.EOF Then
    MsgBox("Table does not exist")
Else
    MsgBox("Table exists")
End If
rsSchema.Close()
rsSchema = Nothing

Connection like this:

Public Sub modOpen()

    Msg = "Provider=SQLOLEDB.1; Persist Security Info=False;" & Chr(10)
    Msg = Msg & "password=******;Data Source=**KOMPUTERNAME**;Initial Catalog=**DATABASENAME**;User ID=**USERID**"


    Try
        conn = New Adodb.Connection
        conn.ConnectionString = Msg

    Catch ex As Exception
        MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error!!")

    End Try


End Sub

It work on VB.NET2010

Upvotes: 0

Steve
Steve

Reputation: 216353

Supposing that these commands are not well understood when they are joined together, I will try to separate them executing two different queries, then execute the two sql commands with two different runs.

The command to DROP the table should be executed only if the table exists. In DBF a table is normally contained in a simple Operating System File. So a check if the file exists should be enough.

dBaseConnection.Open()
Dim dBaseCommand As New System.Data.OleDb.OleDbCommand()
dBaseCommand.Connection = dBaseConnection
if File.Exists(... insert path here .... "\TagSubs.DBF") Then
    sql = "DROP TABLE TagSubs"
    dBaseCommand.CommandText = sql
    dBaseCommand.ExecuteNonQuery()
End If
sql="CREATE TABLE TagSubs (TAGNAME varchar(79), POLLTIME varchar(6), SCALEMODE varchar(8), DEADBAND varchar(15))"
dBaseCommand.CommandText = sql
dBaseCommand.ExecuteNonQuery()
dBaseCommand = Nothing
dBaseConnection.Close()

Upvotes: 3

Related Questions