Error inserting a new record to mySQL 1

[SWTpaul]

I receive the following error... doing a little Google searching it supposedly means the data being inserted is longer than the set value of the table field. I set the field to varchar(255) and it still gives an error no matter what. I added Trim() around the variable for giggles, and that did nothing. I commented out line 52, and got the same error message with lines 53 and 54... all the others work. Anyone have any ideas? I'm baffled... especially since line 51 (works), 52 (doesnt) and 53 (doesnt) are the exact same input and field types.

[ERROR MESSAGE]-------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/add1.asp, line 52

----------------------------------
Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open "cgmst", MySQLConn, 2, 2
Rs.AddNew 
	Rs("cgitno") = sItemNo
	Rs("cgitds") = sItemDesc
	Rs("cgcopy") = sItemCopy
	Rs("cgoth1") = sItemOth1
	Rs("cgoth2") = sItemOth2  <--- Line 52
	Rs("cgoth3") = sItemOth3
	Rs("cgbtry") = sItemBtry
	Rs("cgages") = sItemAges
	Rs("cgdmpr") = sItemPrDm
	Rs("cgdmbx") = sItemBxDm
	Rs("cgmnor") = sItemMnOr
Rs.Update
Rs.Close

 

[DotNetGnat]

does the input contain an apostrophe? If so use Replace() function...

check for any other weird characters in the input.

-DNG

 

[vongrunt]

This happens when ADO updates encounter more than one error in a row. Global Err object then reports "hey, ya have many errors" - and to get detailed info you must query Conn.Errors collection. Try something like:

Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open "cgmst", MySQLConn, 2, 2
On Error Resume Next
RS.AddNew
'code that may fail

If Err.Number > 0 Then
	For Each oErr in MySQLConn.Errors
		Response.Write oErr.description & "<br>"
	Next
	MySQLConn.Errors.Clear
	'Response.End
End If

On Error Goto 0
RS.Close

Most of the time errors are trivial (out of range value, fractional data loss etc) and first error triggers all others.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SWTpaul]

I manually set the variable in the code to a simple string like "this is some text" to test it out... and it still gave an error.

\

 

[tsdragon]

Are you SURE that line 51 is working? From the way you describe the problem (52 errors, if commented 53 errors, etc.) it sounds as if line 51 is the one REALLY causing the problem. And since it is the first of three identical fields, that would make me suspicious of that field too. Try doing what vongrunt suggested and printing ALL the errors in the errors collection.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[bslintx]

i assumed all vars were text...if not get rid of the ' ' ....use sql instead of opening rs and use the sql debug statement to see if values are getting to sql statement

strSQL="INSERT INTO cgmst (cgitno, cgitds, cgcopy, cgoth1, cgoth2, cgoth3,  cgbtry, cgages, cgdmpr, cgdmbx, cgmnor) VALUES ('sItemNo', 'sItemDesc', 'sItemCopy', 'sItemOth1', 'sItemOth2', 'sItemOth3', 'sItemBtry', 'sItemAges', 'sItemPrDm', 'sItemBxDm', 'sItemMnOr')

' Response.Write strSQL 'remove comment to debug
' Response.End 'remove comment to debug


MySQLConn.Execute strSQL

Rs.Close
%>

 

[SWTpaul]

Looks like I will have to resort to SQL statements... those work just fine.

Yes, Line 51 was working, a record was being inserted everytime... \

Thanks for the tips.