darrennelson
asked on
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 .Connectio n")
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_numbe r")
classification=request.for m("classif ication")
title=request.form("title" )
location=request.form("loc ation")
release_date=request.form( "release_d ate")
language=request.form("lan guage")
color_code=request.form("c olor_code" )
format=request.form("forma t")
dim SQL
SQL = "INSERT INTO software (disk_num, classification, title, location, rel_date, lang, color_code, format) VALUES ('"&disk_number&"','"&clas sification &"','"&tit le&"','"&l ocation&"' ,'"&releas e_date&"', '"&languag e&"','"&co lor_code&" ','"&forma t&"')"
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?
Here is my code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim cnnC3DB
Set cnnC3DB = Server.CreateObject("ADODB
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("
classification=request.for
title=request.form("title"
location=request.form("loc
release_date=request.form(
language=request.form("lan
color_code=request.form("c
format=request.form("forma
dim SQL
SQL = "INSERT INTO software (disk_num, classification, title, location, rel_date, lang, color_code, format) VALUES ('"&disk_number&"','"&clas
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?
Do this and look for a missing or extra quote:
response.write SQL
Preece
response.write SQL
Preece
Or, you may find that one of your fields is filled with a lot of spaces...
Preece
Preece
If you have unneeded spaces in front or behind, you can trim each field:
disk_number=trim(request.f orm("disk_ number"))
classification=trim(reques t.form("cl assificati on"))
title=trim(request.form("t itle"))
location=trim(request.form ("location "))
release_date=trim(request. form("rele ase_date") )
language=trim(request.form ("language "))
color_code=trim(request.fo rm("color_ code"))
format=trim(request.form(" format"))
Hope that helps,
Preece
disk_number=trim(request.f
classification=trim(reques
title=trim(request.form("t
location=trim(request.form
release_date=trim(request.
language=trim(request.form
color_code=trim(request.fo
format=trim(request.form("
Hope that helps,
Preece
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks again
ASKER
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)