ADO Recordset Transaction Batchupdate 1

[Ovatvvon]

Hello,

I'm trying to do a batch update. I want to insert the form values into a database table. One the form elements is a multi-select box with states that the rest of the fields apply to, so I take the value of the states and put them into an array, and for each state in the array, I insert a new record into the database table with the rest of the field values set by the form element values.

When there is an error I want to be able to rollback the transaction so that no update occured at all, that way, if 5 states worth of values were updated, but on the 6th there was a problem, I want to cancel everything so no update was performed. I cannot seem to get the code to let me do this. Does anyone know what is wrong with my code? (I'm calling a function to perform the action, and passing in the entire Request.Form as the parameter.)

Function updateClassCode(frm)
	Dim rstCC, intCounter, arrStates, blnTrucking, blnStateSpecific

	arrStates = Split(frm("states_vRequired"), ", ")
	If frm("chkTrucking")="on" Then blnTrucking=True Else blnTrucking=False
	If frm("chkState")="on" Then blnStateSpecific=True Else blnStateSpecific=False

	Set cnnDB = Server.CreateObject("ADODB.Connection")
	cnnDB.Open CNN_DB
	Set rstCC = Server.CreateObject("ADODB.Recordset")
	rstCC.Open "CC_CLASS_CODE", cnnDB, adOpenKeyset, adLockBatchOptimistic, adCmdTableDirect
	cnnDB.BeginTrans
	On Error Resume Next
		For intCounter = 0 to UBound(arrStates)
			rstCC.AddNew

			rstCC("state") = arrStates(intCounter)
			rstCC("class_code") = frm("ClassCode_vRequired")
			rstCC("description") = frm("txtDescription")
			rstCC("trucking") = blnTrucking
			rstCC("state_specific") = blnStateSpecific
			rstCC("status") = frm("cboStatus")
			rstCC("comment") = frm("txtComments")
		Next
		rstCC.UpdateBatch

		If (err.number <> 0) Or (cnnDB.Errors.Count <> 0) Then
			cnnDB.RollbackTrans
			addNewClasscode = True
		Else
			cnnDB.CommitTrans
			addNewClassCode = False
		End If
	rstCC.Close
	Set rstCC = Nothing
	cnnDB.Close
	Set cnnDB = Nothing
End Function

-Ovatvvon :-Q

 

[Ovatvvon]

Also, I posted this by grabbing a new function I was creating. Consequently, the name of the function is different, and at the end of it when I'm assigning the boolean values, they are being assigned to the old function's name. Please disregard this error; that is not how the real function is.

-Ovatvvon :-Q

 

[DotNetGnat]

i did not understand your second post...so were you able to solve the problem??

-DNG

 

[Ovatvvon]

No, I wasn't yet able to.
Sorry: to be more clear, I basically had the wrong function name for the code there that I pasted on this page. So here is the actual code being used (almost identical, except the function name is different):
_________________________

Function addNewClassCode(frm)
	Dim rstCC, intCounter, arrStates, blnTrucking, blnStateSpecific

	arrStates = Split(frm("states_vRequired"), ", ")
	If frm("chkTrucking")="on" Then blnTrucking=True Else blnTrucking=False
	If frm("chkState")="on" Then blnStateSpecific=True Else blnStateSpecific=False

	Set cnnDB = Server.CreateObject("ADODB.Connection")
	cnnDB.Open CNN_DB
	Set rstCC = Server.CreateObject("ADODB.Recordset")
	rstCC.Open "SELECT * FROM CC_CLASS_CODE WHERE (class_code=" & frm("ClassCode_vRequired") & ")", cnnDB, adOpenForwardOnly, adLockReadOnly, adCmdText
	If Not rstCC.EOF Then
		'Record already in database.  Canceling process.
		addNewClassCode = True
	Else
		rstCC.Close
		rstCC.Open "CC_CLASS_CODE", cnnDB, adOpenKeyset, adLockBatchOptimistic, adCmdTableDirect
		cnnDB.BeginTrans
		On Error Resume Next

		For intCounter = 0 to UBound(arrStates)
			rstCC.AddNew

			rstCC("state") = arrStates(intCounter)
			rstCC("class_code") = frm("ClassCode_vRequired")
			rstCC("description") = Trim(frm("txtDescription"))
			rstCC("trucking") = blnTrucking
			rstCC("state_specific") = blnStateSpecific
			rstCC("status") = frm("cboStatus")
			rstCC("comment") = Trim(frm("txtComments"))
		Next
		rstCC.UpdateBatch

		If (err.number <> 0) Or (cnnDB.Errors.Count <> 0) Then
			cnnDB.RollbackTrans
			addNewClasscode = True
		Else
			cnnDB.CommitTrans
			addNewClassCode = False
		End If
	End If
	rstCC.Close
	Set rstCC = Nothing
	cnnDB.Close
	Set cnnDB = Nothing
End Function

-Ovatvvon :-Q

 

[DotNetGnat]

so do you see any error??

also in the code reset the error handler:

Else
cnnDB.CommitTrans
addNewClassCode = False
End If
[red]on Error goto 0[/red]



-DNG

 

[Ovatvvon]

No, I don't get any errors. But, to test the rollback, I set it so that if there are no errors, then rollback, and it still did not work. So I'm thinking that perhaps committrans and updatebatch end up doing the same thing? Only I'm not sure.

-Ovatvvon :-Q

 

[Ovatvvon]

Oh, you know what, it is working now. I didn't do anything different (that I know about), but all of the sudden it is working now. Huh! Weird.

Thank you for being about to help though! =)

-Ovatvvon :-Q

 

[DotNetGnat]

did you add On Error Goto 0.

Is always a good idea to add this when you use On Error Resume next...

ANyways...glad its working for you...

-DNG

 

[Ovatvvon]

What does On Error Goto 0 do? And why is it important?

-Ovatvvon :-Q

 

[DotNetGnat]

read this:

http://powerasp.com/content/new/on-error-resume-next.asp

-DNG

 

[Ovatvvon]

ok, I learned something new. I guess that's worth a star.

-Ovatvvon :-Q

 

[DotNetGnat]

Thanks. I too learn so much here daily from many experts...

Glad to be of help.

-DNG