• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

ODBC Drivers error '80040e57' on SQL Insert

I've seen this posted and answered, but it doesn't fix my issue.

Here is my code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim cnnC3DB
Set cnnC3DB = Server.CreateObject("ADODB.Connection")
cnnC3DB.Open "Driver={SQL Server};" & _
           "Server=xxxxxxx;" & _
           "Database=xxxxxxx;" & _
           "Uid=xxxxxx;" & _
           "Pwd=xxxxxxx"

dim disk_number, classification, title, location, release_date, language, color_code, format
disk_number=request.form("disk_number")
classification=request.form("classification")
title=request.form("title")
location=request.form("location")
release_date=request.form("release_date")
language=request.form("language")
color_code=request.form("color_code")
format=request.form("format")

dim SQL
SQL = "INSERT INTO software (disk_num, classification, title, location, rel_date, lang, color_code, format) VALUES ('"&disk_number&"','"&classification&"','"&title&"','"&location&"','"&release_date&"','"&language&"','"&color_code&"','"&format&"')"

cnnC3DB.Execute(SQL)

cnnC3DB.Close
Set cnnC3DB = Nothing
Set disk_number = Nothing
%>

all datatypes are char, with plenty of space.  I have read that the cause is too many characters into too small of a field.  All fields except from primary key have nulls allowed.  I will get this error even if I submit without any data.  I have also tried with one character in each field.


any ideas?
0
darrennelson
Asked:
darrennelson
  • 4
  • 2
1 Solution
 
darrennelsonAuthor Commented:
more info:

the full error is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e57'

[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.

/msdn2/input.asp, line 25

line 25:
cnnC3DB.Execute(SQL)
0
 
PreeceCommented:
Do this and look for a missing or extra quote:

response.write SQL

Preece
0
 
PreeceCommented:
Or, you may find that one of your fields is filled with a lot of spaces...

Preece
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
PreeceCommented:
If you have unneeded spaces in front or behind, you can trim each field:

disk_number=trim(request.form("disk_number"))
classification=trim(request.form("classification"))
title=trim(request.form("title"))
location=trim(request.form("location"))
release_date=trim(request.form("release_date"))
language=trim(request.form("language"))
color_code=trim(request.form("color_code"))
format=trim(request.form("format"))

Hope that helps,
Preece
0
 
PreeceCommented:
You said that your datatypes are char.  I would go with varchars, as it saves space in your db.  Also, I think that if you retrieve the string "test" from a char(10), for example, you'll get "test      ".  So you may be populating your fields with extra spaces without realizing it...

Also, you may want to limit the textbox sizes to match the defined field sizes in your db:

<input type=text maxlength=5>

Preece
0
 
darrennelsonAuthor Commented:
thanks Preece, all is working.  I actually implemented all suggestions at the same time, so I'm not sure which fixed it.  I am going to undo all changes, then redo one at a time for personal reference.

Thanks again
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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