Retrieving Integers from tables in C# (VS 2005) using SQL commands

Question

string sql="", sql2 = "", rank="";
int basic_wage, rent_allowance, seniority_allowance, overtime_rate;

sql += "select rank, Basic_Wage,Rent_Allowance,Seniority_Allowance,Overtime_rate from   Designation_Details where Designation_ID=(select Designation_ID from Officer where Member_ID=" + id + ")";

sql2 += "select Overtime_Hours from Officer where Member_ID=" + id;

DataTable dtable1 = DataAccess.GetDataTable(sql);
DataTable dtable2 = DataAccess.GetDataTable(sql2);

rank+=dtable1.Rows[0].ItemArray[0];
basic_wage = dtable1.Rows[0].ItemArray[1];
rent_allowance = dtable1.Rows[0].ItemArray[2];
seniority_allowance = dtable1.Rows[0].ItemArray[3];
overtime_rate = dtable1.Rows[0].ItemArray[4];

bBasic_Wage,Rent_Allowance,Seniority_Allowance, and Overtime_rate are all int data types in the table.

The error message says:

....cannot implicitly convert object to int. An explicit conversion exists...

Answer 1

You just need to cast the values to integers:

rank += Convert.ToInt32(dtable1.Rows[0].ItemArray[0]);

or

rank += (int)(dtable1.Rows[0].ItemArray[0]);

Answer 2

Use the Convert.ToInt32, they sometimes come back as decimal, but still have the correct value.

Also if possible, use at least column names, not indexes. Other developers find this habit really annoying.

Answer 3

[I would leave a comment but I haven't got 15 random points...]

1 - As per Yuriy you should use column names. It not only makes it easier to read, but makes your code more robust if (and when) someone edits you SQL query. This will happen 2 years from now when you are no longer on the project, but its nice to be nice

2 - You MUST get used to using bind variables. Your current code is prime for SQL injection attacks and is also potentially inefficient (depending on how your DB is setup). Trust me (even though I have 1 point) that this will save you at some point later on - maybe not on this project - but it will at some point