Solved

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

Posted on 2004-09-24
16
441 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:cmlawson
  • 7
  • 4
  • 3
  • +2
16 Comments
 
LVL 3

Expert Comment

by:w_shaila
ID: 12148390
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.
0
 

Author Comment

by:cmlawson
ID: 12148435
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.
0
 
LVL 3

Expert Comment

by:w_shaila
ID: 12148512
Can you send me what are the values you are passing to the parameters from front end by debugging it.
0
 
LVL 3

Expert Comment

by:w_shaila
ID: 12148546
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)
0
 

Author Comment

by:cmlawson
ID: 12148548
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?

0
 
LVL 3

Expert Comment

by:w_shaila
ID: 12148553
yes. I really appreciate if you can debug and send me all the values what you are passing.
0
 

Author Comment

by:cmlawson
ID: 12148587
"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
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12148617
Hi there,
where is "parameterCustomerID" defined ? Is it an Output parameter ? What is "parameterorderID" then ?
-Baan
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 12

Expert Comment

by:monosodiumg
ID: 12148632
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".
0
 

Author Comment

by:cmlawson
ID: 12148674
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,
0
 

Author Comment

by:cmlawson
ID: 12148839
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
0
 
LVL 10

Accepted Solution

by:
jnhorst earned 500 total points
ID: 12149273
CML:

I have a hunch the input string problem might be your date value or your money value.

SqlDbType.DateTime does not necessarily map to System.DateTime.  Change this:

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

to:

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

Also, based on your Query Analyzer text, it looks like you are trying to put "$ 99.00" into the SmallMoney parameter.  You probably need to get rid of the "$" to create the parameter properly.

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

And then this:

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

All your other string parameters are varchar(), but this one is nvarchar().  Is there a reason for this?  nvarchar is for unicode strings, which are two bytes per character.  varchar() is for ascii strings which are on byte per string.  Unicode was developed to create non-English character sets larger than the 255 possibilities (one byte) afforded by ascii.  I do not know if this would make any difference, but unless you need to support non-English character sets, varchar would be a better choice. The top limit for varchar is 8000, not 255.

John

0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12155508
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
)


0
 

Author Comment

by:cmlawson
ID: 12155657
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
0
 

Author Comment

by:cmlawson
ID: 12156473
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
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12156681
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 "$".

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now