asked on

SqlDataReader is not working - does not read data

When I debug it, it pass the loop while(readerOne.Read()). It never enter into this loop even though there is data.
What am I missing? Hopefully, I can get some assistance soon. It appears no one is out there because of no attention to my previous posts. I deleted them because I eventually find a solution on stackoverflow.

SqlDataReader does not data - does not read data

When I run the SQL statement from SQL Server - a result set is returned.
ID UserName PerLevel
1 US\\nathanm 1

sql string in debug
"select * from PermissionLevel where username = 'US\\nathanm'"

protected int getdata(string username)
{
int permissionlevel = 0;

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyTestDbConnectionString"].ConnectionString);

string sql = "select * from PermissionLevel where username = '" + username + "'";
SqlCommand sc = new SqlCommand(sql,conn);
sc.CommandType = System.Data.CommandType.Text;
conn.Open();

SqlDataReader rdr = sc.ExecuteReader();

while (rdr.Read())
{
permissionlevel = rdr.GetInt16(2);
}
rdr.Close();
conn.Close();
return permissionlevel;
}

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cookiejar

ASKER

There are two backslashes. If I remove the where clause, it works fine.
From researching on line, I tried parametizing but it still doesn't work.

protected int getdata(string username)
{
int permissionlevel = 0;

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyTestDbConnectionString"].ConnectionString);

string sql = "select * from PermissionLevel where UserName= @user";
SqlCommand sc = new SqlCommand(sql,conn);
sc.Parameters.AddWithValue("@user", username);
sc.CommandType = System.Data.CommandType.Text;
conn.Open();

SqlDataReader rdr = sc.ExecuteReader();

while (rdr.Read())
{
permissionlevel = rdr.GetInt32(2);
}
rdr.Close();
conn.Close();
return permissionlevel;
}