[gillivt]

I am writing to two tables which need to keep referential integrity.

When the tables are empty the code functions correctly and writes out to the sales table. It then retrieves the Autonumber Primary key so that it can be used to write sales line items to the second table.

The second time the code is executed I get an Invalid bookmark error.

The code follows:

'Create a database connection
set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open ConnectionString

'
'Open Recordset for sales
'
set rsSale = Server.CreateObject("ADODB.Recordset")
set rsLine = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT tblSales.chrMobile, " & _
"tblSales.chrPaymentMethod, " & _
"tblSales.idsInvoiceNumber " & _
"FROM tblSales;"

rsSale.CursorType = adOpenKeyset
rsSale.LockType = adLockOptimistic
rsSale.Open strSQL, adoCon
rsSale.Addnew

'write mobile number (users account number) & paymentmethod
rsSale.Fields("chrMobile") = Session("Mobile")
rsSale.Fields("chrPaymentMethod") = strPaymentMethod
rsSale.Update
Freeze = rsSale.Bookmark ' First, store the location of you cursor
rsSale.Requery ' Next, update your recordset with the data from the database
rsSale.Bookmark = Freeze ' Restore cursor (This is where the error occurs)
strInvoiceNumber = rsSale("idsInvoiceNumber") 'Get the just written autonumber
rsSale.Close

strSQL = "SELECT tblSalesLineItems.chrProductID, " & _
"tblSalesLineItems.intQuantity, " & _
"tblSalesLineItems.lngzInvoiceNumber, " & _
"tblSalesLineItems.lngzLineNumber " & _
"FROM tblSalesLineItems;"

rsLine.CursorType = adOpenDynamic
rsLine.LockType = adLockOptimistic
rsLine.Open strSQL, adoCon


'write the line items
intItems = 0
For Each strProductID in dictCart
intItems = intItems + 1
rsLine.AddNew
rsLine.Fields("chrProductID") = strProductID
rsLine.Fields("intQuantity") = dictCart(strProductID)
rsLine.Fields("lngzInvoiceNumber") = strInvoiceNumber
rsLine.Fields("lngzLineNumber") = intItems
rsLine.Update
Next

'Close recordSet
set rsLine = Nothing
set rsSale = Nothing
set adocon = Nothing


Can Anyone see what;'s going wrong?