[RESOLVED] Could this be more efficient?

[red_bull_n_vodka]

I tried bringing the open and close statements out, but then it doesnt get myOdbcCommand. Is there a more efficient way to write this? This ran for 45 minutes before thowing an error. It got though most of the tables.

For iTable = 1 To 12
        For iCounter = 0 To (ds.Tables(0).Rows.Count - 1)
            If (ds.Tables(0).Rows(iCounter).Item("U_GAS_SERV") <> 0) Then
                iGasServ = ds.Tables(0).Rows(iCounter).Item("U_GAS_SERV")
                Dim myDeleteStatement As String = "DELETE * FROM GSV00" & iTable & "T WHERE GSV001T.U_GAS_SERV = " & iGasServ
                Dim myOdbcCommand As New OdbcCommand(myDeleteStatement, gsvConnection)
                'Opens the db connection
                gsvConnection.Open()
                'Excutes the delete statement
                myOdbcCommand.ExecuteNonQuery()
                'Closes connection
                myOdbcCommand.Connection.Close()
            End If
        Next
        MessageBox.Show("Done with " & iTable)
        Next

[Zakary]

you get a TimeOut error after 45min! that long ...
How many record your tables have ??
I there some relation between your tables ?

What is your db engine; SQL Server, Oracle, Access ?

In your code you open the conenction on gsvConnection like this
gsvConnection.Open()

Why don't you close it the same way ?
gsvConnection.Close()

[red_bull_n_vodka]

you get a TimeOut error after 45min! that long ...
How many record your tables have ??
I there some relation between your tables ?

What is your db engine; SQL Server, Oracle, Access ?

In your code you open the conenction on gsvConnection like this
gsvConnection.Open()

Why don't you close it the same way ?
gsvConnection.Close()

VB.NET --> Access

I swear I tried that connection like you said, and it wouldnt work. Now it does though. I also moved them outside the loop. Its currently running so hopefully it works

Thanks.

[thecow95]

Have you tried something like this to get the connection out?

Dim myDeleteStatement As String
        Dim myOdbcCommand As New OdbcCommand
 
        'Opens the db connection
        myOdbcCommand.Connection = gsvConnection
        gsvConnection.Open()
 
        For iTable = 1 To 12
        For iCounter = 0 To (ds.Tables(0).Rows.Count - 1)
            If (ds.Tables(0).Rows(iCounter).Item("U_GAS_SERV") <> 0) Then
                iGasServ = ds.Tables(0).Rows(iCounter).Item("U_GAS_SERV")
                myDeleteStatement = "DELETE * FROM GSV00" & iTable & "T WHERE GSV001T.U_GAS_SERV = " & iGasServ
                myOdbcCommand.CommandText = myDeleteStatement                                'Excutes the delete statement
                myOdbcCommand.ExecuteNonQuery()
            End If
        Next
        MessageBox.Show("Done with " & iTable)
        Next
 
        myOdbcCommand.Connection.Close()

This may be totally wrong as I usually work with Sql Server and this is from memory, but hopefully it will help.

EDIT:: Looks like I was late posting, hope you got it

[red_bull_n_vodka]

This is what finally worked. It only took about an hour or to run...

Dim iCounter As Integer = 0
        Dim iTable As Integer = 1
        Dim sQuery As String
 
        DataGrid1.DataSource = ds
 
        gsvConnection.Open()
 
        For iCounter = 0 To (ds.Tables(0).Rows.Count - 1)
            If iCounter = (ds.Tables(0).Rows.Count - 1) Then
                sQuery = sQuery & ds.Tables(0).Rows(iCounter).Item("U_GAS_SERV")
            Else
                sQuery = sQuery & ds.Tables(0).Rows(iCounter).Item("U_GAS_SERV") & ","
            End If
        Next
 
        For iTable = 1 To 12
            If iTable >= 10 Then
                Dim myDeleteStatement As String = "DELETE FROM GSV0" & iTable & "T WHERE GSV0" & iTable & "T.U_GAS_SERV IN(" & sQuery & ")"
                Dim myOdbcCommand As New OdbcCommand(myDeleteStatement, gsvConnection)
                'Excutes the delete statement
                myOdbcCommand.ExecuteNonQuery()
            Else
                Dim myDeleteStatement As String = "DELETE FROM GSV00" & iTable & "T WHERE GSV00" & iTable & "T.U_GAS_SERV IN(" & sQuery & ")"
                Dim myOdbcCommand As New OdbcCommand(myDeleteStatement, gsvConnection)
                'Excutes the delete statement
                myOdbcCommand.ExecuteNonQuery()
            End If
        Next
 
        gsvConnection.Close()