Link to home
Start Free TrialLog in
Avatar of cmlawson
cmlawson

asked on

ASP.NET - VB.NET - SQL Server 2K - "Input string was not in a correct format"

Hey,

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.  That means there isn't a problem with the data type and the database.  What is happening is that I'm passing a parameter that does not match in data type or length with the expected parameter.  

Here's the error:

System.FormatException: Input string was not in a correct format.

Here's my code:

------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, 255)
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,
    '',
    '4323333588557409',
    '06-2007',
    '182'
)
SELECT
    @@Identity
GO
--------------------------

----my table ---------------------
orderID    int  not null (primary key)
customerID int not null
dateOrdered datetime not null
productOrdered varchar(255) not null
quantity smallint  not null
cost smallmoney  not null
comments nvarchar(255) null
CCNum varchar(50) not null
ExpDate varchar(50) not null
CCV varchar(3)  not null
--------------------------------

The error always kicks up on this line:

'execute the order
orderCommand.ExecuteNonQuery()

System.FormatException: Input string was not in a correct format.

Any ideas?

CML
Avatar of w_shaila
w_shaila

parameterQuantity.Value = CInt(quantity)


Quantity u are converting to integer. check once what is the value u are getting before converting it.

if you are getting decimal value like 15.23 and try to convert to interger, then you will get this exception.
Avatar of cmlawson

ASKER

w_shaila
It's good try but it's always a whole number because it come directly from a shopping cart dataTable.  You can also see from my Query Analyzer data that in this case it's 1, an interger.
Can you send me what are the values you are passing to the parameters from front end by debugging it.
hey I found one more thing in your code.

in your procedure your parameter is

 @productOrdered varchar(50),        

where as in your ADO.NET code

Dim parameterProductOrdered As SqlParameter = New SqlParameter("@productOrdered", SqlDbType.VarChar, 255)
parameterProductOrdered.Value = productOrdered
orderCommand.Parameters.Add(parameterProductOrdered)
Uumm....I'm not sure exactly what you're asking?  This is the line I'm sticking in QA and those values are cut/paste from debug mode in VS.NET:

-----Query Analyzer Data---------------------
 <everything under this line>

Is that what you mean?

yes. I really appreciate if you can debug and send me all the values what you are passing.
"yes. I really appreciate if you can debug and send me all the values what you are passing"
OK...I don't know what else I'm sending.  Those are the actual values I'm working with.  I type those into the textboxes and retrieve them in Debug mode and then copy/paste them here (or Query Analyzer.)  I have a SPROC immediately above that in code that executes just fine.  Is that what want?  I'd love to answer your question I just think I'm already giving you all the code.  Am I not?

Good eye on this:

@productOrdered varchar(50),  

As you can imagine, I changed that around to 255 to see if it would help but forgot to do so in the actual SPROC.  I did go back and change the SPROC but it didn't help.  I'm getting the same error.

thanks,

CML
Hi there,
where is "parameterCustomerID" defined ? Is it an Output parameter ? What is "parameterorderID" then ?
-Baan
Identify which parameter is causing the problem by eliminating parameters. Remove one parameter at a time from the stroed proc (hardcode the valuye into the sp if you like, or just use a dummy empty stored proc).
My bet is it's some quirk in the conversion from the .Net types to the sql svr types.
Youi're not passing "$99.00" as a smallmoney, i.e. withthe leading "$"?
If that doens't help, can you post the declarations for the  ASP.NET vairiables so that we know for example what type you are using for "price".
DotNetLover_Baan, customerID is an IDENTITY value taken upon an SPROC INSERT statement immediately above the code I've posted.  It runs and works out fine.  Just for reference, here is that code:

----asp.net code immediate above what I posted before------
Dim parameterCustomerID As New SqlParameter("@CustomerID", SqlDbType.Int, 4)
parameterCustomerID.Direction = ParameterDirection.Output
customerCommand.Parameters.Add(parameterCustomerID)

' Open the connection and execute the Command
myConnection.Open()
customerCommand.ExecuteNonQuery()

' Return the customerID
Dim customerID As Integer = parameterCustomerID.Value
---------------------------------------------------------------------


monosodiumg, I'll try that and post back...thanks,
monosodiumg, sorry to ask a dumb question but what do you mean by "Remove one parameter at a time from the stroed proc (hardcode the valuye into the sp if you like, or just use a dummy empty stored proc)"  Most of the columns require something to be inserted into them.  Also, I'm not familiar with a dummy empty SPROC.  Should I just go into SQL Server and make up a dumb, nothing one and call that?  I'm sorry I guess I'm not familiar with these techniques.  [hence, you guys are the experts and I'm not].  Could I get you to explain just a tiny, liitle bit more?

thanks,

CML
ASKER CERTIFIED SOLUTION
Avatar of jnhorst
jnhorst

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Remove one parameter:
CREATE Procedure addOrder
(
    @customerID int,
    @dateOrdered datetime,
    @productOrdered varchar(50),        
    @quantity smallint,
    @cost smallmoney,
    @comments nvarchar(255),
    @CCNum varchar(50),
    @ExpDate varchar(50),
    @OrderID int OUTPUT
)

REmove another:
CREATE Procedure addOrder
(
    @customerID int,
    @dateOrdered datetime,
    @productOrdered varchar(50),        
    @quantity smallint,
    @cost smallmoney,
    @comments nvarchar(255),
    @CCNum varchar(50),
    @OrderID int OUTPUT
)
 Or you could add in the on deleted before  (makes no difference). Another pretty much equivalent appraoch is to try each param individually e.g.
CREATE Procedure addOrder
(
    @CCNum varchar(50),
    @OrderID int OUTPUT
)

The kind of  error points to some problem in moving values from ASP.NET to SQL svr and my suggested appraoch  is simply to identify which parameter the problem occurs with.

The dummy procedure is just becuase withouth all the parameters you can't do the stuff the procedure is supposed to do and in any case, the problem appears to be in the passing of the values and not in the procedure's execution. Removing hte procedure's code means that you can execute it without ending up with a pile of junk data in your table. and you don;t have to keep adjusting the code to cope with the absence of parameters.

...
AS
(
  set @OrderID = 1
)


jnhorst, thanks for taking the time to comment.  I don't think your answer will solve the problem however.  The issue isn't with data going into the dB.  It's not a SQL Server error but a .NET framework error.  That data that I'm using doesn't cause an issue when I put it in SQL server but before that somewhere.  Here is the error:

System.FormatException: Input string was not in a correct format.

I had this question initially posted under SQL Server here on the Exchange and the guys over there helped me understand that it wasn't a SQL Server error but .NET Framework error.  I'll go ahead and try it out however and let you know what happens.  Also, thanks for the explanation of the SQL Server types.  I did not know all that and will change that one field from a nvarchar to a varchar with 8000.  Thanks so much!

monosodiumg, thanks for the explanation.  I'll try it out and post back!

CML
John,

Your example worked but I'm not sure why?  I guess the dB was having problems with the dollar sign?  I don't understand it because when I use the dollar sign to stick it in via QA, it doesn't complain?  Anyway, I'm glad to have the problem solved and I appreciate your comments.

thanks,

CML
QA (not SQL server) reads the $ and knows to interpret what folllows as a money value. I mentioned this in a previous post. QA then passes the correctly typed currency (numeric) value to SQL server so SQL server never sees the "$".
.Net does not follow this convention. It will try to convert the string to a small money and choke on the "$".