[RESOLVED] Multiple Record set in one execute

Looking for information on how to place two select statement within a loop

The first is to look up the balence for each customer then the second is to insert that balance into another row in a another table...

I have this though the second Select Statement is still connected so I can not rs.movenext from the first select statement...

Let me know what you think, Thank you


Code Snippet

##################################################

Private Sub RUNBATCH()

conn.Open sConnString
Set cmd.ActiveConnection = conn

'SELECT STATEMENT

cmd.CommandText = "SELECT BatchID, CustID, Balance " & _
" From CUSTBAL" 'WHERE CustID = '" & Val(txtCustID.Text) & "'"



cmd.CommandType = adCmdText
Set rs = cmd.Execute


Do Until rs.EOF

[INDENT]strIBatchID = rs!BatchID
[INDENT]strICustID = rs!CustID

strIBal = rs!Balance

'INSERT VALUES FROM FIRST SELCT STATEMENT - CURRENT RECORD
cmd.CommandText = "INSERT INTO TBLINVOICE (BID, CID, TOTBILL)" & _
" VALUES (" & Val(strIBatchID) & " , " & Val(strICustID) & " , " & Val(strIBal) & ")"


'EXECUTES SECOND INSERT STATEMENT
cmd.CommandType = adCmdText
Set rs = cmd.Execute



'MOVE TO NEXT RECORD - NEEDS TO BE FROM FIRST STATEMENT
rs.MoveNext


'LOOPS
Loop


'CLEARS
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

:afrog:

I'm not sure why this thread is buried in the COM and ActiveX section of the forum - we do have a very active DATABASE DEVELOPMENT forum...

I would execute an action query first - such as:

Code:

---

INSERT INTO TBLINVOICE (BID, CID, TOTBILL) SELECT BatchID, CustID, Balance " & _
" From CUSTBAL" 'WHERE CustID = '" & Val(txtCustID.Text) & "'"

---

That way the database work was already done - the TBLINVOICE was populated with the rows desired.

No iterative/looping action against the database.

Why do you need to process the recordset in VB?

The program is being done in VB6.. Thanks :)

Quote:

---

Originally Posted by Zoila

The program is being done in VB6.. Thanks :)

---

Yes, but you can write your database code in SQL Server and call it from VB.

. . . and this code really needs to be in transaction, too. . . .

Definately repost to the boys in the DB development bit. Those guys live for this sort of stuff . . . . ;)