SQL 2k - ASP.NET - SPROC - "Input string was not in a correct format."

Discussion in 'ASP .Net' started by Chad Micheal Lawson via .NET 247, Sep 24, 2004.

  1. I'm stumped at this point and I'm tired of trying things so I'mposting in hopes of some guru with a sharp eye. I have anasp.net app running on a local Win XP Pro box. Within the app,I call a SPROC and pass the info to the SQL box and it hiccups. I'll drop VS.NET into debug mode and step through the SPROCcreation and copy those values into Query Analyzer to get alittle more info. When I run the Query Analyer that data worksjust fine. What?! Here is the relevant info:

    ------ASP.NET CODE---------------------
    Dim parameterCustID As SqlParameter = NewSqlParameter("@customerID", SqlDbType.Int, 4)
    parameterCustID.Value = customerID
    orderCommand.Parameters.Add(parameterCustID)

    Dim parameterDateOrdered As SqlParameter = NewSqlParameter("@dateOrdered", SqlDbType.DateTime, 8)
    parameterDateOrdered.Value = Today
    orderCommand.Parameters.Add(parameterDateOrdered)

    Dim parameterProductOrdered As SqlParameter = NewSqlParameter("@productOrdered", SqlDbType.VarChar, 50)
    parameterProductOrdered.Value = productOrdered
    orderCommand.Parameters.Add(parameterProductOrdered)

    Dim parameterQuantity As SqlParameter = NewSqlParameter("@quantity", SqlDbType.SmallInt, 2)
    parameterQuantity.Value = CInt(quantity)
    orderCommand.Parameters.Add(parameterQuantity)

    Dim parameterCost As SqlParameter = New SqlParameter("@cost",SqlDbType.SmallMoney, 4)
    parameterCost.Value = price
    orderCommand.Parameters.Add(parameterCost)

    Dim parameterComments As SqlParameter = NewSqlParameter("@comments", SqlDbType.NVarChar, 255)
    parameterComments.Value = Trim(txtComments.Text)
    orderCommand.Parameters.Add(parameterComments)

    Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum",SqlDbType.VarChar, 50)
    parameterCCNum.Value = Trim(txtCardNumber.Text)
    orderCommand.Parameters.Add(parameterCCNum)

    Dim parameterExpDate As SqlParameter = NewSqlParameter("@ExpDate", SqlDbType.VarChar, 50)
    parameterExpDate.Value = expDate
    orderCommand.Parameters.Add(parameterExpDate)

    Dim parameterCCV As SqlParameter = New SqlParameter("@CCV",SqlDbType.VarChar, 3)
    parameterCCV.Value = Trim(Issue.Text)
    orderCommand.Parameters.Add(parameterCCV)

    Dim parameterorderID As SqlParameter = NewSqlParameter("@orderID", SqlDbType.Int, 4)
    parameterorderID.Direction = ParameterDirection.Output
    orderCommand.Parameters.Add(parameterorderID)

    orderCommand.ExecuteNonQuery()

    ' Return the OrderID
    Dim orderID As Integer = parameterCustomerID.Value
    --------------------------

    -------SPROC in SQL 2K-------------------
    CREATE Procedure addOrder
    (
    @customerID int,
    @dateOrdered datetime,
    @productOrdered varchar(50),
    @quantity smallint,
    @cost smallmoney,
    @comments nvarchar(255),
    @CCNum varchar(50),
    @ExpDate varchar(50),
    @CCV varchar(3),
    @OrderID int OUTPUT
    )

    AS

    /* Create the Order header */
    INSERT INTO psiOrders
    (
    customerID,
    dateOrdered,
    productOrdered,
    quantity,
    cost,
    comments,
    CCNum,
    ExpDate,
    CCV
    )
    VALUES
    (
    @customerID,
    @dateOrdered,
    @productOrdered,
    @quantity,
    @cost,
    @comments,
    @CCNum,
    @ExpDate,
    @CCV
    )
    SELECT
    @OrderID = @@Identity
    GO
    --------------------------

    -----Query Analyzer Data---------------------
    INSERT INTO psiOrders
    (
    customerID,
    dateOrdered,
    productOrdered,
    quantity,
    cost,
    comments,
    CCNum,
    ExpDate,
    CCV
    )
    VALUES
    (
    40,
    9/22/2004,
    'SSK Scooter Kit',
    1,
    $ 99.00,
    '',
    '5555555555555555',
    '06-2007',
    '182'
    )
    SELECT
    @@Identity
    GO
    --------------------------


    Any ideas?


    --------------------------------
    From: Chad Micheal Lawson

    -----------------------
    Posted by a user from .NET 247 (http://www.dotnet247.com/)

    <Id>BqE+WdnJs0uNaGpvrOAVZw==</Id>
     
    Chad Micheal Lawson via .NET 247, Sep 24, 2004
    #1
    1. Advertising

  2. RE: SQL 2k - ASP.NET - SPROC - "Input string was not in a correct form

    When you declared the command object did you set the CommandType =
    CommandType.StoredProcedure?

    Also you may want to run a sqltrace and watch Stored Procedure class.

    What's the exact error and line that it fails on?


    "Chad Micheal Lawson via .NET 247" wrote:

    > I'm stumped at this point and I'm tired of trying things so I'm posting in hopes of some guru with a sharp eye. I have an asp.net app running on a local Win XP Pro box. Within the app, I call a SPROC and pass the info to the SQL box and it hiccups. I'll drop VS.NET into debug mode and step through the SPROC creation and copy those values into Query Analyzer to get a little more info. When I run the Query Analyer that data works just fine. What?! Here is the relevant info:
    >
    > ------ASP.NET CODE---------------------
    > Dim parameterCustID As SqlParameter = New SqlParameter("@customerID", SqlDbType.Int, 4)
    > parameterCustID.Value = customerID
    > orderCommand.Parameters.Add(parameterCustID)
    >
    > Dim parameterDateOrdered As SqlParameter = New SqlParameter("@dateOrdered", SqlDbType.DateTime, 8)
    > parameterDateOrdered.Value = Today
    > orderCommand.Parameters.Add(parameterDateOrdered)
    >
    > Dim parameterProductOrdered As SqlParameter = New SqlParameter("@productOrdered", SqlDbType.VarChar, 50)
    > parameterProductOrdered.Value = productOrdered
    > orderCommand.Parameters.Add(parameterProductOrdered)
    >
    > Dim parameterQuantity As SqlParameter = New SqlParameter("@quantity", SqlDbType.SmallInt, 2)
    > parameterQuantity.Value = CInt(quantity)
    > orderCommand.Parameters.Add(parameterQuantity)
    >
    > Dim parameterCost As SqlParameter = New SqlParameter("@cost", SqlDbType.SmallMoney, 4)
    > parameterCost.Value = price
    > orderCommand.Parameters.Add(parameterCost)
    >
    > Dim parameterComments As SqlParameter = New SqlParameter("@comments", SqlDbType.NVarChar, 255)
    > parameterComments.Value = Trim(txtComments.Text)
    > orderCommand.Parameters.Add(parameterComments)
    >
    > Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum", SqlDbType.VarChar, 50)
    > parameterCCNum.Value = Trim(txtCardNumber.Text)
    > orderCommand.Parameters.Add(parameterCCNum)
    >
    > Dim parameterExpDate As SqlParameter = New SqlParameter("@ExpDate", SqlDbType.VarChar, 50)
    > parameterExpDate.Value = expDate
    > orderCommand.Parameters.Add(parameterExpDate)
    >
    > Dim parameterCCV As SqlParameter = New SqlParameter("@CCV", SqlDbType.VarChar, 3)
    > parameterCCV.Value = Trim(Issue.Text)
    > orderCommand.Parameters.Add(parameterCCV)
    >
    > Dim parameterorderID As SqlParameter = New SqlParameter("@orderID", SqlDbType.Int, 4)
    > parameterorderID.Direction = ParameterDirection.Output
    > orderCommand.Parameters.Add(parameterorderID)
    >
    > orderCommand.ExecuteNonQuery()
    >
    > ' Return the OrderID
    > Dim orderID As Integer = parameterCustomerID.Value
    > --------------------------
    >
    > -------SPROC in SQL 2K-------------------
    > CREATE Procedure addOrder
    > (
    > @customerID int,
    > @dateOrdered datetime,
    > @productOrdered varchar(50),
    > @quantity smallint,
    > @cost smallmoney,
    > @comments nvarchar(255),
    > @CCNum varchar(50),
    > @ExpDate varchar(50),
    > @CCV varchar(3),
    > @OrderID int OUTPUT
    > )
    >
    > AS
    >
    > /* Create the Order header */
    > INSERT INTO psiOrders
    > (
    > customerID,
    > dateOrdered,
    > productOrdered,
    > quantity,
    > cost,
    > comments,
    > CCNum,
    > ExpDate,
    > CCV
    > )
    > VALUES
    > (
    > @customerID,
    > @dateOrdered,
    > @productOrdered,
    > @quantity,
    > @cost,
    > @comments,
    > @CCNum,
    > @ExpDate,
    > @CCV
    > )
    > SELECT
    > @OrderID = @@Identity
    > GO
    > --------------------------
    >
    > -----Query Analyzer Data---------------------
    > INSERT INTO psiOrders
    > (
    > customerID,
    > dateOrdered,
    > productOrdered,
    > quantity,
    > cost,
    > comments,
    > CCNum,
    > ExpDate,
    > CCV
    > )
    > VALUES
    > (
    > 40,
    > 9/22/2004,
    > 'SSK Scooter Kit',
    > 1,
    > $ 99.00,
    > '',
    > '5555555555555555',
    > '06-2007',
    > '182'
    > )
    > SELECT
    > @@Identity
    > GO
    > --------------------------
    >
    >
    > Any ideas?
    >
    >
    > --------------------------------
    > From: Chad Micheal Lawson
    >
    > -----------------------
    > Posted by a user from .NET 247 (http://www.dotnet247.com/)
    >
    > <Id>BqE+WdnJs0uNaGpvrOAVZw==</Id>
    >
     
    =?Utf-8?B?dG1laXN0ZXI=?=, Sep 24, 2004
    #2
    1. Advertising

  3. Chad Micheal Lawson via .NET 247

    Scott Allen Guest

    Hi Chad:

    Just one idea. You might want to try
    SqlCommandBuilder.DeriveParameters once, just to compare the
    parameters it would select and compare them to the types and sizes you
    have.

    HTH,

    --
    Scott
    http://www.OdeToCode.com

    On Thu, 23 Sep 2004 17:27:07 -0700, Chad Micheal Lawson via .NET 247
    <> wrote:

    >I'm stumped at this point and I'm tired of trying things so I'm posting in hopes of some guru with a sharp eye. I have an asp.net app running on a local Win XP Pro box. Within the app, I call a SPROC and pass the info to the SQL box and it hiccups. I'll drop VS.NET into debug mode and step through the SPROC creation and copy those values into Query Analyzer to get a little more info. When I run the Query Analyer that data works just fine. What?! Here is the relevant info:
    >
    >------ASP.NET CODE---------------------
    >Dim parameterCustID As SqlParameter = New SqlParameter("@customerID", SqlDbType.Int, 4)
    >parameterCustID.Value = customerID
    >orderCommand.Parameters.Add(parameterCustID)
    >
    >Dim parameterDateOrdered As SqlParameter = New SqlParameter("@dateOrdered", SqlDbType.DateTime, 8)
    >parameterDateOrdered.Value = Today
    >orderCommand.Parameters.Add(parameterDateOrdered)
    >
    >Dim parameterProductOrdered As SqlParameter = New SqlParameter("@productOrdered", SqlDbType.VarChar, 50)
    >parameterProductOrdered.Value = productOrdered
    >orderCommand.Parameters.Add(parameterProductOrdered)
    >
    >Dim parameterQuantity As SqlParameter = New SqlParameter("@quantity", SqlDbType.SmallInt, 2)
    >parameterQuantity.Value = CInt(quantity)
    >orderCommand.Parameters.Add(parameterQuantity)
    >
    >Dim parameterCost As SqlParameter = New SqlParameter("@cost", SqlDbType.SmallMoney, 4)
    >parameterCost.Value = price
    >orderCommand.Parameters.Add(parameterCost)
    >
    >Dim parameterComments As SqlParameter = New SqlParameter("@comments", SqlDbType.NVarChar, 255)
    >parameterComments.Value = Trim(txtComments.Text)
    >orderCommand.Parameters.Add(parameterComments)
    >
    >Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum", SqlDbType.VarChar, 50)
    >parameterCCNum.Value = Trim(txtCardNumber.Text)
    >orderCommand.Parameters.Add(parameterCCNum)
    >
    >Dim parameterExpDate As SqlParameter = New SqlParameter("@ExpDate", SqlDbType.VarChar, 50)
    >parameterExpDate.Value = expDate
    >orderCommand.Parameters.Add(parameterExpDate)
    >
    >Dim parameterCCV As SqlParameter = New SqlParameter("@CCV", SqlDbType.VarChar, 3)
    >parameterCCV.Value = Trim(Issue.Text)
    >orderCommand.Parameters.Add(parameterCCV)
    >
    >Dim parameterorderID As SqlParameter = New SqlParameter("@orderID", SqlDbType.Int, 4)
    >parameterorderID.Direction = ParameterDirection.Output
    >orderCommand.Parameters.Add(parameterorderID)
    >
    >orderCommand.ExecuteNonQuery()
    >
    >' Return the OrderID
    >Dim orderID As Integer = parameterCustomerID.Value
    >--------------------------
    >
    >-------SPROC in SQL 2K-------------------
    >CREATE Procedure addOrder
    >(
    > @customerID int,
    > @dateOrdered datetime,
    > @productOrdered varchar(50),
    > @quantity smallint,
    > @cost smallmoney,
    > @comments nvarchar(255),
    > @CCNum varchar(50),
    > @ExpDate varchar(50),
    > @CCV varchar(3),
    > @OrderID int OUTPUT
    >)
    >
    >AS
    >
    >/* Create the Order header */
    >INSERT INTO psiOrders
    >(
    > customerID,
    > dateOrdered,
    > productOrdered,
    > quantity,
    > cost,
    > comments,
    > CCNum,
    > ExpDate,
    > CCV
    >)
    >VALUES
    >(
    > @customerID,
    > @dateOrdered,
    > @productOrdered,
    > @quantity,
    > @cost,
    > @comments,
    > @CCNum,
    > @ExpDate,
    > @CCV
    >)
    >SELECT
    > @OrderID = @@Identity
    >GO
    >--------------------------
    >
    >-----Query Analyzer Data---------------------
    >INSERT INTO psiOrders
    >(
    > customerID,
    > dateOrdered,
    > productOrdered,
    > quantity,
    > cost,
    > comments,
    > CCNum,
    > ExpDate,
    > CCV
    >)
    >VALUES
    >(
    > 40,
    > 9/22/2004,
    > 'SSK Scooter Kit',
    > 1,
    > $ 99.00,
    > '',
    > '5555555555555555',
    > '06-2007',
    > '182'
    >)
    >SELECT
    > @@Identity
    >GO
    >--------------------------
    >
    >
    >Any ideas?
    >
    >
    >--------------------------------
    >From: Chad Micheal Lawson
    >
    >-----------------------
    >Posted by a user from .NET 247 (http://www.dotnet247.com/)
    >
    ><Id>BqE+WdnJs0uNaGpvrOAVZw==</Id>
     
    Scott Allen, Sep 24, 2004
    #3
  4. Chad Micheal Lawson via .NET 247

    Greg Burns Guest

    >' Return the OrderID
    >Dim orderID As Integer = parameterCustomerID.Value <<<wrong paramter???


    Dim orderID As Integer = parameterOrderID.Value

    I find creating individual variables for each parameter error prone.

    This is how I do it:


    cmd.Parameters.Add("@EmpID", SqlDbType.Int).Value = empID
    cmd.Parameters.Add("@WeekEnd", SqlDbType.SmallDateTime).Value =
    weekend
    cmd.Parameters.Add("@TimeSheetID", SqlDbType.Int).Direction =
    ParameterDirection.Output

    ...
    Dim TimeSheetID As Integer =
    CType(cmd.Parameters("@TimeSheetID").Value, Integer)

    HTH,
    Greg



    "Chad Micheal Lawson via .NET 247" <> wrote in
    message news:%...
    I'm stumped at this point and I'm tired of trying things so I'm posting in
    hopes of some guru with a sharp eye. I have an asp.net app running on a
    local Win XP Pro box. Within the app, I call a SPROC and pass the info to
    the SQL box and it hiccups. I'll drop VS.NET into debug mode and step
    through the SPROC creation and copy those values into Query Analyzer to get
    a little more info. When I run the Query Analyer that data works just fine.
    What?! Here is the relevant info:

    ------ASP.NET CODE---------------------
    Dim parameterCustID As SqlParameter = New SqlParameter("@customerID",
    SqlDbType.Int, 4)
    parameterCustID.Value = customerID
    orderCommand.Parameters.Add(parameterCustID)

    Dim parameterDateOrdered As SqlParameter = New SqlParameter("@dateOrdered",
    SqlDbType.DateTime, 8)
    parameterDateOrdered.Value = Today
    orderCommand.Parameters.Add(parameterDateOrdered)

    Dim parameterProductOrdered As SqlParameter = New
    SqlParameter("@productOrdered", SqlDbType.VarChar, 50)
    parameterProductOrdered.Value = productOrdered
    orderCommand.Parameters.Add(parameterProductOrdered)

    Dim parameterQuantity As SqlParameter = New SqlParameter("@quantity",
    SqlDbType.SmallInt, 2)
    parameterQuantity.Value = CInt(quantity)
    orderCommand.Parameters.Add(parameterQuantity)

    Dim parameterCost As SqlParameter = New SqlParameter("@cost",
    SqlDbType.SmallMoney, 4)
    parameterCost.Value = price
    orderCommand.Parameters.Add(parameterCost)

    Dim parameterComments As SqlParameter = New SqlParameter("@comments",
    SqlDbType.NVarChar, 255)
    parameterComments.Value = Trim(txtComments.Text)
    orderCommand.Parameters.Add(parameterComments)

    Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum",
    SqlDbType.VarChar, 50)
    parameterCCNum.Value = Trim(txtCardNumber.Text)
    orderCommand.Parameters.Add(parameterCCNum)

    Dim parameterExpDate As SqlParameter = New SqlParameter("@ExpDate",
    SqlDbType.VarChar, 50)
    parameterExpDate.Value = expDate
    orderCommand.Parameters.Add(parameterExpDate)

    Dim parameterCCV As SqlParameter = New SqlParameter("@CCV",
    SqlDbType.VarChar, 3)
    parameterCCV.Value = Trim(Issue.Text)
    orderCommand.Parameters.Add(parameterCCV)

    Dim parameterorderID As SqlParameter = New SqlParameter("@orderID",
    SqlDbType.Int, 4)
    parameterorderID.Direction = ParameterDirection.Output
    orderCommand.Parameters.Add(parameterorderID)

    orderCommand.ExecuteNonQuery()

    ' Return the OrderID
    Dim orderID As Integer = parameterCustomerID.Value
    --------------------------

    -------SPROC in SQL 2K-------------------
    CREATE Procedure addOrder
    (
    @customerID int,
    @dateOrdered datetime,
    @productOrdered varchar(50),
    @quantity smallint,
    @cost smallmoney,
    @comments nvarchar(255),
    @CCNum varchar(50),
    @ExpDate varchar(50),
    @CCV varchar(3),
    @OrderID int OUTPUT
    )

    AS

    /* Create the Order header */
    INSERT INTO psiOrders
    (
    customerID,
    dateOrdered,
    productOrdered,
    quantity,
    cost,
    comments,
    CCNum,
    ExpDate,
    CCV
    )
    VALUES
    (
    @customerID,
    @dateOrdered,
    @productOrdered,
    @quantity,
    @cost,
    @comments,
    @CCNum,
    @ExpDate,
    @CCV
    )
    SELECT
    @OrderID = @@Identity
    GO
    --------------------------

    -----Query Analyzer Data---------------------
    INSERT INTO psiOrders
    (
    customerID,
    dateOrdered,
    productOrdered,
    quantity,
    cost,
    comments,
    CCNum,
    ExpDate,
    CCV
    )
    VALUES
    (
    40,
    9/22/2004,
    'SSK Scooter Kit',
    1,
    $ 99.00,
    '',
    '5555555555555555',
    '06-2007',
    '182'
    )
    SELECT
    @@Identity
    GO
    --------------------------


    Any ideas?


    --------------------------------
    From: Chad Micheal Lawson

    -----------------------
    Posted by a user from .NET 247 (http://www.dotnet247.com/)

    <Id>BqE+WdnJs0uNaGpvrOAVZw==</Id>
     
    Greg Burns, Sep 24, 2004
    #4
  5. Chad Micheal Lawson via .NET 247

    Greg Burns Guest

    P.S.

    SELECT @OrderID = @@Identity

    can cause your problems (if you have triggers), better to use:

    SELECT @OrderID = SCOPE_IDENTITY()

    Greg
     
    Greg Burns, Sep 24, 2004
    #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. phmyhn
    Replies:
    0
    Views:
    11,464
    phmyhn
    Jun 24, 2003
  2. TJS
    Replies:
    0
    Views:
    1,372
  3. sbox
    Replies:
    2
    Views:
    15,417
    Ben Miller [MSFT]
    Feb 15, 2004
  4. Replies:
    0
    Views:
    660
  5. chongo
    Replies:
    2
    Views:
    8,222
    Edward
    Jul 8, 2004
Loading...

Share This Page