Solved

Truncate insert into SQL string

Posted on 2009-04-12
6
1,396 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
6 Comments
 
LVL 49

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 49

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 49

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 40

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now