p2p.wrox.com: Archives - asp_web

asp_web_howto thread: ODBC Drivers error '80040e57'

Message #1 by "John Eix" <jeix@s...> on Wed, 8 Jan 2003 21:46:08

I realize there has been much written about this error but all I have read
has not helped me solve the problem. Here is the full error I receive using
Mozilla:

ERROR:

2
2003-01-08
2003-03-09
String
String

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

[Microsoft][ODBC Microsoft Access Driver]Invalid string or buffer length

/jeix/onlinequiz/addsubscription.asp, line 143

COMMENTS:
Line 143 is objCommand.Execute. The first five lines are from
Response.Write commands in the file, one attempt I made to try and solve
the problem. I have provided the offending code below. I am attempting to
use an append query to add login information to an access database. I have
attempted to change the two dates strORDERDATE and strTERMINATIONDATE to
strings in a format I like. As the report shows the variables are strings
and the data is in the format I want. Thus the data should be added to the
database which has all fields as text with 255 characters. I am at a loss
to know what the problem is?? Thank you for your help!

HERE IS THE CODE:
<%@ LANGUAGE = "VBSCRIPT" %>
<!-- #include file = "LoginDataStore.inc"-->
<!-- #include file = "adovbs.inc"-->
<% 
  dim strUSERNAME
  dim strPASSWORD
  dim strPASSWORDHINT
  dim strFIRSTNAME
  dim strLASTNAME
  dim strEMAIL
  dim strCITY
  dim strSTATEORPROVINCE
  dim strCOUNTRY
  dim strSCHOOL
  dim strORDERDATE
  dim strTERMINATIONDATE
  dim strSUBSCRIPTIONNAME
  Dim objCommand			'command object
  Dim objRec
  Dim strConnect
   
 strUSERNAME = Request("UserName")
 strPASSWORD = Request("Password")
 strPASSWORDHINT = Request("PasswordHint")
 strFIRSTNAME = Request("FirstName")
 strLASTNAME = Request("LastName")
 strEMAIL = Request("Email")
 strCITY = Request("City")
 strSTATEORPROVINCE = Request("StateOrProvince")
 strCOUNTRY = Request("Country")
 strSCHOOL = Request("School")
 
 'determine Order & Termination dates
 SubscriptMonth = Request("hidSubscriptMonth")
 Response.Write SubscriptMonth & "<BR>"

 dim strMonth
 dim strDay
 
 If month(Date()) < 10 then
 strMonth = "0"
 Else
 strMonth = ""
 End if
 If Day(Now()) < 10 then
 strDay = "0"
 Else
 strDay = ""
 End if
 
 strORDERDATE = year(Date()) & "-" & strMonth & month(Date()) & "-" &
strDay & Day(Now())
 strTERMINATIONDATE = year(Date()) + SubscriptYear & "-" & strMonth &
month(Date()) + SubscriptMonth & "-" & strDay & Day(Now()) + 1
 Response.Write strORDERDATE & "<BR>"
 Response.Write strTERMINATIONDATE & "<BR>"
 WhatIsstrORDERDATE = TypeName(strORDERDATE)
 WhatIsstrTERMINATIONDATE = TypeName(strTERMINATIONDATE)
 Response.Write WhatIsstrORDERDATE & "<BR>"
 Response.Write WhatIsstrTERMINATIONDATE
 
 	'create the objects
	Set objCommand = Server.CreateObject ("ADODB.Command")
	Set objRec = Server.CreateObject ("ADODB.Recordset")

	'fill in command properties
	objCommand.ActiveConnection = strConnect
	objCommand.CommandText = "qryAddSubscription"
	objCommand.CommandType = adCmdStoredProc
	
	'now the parameters
	
	Set objParameter = objCommand.CreateParameter ("varUserName", adChar, _ 
					 adParamInput, 255)
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varUserName") = strUSERNAME
	
	Set objParameter = objCommand.CreateParameter ("varPassword", adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varPassword") = strPASSWORD

	Set objParameter = objCommand.CreateParameter ("varPasswordHint", adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varPasswordHint") = strPASSWORDHINT

	Set objParameter = objCommand.CreateParameter ("varFirstName", adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varFirstName") = strFIRSNAME
	
	Set objParameter = objCommand.CreateParameter ("varLastName", adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varLastName") = strLASTNAME

	Set objParameter = objCommand.CreateParameter ("varCity", adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varCity") = strCITY

	Set objParameter = objCommand.CreateParameter ("varStateOrProvince",
adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varStateOrProvince") = strSTATEORPROVINCE
	
	Set objParameter = objCommand.CreateParameter ("varCountry", adChar, _ 
					 adParamInput, 25)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varCountry") = strCOUNTRY
	
	Set objParameter = objCommand.CreateParameter ("varSchool", adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varSchool") = strSCHOOL
	
	Set objParameter = objCommand.CreateParameter ("varOrderDate", adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varOrderDate") = strORDERDATE
	
	Set objParameter = objCommand.CreateParameter ("varTerminationDate",
adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varTerminationDate") = strTERMINATIONDATE
	
	Set objParameter = objCommand.CreateParameter ("varSubscriptionName",
adChar, _ 
					 adParamInput, 255)
						 
	objCommand.Parameters.Append objParameter
	objCommand.Parameters("varSubscriptionName") = strSUBSCRIPTIONNAME	
	
	'open the recordset
	objCommand.Execute
		
'objRec.Close
'Set objRec = Nothing 
Set objCommand = Nothing
Set objParameter = Nothing

%>

Message #2 by Imar Spaanjaars <Imar@S...> on Wed, 08 Jan 2003 22:43:18 +0100

Hi John,

My first guess for this problem is the parameter type. You are passing 
adChar which indicates a fixed data length.
Instead, try adVarChar (also defined in adovbs.inc with a value of 200) 
instead.

HtH,

Imar


At 09:46 PM 1/8/2003 +0000, you wrote:
>I realize there has been much written about this error but all I have read
>has not helped me solve the problem. Here is the full error I receive using
>Mozilla:
>
>ERROR:
>
>2
>2003-01-08
>2003-03-09
>String
>String
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
>
>[Microsoft][ODBC Microsoft Access Driver]Invalid string or buffer length
>
>/jeix/onlinequiz/addsubscription.asp, line 143
>
>COMMENTS:
>Line 143 is objCommand.Execute. The first five lines are from
>Response.Write commands in the file, one attempt I made to try and solve
>the problem. I have provided the offending code below. I am attempting to
>use an append query to add login information to an access database. I have
>attempted to change the two dates strORDERDATE and strTERMINATIONDATE to
>strings in a format I like. As the report shows the variables are strings
>and the data is in the format I want. Thus the data should be added to the
>database which has all fields as text with 255 characters. I am at a loss
>to know what the problem is?? Thank you for your help!
>
>HERE IS THE CODE:
><%@ LANGUAGE = "VBSCRIPT" %>
><!-- #include file = "LoginDataStore.inc"-->
><!-- #include file = "adovbs.inc"-->
><%
>   dim strUSERNAME
>   dim strPASSWORD
>   dim strPASSWORDHINT
>   dim strFIRSTNAME
>   dim strLASTNAME
>   dim strEMAIL
>   dim strCITY
>   dim strSTATEORPROVINCE
>   dim strCOUNTRY
>   dim strSCHOOL
>   dim strORDERDATE
>   dim strTERMINATIONDATE
>   dim strSUBSCRIPTIONNAME
>   Dim objCommand                        'command object
>   Dim objRec
>   Dim strConnect
>
>  strUSERNAME = Request("UserName")
>  strPASSWORD = Request("Password")
>  strPASSWORDHINT = Request("PasswordHint")
>  strFIRSTNAME = Request("FirstName")
>  strLASTNAME = Request("LastName")
>  strEMAIL = Request("Email")
>  strCITY = Request("City")
>  strSTATEORPROVINCE = Request("StateOrProvince")
>  strCOUNTRY = Request("Country")
>  strSCHOOL = Request("School")
>
>  'determine Order & Termination dates
>  SubscriptMonth = Request("hidSubscriptMonth")
>  Response.Write SubscriptMonth & "<BR>"
>
>  dim strMonth
>  dim strDay
>
>  If month(Date()) < 10 then
>  strMonth = "0"
>  Else
>  strMonth = ""
>  End if
>  If Day(Now()) < 10 then
>  strDay = "0"
>  Else
>  strDay = ""
>  End if
>
>  strORDERDATE = year(Date()) & "-" & strMonth & month(Date()) & "-" &
>strDay & Day(Now())
>  strTERMINATIONDATE = year(Date()) + SubscriptYear & "-" & strMonth &
>month(Date()) + SubscriptMonth & "-" & strDay & Day(Now()) + 1
>  Response.Write strORDERDATE & "<BR>"
>  Response.Write strTERMINATIONDATE & "<BR>"
>  WhatIsstrORDERDATE = TypeName(strORDERDATE)
>  WhatIsstrTERMINATIONDATE = TypeName(strTERMINATIONDATE)
>  Response.Write WhatIsstrORDERDATE & "<BR>"
>  Response.Write WhatIsstrTERMINATIONDATE
>
>         'create the objects
>         Set objCommand = Server.CreateObject ("ADODB.Command")
>         Set objRec = Server.CreateObject ("ADODB.Recordset")
>
>         'fill in command properties
>         objCommand.ActiveConnection = strConnect
>         objCommand.CommandText = "qryAddSubscription"
>         objCommand.CommandType = adCmdStoredProc
>
>         'now the parameters
>
>         Set objParameter = objCommand.CreateParameter ("varUserName", 
> adChar, _
>                                         adParamInput, 255)
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varUserName") = strUSERNAME
>
>         Set objParameter = objCommand.CreateParameter ("varPassword", 
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varPassword") = strPASSWORD
>
>         Set objParameter = objCommand.CreateParameter ("varPasswordHint", 
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varPasswordHint") = strPASSWORDHINT
>
>         Set objParameter = objCommand.CreateParameter ("varFirstName", 
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varFirstName") = strFIRSNAME
>
>         Set objParameter = objCommand.CreateParameter ("varLastName", 
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varLastName") = strLASTNAME
>
>         Set objParameter = objCommand.CreateParameter ("varCity", adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varCity") = strCITY
>
>         Set objParameter = objCommand.CreateParameter ("varStateOrProvince",
>adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varStateOrProvince") = strSTATEORPROVINCE
>
>         Set objParameter = objCommand.CreateParameter ("varCountry", 
> adChar, _
>                                         adParamInput, 25)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varCountry") = strCOUNTRY
>
>         Set objParameter = objCommand.CreateParameter ("varSchool", 
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varSchool") = strSCHOOL
>
>         Set objParameter = objCommand.CreateParameter ("varOrderDate", 
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varOrderDate") = strORDERDATE
>
>         Set objParameter = objCommand.CreateParameter ("varTerminationDate",
>adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varTerminationDate") = strTERMINATIONDATE
>
>         Set objParameter = objCommand.CreateParameter ("varSubscriptionName",
>adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varSubscriptionName") = 
> strSUBSCRIPTIONNAME
>
>         'open the recordset
>         objCommand.Execute
>
>'objRec.Close
>'Set objRec = Nothing
>Set objCommand = Nothing
>Set objParameter = Nothing

Message #3 by "John Eix" <jeix@s...> on Thu, 9 Jan 2003 14:57:56 -0500

Hi Imar

Thank you for the suggestion I did a substitute replace for the changes
you suggested but got the same error. I have since rechecked to make
sure all the parameters in the query are correctly spelled and are all
text and ordered them the same as the order in the ASP file which is the
same as the order of the fields in the query. I have checked and double
checked to see that all variable, parameter etc are spelled the same in
all files. I have also tried using different browsers just in case
caching was the problem with no luck, this really has me stumped. The
annoying part is that I am using almost the identical coding to update
records in another project and it works fine and I am at a loss to see
what I am doing differently. At first I thought it was the use of the
date type and that is why I converted the dates to strings which are
easier to work with. I also changed the values in the database and the
asp file to better reflect the actual data and still no luck. Any
thoughts anyone has on how to solve this problem would be most
appreciated. TTYS John

-----Original Message-----
From: Imar Spaanjaars [mailto:Imar@S...] 
Sent: January 8, 2003 16:43
To: ASP Web HowTo
Subject: [asp_web_howto] Re: ODBC Drivers error '80040e57'


Hi John,

My first guess for this problem is the parameter type. You are passing 
adChar which indicates a fixed data length.
Instead, try adVarChar (also defined in adovbs.inc with a value of 200) 
instead.

HtH,

Imar


At 09:46 PM 1/8/2003 +0000, you wrote:
>I realize there has been much written about this error but all I have
>read has not helped me solve the problem. Here is the full error I 
>receive using
>Mozilla:
>
>ERROR:
>
>2
>2003-01-08
>2003-03-09
>String
>String
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
>
>[Microsoft][ODBC Microsoft Access Driver]Invalid string or buffer
>length
>
>/jeix/onlinequiz/addsubscription.asp, line 143
>
>COMMENTS:
>Line 143 is objCommand.Execute. The first five lines are from
>Response.Write commands in the file, one attempt I made to try and 
>solve the problem. I have provided the offending code below. I am 
>attempting to use an append query to add login information to an access

>database. I have attempted to change the two dates strORDERDATE and 
>strTERMINATIONDATE to strings in a format I like. As the report shows 
>the variables are strings and the data is in the format I want. Thus 
>the data should be added to the database which has all fields as text 
>with 255 characters. I am at a loss to know what the problem is?? Thank

>you for your help!
>
>HERE IS THE CODE:
><%@ LANGUAGE = "VBSCRIPT" %>
><!-- #include file = "LoginDataStore.inc"-->
><!-- #include file = "adovbs.inc"-->
><%
>   dim strUSERNAME
>   dim strPASSWORD
>   dim strPASSWORDHINT
>   dim strFIRSTNAME
>   dim strLASTNAME
>   dim strEMAIL
>   dim strCITY
>   dim strSTATEORPROVINCE
>   dim strCOUNTRY
>   dim strSCHOOL
>   dim strORDERDATE
>   dim strTERMINATIONDATE
>   dim strSUBSCRIPTIONNAME
>   Dim objCommand                        'command object
>   Dim objRec
>   Dim strConnect
>
>  strUSERNAME = Request("UserName")
>  strPASSWORD = Request("Password")
>  strPASSWORDHINT = Request("PasswordHint")
>  strFIRSTNAME = Request("FirstName")
>  strLASTNAME = Request("LastName")
>  strEMAIL = Request("Email")
>  strCITY = Request("City")
>  strSTATEORPROVINCE = Request("StateOrProvince")
>  strCOUNTRY = Request("Country")
>  strSCHOOL = Request("School")
>
>  'determine Order & Termination dates
>  SubscriptMonth = Request("hidSubscriptMonth")
>  Response.Write SubscriptMonth & "<BR>"
>
>  dim strMonth
>  dim strDay
>
>  If month(Date()) < 10 then
>  strMonth = "0"
>  Else
>  strMonth = ""
>  End if
>  If Day(Now()) < 10 then
>  strDay = "0"
>  Else
>  strDay = ""
>  End if
>
>  strORDERDATE = year(Date()) & "-" & strMonth & month(Date()) & "-" &
>strDay & Day(Now())
>  strTERMINATIONDATE = year(Date()) + SubscriptYear & "-" & strMonth &
>month(Date()) + SubscriptMonth & "-" & strDay & Day(Now()) + 1
>  Response.Write strORDERDATE & "<BR>"
>  Response.Write strTERMINATIONDATE & "<BR>"
>  WhatIsstrORDERDATE = TypeName(strORDERDATE)
>  WhatIsstrTERMINATIONDATE = TypeName(strTERMINATIONDATE)
>  Response.Write WhatIsstrORDERDATE & "<BR>"
>  Response.Write WhatIsstrTERMINATIONDATE
>
>         'create the objects
>         Set objCommand = Server.CreateObject ("ADODB.Command")
>         Set objRec = Server.CreateObject ("ADODB.Recordset")
>
>         'fill in command properties
>         objCommand.ActiveConnection = strConnect
>         objCommand.CommandText = "qryAddSubscription"
>         objCommand.CommandType = adCmdStoredProc
>
>         'now the parameters
>
>         Set objParameter = objCommand.CreateParameter ("varUserName", 
> adChar, _
>                                         adParamInput, 255)
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varUserName") = strUSERNAME
>
>         Set objParameter = objCommand.CreateParameter ("varPassword", 
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varPassword") = strPASSWORD
>
>         Set objParameter = objCommand.CreateParameter
> ("varPasswordHint",
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varPasswordHint") = strPASSWORDHINT
>
>         Set objParameter = objCommand.CreateParameter ("varFirstName",

> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varFirstName") = strFIRSNAME
>
>         Set objParameter = objCommand.CreateParameter ("varLastName", 
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varLastName") = strLASTNAME
>
>         Set objParameter = objCommand.CreateParameter ("varCity",
adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varCity") = strCITY
>
>         Set objParameter = objCommand.CreateParameter
>("varStateOrProvince", adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varStateOrProvince") 
> strSTATEORPROVINCE
>
>         Set objParameter = objCommand.CreateParameter ("varCountry", 
> adChar, _
>                                         adParamInput, 25)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varCountry") = strCOUNTRY
>
>         Set objParameter = objCommand.CreateParameter ("varSchool", 
> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varSchool") = strSCHOOL
>
>         Set objParameter = objCommand.CreateParameter ("varOrderDate",

> adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varOrderDate") = strORDERDATE
>
>         Set objParameter = objCommand.CreateParameter
>("varTerminationDate", adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varTerminationDate") 
> strTERMINATIONDATE
>
>         Set objParameter = objCommand.CreateParameter
>("varSubscriptionName", adChar, _
>                                         adParamInput, 255)
>
>         objCommand.Parameters.Append objParameter
>         objCommand.Parameters("varSubscriptionName") = 
> strSUBSCRIPTIONNAME
>
>         'open the recordset
>         objCommand.Execute
>
>'objRec.Close
>'Set objRec = Nothing
>Set objCommand = Nothing
>Set objParameter = Nothing

Message #4 by Imar Spaanjaars <Imar@S...> on Thu, 09 Jan 2003 21:07:11 +0100

Hi John,

Did you also check the data types in the stored procedure / query? Maybe 
you have forgotten to set a length there, or a smaller length than the 
parameter defines....

If I were you, I'd create a dummy table based on your original table with 
just one column. Also copy the procedure and have it accept only one 
parameter for the 1 column in your table.
Then run your code and only set one variable / fields.

If that works, keep adding more parameters and columns until the code breaks.
Other than this, it's hard to suggest something without the actual data 
structure and procedures.

Cheers,

Imar

(P.S. You probably checked this, but is your connection string OK? Are 
adovbs.inc and your connection file in the same dir as your current ASP page?)

At 02:57 PM 1/9/2003 -0500, you wrote:
>Hi Imar
>
>Thank you for the suggestion I did a substitute replace for the changes
>you suggested but got the same error. I have since rechecked to make
>sure all the parameters in the query are correctly spelled and are all
>text and ordered them the same as the order in the ASP file which is the
>same as the order of the fields in the query. I have checked and double
>checked to see that all variable, parameter etc are spelled the same in
>all files. I have also tried using different browsers just in case
>caching was the problem with no luck, this really has me stumped. The
>annoying part is that I am using almost the identical coding to update
>records in another project and it works fine and I am at a loss to see
>what I am doing differently. At first I thought it was the use of the
>date type and that is why I converted the dates to strings which are
>easier to work with. I also changed the values in the database and the
>asp file to better reflect the actual data and still no luck. Any
>thoughts anyone has on how to solve this problem would be most
>appreciated. TTYS John

Return to Index

P2P Archives