Application uses a variable of the wrong type...

Discussion in 'ASP General' started by Richard Davies, Jul 30, 2003.

  1. 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
    Richard Davies, Jul 30, 2003
    #1
    1. Advertising

  2. Richard Davies

    Bob Barrows Guest

    Richard Davies wrote:
    > 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, Jul 30, 2003
    #2
    1. Advertising

  3. Richard Davies

    Bob Barrows Guest

    :)
    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.
    Bob Barrows, Jul 30, 2003
    #3

  4. > 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
    Richard Davies, Jul 30, 2003
    #4
  5. Richard Davies

    Bob Barrows Guest

    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
    Bob Barrows, Jul 31, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gabriel Schreiber

    Error: template argument uses local type

    Gabriel Schreiber, Nov 27, 2003, in forum: C++
    Replies:
    2
    Views:
    8,733
    tom_usenet
    Nov 27, 2003
  2. jprunier
    Replies:
    1
    Views:
    317
    Karl Heinz Buchegger
    Aug 31, 2004
  3. Thomas W.
    Replies:
    1
    Views:
    1,089
    Thomas W.
    Nov 4, 2006
  4. Replies:
    1
    Views:
    344
    Nathan Sokalski
    Apr 17, 2008
  5. Daisy

    Itemcommand event uses wrong comand

    Daisy, May 24, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    4
    Views:
    125
    Ken McAndrew
    Jul 9, 2004
Loading...

Share This Page