?
Solved

Truncate insert into SQL string

Posted on 2009-04-12
6
Medium Priority
?
1,407 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 53

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 53

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
Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

 
LVL 53

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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
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…
Suggested Courses

743 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