ADO Delete Record from Record Set - Driving me mad!!!! 1

[WillShakespeare]

Hia ll,

I have the following code. I am trying to select a RecordSet from a table based on a Client ID. Then, based on my looping, delete only those records that have the same ID as in the array. The idea is I have a number of funds attached to a client in the table Investments. And someone needs to update this table to reflect a customers need to be withdrawn from an (or many) investment fund.
The following code, if you select, say four funds for deletion, deletes the first in that list, but then an exception is raised on the inner If clause (arrFundsOff(i) = objRS("FundID")), seemingly after one deletion has taken place.
Now, if I remove the delete and instead build a table dynamically of the values to be deleted, the resulting table (commented out in my code) does reflect exactly, the funds that should be deleted,a nd the code runs fine. It is only when I add the Delete statement (and I have tried using Update at various places too), that an exception occurs.
Can anyone help? I have poured over the MSDN site on ADO, and been careful to select the appropriate Cursor and locktype, etc. I have noted talk of UpdateBatch (or BatchUpdate), but I am not sure if this is relevant. It "seems" what I am trying to do should work, and is within the ability of ADO, but my code just won't work!!!
Please help if you can!

strSQL = "Select * FROM Investments WHERE ClientID = " & intUserID
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, 2, 3
'strTableTest = "<table><tr><td><b>i</b></td><td width=""50""></td><td><b>Fund ID</b></td><td width=""50""></td><td><b>arrFundsOff(i) ID</b></td><td width=""50""></td></tr><tr><td colspan = ""6""><hr></td></tr>"
Do While objRS.EOF <> True
For i = 0 to Ubound(arrFundsOff)
If arrFundsOff(i) <> "xxx" Then
If arrFundsOff(i) = objRS("FundID") Then
'strTableTest = strTableTest & "<tr><td>" & i & "</td><td width=""50""></td><td>" & objRS("FundID") & "</td><td width=""50""></td><td>" & arrFundsOff(i) & "</td><td width=""50""></td></tr>"
objRS.Delete
'objRS.Update
'objRS.Close
'Set objRS = Nothing
End If
End If
Next
'objRS.Update
objRS.MoveNext
'objRS.Update
Loop
objRS.Close
'strTableTest = strTableTest & "</table>"
'Response.Write(strTableTest)

Thanks in adavnce,

Will

 

[JJ26]

Sharing with you abt the usual way of my coding

strSQL = "Select * FROM Investments WHERE ClientID = " & intUserID
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, 1, 3
'strTableTest = "<table><tr><td><b>i</b></td><td width=""50""></td><td><b>Fund ID</b></td><td width=""50""></td><td><b>arrFundsOff(i) ID</b></td><td width=""50""></td></tr><tr><td colspan = ""6""><hr></td></tr>"
Do While objRS.EOF <> True
For i = 0 to Ubound(arrFundsOff)
    If arrFundsOff(i) <> "xxx" Then
        If arrFundsOff(i) = objRS("FundID") Then
            'strTableTest = strTableTest & "<tr><td>" & i & "</td><td width=""50""></td><td>" & objRS("FundID") & "</td><td width=""50""></td><td>" & arrFundsOff(i) & "</td><td width=""50""></td></tr>"
[red]
            objConn.Execute "DELETE FROM Investment Where FundID = " & arrFundsOff(i) [i]--> adding more parameters so that you can touch down your specific record [/i][/red]
         End If
    End If
Next
objRS.MoveNext
Loop

objRS.Close
strTableTest = strTableTest & "</table>"
Response.Write(strTableTest)

To delete the record, use SQL syntax for safety.

Hope it helps

*JJ*

 

[WillShakespeare]

JJ,

Thanks! You're a star! I have been struggling with this all day yesterday and this morning! (And deadline was Friday!)
Thing is I am "altering" an existing site (next step is to re-write the site from scratch), because it only needs an added secure section for clients to log onto, and the code on this site is awful! Seeing as I have not used ADO, ASP, and VBScript before (my experience before this job was ASP.NET and C#), I have struggled getting to grips with how it all works. I had finally done this up until this one litlle bit of code!

You see, I originally "Borrowed" a Recordset Delete section from another part of the site, whcih is used to delete only one record (the entire recordset is only one row), and I assumed this was why it wasn't working, so spent time trying to "read up".

Anyway, when I asked my boss (a SQL developer from previous job), he said that their are 2 types of developers.... application developers, and SQL developers. A SQL developer would delete direct form the database (which you did her, and he suggested I do), and the app developer would delete from the recordset and update (a much less efficient use of computer processing).

SO I did start looking at using a Command to do this, but lacked the samples of code to help me figure out how to use it properly>..

And now you have helped! Great! Thanks!

Will

 

[JJ26]

Thanks for the *Star*
Anyway, I was thinking abt one other way. Still don't know suit to your needs or not, and intending to know whether it's a better solution (perhaps you can consult with your SQL Developer).

Here's my idea :
1. Write an SQL stored procedure that will complete deleting all your specific matched-to-array records; If deleting process is succeded, return a recordset which is already "clean".
2. send all of your array values (of course you have to parse the array first into long string).
3. Execute the stored Proc
4. Get the returnable recordset from your executed Stored Proc.

[purple]ASP Page[/purple]

tempArr = ""

For i=0 to Ubound(arrFundsOff)
    If arrFundsOff(i) <> "xxx" Then
        tempArr = tempArr & arrFundsOff(i) & "@"
    End if
Next

Set objRS = Server.CreateObject("ADODB.Recordset")

objRs.open "Exec sp_Del '" & tempArr & "' ", ObjConn
If Not ObjRs.BOF then
   Do while Not ObjRs.EOF
      response.write ObjRs("ClientID") 
      ObjRs.MoveNext
   Loop
End if
ObjRs.close
set ObjRs = nothing

[yellow]SQL Stored Proc[/yellow]

Create Proc sp_Del  @FundsID varchar(1000)
AS
-- you need to unpack your parameter which has been parsed into specific order
-- put it into array
-- do looping thru the array; examine for the matched FundID; Delete the record

Wuiiihh... it's a long work to do.
Wanna know what's the advantage & disadvantage of this method

Hope hearing from U soon.