Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1420
  • 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
Independent Software Vendors: 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!

 
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 GrootCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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