Question Executenonquery in a loop

[opie]

Ok. I know I'm missing something easy here but for the life of me I haven't found an answer to this. I make a ODBC connection to my database and I have 13 tables that I need to delete data from based on a column (called JOB_ID) in the table. When I run the code on a single table it works fine, when I try to loop through it's stopping at the executenonreader()

Here's my code

Private Sub wod_purge()
        Dim time1 As DateTime
        Dim time2 As DateTime
        Dim span As TimeSpan

        time1 = Date.Now


        Dim wod_tables As New ArrayList()
        wod_tables.Add("WOD_TABLE1")
        wod_tables.Add("WOD_TABLE2")
        wod_tables.Add("WOD_TABLE3")
        wod_tables.Add("WOD_TABLE4")
        wod_tables.Add("WOD_TABLE5")
        wod_tables.Add("WOD_TABLE6")
        wod_tables.Add("WOD_TABLE7")
        wod_tables.Add("WOD_TABLE8")
        wod_tables.Add("WOD_TABLE9")
        wod_tables.Add("WOD_TABLE10")
        wod_tables.Add("WOD_TABLE11")
        wod_tables.Add("WOD_TABLE12")
        wod_tables.Add("WOD_TABLE13")
        wod_tables.Add("WOD_TABLE14")
        wod_tables.Add("WOD_TABLE15")
        wod_tables.Add("WOD_TABLE16")


        Dim ConStr As String = "Driver={Microsoft ODBC for Oracle};Server="".WORLD;UID=uid;pwd=pwd"

        Dim conn As New Odbc.OdbcConnection(ConStr)
        conn.Open()
        Dim cmd As String = ""       
        Dim sqlcmd As New Odbc.OdbcCommand(cmd, conn)
      

        Dim i As String
        Dim k As Integer = 0
        Dim rowsdeleted As Integer

        For Each i In wod_tables

            cmd = "DELETE FROM WOD_WIRE_DESCRIPTION WHERE JOB_ID in &_
(select a.JOB_ID from WOD_JOB a, JOB_EVENT b where b.EVENT_TYPE_CD = &_
'CRE' and b.EVENT_DT between '" & fromdate & "' and '" & todate & "' and a.JOB_ID = b.JOB_ID)"
            

            sqlcmd.CommandText = cmd
            rowsdeleted = sqlcmd.ExecuteNonQuery()                 
            
            ListBox1.Items.Add(i & "=" & rowsdeleted)
        Next
        

        time2 = Date.Now
        span = time2.Subtract(time1)
        Dim n As String = span.TotalSeconds.ToString("0.0")

        TextBox1.Text = k
        TextBox3.Text = n
       
        conn.Close()

    End Sub

 

[opie]

Ok.. took a while but I figured it out

I had to

Dim sqlcmd as new ODBC.ODBCCommcand

then in the For Each loop

For Each i In wod_tables
cmd = " stuff "
sqlcmd = New Odbc.OdbcCommand(cmd, conn)
...
...
Next