Can't drop a temp table

[Nebil]

Hi,
I am having trouble with dropping a temp table. I was using it so many times in other projects but this time it went bad.
The only thing different now is that I referred a string value from the main form to another form.The other form populates a dgv based on the string value being accessed from the main form.

The drop table statement is placed at the form_load event of the other form.

Now the drop table procedure works fine when the form is closed and reopened with the condition of no record being passed to the temp table. But if at least one record is stored in the temp table and when the form is closed and reopened instead of dropping the table it pops a message saying 'temptable already exists'. Any help would be appreciated.Thank you

Here's the code that I've used:

This is a code i used in the main form under a toolstrip button

        Dim node As TreeNode

        node = New TreeNode
        node = TreeView3.SelectedNode

        If node Is Nothing Then
            MsgBox("Select a location from the list", MsgBoxStyle.Exclamation, "Payroll")
            TreeView3.ExpandAll()
            Exit Sub

        ElseIf node.Level <> 1 Then
            MsgBox("Select a location from the Locations list", MsgBoxStyle.Exclamation, "Payroll")
            TreeView3.ExpandAll()
            Exit Sub
        Else
            frmNewPerdiem.Show()
        End If

These are codes i used in the other form:

Private Sub frmNewPerdiem_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim qry2 As String = "SELECT * INTO temppd FROM tblPerDiem WHERE [monthIssued] AND [employeeID] IS NULL"
        DroptempTable()
        ExecSQL(qry2)
        LoadEmployees()
    End Sub



Public Sub LoadEmployees()
        conn = New OleDb.OleDbConnection(sconnString)
        conn.Open()
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text

        Dim empLocation As String = String.Empty
        empLocation = frmMain.pdlocation 'This is where i referred the value of the string from the main form
        cmd.CommandText = "sql statement"

        da.SelectCommand = cmd
        dtresult = New DataTable
        da.Fill(dtresult)
        Me.employeeDataGridView.DataSource = dtresult
        da.Dispose()
        cmd.Dispose()
        conn.Close()
    End Sub



Private Sub DroptempTable()
        On Error GoTo ErrorHandler
        'conn = New OleDb.OleDbConnection(sconnString)
        'conn.Open()
        'cmd.Connection = conn
        'cmd.CommandType = CommandType.Text

        Dim str As String = "DROP TABLE temppd"
        ExecSQL(str)
        'cmd.ExecuteNonQuery()
        'cmd.Dispose()
        'conn.Close()
ErrorHandler:
        If Err.Number = 7874 Then
            Resume Next
        End If
    End Sub

[djjeavons]

Hi

It would be better if you were to check whether the table existed before trying to create it or drop it. That way, regardless of what happens in your program (for example, a crash), you won't be left in a state where you can't create the table as it hasn't been dropped yet.

One way to check for the existence of a table is to use the GetSchema method of the connection object. So for example, the following function will drop a table if it exists and that is determined by using the GetSchema method:

Private Sub DropTable(ByVal tableName As String)

    Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\database3.accdb;Persist Security Info=False;")

        connection.Open()

        If connection.GetSchema("TABLES").Select(String.Format("TABLE_TYPE='TABLE' AND TABLE_NAME='{0}'", tableName)).Count > 0 Then

            Using Command As New OleDbCommand(String.Format("DROP TABLE {0}", tableName), connection)

                Command.ExecuteNonQuery()

            End Using

        End If

    End Using

End Sub

Then in your main code, you simply call DropTable each time before you attempt to insert data into the temporary table.

Also, I note in your code that you are using some very bad error handling techniques which are from the old vb days. And to make matters worse, you are not resetting your error handler when an error does occur. This will lead you down a very bad path in the long run and make debugging your program a nightmare. Please take some time to familiarise yourself with the Exception Handling techniques that are available in .NET.

HTH

[Nebil]

Thanks for the reply djjeavons,are you an arsenal fan by the way. One last thing to check, I had created lots of queries,join statements between like seven tables and does that have anything to do with not allowing the deletion...I get this message when i view the temp table design view in the database 'the database engine could not lock table'. thanks again

[djjeavons]

Hi

I'm not sure why you should get an error when viwing the temp table in design view. I wouldn't imagine it has anything to do with joins etc. as this is a temp table so it shouldn't have any referential integrity associated with it. As this is an Access database, try closing the database, opening it again and performing a compact and repair. Maybe somewhere along the way with all of the messing around it has become a little corrupt.

are you an arsenal fan by the way

I am indeed, hence the little dig at the other half of North London :)