My SqlDatareader was not working

Question

I have a code for fetching customer_id and I use a SqlDataReader for reading customer_id from SQL Server. I test witch using breakpoint and step by step debugging and I understand the SqlDataReader condition was not compile and compiler jump straight in to the connection.close line:

   string strQuery = "select customer_id from Registration where username=@username and password=@password";
    SqlConnection connection1 = DBConnection.getConnection();
    connection1.Open();

   SqlCommand cmd = new SqlCommand();
   cmd.Connection = connection1;
   cmd.CommandText = strQuery;

   cmd.Parameters.AddWithValue("username", txt1_username.Text);
   cmd.Parameters.AddWithValue("password", txt2_password.Text);

   string customer_id = cmd.ExecuteScalar() as string;

   connection1.Close();

   if (customer_id == null)
    {
    Messages myMsg = new Messages();
    myMsg.CreateMessageAlert("The User does not Registered or your using incorect username or password");

     } 

      else {
     Session["customer_id"] = customer_id; 
        }

Answer 1

updated

        string strQuery = "select count(*) from Employee where  FullName=@username";
        SqlConnection connection = DBConnection.getConnection();
        connection.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = connection;
        cmd.CommandText = strQuery;

        cmd.Parameters.AddWithValue("username", txt1_username.Text);
        cmd.Parameters.AddWithValue("password", txt2_password.Text);

        int intRowCount = (int)cmd.ExecuteScalar();
        txt1_username.Text = intRowCount.ToString();
        if (intRowCount == 1)
        {
            string strquery = "select customer_id from Registration where  username=@username and password=@password";
            SqlCommand cmd2 = new SqlCommand();
            cmd2.Connection = connection;
            cmd2.CommandText = strquery;
            cmd2.Parameters.AddWithValue("username", txt1_username.Text);
            cmd2.Parameters.AddWithValue("password", txt2_password.Text);
            SqlDataReader reader = cmd2.ExecuteReader();
            if (reader.Read())
            {
                string customerID = reader[0].ToString();                    
            }
        }
        connection.Close();`

This is complete solution for your issue. Do not need to open connection everytime. just make sure, connection is being closed once it's used.

Answer 2

Although the issue is not very clear, you can try to revise the code taking following into account:

• There is no need to open/close db connection for every sql query in a method. Open it once, execute all queries, close. That will make code clear and faster.
• As you take connection from somewhere else, make sure it is closed before you open it (Example: Check if SQL Connection is Open or Closed)
• You run 2 queries and in both cases you get only 1 result (select count(*), select customer_id). Why then in first case you do ExecuteScalar() and ExecuteReader() in the other?
• The other thought is there is no need to have 2 SqlCommand(), etc if you need to return results of 2 queries. Read about Retrieving Multiple Result Sets using NextResult
• And last but not least - it seems you need to check if user is already registered and if true, get his id. Why not do it in one shot? The second query is good for both cases - if user does not exist, query will not return any result, if he does - his id will be returned. Doing this way, you would need only one query and less coding.

UPDATE:

The updated code looks more clear and straightforward, but you didn't get the point of my last comment. If you select count(customer_id) you get a count that you don't need. Why not simply select customer_id and check if it was returned or not?

Example:

//string strQuery = "select count(customer_id) from Registration where username=@username and password=@password";
string strQuery = "select customer_id from Registration where username=@username and password=@password";
SqlConnection connection1 = DBConnection.getConnection();
connection1.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = connection1;
cmd.CommandText = strQuery;

cmd.Parameters.AddWithValue("username", txt1_username.Text);
cmd.Parameters.AddWithValue("password", txt2_password.Text);

//int intRowCount = (int)cmd.ExecuteScalar();
string customer_id = cmd.ExecuteScalar() as string;

//txt1_username.Text = intRowCount.ToString(); <-- What's this?
connection1.Close();

//if (intRowCount == 1)
if (customer_id == null)
{
   // user does not exist, because sql returned no rows
   ... <-- do something here
} else {
   Session["customer_id"] = customer_id; 
}

UPDATE #2:

To troubleshoot

1. Make sure txt1_username.Text and txt2_password.Text have expected values. It could be that you reset the Text somewhere and that could be the reason why the query returned no result. Try to hardcode the value in the code, for example,

   cmd2.Parameters.AddWithValue("username", "admin");
   cmd2.Parameters.AddWithValue("password", "123");
   

2. Copy-paste entire sql in Sql Server Management Studio (or other tool) and run it from here to ensure what result it returned.

3. Make sure you execute it against correct database (maybe you have different databases with same tables where data is different).

Answer 3

This is because your username is no longer the username. It is actually 1 because of the line

int intRowCount = (int) cmd.ExecuteScalar();
txt1_username.Text = intRowCount.ToString(); <-- RED FLAG

So in the inside the If, you are actually running

SELECT customer_id FROM registration WHERE username=1 and password=my_password

Comment line 15 and you should do fine.