OleDbDataReader and delete

I have a reader looping through a table using a while statement.
If certain conditions are met, I want to delete the record.
The problem with the code below is in the If statement.
I have tried a few variations, and can't seem to get the right one working.
Either the connection is being used or it's open and should be closed to reopen, and so forth.

Sub delComms()
 Dim dbRead2 As OleDbDataReader
 Dim dbComm2 As OleDbCommand
 Dim strEmail As String
 Dim strTime As DateTime
 Dim intDayDiff As Integer
 Dim i As Integer
 sql = "Select * from tblComms"
 conn2.Open()
 dbComm2 = New OleDbCommand(sql, conn2)
 dbRead2 = dbComm2.ExecuteReader()
 While dbRead2.Read()
 strEmail = dbRead2.Item("fldUserEmail")
 strTime = dbRead2.Item("fldDate")
 i = dbRead2.Item("fldNum")
 intDayDiff = DateDiff(DateInterval.Day, strTime, Now)
 If intDayDiff > 14 Then
 sql = "DELETE FROM tblComms WHERE fldNum =" & i
Dim cCommand As New OleDbCommand(sql, conn2)
 cCommand.ExecuteNonQuery()
 End If
 End While
 conn2.Close()
 End Sub

Any suggestion on how to get this to work properly?
I'm stuck again....

Thanks all,

ZathYou have to close the reader just like its telling you.

I would probably put all the IDs to be deleted into an arraylist and then use that list as your delete criteria after you close your readers connection. Use the sql IN clause to do this. That way you're only calling the database once instead of up to 500 times in a row.

I believe you'll have to pass your list as a comma seperated list though, so something like this would do it.

 Dim myAL As ArrayList = New ArrayList()
 Dim myAL1 As ArrayList = New ArrayList()
 Dim NumofIDs As Integer = 0
 Dim x As Integer
 myAL.Add("bob") 'This line would actually go where you have your if intDayDiff > 14 end If
 NumofIDs = myAL.Count
 For x = 0 To NumofIDs - 1
 If NumofIDs > 1 Then
 If x <> NumofIDs - 1 Then
 myAL1.Add(myAL(x) & ",")
 Else ' this is the last record, don't add a comma
 myAL1.Add(myAL(x))
 End If
 myAL1.Add(myAL(x) & ",")
 Else
 myAL1.Add(myAL(0)) ' there was only 1 record, don't add a comma
 End If
 Next x

sql = "DELETE FROM tblComms WHERE fldNum IN " & myAL1

Thanks for the reply but I'm not following how to integrate that into the dbread...

Can you be a little more specific?

Thanks,

Zath
The DIMs would go up at the top

 If intDayDiff > 14 Then
 myAL.Add(i)
 'sql = "DELETE FROM tblComms WHERE fldNum =" & i
 'Dim cCommand As New OleDbCommand(sql, conn2)
 'cCommand.ExecuteNonQuery()
End If

The rest goes anywhere after

 End While
 conn2.Close()

With the appropriate connection openings, etc..
Thanks again for the reply...

So I hope this is something like it should look...

 Dim dbRead2 As OleDbDataReader
 Dim dbComm2 As OleDbCommand
 Dim strEmail As String
 Dim strTime As DateTime
 Dim intDayDiff As Integer
 Dim i As Integer
 sql = "Select * from tblComms"
 conn2.Open()
 dbComm2 = New OleDbCommand(sql, conn2)
 dbRead2 = dbComm2.ExecuteReader()
 While dbRead2.Read()
 Dim myAL As ArrayList = New ArrayList()
 Dim myAL1 As ArrayList = New ArrayList()
 Dim NumofIDs As Integer = 0
 Dim x As Integer
 NumofIDs = myAL.Count
 For x = 0 To NumofIDs - 1
 If NumofIDs > 1 Then
 If x <> NumofIDs - 1 Then
 myAL1.Add(myAL(x) & ",")
 Else ' this is the last record, don't add a comma
 myAL1.Add(myAL(x))
 End If
 myAL1.Add(myAL(x) & ",")
 Else
 myAL1.Add(myAL(0)) ' there was only 1 record, don't add a comma
 End If
 If intDayDiff > 14 Then
 myAL.Add(i)
 sql = "DELETE FROM tblComms WHERE fldNum =" & i
 Dim cCommand As New OleDbCommand(sql, conn2)
 cCommand.ExecuteNonQuery()
 End If
 Next x
 End While
 conn2.Close()

I'm not getting any errors, but I think this is due to NumofIDs is still 0, so it's not getting called.

Just a little more help please? Been over a year since I dealt with arrarys and that was with Java Applets...

Thanks,

Zath
Well

while dbread2.read()
...
end while

is a looping structure. It will read 1 record and then go to the top of the code again. So no matter how many times you go through this you'll be executing this line every time a new record is read

Dim NumofIDs As Integer = 0

Most of those DIMs shouldn't be in the reading loop (I think I said that in my reply) because you'll be executing those lines on every read.
Ok, a rookie mistake. Should have seen that...

Here is the adjusted code... almost there...

Sub delComms()
 Dim dbRead2 As OleDbDataReader
 Dim dbComm2 As OleDbCommand
 Dim strEmail As String
 Dim strTime As DateTime
 Dim intDayDiff As Integer
 Dim i As Integer
 Dim myAL As ArrayList = New ArrayList()
 Dim myAL1 As ArrayList = New ArrayList()
 Dim NumofIDs As Integer = 0
 Dim x As Integer
 sql = "Select * from tblComms"
 conn2.Open()
 dbComm2 = New OleDbCommand(sql, conn2)
 dbRead2 = dbComm2.ExecuteReader()
 While dbRead2.Read()
 strEmail = dbRead2.Item("fldUserEmail")
 strTime = dbRead2.Item("fldDate")
 i = dbRead2.Item("fldNum")
 intDayDiff = DateDiff(DateInterval.Day, strTime, Now)
 NumofIDs = myAL.Count
 For x = 0 To NumofIDs - 1
 If NumofIDs > 1 Then
 If x <> NumofIDs - 1 Then
 myAL1.Add(myAL(x) & ",")
 Else ' this is the last record, don't add a comma
 myAL1.Add(myAL(x))
 End If
 myAL1.Add(myAL(x) & ",")
 Else
 myAL1.Add(myAL(0)) ' there was only 1 record, don't add a comma
 End If
 If intDayDiff > 14 And strEmail <> "admin@.yahoo.com" Then
 myAL.Add(i)
 sql = "DELETE FROM tblComms WHERE fldNum =" & i
 Dim cCommand As New OleDbCommand(sql, conn2)
 cCommand.ExecuteNonQuery()
 End If
 Next x
 End While
 conn2.Close()
 End Sub

It is not deleting any records at this point.

What am I not seeing this time? Does it have to do with the NumofIDs = myAL.Count and where it is?

Thanks again,

Zath
Eh, you've still got your logic really screwed up.
FYI - this is only going to put IDs that have an e-mail address of admin@.yahoo.com into the array.

Try this:

Sub delComms()
 Dim dbRead2 As OleDbDataReader
 Dim dbComm2 As OleDbCommand
 Dim strEmail As String
 Dim strTime As DateTime
 Dim intDayDiff As Integer
 Dim i As Integer
 Dim myAL As ArrayList = New ArrayList()
 Dim myAL1 As ArrayList = New ArrayList()
 Dim NumofIDs As Integer = 0
 Dim x As Integer
 sql = "Select * from tblComms"
 conn2.Open()
 dbComm2 = New OleDbCommand(sql, conn2)
 dbRead2 = dbComm2.ExecuteReader()
 While dbRead2.Read()
 ' The read simply puts the fldNum into the array. '
 strEmail = dbRead2.Item("fldUserEmail")
 strTime = dbRead2.Item("fldDate")
 i = dbRead2.Item("fldNum")
 intDayDiff = DateDiff(DateInterval.Day, strTime, Now)
 NumofIDs = myAL.Count
 If intDayDiff > 14 And strEmail <> "admin@.yahoo.com" Then
 myAL.Add(i)
 End If
 End While
 'Reading is done and array is populated. Determine what kind of commas we need for our SQL IN clause '
 For x = 0 To NumofIDs - 1
 If NumofIDs > 1 Then
 If x <> NumofIDs - 1 Then
 myAL1.Add(myAL(x) & ",")
 Else ' this is the last record, don't add a comma
 myAL1.Add(myAL(x))
 End If
 myAL1.Add(myAL(x) & ",")
 Else
 myAL1.Add(myAL(0)) ' there was only 1 record, don't add a comma
 End If
 Next x
 conn2.Close()
'Now we have an arraylist of IDs to build up. Execute the query to delete them
 sql = "DELETE FROM tblComms WHERE fldNum IN" & myAL1
 Dim cCommand As New OleDbCommand(sql, conn2)
 response.write("SQL = " & sql) 'Print out the SQL comman to visually CONFIRM it looks ok before we uncomment the next line.
 'cCommand.ExecuteNonQuery()
 End Sub

I really do appreciate the help with this. Once complete, I can adapt it to other tables that it needs to be done...

One last thing then it does look like it will work...

The line

sql = "DELETE FROM tblComms WHERE fldNum IN" & myAL1

is getting an error. Operator '&' is not defined for types 'String' and 'System.Collections.ArrayList'.

Should there be an additional import done like imports system.array or Imports System.Collections.ArrayList
I tried that both but the error still exists...

Thanks again,

Zath
One more line change..move it down

 If intDayDiff > 14 And strEmail <> "admin@.yahoo.com" Then
 myAL.Add(i)
 End If
 NumofIDs = myAL.Count

And try myAL1.ToString()
Just when I thought I had sql statements down packed. I even have some 10 lines long and this one is giving me trouble...

'sql = "DELETE FROM tblComms WHERE fldNum IN " & myAL1.ToString()
'In operator without () in query expression 'fldNum IN System.Collections.ArrayList'.

I've tried this as well

sql = "DELETE FROM tblComms WHERE fldNum IN ('" & myAL1.ToString() & "')"

But get a datatype mismatch. Now fldNum is an AutoNumber in the db so that may be it.

There needs to be () somewhere in the statement and I have tried a few places and looked up some examples but just can't seem to get it right.

Thanks for you time,

Zath
Is fldNum a text datatype or numeric datatype? If it is text make sure you have single quotes around each item in myAL1 (myAL1='a','b','c'). It should then look like

sql = "DELETE FROM tblComms WHERE fldNum IN (" & myAL1.ToString() & ")"
It's numeric.
That's why I put that response.write() in there and commented out the command to execute the query.

That's the fastest way to debug a SQL statement. What is it outputting?
Thanks again...

For the sql -
sql = "DELETE FROM tblComms WHERE fldNum IN ('" & myAL1.ToString() & "')"

The .write() ouput is -
SQL = DELETE FROM tblComms WHERE fldNum IN ('System.Collections.ArrayList')

For the sql -
sql = "DELETE FROM tblComms WHERE fldNum IN (" & myAL1.ToString() & ")"

The .write() ouput is -

SQL = DELETE FROM tblComms WHERE fldNum IN (System.Collections.ArrayList)

And you are right, debugging an sql with a write is a good idea. Wish I thought of it when I was debugging that 10 line one...

Thanks again,

Zath