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

[Chad Micheal Lawson via .NET 247]

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

 

[Guest]

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?

 

[Scott Allen]

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,

 

[Greg Burns]

' 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



message 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

 

[Greg Burns]

P.S.

SELECT @OrderID = @@Identity

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

SELECT @OrderID = SCOPE_IDENTITY()

Greg