OT: SQL Server database problem
** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** Hi, I have a SQL Server 7 database and I am trying to insert a large amount of text, passed by a ColdFusion form, into a table within this database. The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. This is the complete error message: ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = INSERT INTO Messages ( MessageType, Department, CreationDate, PublicationDate, MessageTitle, MessageTeaser, MessageBody, Status ) VALUES ( 'None', 'Housing', '28/10/2002', 'Awaiting', 'Some text to throw error', 'Some text to throw error, Some text to throw error', 'Some text to throw error,Some text to throw error and Some text to throw error', 'Awating Approval' ) Data Source = WIRE_USERS The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (24:1) to (24:57) in the template file E:\INETPUB\WWWROOT\WIRE\MESSAGES\SENDFORAPPROVAL.CFM. Anyone know how I can fix this error? Thanks Stephen ** Westminster City Council switchboard: +44 20 7641 6000 ** This E-Mail may contain information which is privileged, confidential and protected from disclosure. If you are not the intended recipient of this E-mail or any part of it, please telephone Westminster City Council immediately on receipt. You should not disclose the contents to any other person or take copies. ** ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: OT: SQL Server database problem
Quoting Adams, Stephen [EMAIL PROTECTED]: The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. 'Some text to throw error' is indeed longer as 16 characters. Not entirely unexpected behaviour, isn't it? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: OT: SQL Server database problem
You should either trim the text being inserted using the left function ... EXAMPLE: Left(form.MessageTeaser, 16 ) or you should expand the size of the field in the table. If this is data input via a form you could use the size option in the input tag to enforce the size restriction but I would still trim the data with the left function for safety sake. EXAMPLE: input type=Text name=MessageTeaser size=16 -John- At 02:58 PM 10/28/2002 +, you wrote: ** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** Hi, I have a SQL Server 7 database and I am trying to insert a large amount of text, passed by a ColdFusion form, into a table within this database. The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. This is the complete error message: ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = INSERT INTO Messages ( MessageType, Department, CreationDate, PublicationDate, MessageTitle, MessageTeaser, MessageBody, Status ) VALUES ( 'None', 'Housing', '28/10/2002', 'Awaiting', 'Some text to throw error', 'Some text to throw error, Some text to throw error', 'Some text to throw error,Some text to throw error and Some text to throw error', 'Awating Approval' ) Data Source = WIRE_USERS The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (24:1) to (24:57) in the template file E:\INETPUB\WWWROOT\WIRE\MESSAGES\SENDFORAPPROVAL.CFM. Anyone know how I can fix this error? Thanks Stephen ** Westminster City Council switchboard: +44 20 7641 6000 ** This E-Mail may contain information which is privileged, confidential and protected from disclosure. If you are not the intended recipient of this E-mail or any part of it, please telephone Westminster City Council immediately on receipt. You should not disclose the contents to any other person or take copies. ** ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: OT: SQL Server database problem
** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** O.K I have increased the length of these three fields to 100, 150 and 500 respectively, but still I get the 22001 error. -Original Message- From: Jochem van Dieten [mailto:jochemd;oli.tudelft.nl] Sent: 28 October 2002 15:05 To: CF-Talk Subject: Re: OT: SQL Server database problem Quoting Adams, Stephen [EMAIL PROTECTED]: The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. 'Some text to throw error' is indeed longer as 16 characters. Not entirely unexpected behaviour, isn't it? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: OT: SQL Server database problem
I think the error message is straight forward, that is you are trying to insert string that's longer than your datatype can handle. For example: Field: MessageTitle (16): You are trying to insert: Some text to throw error which has 24 characters. Try to increase the length of the field in your DB. HTH, Rizal At 09:58 PM 10/28/2002, you wrote: ** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** Hi, I have a SQL Server 7 database and I am trying to insert a large amount of text, passed by a ColdFusion form, into a table within this database. The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. This is the complete error message: ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = INSERT INTO Messages ( MessageType, Department, CreationDate, PublicationDate, MessageTitle, MessageTeaser, MessageBody, Status ) VALUES ( 'None', 'Housing', '28/10/2002', 'Awaiting', 'Some text to throw error', 'Some text to throw error, Some text to throw error', 'Some text to throw error,Some text to throw error and Some text to throw error', 'Awating Approval' ) Data Source = WIRE_USERS The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (24:1) to (24:57) in the template file E:\INETPUB\WWWROOT\WIRE\MESSAGES\SENDFORAPPROVAL.CFM. Anyone know how I can fix this error? Thanks Stephen ** Westminster City Council switchboard: +44 20 7641 6000 ** This E-Mail may contain information which is privileged, confidential and protected from disclosure. If you are not the intended recipient of this E-mail or any part of it, please telephone Westminster City Council immediately on receipt. You should not disclose the contents to any other person or take copies. ** ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT: SQL Server database problem
You don;t need to trim these down to 16 chars as that isn't the max length you can store in a SQL 'text' field - it's something like up to 2Gb of data (on SQL2K) - even though the length says 16 on the table design (I think this was discussed in a thread here 2 or 3 weeks ago) and changing this length will make no difference. The SQL text field is like a memo field in Access. So with the data you are inputting to MessageTitle, MessageTeaser and MessageBody you should have no problem. You've not given the field length for the 'Status' column into which you;re trying to insert 16 chars - could this be the problem ? Alex -Original Message- From: John Morgan [mailto:gameczar;zbzoom.net] Sent: 28 October 2002 15:14 To: CF-Talk Subject: Re: OT: SQL Server database problem You should either trim the text being inserted using the left function ... EXAMPLE: Left(form.MessageTeaser, 16 ) or you should expand the size of the field in the table. If this is data input via a form you could use the size option in the input tag to enforce the size restriction but I would still trim the data with the left function for safety sake. EXAMPLE: input type=Text name=MessageTeaser size=16 -John- At 02:58 PM 10/28/2002 +, you wrote: * * WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message * * Hi, I have a SQL Server 7 database and I am trying to insert a large amount of text, passed by a ColdFusion form, into a table within this database. The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. This is the complete error message: ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = INSERT INTO Messages ( MessageType, Department, CreationDate, PublicationDate, MessageTitle, MessageTeaser, MessageBody, Status ) VALUES ( 'None', 'Housing', '28/10/2002', 'Awaiting', 'Some text to throw error', 'Some text to throw error, Some text to throw error', 'Some text to throw error,Some text to throw error and Some text to throw error', 'Awating Approval' ) Data Source = WIRE_USERS The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (24:1) to (24:57) in the template file E:\INETPUB\WWWROOT\WIRE\MESSAGES\SENDFORAPPROVAL.CFM. Anyone know how I can fix this error? Thanks Stephen * * Westminster City Council switchboard: +44 20 7641 6000 * * This E-Mail may contain information which is privileged, confidential and protected from disclosure. If you are not the intended recipient of this E-mail or any part of it, please telephone Westminster City Council immediately on receipt. You should not disclose the contents to any other person or take copies. * * ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT: SQL Server database problem
Are you sure that the fields you think are the problem really are. Try doing the insert with short strings for the fields in question and see if you still get the error. -John- At 03:16 PM 10/28/2002 +, you wrote: ** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** O.K I have increased the length of these three fields to 100, 150 and 500 respectively, but still I get the 22001 error. -Original Message- From: Jochem van Dieten [mailto:jochemd;oli.tudelft.nl] Sent: 28 October 2002 15:05 To: CF-Talk Subject: Re: OT: SQL Server database problem Quoting Adams, Stephen [EMAIL PROTECTED]: The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. 'Some text to throw error' is indeed longer as 16 characters. Not entirely unexpected behaviour, isn't it? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT: SQL Server database problem
** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** Alex thank you that was the cause of the problem. Stephen -Original Message- From: A.Little [mailto:A.Little;open.ac.uk] Sent: 28 October 2002 15:23 To: CF-Talk Subject: RE: OT: SQL Server database problem You don;t need to trim these down to 16 chars as that isn't the max length you can store in a SQL 'text' field - it's something like up to 2Gb of data (on SQL2K) - even though the length says 16 on the table design (I think this was discussed in a thread here 2 or 3 weeks ago) and changing this length will make no difference. The SQL text field is like a memo field in Access. So with the data you are inputting to MessageTitle, MessageTeaser and MessageBody you should have no problem. You've not given the field length for the 'Status' column into which you;re trying to insert 16 chars - could this be the problem ? Alex -Original Message- From: John Morgan [mailto:gameczar;zbzoom.net] Sent: 28 October 2002 15:14 To: CF-Talk Subject: Re: OT: SQL Server database problem You should either trim the text being inserted using the left function ... EXAMPLE: Left(form.MessageTeaser, 16 ) or you should expand the size of the field in the table. If this is data input via a form you could use the size option in the input tag to enforce the size restriction but I would still trim the data with the left function for safety sake. EXAMPLE: input type=Text name=MessageTeaser size=16 -John- At 02:58 PM 10/28/2002 +, you wrote: * * WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message * * Hi, I have a SQL Server 7 database and I am trying to insert a large amount of text, passed by a ColdFusion form, into a table within this database. The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. This is the complete error message: ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = INSERT INTO Messages ( MessageType, Department, CreationDate, PublicationDate, MessageTitle, MessageTeaser, MessageBody, Status ) VALUES ( 'None', 'Housing', '28/10/2002', 'Awaiting', 'Some text to throw error', 'Some text to throw error, Some text to throw error', 'Some text to throw error,Some text to throw error and Some text to throw error', 'Awating Approval' ) Data Source = WIRE_USERS The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (24:1) to (24:57) in the template file E:\INETPUB\WWWROOT\WIRE\MESSAGES\SENDFORAPPROVAL.CFM. Anyone know how I can fix this error? Thanks Stephen * * Westminster City Council switchboard: +44 20 7641 6000 * * This E-Mail may contain information which is privileged, confidential and protected from disclosure. If you are not the intended recipient of this E-mail or any part of it, please telephone Westminster City Council immediately on receipt. You should not disclose the contents to any other person or take copies. * * ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: OT: SQL Server database problem
Just another taught... Using varchar instead of text as a datatype for this kind of data maybe helpful... Rizal At 10:21 PM 10/28/2002, you wrote: I think the error message is straight forward, that is you are trying to insert string that's longer than your datatype can handle. For example: Field: MessageTitle (16): You are trying to insert: Some text to throw error which has 24 characters. Try to increase the length of the field in your DB. HTH, Rizal ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT: SQL Server database problem
** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** Thanks Rizal, but the problem was caused by another field being to small, not the three I originally thought. Stephen -Original Message- From: Rizal Firmansyah [mailto:rizal.firmansyah;sentracommerce.com] Sent: 28 October 2002 15:42 To: CF-Talk Subject: Re: OT: SQL Server database problem Just another taught... Using varchar instead of text as a datatype for this kind of data maybe helpful... Rizal At 10:21 PM 10/28/2002, you wrote: I think the error message is straight forward, that is you are trying to insert string that's longer than your datatype can handle. For example: Field: MessageTitle (16): You are trying to insert: Some text to throw error which has 24 characters. Try to increase the length of the field in your DB. HTH, Rizal ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
