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?
darrennelsonAsked:
Who is Participating?
 
PreeceConnect With a Mentor Commented:
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:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

Preece
0
 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.