Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1425
  • Last Modified:

Truncate insert into SQL string

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
rivkamak
Asked:
rivkamak
1 Solution
 
Ryan ChongCommented:
>>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
 
Ryan ChongCommented:
or lengthen the size of the char field in your table.
0
 
rivkamakAuthor Commented:
If I do something like "SET ANSI_WARNINGS OFF" help the situation.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Ryan ChongCommented:
but i guess "SET ANSI_WARNINGS OFF" will returns no error, but will still truncated your string to store into database.
0
 
SharathData EngineerCommented:
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
 
Abiel de GrootDeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now