ODBC Error!!

micjohnson · May 3, 2001

Hi there!

When i am updating or inserting a table, i am getting this error always.

ODBC Error Code = 22001 (String data right truncation)

[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (101:3) to (101:58).

Can anybody tell me plse why its coming?

Thanx
micjohnson

eagle1wi · May 3, 2001

Please post your code. I would assume that you are trying to put too much data in a particular field. This allaire document might help as well

http://www.allaire.com/Handlers/index.cfm?ID=14205&Method=Full

micjohnson · May 3, 2001

Hi eagle1wi

Thanx for your reply! Here is my code.Its is a big one. Please have a patience to look at this. I think the problem is in the Loop.

<CFIF Group IS "Staff">
<CFSET RTable = "StaffRegTable">
<CFSET Code = "Spec_Code">
<CFELSE>
<CFSET RTable = "RegTable">
<CFSET Code = "Spec_code">
</cfif>
<CFIF Departure IS "Y">
<CFSET Time_in_first_half=#Cookie.First#>
<CFSET Time_out_second_half=#Cookie.Second#>
</CFIF>

<HTML><HEAD>
<TITLE><CFOUTPUT>#Group#</CFOUTPUT> Attendence Submitted</TITLE>

<CFINCLUDE TEMPLATE="shortheaders-light.cfm">

</HEAD>
<BODY>

<div align="center"><img src="resources/dcflag.gif" alt="" border="0"></div>

<CFIF Group IS NOT "Staff">
<CFSET Time = "Time_in_first_half">
<CFELSE>
<CFSET Time = "Hours">
</CFIF>

<CFSET AttendanceArray=ArrayNew(2)>
<CFSET ListsArray=ArrayNew(1)>
<CFSET ListsArray[1]="Spec_Code">
<CFSet ListsArray[2]="Time_in_first_half">
<CFSET ListsLength=ArrayLen(ListsArray)>
<CFIF Departure IS "N">
<CFSET List="Spec_code, #Time#">
<CFELSE>
<CFSET List="Spec_code, Time_in_first_half, Time_out_second_half">
</CFIF>

<CFSET incount=1>
<CFLOOP INDEX="innercounter" LIST="#List#">
<CFSET loopcount=1>
<CFLOOP INDEX="outercounter" LIST="#Evaluate("#innercounter#&quot

#">
<CFSET AttendanceArray[#incount#][#loopcount#] = "#outercounter#">
<CFIF innercounter IS "Spec_code">
<CFSET PresentVar = "Present#Evaluate("#outercounter#&quot

#">
<CFSET PresVar = "Present#Evaluate("#outercounter#&quot

#">
<CFIF NOT Evaluate("ParameterExists(#PresVar#)&quot

>
<CFSET AttendanceArray[#incount#+2][#loopcount#] = "N">
<CFELSE>
<CFSET AttendanceArray[#incount#+2][#loopcount#] = Evaluate("Present#outercounter#&quot

>
</CFIF>
</CFIF>
<CFSET loopcount = #loopcount#+1>
</CFLOOP>
<CFSET incount = #incount#+1>
</CFLOOP>

<CFSET temp=ArrayLen(AttendanceArray)>

<CFSET temp2=ArrayLen(AttendanceArray[temp])>

<CFSET insertCount = 0>
<CFSET Time_out="12:00 AM">
<CFSET Time_in_2nd="12:00 AM">
<CFSET Time_out_2nd="12:00 AM">
<CFOUTPUT>
<CFSET String_Date=#DateFormat(Now(), "mm/dd/yy&quot

#>
</CFOUTPUT>
<TABLE width="70%" ALIGN="CENTER">

<CFOUTPUT>
<CFSET loopcount=1>
<CFLOOP FROM="1" TO="#ArrayLen(AttendanceArray[temp])#" INDEX="OuterCounter">
<TR>
<CFLOOP INDEX="InnerCounter" FROM="1" TO="#temp#">
</CFLOOP>
</TR>
<CFIF AttendanceArray[3][outercounter] IS "Y">

<CFQUERY NAME="Attendcheck" DATASOURCE="DC_Schools">
SELECT *
FROM dbo.Daily_Rec, dbo.#RTable#
WHERE dbo.Daily_Rec.String_Date = '#DateFormat(Now(),"mm/dd/yy&quot

#' AND dbo.Daily_Rec.Spec_code = dbo.#RTable#.#Code#
</CFQUERY>

<CFLOOP QUERY="Attendcheck">
<CFIF AttendanceArray[1][outercounter] IS #Attendcheck.Spec_code#>
<TR><TD ALIGN="CENTER"><BR>Attendance for #Attendcheck.First_Name# #Attendcheck.Last_Name# has already been recorded. Please click the Back or Previous button on your browser and try again.</TD></TR>
<CFABORT>
</CFIF>
</CFLOOP>

<CFQUERY NAME="DailyAttendance" DATASOURCE="DC_Schools">
INSERT INTO dbo.Daily_Rec (<CFIF Group IS NOT "Staff">Time_in_first_half<CFELSE>Hours, Time_in_first_half</CFIF>, Spec_code, Time_Out_First_half, Time_out_second_half, Time_in_second_half, String_Date)
VALUES ('#AttendanceArray[2][outercounter]#', <CFIF Group IS "Staff">'12:00 AM', </CFIF>'#AttendanceArray[1][outercounter]#', '#Time_out#', '#Time_out_2nd#', '#Time_in_2nd#', '#String_Date#')
</CFQUERY>



<CFSET insertCount=#insertCount#+1>
<CFELSE>
<CFIF Departure IS "Y">
<CFQUERY name="DailyDeparture" datasource="DC_Schools">
UPDATE Daily_Rec
SET  Time_out_second_half='#AttendanceArray[3][outercounter]#'
WHERE Spec_Code='#AttendanceArray[1][outercounter]#' AND String_Date='#DateFormat(Now(), "mm/dd/yy&quot

#'
</cfquery>

<CFSET insertCount=#insertCount#+1>
</CFIF>
</CFIF>

</CFLOOP>
</CFOUTPUT>

</TABLE>

<CFSET AttendanceList=ArrayNew(2)>
<CFSET loopcount=1>

<cfloop index="Spec_codeList" list="#Spec_code#">
<CFSET AttendanceList[loopcount][loopcount]=#Spec_codeList#>
<cfloop INDEX="TimeIn" LIST="#Time#">
<CFSET AttendanceList[loopcount][loopcount+1]=#TimeIn#>
</CFLOOP>
<CFOUTPUT>
<CFSET PresentVar = "Present#Spec_codeList#">
<CFSET PresVar = "Present#Spec_codeList#">
<CFIF NOT Evaluate("ParameterExists(#PresVar#)&quot

>
<CFSET Present = "N">
<CFELSE>
<CFSET Present = Evaluate("Present#Spec_codeList#&quot

>
</CFIF>
</cfoutput>

<CFSET loopcount=#loopcount#+1>
</cfloop>

<BR>
<div align="center"><FONT size="+1"><B>Attendance was successfully recorded for <CFOUTPUT>#insertCount# #Group#</CFOUTPUT><CFIF insertcount GT 1>s</cfif> </B></FONT></div><br><br><br>

<BR>
<div align="center"><a href="DCPSAttendMenu.cfm"><font size="+1" color="Red">Return to Attendance Menu</font></a></div>
</BODY></HTML>

Thanx
micjohnson

eagle1wi · May 3, 2001

Nothing jumps out at me. Maybe it will for someone else. If you could post the structure of your database(s) field widths,types etc that may be helpful. I still bet you are overfilling a field. What database are you using? I would try checking your date fields. They can be very picky about formatting. What line is 101?

micjohnson · May 3, 2001

Hi eagle1wi

Thanx for your reply!. I too feel that it may overfilling a field.

Here is the table structure.

Table Name: daily_rec

Field Datatype Length

Spec_Code char 10 Time_In_First_half char 8
Time_Out_First_half char 8
Time_In_Second_half char 8
Time_Out_Second_half char 8
Attendence_Date char 8
Attendence_Code char 7
String_Date char 8
Present char 1
Snack char 1
Hours char 2
Shift char 2
School_Name char 32
AM_Teacher char 10
PM_Teacher char 10

Thanx
micjohnson

ram123 · May 3, 2001

I think your are inserting/update a field with the size larger than specified in table.
For example if you are inserting more than 2 characters for "Shipt" field you will get this error. you may have to use left function to truncate the field value while inserting.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ODBC Error!!

micjohnson

Programmer

eagle1wi

MIS

micjohnson

Programmer

eagle1wi

MIS

micjohnson

Programmer

ram123

Programmer

Similar threads

Part and Inventory Search

Sponsor