Solved

Truncate insert into SQL string

Posted on 2009-04-12
6
1,403 Views
Last Modified: 2013-12-24
I am trying to send a long insert string into an sql table and whenever a field is sent with too many characters the form gets an error thrown with this error.
 [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
Is there a way to set it up that it just cuts off the long field instead of throwing an error?
MM_editConnection = MM_TowerConn2_STRING
  MM_editTable = "dbo.RebbetzinOnlineApps"
  MM_editRedirectUrl = "thanks.html"
  MM_fieldsStr  = "Title|value|Lastname|value|FirstName|value|Address|value|Apt|value|City|value|State|value|Zip|value|email|value|phone|value|Phone2|value|Cell|value|gender|value|height|value|DOB|value|JewByBirth|value|BeisDinInfo|value|ObservantSince|value|FrumFromBirth|value|ReligiousStyle|value|ReligiousStyleOther|value|Kohein|value|HebrewSchool|value|ElementaryYeshiva|value|YeshivaHighSchool|value|PostHighSchoolYeshiva|value|College|value|LearningFullTime|value|LearningPartTime|value|AttendingCollege|value|AttendingSeminary|value|Working|value|SeekingEmployment|value|YeshivaAttended|value|FuturePreference|value|AboutYou|value|MotherName|value|FatherName|value|GaurdianResidence|value|FamilyOrigin|value|FamilyOther|value|Reference1Name|value|Reference1City|value|Reference1Number|value|Reference2Name|value|Reference2City|value|Reference2Number|value|RabbiConnection1Name|value|RabbiConnection1State|value|RabbiConnection1Number|value|RabbiConnection2Name|value|RabbiConnection2State|value|RabbiConnection2Number|value|RabbiConnection3Name|value|RabbiConnection3State|value|RabbiConnection3Number|value|ContactPersonName|value|ContactPersonState|value|ContactPersonNumber|value|RefferedBy|value|ReferredByOther|value|BirthplaceSelf|value|BirthplaceParents|value|ParentsBT|value"
  MM_columnsStr = "Title|',none,''|LastName|',none,''|FirstName|',none,''|Address|',none,''|Apt|',none,''|City|',none,''|State|',none,''|Zip|',none,''|Email|',none,''|Phone|',none,''|Phone2|',none,''|Cell|',none,''|Gender|',none,''|Height|none,none,NULL|DOB|',none,NULL|JewByBirth|none,none,NULL|BeisDinInfo|',none,''|ObservantSince|none,none,NULL|FrumFromBirth|none,1,0|ReligiousStyle|none,none,NULL|ReligiousStyleOther|',none,''|Kohein|none,none,NULL|HebrewSchool|none,1,0|ElementaryYeshiva|none,1,0|YeshivaHighSchool|none,1,0|PostHighSchoolYeshiva|none,1,0|College|none,1,0|LearningFullTime|none,1,0|LearningPartTime|none,1,0|AttendingCollege|none,1,0|AttendingSeminary|none,1,0|Working|none,1,0|SeekingEmployment|none,1,0|YeshivaAttended|',none,''|FuturePreference|none,none,NULL|AboutYou|',none,''|MotherName|',none,''|FatherName|',none,''|GaurdianResidence|',none,''|FamilyOrigin|',none,''|FamilyOther|',none,''|Reference1Name|',none,''|Reference1City|',none,''|Reference1Number|',none,''|Reference2Name|',none,''|Reference2City|',none,''|Reference2Number|',none,''|RabbiConnection1Name|',none,''|RabbiConnection1State|',none,''|RabbiConnection1Number|',none,''|RabbiConnection2Name|',none,''|RabbiConnection2State|',none,''|RabbiConnection2Number|',none,''|RabbiConnection3Name|',none,''|RabbiConnection3State|',none,''|RabbiConnection3Number|',none,''|ContactPersonName|',none,''|ContactPersonState|',none,''|ContactPersonNumber|',none,''|ReferredBy|',none,''|ReferredByOther|',none,''|BirthplaceSelf|',none,''|BirthplaceParents|',none,''|ParentsBT|none,1,0"
..
.
.
 
  MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

Open in new window

0
Comment
Question by:rivkamak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 24127608
>>Is there a way to set it up that it just cuts off the long field instead of throwing an error?

you can try set the form elements size to maximum allowed.
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 24127613
or lengthen the size of the char field in your table.
0
 

Author Comment

by:rivkamak
ID: 24127641
If I do something like "SET ANSI_WARNINGS OFF" help the situation.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Expert Comment

by:Ryan Chong
ID: 24127662
but i guess "SET ANSI_WARNINGS OFF" will returns no error, but will still truncated your string to store into database.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24127797
No, you need to send the maximum allowed characters to database or you can set alter the column length in the database to maxmimum or more as suggested by ryancys.
If you dn't have control on the number of characters from your front end, you can alter your column length in the databse.
0
 
LVL 5

Accepted Solution

by:
Abiel de Groot earned 500 total points
ID: 24129155
You could simply tructate the value being inserted as follows

Left(MM_dbValues,150)

This takes the first 150 characters only.

Kind regards

A.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question