Converting a string to int, for a SQL Database call

Question

I am trying to execute a SQL select statement to retrieve my id, then increment the id. I can select from the database just fine. But when I try to convert the string to a int, I get the error of format exception was caught

Input string was not in a correct format.

Here is my code:

var sql = new SQL_Statements();
var string_sql_id ="";
var string_sql_select = "select * from is_inventory where id =(select max(id) from is_inventory)";

var sql_ds = sql.SelectFromDB(string_sql_select);

int DataIndex;

if (sql_ds.Tables["CurData"].Rows.Count > 0)
   for (DataIndex = 0; DataIndex <= sql_ds.Tables["CurData"].Rows.Count - 1; DataIndex++)
   {
       (sql_ds.Tables["CurData"].Rows[DataIndex]["id"]).ToString();
       string_sql_id ="CurData";
       var int_sql_id = Convert.ToInt32(string_sql_id);
       ++int_sql_id;
   }

On the second to last line of code is where it throws the exception. I have tried to change the convert to something else like double, but that didn't work either. Thanks in advance for the help.

You're using so many vars that I can't even tell what you're trying to do. Can you add some comments maybe? — emd
– emd, Commented May 22, 2013 at 15:00
You need to store the result of (sql_ds.Tables["CurData"].Rows[DataIndex]["id"]).ToString(); in a local variable — vc 74
– vc 74, Commented May 22, 2013 at 15:00
Prefacing all your variables with their type is really going to anger the next dev that reads your code. — Austin Salonen
– Austin Salonen, Commented May 22, 2013 at 15:09

Jon Skeet · Accepted Answer · 2013-05-22 15:01:21Z

3

For some reason you're fetching the value then discarding it, and then trying to convert the string CurData to an int.

(sql_ds.Tables["CurData"].Rows[DataIndex]["id"]).ToString();
string_sql_id ="CurData";
var int_sql_id = Convert.ToInt32(string_sql_id);

The value of string_sql_id is always just CurData then - how did you expect it to be anything else?

The value in the datatable should already be an integer, so you shouldn't need any parsing:

var id = (int) sql_ds.Tables["CurData"].Rows[DataIndex]["id"];

(You may find that it's a long instead of an int - it depends on the type of your field.)

You should avoid unnecessary string conversions where possible. In this case it's not clear why you're using a DataTable at all. You could just use a DataReader - and if you change you code to just select the max ID, you can just use ExecuteScalar to get a single value. Your current approach is very convoluted.

answered May 22, 2013 at 15:01

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

1 Comment

Nevyn Over a year ago

to be perfectly honest, I like your answer better than mine :(. lol.

Comfortably Numb · Accepted Answer · 2013-05-22 15:02:06Z

3

string_sql_id ="CurData";
var int_sql_id = Convert.ToInt32(string_sql_id);

You're trying co convert to int string "CurData"

answered May 22, 2013 at 15:02

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

1 Comment

Darren Over a year ago

Quite surprised this has more up votes than Jon's answer - especially when there is no further explanation on how to fix the issue.

Nevyn · Accepted Answer · 2013-05-22 15:00:45Z

1

in the line immediately before you're convert line, you set string_sql_id ="CurData";. "CurData" is not an integer, and attempting to convert it to one will not work.

try this instead;

string_sql_id = (sql_ds.Tables["CurData"].Rows[DataIndex]["id"]).ToString();

answered May 22, 2013 at 15:00

Nevyn

2,6834 gold badges22 silver badges33 bronze badges

3 Comments

Nevyn Over a year ago

in that case, its highly likely that you are either retrieving the wrong piece of data, or retrieving it incorrectly. What, EXACTLY, is returned by this statement: (sql_ds.Tables["CurData"].Rows[DataIndex]["id"]).ToString()?

nate Over a year ago

I replaced string_sql_id ="CurData"; with string_sql_id = (sql_ds.Tables["CurData"].Rows[DataIndex]["id"]).ToString();

l33tmike Over a year ago

It still looks like you're doing some redundant conversion here - is the "id" field of "CurData" actually stored as an int in the returned DataSet? If so, it should be able to be cast to an int. If "id" can ever return null, you'll need to check for DBNull first though.

Darren · Accepted Answer · 2013-05-22 15:00:41Z

1

You are trying to convert the string "CurData" to an integer which is what is throwing the exception.

 string_sql_id ="CurData";
 var int_sql_id = Convert.ToInt32(string_sql_id);

I assume you mean to assign string_sql_id to the Row ID:

 string_sql_id = sql_ds.Tables["CurData"].Rows[DataIndex]["id"]).ToString();

Then you can attempt to convert:

 var int_sql_id = Convert.ToInt32(string_sql_id);

answered May 22, 2013 at 15:00

Darren

71.2k24 gold badges141 silver badges146 bronze badges

Comments

S.N · Accepted Answer · 2013-05-22 15:12:38Z

0

You are trying convert primitive string type to Int. So it is something to do with your logic.

string_sql_id ="CurData";
var int_sql_id = Convert.ToInt32(string_sql_id);

Note: If you intend to work with Identity ( Incrementing field ) then I advise not to explicitly create incremental fields say Identity columns. Try to use database built in feature which generate it for you. Google it on how to defined AUTO INCREMENT Field in SQL or the db which you prefer.

answered May 22, 2013 at 15:12

S.N

5,1685 gold badges34 silver badges51 bronze badges

Collectives™ on Stack Overflow

Converting a string to int, for a SQL Database call

5 Answers 5

1 Comment

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related