Input string was not in a correct format

[Jim]

im using asp.net, C# to enter data into a table in sql
server...however im getting this error:

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

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

Source Error:
Line 120:cmd.Parameters.Add("@commission",SqlDbType.Mon ey);
Line 121:cmd.Parameters["@commission"].Direction=ParameterDirection.Input;
Line 122:cmd.Parameters["@commission"].Value =
Convert.ToDouble(txtinvoice.Text);
Line 123:cmd.Parameters.Add("@pricesold",SqlDbType.Mone y);
Line 124:cmd.Parameters["@pricesold"].Direction=ParameterDirection

Similar error messages appear when I leave text fields blank that are
going to be inserted into fields in the table of datatype money,
datetime or int...

however when i execute this in query analyzer it works and inputs null
values for those fields:

declare @empid varchar(20), @inventoryid int, @clientid int,
@commission money,@pricesold money, @datesold datetime, @invoiceid
int, @shippingcost money, @terms varchar(50), @details varchar(50),
@clienttype smallint, @checker int, @errordesc varchar(100)
set @empid = 'admin'
set @inventoryid = 14
set @clientid= 2
set @clienttype = 0
set @commission = null
exec STP_updateconsignment @empid, @inventoryid, @clientid,
@commission,@pricesold, @datesold, @invoiceid, @shippingcost, @terms,
@details, @clienttype, @checker output, @errordesc output

If Im in enterprise manager and try to enter a blank value into one of
those fields I get: The value you entered is not consistent with the
data type or length of the column...so im wondering if the C# page is
sending my stored procedure a blank value instead of a Null and
causing this error?

any advice would be nice..thanks

-Jim

[Simon Hayes]

"Jim" <ji********@motorola.com> wrote in message
news:72**************************@posting.google.c om...

im using asp.net, C# to enter data into a table in sql
server...however im getting this error:

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

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

Source Error:
Line 120:cmd.Parameters.Add("@commission",SqlDbType.Mon ey);
Line 121:cmd.Parameters["@commission"].Direction=ParameterDirection.Input;
Line 122:cmd.Parameters["@commission"].Value =
Convert.ToDouble(txtinvoice.Text);
Line 123:cmd.Parameters.Add("@pricesold",SqlDbType.Mone y);
Line 124:cmd.Parameters["@pricesold"].Direction=ParameterDirection

Similar error messages appear when I leave text fields blank that are
going to be inserted into fields in the table of datatype money,
datetime or int...

however when i execute this in query analyzer it works and inputs null
values for those fields:

declare @empid varchar(20), @inventoryid int, @clientid int,
@commission money,@pricesold money, @datesold datetime, @invoiceid
int, @shippingcost money, @terms varchar(50), @details varchar(50),
@clienttype smallint, @checker int, @errordesc varchar(100)
set @empid = 'admin'
set @inventoryid = 14
set @clientid= 2
set @clienttype = 0
set @commission = null
exec STP_updateconsignment @empid, @inventoryid, @clientid,
@commission,@pricesold, @datesold, @invoiceid, @shippingcost, @terms,
@details, @clienttype, @checker output, @errordesc output

If Im in enterprise manager and try to enter a blank value into one of
those fields I get: The value you entered is not consistent with the
data type or length of the column...so im wondering if the C# page is
sending my stored procedure a blank value instead of a Null and
causing this error?

any advice would be nice..thanks

-Jim

As a complete guess, you're trying to convert a string value (possibly an
empty string, as you suspect) to a non-character data type, and the value
cannot be converted. Although an empty string can be converted to money, at
least. But without more information about the parameter values (ie. what is
the value of txtinvoice.Text when you run your C# code?), and the CREATE
TABLE statement for the table, it's not easy to give a good answer.

Simon

[Erland Sommarskog]

Jim (ji********@motorola.com) writes:

im using asp.net, C# to enter data into a table in sql
server...however im getting this error:

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a
correct format.
...
however when i execute this in query analyzer it works and inputs null
values for those fields:
...
If Im in enterprise manager and try to enter a blank value into one of
those fields I get: The value you entered is not consistent with the
data type or length of the column...so im wondering if the C# page is
sending my stored procedure a blank value instead of a Null and
causing this error?

That error looks very much to me like something which is happnening
in the CLR, and thus before the data reaches SQL Server at all.

Judging from your code snippet, you will have to investigate in what
Convert.ToDouble accepts.

By the way, I am not really sure that converting to double is the
best thing to do. Double is an imprecise data type, and money is
exact. I don't know how the CLR handles these conversions, but
there could be a risk that the value entered in the text box is
not what ends up in the database, if you use all four decimals of
the money value.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp