How to get Sql data into string variable or into label

Question

How can I get the data from the a table and convert it into string. I'm trying to make [User Level] into a string variable or get it to a label.

My current code is

private void btnLogin_Click(object sender, EventArgs e)
{
    if (attempt == 0)
    {
        lblMsg.Text = ("ALL 3 ATTEMPTS HAVE FAILED - CONTACT ADMIN");
        return;
    }

    scn.ConnectionString = @"Data Source=DESKTOP-39SPLT0;Initial Catalog=SalesandInventory;Integrated Security=True";            
    SqlCommand scmd = new SqlCommand("select count ([User Level]) as count from tblUsers where [User Name]=@usr and Password=@pwd", scn);            
    scmd.Parameters.Clear();
    scmd.Parameters.AddWithValue("@usr", txtUser.Text);
    scmd.Parameters.AddWithValue("@pwd", txtPass.Text);            
    scn.Open();

    if (scmd.ExecuteScalar().ToString() == "1")                
    {

        MessageBox.Show("You are granted with access.");
        this.Hide();
        frmMain frmmain = new frmMain();
        frmmain.Closed += (s, args) => this.Close();
        frmmain.Show();

    }
    else
    {
        MessageBox.Show("Invalid Username or Password.");
        lblMsg.Text = ("You have only " + Convert.ToString(attempt) + " attempt left to try.");
        --attempt;
        txtUser.Clear();
        txtPass.Clear();
    }
}

Answer 1

If you want to read both the count and the user level you can use the SqlDataReader and ExecuteReader instead of ExecuteScalar(). I made a slight change to your SQL query string. Run this and review your variables to see what you're returning.

SqlCommand scmd = new SqlCommand("select count ([User Level]) as UserCount, [User Level] from tblUsers where [User Name]=@usr and Password=@pwd", scn);
scmd.Parameters.Clear();
scmd.Parameters.AddWithValue("@usr", txtUser.Text);
scmd.Parameters.AddWithValue("@pwd", txtPass.Text);            
scn.Open();

string userLabel = "";
int userCount = 0;
try
{
      SqlDataReader reader = cmd.ExecuteReader();
      if (reader.Read())
      {
        userLabel = (string)reader["User Level"];
        userCount = (int)reader["UserCount"];
      }
 }
 catch (Exception ex)
 {
      //do something here if it fails. 
 }

if (userCount == 1) 
{
     MessageBox.Show("You are granted with access.");
     //rest of your code....
}

Maybe your query is just not returning 1 user as you're expecting. Try running the query in your db to see what it's returning. If this is the case then adjust the code as needed.

Answer 2

If want to read all data from table then please use this code.

    SqlCommand scmd = new SqlCommand("select * from tblUsers where [User Name]=@usr and Password=@pwd", scn);
    DataTable result = new DataTable();
    using (SqlDataReader reader = scmd.ExecuteReader())
    {
     result.Load(reader);
    }

then you can read data from table column.use this code

    result.Load(reader);          
    var userLevel=result[0]["User Level"]

Answer 3

Your problem resides in your SQL query.

select count ([User Level]) as count from tblUsers where [User Name]=@usr and Password=@pwd

You are selecting a COUNT() and not the actual [User Level]. The COUNT() will always return a number, in this case 1 if an user with the supplied username and password exists (if it has a PRIMARY KEY) or 0 if it doesn't. You should try changing the query to:

select [User Level] from tblUsers where [User Name]=@usr and Password=@pwd

Then you can check it's user level with

string userLevel = scmd.ExecuteScalar();

If userLevel == null then the username does not exists (or doesn't have the supplied password). Keep in mind that if the value of [User Level] is NULL in your database, the ExecuteScalar() will return a DBNull which can't be cast into a String and will fail.

Answer 4

I changed my code and send a String to the mainform in a label form. I used sqladapter instead of sqlcommand.

if (dt.Rows.Count == 1)
            {
                if (dt.Rows[0][0].ToString() == "Admin")
                {
                    MessageBox.Show("You are granted with access.", "Welcome!");
                    this.Hide();
                    frmMain fma = new frmMain(dt.Rows[0][0].ToString());
                    fma.Closed += (s, args) => this.Close();
                    fma.Show();
                }
                else if (dt.Rows[0][0].ToString() == "User")
                {
                    MessageBox.Show("You are granted with access.", "Welcome!");
                    this.Hide();
                    frmMain fma = new frmMain(dt.Rows[0][0].ToString());
                    fma.Closed += (s, args) => this.Close();
                    fma.Show();
                }
            }