C# SqlDataReader for int

Question

I am confused by the following issue;

I am trying to retrieve a record (column 'EmployeeId' (int)) using a SqlDataReader.

When I run the query directly on the server, I got the record correctly. When I run the same procedure to get another column (string) I got the record correctly.

I assume the issue is due to the fact that I try to get an int, the program skips the

while (mySqlDbDataReader.Read())

and goes directly to

catch (Exception eMsg1)
            {

Here is the full procedure

public string getUserId()
{
    //Check user details against login in Db
    //Local variables to capture values from Db
    string varId = "";

    //Connection string
    conn = sqlDbConnection.GetConnection();
    SqlCommand newCmd = conn.CreateCommand();
    newCmd.Connection = conn;
    newCmd.CommandType = CommandType.Text;

    //Query
    userCredentials connectedUser = new userCredentials();
    newCmd.CommandText = "SELECT EmployeeId FROM tblEmployees WHERE WinLogin='" + connectedUser.getWindowsLogin() + "';";

    //Connect
    newCmd.Connection = conn;
    conn.Open();

    //Utilisation du try-catch permettant de fermer la connexion même en cas de plantage
    try
    {
        SqlDataReader mySqlDbDataReader = newCmd.ExecuteReader();
        while (mySqlDbDataReader.Read())
        {
            //Tester que le résultat n’est pas NULL afin d’éviter un plantage au moment du crash
            if (mySqlDbDataReader["EmployeeId"] != System.DBNull.Value)
            {
                //Récupérer le nom à l’aide d’un cast
                varId = (string)mySqlDbDataReader["EmployeeId"];                        
            }
            else
            {
                varId = "Unknown";                       
            }
        }
    }
    catch (Exception eMsg1)
    {
        //In the event of trouble, display error msg
        //MessageBox.Show("Error while executing query: " + eMsg1.Message);
        Console.WriteLine(eMsg1);

        //Control
        MessageBox.Show("Empty");
    }
    finally
    {
        //Close DB connection
        conn.Close();
    }

    return varId;
}`

Thanks for your help!

Brice

Well if it's hitting the catch block, it's getting an exception, so what is the exception that it is raising? — Arran
– Arran, Commented Dec 3, 2012 at 14:28
FYI. You have a risky SQL injection opportunity in your code. You should strongly consider reading about SQL injection attacks and how to avoid them. — tgolisch
– tgolisch, Commented Dec 3, 2012 at 14:30

Jon Skeet · Accepted Answer · 2015-04-21 12:59:47Z

7

I am trying to retrieve a record (column 'EmployeeId' (int)) using a SqlDataReader.

So why are you casting to a string?

varId = (string)mySqlDbDataReader["EmployeeId"];

That's the problem. You should be casting it to an int instead, or using GetInt32:

// You don't need a local variable for this at all...
return mySqlDbDataReader.GetInt32(0);

(Here the 0 is "the ordinal of the EmployeeId column; you can use GetOrdinal to get the ordinal for a named column.)

You'll need to change your method's return type to int as well, of course - or call ToString if you really want the value as a string. (I'd strongly advise against it though.)

You should be able to see the details when you write the exception out to the console. I don't think execution is skipping the Read call at all - it's reading the result, then failing to convert the int to a string, causing an exception to be thrown.

edited Apr 21, 2015 at 12:59

answered Dec 3, 2012 at 14:28

Jon Skeet

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

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

3 Comments

Panda Over a year ago

Thank you all for your quick response ;)

shashwat Over a year ago

You cannot pass column name in GetInt32(...) method. It only accepts The zero-based column ordinal.

Jon Skeet Over a year ago

@AnimalsAreNotOursToEat: Yup, I always forget that. Will edit.

user27414 · Accepted Answer · 2012-12-03 14:29:56Z

1

You are casting an int to a string, which you cannot do.

// Change varId to an int and cast to an int
varId = (int)mySqlDbDataReader["EmployeeId"];

Or

// Call ToString to get the string representation of the underlying value
varId = mySqlDbDataReader["EmployeeId"].ToString();

answered Dec 3, 2012 at 14:29

user27414

Comments

dotNET · Accepted Answer · 2012-12-03 14:40:37Z

1

Both Jons are correct. It is just that casting that appears to be raising the problem. I'd like to suggest using a Typed QueryTableAdapter for your purpose. Simply add a new DataSet to your project, right-click in the DataSet Designer and add a Query. Type your query there, introduce your variable (user name in this case I guess) and it will generate a query function in your QueryAdapter that will accept the variable from you, run the query and return EmployeeID. Moreover it will also save you from SQL injection type of things because it uses typed parameters internally.

answered Dec 3, 2012 at 14:40

dotNET

35.7k29 gold badges176 silver badges276 bronze badges

Comments

Jaime Torres · Accepted Answer · 2012-12-03 14:39:14Z

0

Try to convert the int:

instead of

//Récupérer le nom à l’aide d’un cast
varId = (string)mySqlDbDataReader["EmployeeId"];

use

//Récupérer le nom à l’aide d’un cast
varId = mySqlDbDataReader["EmployeeId"].ToString();

This is of course assuming that EmployeeId is not nullable.

edited Dec 3, 2012 at 14:39

answered Dec 3, 2012 at 14:28

Jaime Torres

10.5k1 gold badge50 silver badges56 bronze badges

2 Comments

dotNET Over a year ago

What's the difference between the two?

Jaime Torres Over a year ago

Hah! Copy and paste got me!

Collectives™ on Stack Overflow

C# SqlDataReader for int

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related