Help using two insert statement

[ViperD]

When I submit with the following code:

IF request.form ("Message")="True" THEN
strPatSSN=request.form("patSSN") 'patients social security number '10
strPatFN=request.form("patFName") 'patients first name
strPatLN=request.form("patLName") 'patients last name
strPatIns=request.form("patIns") 'patients insurance company
strCall=request.form("callDate") 'Date of call
strMemo=request.form("callNote") 'Call comments
strCallBack=request.form("callBack") 'Date to call back


IF strMemo = "" THEN 'Test if strMemo is null
iLenMB1=255 '20
ELSE 'Else for Test test if strMemo is null
iLenMB1 = Len(strMemo)
END IF 'End Test if strMemo is null


'Connects to the Access driver and Access database in the Inetpub directory where the database is saved
strProvider = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\Inetpub\

http://wwwroot\Tutorial\TinaDB.mdb;"

'Creates an instance of an Active Server component
set objConn = server.createobject("ADODB.Connection") '30

'Opens the connection to the data store
objConn.Open strProvider

'Instantiate Command object and use ActiveConnection property to attach connection to Command object
set cm = Server.CreateObject("ADODB.Command")
cm.ActiveConnection = objConn

'Define SQL query
cm.CommandText ="INSERT INTO Patient (patSSN,patFName,patLName,patIns) VALUES (?,?,?,?)" '40


'Define query parameter configuration information for guestbook fields
set objparam=cm.createparameter(, 200, , 9, strPatSSN)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 25, strPatFN)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 25, strPatLN)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 50, strPatIns) '50
cm.parameters.append objparam
cm.execute

cm.CommandText = "INSERT INTO Call (callDate, callNote, patSSN, callBack) VALUES (?,?,?,?)"
set objparam=cm.createparameter(, 200, , 8, strCall)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , iLenMB1, strMemo)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 9, strPatSSN)
cm.parameters.append objparam '60
set objparam=cm.createparameter(, 200, , 8, strCallBack)
cm.parameters.append objparam
cm.execute
response.write("Patient reminder submitted.")

I get the following error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

If I comment out one of the insert sections it works fine. Any suggestions?

Thanks,
ViperD

 

[MoLaker]

The execute command returns a closed recordset object if it is not a record-returning query. Could this affect the Command object as well? Try recreating the Command object by inserting

'Instantiate Command object and use ActiveConnection property to attach connection to Command object
    set cm = Server.CreateObject("ADODB.Command")
    cm.ActiveConnection = objConn

immediately after the first execute command line.

 

[ViperD]

Thanks it works now.