Application uses a variable of the wrong type...

[Richard Davies]

Hi

I have a ASP and SQL Server website which uses a couple of Stored
Procedures during the Shopping Cart process. 1 inserts the Customer
information into a table and the next inserts the Payment information
into a table.

I don't have access to the code now, but have found that many of our
customers, especially those who use I.E 6 are having problems with a

" -2146824867 Application uses a value of the wrong type for the
current operation"

error message which occurs when the Stored Procedure is executed.

I have looked through the Google archive but can't find anything
specific about this message. Could anyone tell me if there is a known
issue with IE6 and Datatypes with Stored Procedures, and if so how to
resolve it? I will post the code later today if anyone thinks it will
help.

Regards

Richard

 

[Bob Barrows]

Hi

I have a ASP and SQL Server website which uses a couple of Stored
Procedures during the Shopping Cart process. 1 inserts the Customer
information into a table and the next inserts the Payment information
into a table.

I don't have access to the code now, but have found that many of our
customers, especially those who use I.E 6 are having problems with a

" -2146824867 Application uses a value of the wrong type for the
current operation"

error message which occurs when the Stored Procedure is executed.

I have looked through the Google archive but can't find anything
specific about this message. Could anyone tell me if there is a known
issue with IE6 and Datatypes with Stored Procedures,

None that I've seen

and if so how to
resolve it? I will post the code later today if anyone thinks it will
help.

<chuckle> post the code.

 

[Bob Barrows]

PS. Please post only the relevant portion of the code. We don't need to see
the entire page, html and all. Just the snippet where you set the parameter
values and execute the stored procedure.

 

[Richard Davies]

PS. Please post only the relevant portion of the code. We don't need to see
the entire page, html and all. Just the snippet where you set the parameter
values and execute the stored procedure.

Hi

Here is the relevant code from the page:

<%
set rsCountry = Server.CreateObject("ADODB.Recordset")
rsCountry.ActiveConnection = MM_connrails_STRING
rsCountry.Source = "SELECT CountryName FROM dbo.tblcountry ORDER BY
CountryName ASC"
rsCountry.CursorType = 0
rsCountry.CursorLocation = 2
rsCountry.LockType = 3
rsCountry.Open()
rsCountry_numRows = 0
%>
<%

Dim Command1__title
Command1__title = ""
if(Request("title") <> "") then Command1__title = Request("title")

Dim Command1__fname
Command1__fname = ""
if(Request.Form("FirstName") <> "") then Command1__fname =
Request.Form("FirstName")

Dim Command1__lname
Command1__lname = ""
if(Request.Form("LastName") <> "") then Command1__lname =
Request.Form("LastName")

Dim Command1__cemail
Command1__cemail = ""
if(Request.Form("CustomerEmail") <> "") then Command1__cemail =
Request.Form("CustomerEmail")

Dim Command1__billadd1
Command1__billadd1 = ""
if(Request.Form("BillingAddress1") <> "") then Command1__billadd1 =
Request.Form("BillingAddress1")

Dim Command1__billadd2
Command1__billadd2 = ""
if(Request.Form("BillingAddress2") <> "") then Command1__billadd2 =
Request.Form("BillingAddress2")

Dim Command1__billcity
Command1__billcity = ""
if(Request.Form("BillingCity") <> "") then Command1__billcity =
Request.Form("BillingCity")

Dim Command1__billregion
Command1__billregion = ""
if(Request.Form("BillingState") <> "") then Command1__billregion =
Request.Form("BillingState")

Dim Command1__billpostcode
Command1__billpostcode = ""
if(Request.Form("BillingPostalcode") <> "") then Command1__billpostcode =
Request.Form("BillingPostalcode")

Dim Command1__billcountry
Command1__billcountry = ""
if(Request.Form("BillingCountryID") <> "") then Command1__billcountry =
Request.Form("BillingCountryID")

Dim Command1__phone
Command1__phone = ""
if(Request.Form("Phone") <> "") then Command1__phone = Request.Form("Phone")

Dim Command1__deladd1
Command1__deladd1 = ""
if(Request.Form("DeliveryAddress1") <> "") then Command1__deladd1 =
Request.Form("DeliveryAddress1")

Dim Command1__deladd2
Command1__deladd2 = ""
if(Request.Form("DeliveryAddress2") <> "") then Command1__deladd2 =
Request.Form("DeliveryAddress2")

Dim Command1__scity
Command1__scity = ""
if(Request.Form("DeliveryCity") <> "") then Command1__scity =
Request.Form("DeliveryCity")

Dim Command1__sregion
Command1__sregion = ""
if(Request.Form("deliverystate") <> "") then Command1__sregion =
Request.Form("deliverystate")

Dim Command1__spostcode
Command1__spostcode = ""
if(Request.Form("DeliveryPostalcode") <> "") then Command1__spostcode =
Request.Form("DeliveryPostalcode")

Dim Command1__shipcountry
Command1__shipcountry = ""
if(Request.Form("DeliveryCountryID") <> "") then Command1__shipcountry =
Request.Form("DeliveryCountryID")

Dim Command1__special
Command1__special = ""
if(Request.Form("SpecialInstructions") <> "") then Command1__special =
Request.Form("SpecialInstructions")

%>
<%
' *** Edit Operations: declare variables

MM_editAction = CStr(Request("URL"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
If (Request.Form("flag") = "add")Then
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_connrails_STRING
Command1.CommandText = "dbo.spcheckout1"
Command1.Parameters.Append Command1.CreateParameter("@title", 200,
1,50,Command1__title)
Command1.Parameters.Append Command1.CreateParameter("@fname", 200,
1,50,Command1__fname)
Command1.Parameters.Append Command1.CreateParameter("@lname", 200,
1,50,Command1__lname)
Command1.Parameters.Append Command1.CreateParameter("@cemail", 200,
1,50,Command1__cemail)
Command1.Parameters.Append Command1.CreateParameter("@billadd1", 200,
1,50,Command1__billadd1)
Command1.Parameters.Append Command1.CreateParameter("@billadd2", 200,
1,50,Command1__billadd2)
Command1.Parameters.Append Command1.CreateParameter("@billcity", 200,
1,50,Command1__billcity)
Command1.Parameters.Append Command1.CreateParameter("@billregion", 200,
1,50,Command1__billregion)
Command1.Parameters.Append Command1.CreateParameter("@billpostcode", 200,
1,50,Command1__billpostcode)
Command1.Parameters.Append Command1.CreateParameter("@billcountry", 200,
1,50,Command1__billcountry)
Command1.Parameters.Append Command1.CreateParameter("@phone", 200,
1,50,Command1__phone)
Command1.Parameters.Append Command1.CreateParameter("@deladd1", 200,
1,50,Command1__deladd1)
Command1.Parameters.Append Command1.CreateParameter("@deladd2", 200,
1,50,Command1__deladd2)
Command1.Parameters.Append Command1.CreateParameter("@scity", 200,
1,50,Command1__scity)
Command1.Parameters.Append Command1.CreateParameter("@sregion", 200,
1,50,Command1__sregion)
Command1.Parameters.Append Command1.CreateParameter("@spostcode", 200,
1,50,Command1__spostcode)
Command1.Parameters.Append Command1.CreateParameter("@shipcountry", 200,
1,50,Command1__shipcountry)
Command1.Parameters.Append Command1.CreateParameter("@special", 200,
1,200,Command1__special)
Command1.Parameters.Append Command1.CreateParameter("@Ident", 3, 4)
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
LastIdent = Command1.Parameters("@Ident")
Session("customerid")=LastIdent
Response.Redirect "/shop/checkout2.asp"
End if
%>
<html>

Here is the Stored Procedure:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spcheckout1]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[spcheckout1]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[spcheckout1]
@title nvarchar(50),
@fname nvarchar(50),
@lname nvarchar(50),
@cemail nvarchar(50),
@billadd1 nvarchar(50),
@billadd2 nvarchar(50),
@billcity nvarchar(50),
@billregion nvarchar(50),
@billpostcode nvarchar(50),
@billcountry nvarchar(50),
@phone nvarchar(50),
@deladd1 nvarchar(50),
@deladd2 nvarchar(50),
@scity nvarchar(50),
@sregion nvarchar(50),
@spostcode nvarchar(50),
@shipcountry nvarchar(50),
@special nvarchar(200),
@Ident int OUTPUT
AS
SET NOCOUNT ON
insert into dbo.tblCustomer
(Title,FirstName,LastName,CustomerEmail,BillingAddress1,BillingAddress2,
BillingCity,BillingRegion,BillingPostalCode,BillingCountry,Phone,DeliveryAdd
ress1,DeliveryAddress2,
DeliveryCity,DeliveryRegion,DeliveryPostalCode,DeliveryCountry,SpecialInstru
ctions) values
(@title,@fname,@lname,@cemail,@billadd1,@billadd2,@billcity,@billregion,@bil
lpostcode,
@billcountry,@phone,@deladd1,@deladd2,@scity,@sregion,@spostcode,@shipcountr
y,@special)
SET NOCOUNT OFF
SELECT @Ident = @@IDENTITY
RETURN @Ident
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Cheers

Richard

 

[Bob Barrows]

OK, my initial thought regarding your sproc: why use nvarchar? Do you need
to support international (unicode) characters? If not, you don't need to be
using "n" datatypes.

The Parameters collection of the Command object looks to be well-formed ...
I suggest you use the ADO constants (adCmdStoredProc instead of 4 when
setting the CommandType, etc.) rather than the numeric values for the
arguments: it will make your code more self-documenting. Of course, that
will mean that you will have to include the declarations for the constants:
you can do that by #including the adovbs.inc file which can be found in your
Program Files\Common Files\System\ADO folder. Or, better yet, by using the
technique described here:
http://www.aspfaq.com/show.asp?id=2112

I have a stored procedure code generator that eases the process of creating
these CreateParameter statements. It's available here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear.
An example of the ADO code generated for your procedure appears below.

I suggest that you explicitly force all your variables to the proper data
subtypes (string) using CStr. And instead of "if Request("title") <> ""
then", use "if len(Request("title")) > 0 then".

Dim cmd, param

Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "spcheckout1"
set .ActiveConnection=cnSQL
set param = .createparameter("RETURN_VALUE", adInteger,
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@title", adVarWChar, adParamInput, 50, [put
value here])
.parameters.append param
set param = .createparameter("@fname", adVarWChar, adParamInput, 50, [put
value here])
.parameters.append param
set param = .createparameter("@lname", adVarWChar, adParamInput, 50, [put
value here])
.parameters.append param
set param = .createparameter("@cemail", adVarWChar, adParamInput, 50, [put
value here])
.parameters.append param
set param = .createparameter("@billadd1", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@billadd2", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@billcity", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@billregion", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@billpostcode", adVarWChar, adParamInput,
50, [put value here])
.parameters.append param
set param = .createparameter("@billcountry", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@phone", adVarWChar, adParamInput, 50, [put
value here])
.parameters.append param
set param = .createparameter("@deladd1", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@deladd2", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@scity", adVarWChar, adParamInput, 50, [put
value here])
.parameters.append param
set param = .createparameter("@sregion", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@spostcode", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@shipcountry", adVarWChar, adParamInput, 50,
[put value here])
.parameters.append param
set param = .createparameter("@special", adVarWChar, adParamInput, 200,
[put value here])
.parameters.append param
set param = .createparameter("@Ident", adInteger, adParamInputOutput, 0,
[put value here])
.parameters.append param
.execute ,,adexecutenorecords
end with

If you do not wish to supply values for your output parameters, replace
adParamInputOutput with adParamOutput