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.Ad d(paramete rCustID)
Dim parameterDateOrdered As SqlParameter = New SqlParameter("@dateOrdered ", SqlDbType.DateTime, 8)
parameterDateOrdered.Value = Today
orderCommand.Parameters.Ad d(paramete rDateOrder ed)
Dim parameterProductOrdered As SqlParameter = New SqlParameter("@productOrde red", SqlDbType.VarChar, 255)
parameterProductOrdered.Va lue = productOrdered
orderCommand.Parameters.Ad d(paramete rProductOr dered)
Dim parameterQuantity As SqlParameter = New SqlParameter("@quantity", SqlDbType.SmallInt, 2)
parameterQuantity.Value = CInt(quantity)
orderCommand.Parameters.Ad d(paramete rQuantity)
Dim parameterCost As SqlParameter = New SqlParameter("@cost", SqlDbType.SmallMoney, 4)
parameterCost.Value = price
orderCommand.Parameters.Ad d(paramete rCost)
Dim parameterComments As SqlParameter = New SqlParameter("@comments", SqlDbType.NVarChar, 255)
parameterComments.Value = Trim(txtComments.Text)
orderCommand.Parameters.Ad d(paramete rComments)
Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum", SqlDbType.VarChar, 50)
parameterCCNum.Value = Trim(txtCardNumber.Text)
orderCommand.Parameters.Ad d(paramete rCCNum)
Dim parameterExpDate As SqlParameter = New SqlParameter("@ExpDate", SqlDbType.VarChar, 50)
parameterExpDate.Value = expDate
orderCommand.Parameters.Ad d(paramete rExpDate)
Dim parameterCCV As SqlParameter = New SqlParameter("@CCV", SqlDbType.VarChar, 3)
parameterCCV.Value = Trim(Issue.Text)
orderCommand.Parameters.Ad d(paramete rCCV)
Dim parameterorderID As SqlParameter = New SqlParameter("@orderID", SqlDbType.Int, 4)
parameterorderID.Direction = ParameterDirection.Output
orderCommand.Parameters.Ad d(paramete rorderID)
orderCommand.ExecuteNonQue ry()
' 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.ExecuteNonQue ry()
System.FormatException: Input string was not in a correct format.
Any ideas?
CML
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"
parameterCustID.Value = customerID
orderCommand.Parameters.Ad
Dim parameterDateOrdered As SqlParameter = New SqlParameter("@dateOrdered
parameterDateOrdered.Value
orderCommand.Parameters.Ad
Dim parameterProductOrdered As SqlParameter = New SqlParameter("@productOrde
parameterProductOrdered.Va
orderCommand.Parameters.Ad
Dim parameterQuantity As SqlParameter = New SqlParameter("@quantity", SqlDbType.SmallInt, 2)
parameterQuantity.Value = CInt(quantity)
orderCommand.Parameters.Ad
Dim parameterCost As SqlParameter = New SqlParameter("@cost", SqlDbType.SmallMoney, 4)
parameterCost.Value = price
orderCommand.Parameters.Ad
Dim parameterComments As SqlParameter = New SqlParameter("@comments", SqlDbType.NVarChar, 255)
parameterComments.Value = Trim(txtComments.Text)
orderCommand.Parameters.Ad
Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum", SqlDbType.VarChar, 50)
parameterCCNum.Value = Trim(txtCardNumber.Text)
orderCommand.Parameters.Ad
Dim parameterExpDate As SqlParameter = New SqlParameter("@ExpDate", SqlDbType.VarChar, 50)
parameterExpDate.Value = expDate
orderCommand.Parameters.Ad
Dim parameterCCV As SqlParameter = New SqlParameter("@CCV", SqlDbType.VarChar, 3)
parameterCCV.Value = Trim(Issue.Text)
orderCommand.Parameters.Ad
Dim parameterorderID As SqlParameter = New SqlParameter("@orderID", SqlDbType.Int, 4)
parameterorderID.Direction
orderCommand.Parameters.Ad
orderCommand.ExecuteNonQue
' 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.ExecuteNonQue
System.FormatException: Input string was not in a correct format.
Any ideas?
CML
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.
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("@productOrde red", SqlDbType.VarChar, 255)
parameterProductOrdered.Va lue = productOrdered
orderCommand.Parameters.Ad d(paramete rProductOr dered)
in your procedure your parameter is
@productOrdered varchar(50),
where as in your ADO.NET code
Dim parameterProductOrdered As SqlParameter = New SqlParameter("@productOrde
parameterProductOrdered.Va
orderCommand.Parameters.Ad
ASKER
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?
-----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.
ASKER
"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
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
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".
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".
ASKER
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.Direct ion = ParameterDirection.Output
customerCommand.Parameters .Add(param eterCustom erID)
' Open the connection and execute the Command
myConnection.Open()
customerCommand.ExecuteNon Query()
' Return the customerID
Dim customerID As Integer = parameterCustomerID.Value
-------------------------- ---------- ---------- ---------- ---------- ---
monosodiumg, I'll try that and post back...thanks,
----asp.net code immediate above what I posted before------
Dim parameterCustomerID As New SqlParameter("@CustomerID"
parameterCustomerID.Direct
customerCommand.Parameters
' Open the connection and execute the Command
myConnection.Open()
customerCommand.ExecuteNon
' Return the customerID
Dim customerID As Integer = parameterCustomerID.Value
--------------------------
monosodiumg, I'll try that and post back...thanks,
ASKER
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
thanks,
CML
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
)
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
)
ASKER
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
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
ASKER
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
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 "$".
.Net does not follow this convention. It will try to convert the string to a small money and choke on the "$".
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.