parameterized sql query issue

Question

I have a csv from where I import data to a database.

myCommand.Parameters.Add("@cn", SqlDbType.Text);
myCommand.Parameters["@cn"].Value = comp.Rows[i][1].ToString();

In case the value of comp.Rows[i][1] is X'496e626randomcharacters' I receive this error: The floating point value '496e626' is out of the range of computer representation (8 bytes). Incorrect syntax near '496e626'.

It should be handled as string/text so I don't understand the floating point error. What is wrong here?

Have you tried using AddWithValue("@cn", comp.Rows[i][1])? — James
– James, Commented Jan 14, 2014 at 12:41
AddWithValue is more likely to suffer from implicit conversion problems than explicitly defining a parameter type. — Mitch Wheat
– Mitch Wheat, Commented Jan 14, 2014 at 12:43

Mitch Wheat · Accepted Answer · 2014-01-14 12:42:10Z

3

Use

 SqlDbType.VarChar

rather than

 SqlDbType.Text

SqlDbType.Text: String. A variable-length stream of non-Unicode data with a maximum length of 2 31 -1 (or 2,147,483,647) characters.

Ref.

answered Jan 14, 2014 at 12:42

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manoj Purohit · Accepted Answer · 2014-01-14 12:46:26Z

0

Try this :

myCommand.Parameters.Add("@cn", SqlDbType.VarChar).Value=comp.Rows[i][1].ToString();

answered Jan 14, 2014 at 12:46

Manoj Purohit

3,4631 gold badge17 silver badges17 bronze badges

Comments

Michael Edenfield · Accepted Answer · 2014-01-14 12:47:11Z

The error you are getting is coming from the SQL Server, and happens whenever SQL tries to convert a string value into a numeric value.

You haven't shown any of the relevant SQL code, but I assume you are passing this value into a stored procedure, which is then inserting it into a data table. Both the parameter type and column type in the table needs to be VARCHAR or TEXT as well, or SQL will try to do the conversion.

Also, be careful about doing comparisons with your parameter against numeric values; for example, if you try to do a comparison like:

WHERE @CN = 1.234

SQL will try to convert your VARCHAR to a floating point value. You can use methods like ISNUMERIC to avoid the comparisons that are going to fail.

Collectives™ on Stack Overflow

parameterized sql query issue

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related