Inserting multiple records using array values - truncation error

[Coder76]

Here's the problem. I have a form that, among other things, asks 7 questions. The 7 questions are pulled from the database. When the user enters all of their answers and clicks Submit, I sometimes get a "String data, right truncation" error and can't figure out why. I am only putting less than a sentence or two into each textarea box. The Answer column in the database is defined as a varchar(4096) so it should be plenty big. The stored procedure is also expecting the Answer to come in as a varchar(4096). I just can't understand why sometimes it works and other times it gives a string truncation error. Any help would be much appreciated. Thanks!

Sub setVariables()
		iTicketInfoID = Request.Form("iTicketInfoID")
		iWhichTech = Request.form("WhichTech")
		sRequestor = Request.Form("txtRequestor")	
		sCallSummary = Request.form("txtCallSummary")
		dtCallCompleteDate = Request.Form("CallCompleteMonth") & "/" & Request.Form("CallCompleteDay") & "/" & Request.Form("CallCompleteYear")
		sQuestion1 = Request.form("txtQuestion1")		
		sQuestion2 = Request.form("txtQuestion2")
		sQuestion3 = Request.form("txtQuestion3")
		sQuestion4 = Request.form("txtQuestion4")
		sQuestion5 = Request.form("txtQuestion5")
		sQuestion6 = Request.form("txtQuestion6")
		sQuestion7 = Request.form("txtQuestion7")
			if sQuestion7 = "" then sQuestion7 = NULL
	End Sub

'set variables
			setVariables
		'test variables if necessary for testing
			'testVariables
		'connect to the database
			Connection objConn
		
		'build the command
			BuildCommand cmdAction, objConn, "TrackItCallFeedback_UpdateInfo"
			set rsAction = Server.CreateObject("ADODB.Recordset")
			set rsAction = cmdAction.Execute(, array(iTicketInfoID, iWhichTech, sRequestor, sCallSummary, dtCallCompleteDate, iUserID, sIPAddress))		
		
		'build the command
			BuildCommand cmdAction2, objConn, "TrackItCallFeedback_AddAnswer"
			set rsAction2 = Server.CreateObject("ADODB.Recordset")
				
			dim arrQuestions(6)
			dim i
				i = 0 
			
				arrQuestions(0) = sQuestion1
				arrQuestions(1) = sQuestion2
				arrQuestions(2) = sQuestion3
				arrQuestions(3) = sQuestion4
				arrQuestions(4) = sQuestion5
				arrQuestions(5) = sQuestion6
				arrQuestions(6) = sQuestion7
				
			for i = 0 to 6
				iQuestionID = i + 1
				if arrQuestions(i) <> "" then 
					
					'Response.Write ("arrQuestions(" & i & ") = " & arrQuestions(i) & "<BR>")
					
					set rsAction2= cmdAction2.Execute(, array(iTicketInfoID, iQuestionID, trim(arrQuestions(i)), iUserID, sIPAddress))					
	
				end if 
			next

 

[theniteowl]

Can you reproduce the error at will?
If so, you can narrow down specifically which field the error is occuring in and the text that is entered that causes it.

Is it possible the data submitted contains characters that screw up your SQL string? Like a single quote?
And does your stored procedure return a result somewhere?

Microsoft has this to say:
String or binary data returned for an output parameter resulted in the truncation of nonblank character or non-NULL binary data. If it was a string value, it was right-truncated. (Function returns SQL_SUCCESS_WITH_INFO.)
OR
The assignment of a character or binary value to a column resulted in the truncation of nonblank (character) or non-null (binary) characters or bytes.



Paranoid? ME?? WHO WANTS TO KNOW????

 

[Coder76]

Yes, I can reproduce the error at will. And no, no funky characters were entered. Here is the nonsense test data I have been using as answers to the 7 questions:

testtse
setasdf
tasdf
asdf
asdf
asdf
gdsdsdfx

Here's where it gets really weird. The above data throws the truncation error, but if I remove the letter "x" from the last answer, it goes into the database fine. It appears as though when I make any of the answers above more than 7 characters I get the error.

Also, don't know if this helps or not, but when I look at the data that does get inserted into the database, the Answers each seem to be padded to the right with blank space (enough blank space to match the length of the longest Answer inserted). It's like it's expecting the field to be a certain length.?!?

 

[theniteowl]

Depending on your database server and data types it is common for the database to full utilize the number of characters it was set for. So if you have a text field of 10 characters and only insert 7 it fills in the rest of the field with spaces (nonblank character or non-NULL binary data).

If you are appending to the field instead of overwriting it then you may be appending your text to the end of a string that already completely fills the field which will result in right truncation of the data.

Paranoid? ME?? WHO WANTS TO KNOW????