Drop table 1

[vladk]

Hi,

The "DROP TABLE My_Table;" statement causes exception in my vb application if the table was already dropped. How should I change that statement to address this issue?

Thank you
vladk

 

[lynchg]

you can check the tabledefs collection first to see if the table exists, then drop it.

 

[vladk]

lynchg,
I use OleDb in .net. I can't locate such a collection in OleDb. That's why I am asking about SQL approach.
vladk

 

[PHV]

Why not catching the exception ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

PHV knows somthing more on what he says than I ....

but a recordset of this sql

"SELECT [Name] From MSysObjects WHERE [Name]='" & My_Table & "'"

should be empty if the table doesnt exist any more.

 

[vladk]

PHV,
Thank you for the suggestion. It is good one. I usually prefer not to resort to exceptions but instead query things directly. Neverthless, I did try exceptions, and could not make it work for me. The problem is that I never really worked with ACCESS and I am very new to .NET.

JerryKlmns,
This is what I was going after. Thanks a lot!

Thank all of you. Your help is very valuable for me.

vladk

 

[vladk]

PHV,
Below is the example of my attempt to catch the exception. It does work and I hate it: it contains GoTo and interrogates message and not the code. I have no clue what substitutes On Error Resume Next and Err.Clear in VB.NET.

Private Sub DropTempTables()

Dim objCmd As New OleDb.OleDbCommand
Dim strSQL As String

Dim intTerr As Integer
Dim intTerrCount As Integer
Dim dctTerrToSkip As New ListDictionary

DropItAgain:
Try
OpenDB()
intTerrCount = mobjState.Territories.Count
objCmd = New OleDb.OleDbCommand(strSQL, mobjConn)
objCmd.CommandType = CommandType.Text

For intTerr = 1 To intTerrCount
If Not dctTerrToSkip.Contains(intTerr) Then
strSQL = "DROP TABLE TEMP_" & CStr(intTerr) & ";"
objCmd.CommandText = strSQL
objCmd.ExecuteNonQuery()
End If
Next intTerr

Catch ex As Exception

If InStr(ex.Message, "does not exist", CompareMethod.Binary) > 0 Then
If Not dctTerrToSkip.Contains(intTerr) Then
dctTerrToSkip.Add(intTerr, intTerr)
End If
GoTo DropItAgain
Else
MessageBox.Show(ex.Message & vbCr & ex.TargetSite.Name, "Cannot drop Temp table.", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End If

Finally
CloseDB()
objCmd = Nothing
End Try

vladk