CODEWITHSUNDEEP
c#sql
Azri Zakaria
Azri Zakaria

Reputation: 1364

Take value from query to label in C#

How can i take value from query result to label?

I have two label, one is labelName and one more is labelDepartment

So when i run the query, how can i get value from query result and assign it to label using c#?

This is my Sql Command:

  "SELECT tbl_staff.staffName,tbl_department.department 
    FROM tbl_staff,tbl_logs,tbl_department 
    WHERE tbl_staff.userID = tbl_logs." + listStaff.SelectedValue + " and tbl_staff.idDepartment = tbl_department.idDepartment;"

This is current code in C#

//Open SQL connection

SqlConnection openCon = new SqlConnection(connString);
openCon.Open();

string SQL = string.Format("SELECT tbl_staff.staffName,tbl_department.department FROM tbl_staff,tbl_logs,tbl_department WHERE tbl_staff.userID = tbl_logs.userID and tbl_staff.idDepartment = tbl_department.idDepartment" + listStaff.SelectedValue + ";");


SqlCommand command = new SqlCommand(SQL);
SqlDataReader reader = command.ExecuteReader();

while(reader.Read())
{
    labelName.Text = reader["tbl_staff.staffName"];
    labelDepartment.Text = reader["tbl_department.department"];
}

Note : Our record will return one row only.. MS SQL and C#.. thanks for help...;)

Upvotes: 0

Views: 26627

Answers (8)

4b0
4b0

Reputation: 22323

One way to do:

private void getData()
{
    DataTable dt = new DataTable();
    SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE");
    connection.Open();
    SqlCommand sqlCmd = new SqlCommand(" SELECT tbl_staff.staffName,tbl_department.department 
FROM tbl_staff,tbl_logs,tbl_department 
WHERE tbl_staff.userID = tbl_logs." + listStaff.SelectedValue + " and tbl_staff.idDepartment = tbl_department.idDepartment", connection);
    SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

    sqlCmd.Parameters.AddWithValue("@username",user);
    sqlDa.Fill(dt);
    if (dt.Rows.Count > 0)
    {
           lable.Text = dt.Rows[0]["staffName"].ToString(); //Where "staffName" is ColumnName 

    }
        connection.Close();
}

Suggest to use a stored procedure not in line query to avoid SQL injection attacks.

stored procedure example

Upvotes: 0

SurajVitekar
SurajVitekar

Reputation: 17

Try Changing only This

SqlConnection openCon = new SqlConnection(connString);
openCon.Open();

string SQL = string.Format("SELECT tbl_staff.staffName,tbl_department.department FROM tbl_staff,tbl_logs,tbl_department WHERE tbl_staff.userID = tbl_logs.userID and tbl_staff.idDepartment = tbl_department.idDepartment" + listStaff.SelectedValue + ";");


SqlCommand command = new SqlCommand(SQL);
SqlDataReader reader = command.ExecuteReader();

while(reader.Read())
{
    labelName.Text = reader["tbl_staff.staffName"].toString();
    labelDepartment.Text = reader["tbl_department.department"].toString();
}`

Upvotes: 0

Rohit
Rohit

Reputation: 10236

Populate the Dataset with your Query and then retrieve values from Dataset and assign it to your label.

Lets say your Dataset is DS then: 

labelName.Text=DS.Tables[0]["tbl_staff.staffName"].tostring();
labelDepartment.Text=DS.Tables[0]["tbl_department.department"].tostring();

Hope this helps.

Upvotes: -2

Dinesh Guptha
Dinesh Guptha

Reputation: 97

//Open SQL connection

SqlConnection openCon = new SqlConnection(connString);
openCon.Open();

string SQL = string.Format("SELECT tbl_staff.staffName,tbl_department.department FROM tbl_staff,tbl_logs,tbl_department WHERE tbl_staff.userID = tbl_logs.userID and tbl_staff.idDepartment = tbl_department.idDepartment" + listStaff.SelectedValue + ";");


SqlCommand command = new SqlCommand(SQL);
SqlDataReader reader = command.ExecuteReader();

while(reader.Read())
{
    labelName.Text = reader["tbl_staff.staffName"].ToString();
    labelDepartment.Text = reader["tbl_department.department"].ToString();
}

Convert Type is what you left in your code....

Upvotes: 0

ssilas777
ssilas777

Reputation: 9804

Check this one - Also set your connection string along with this code

SQLCommand command = new SQLCommand();
command.CommandText = " SELECT tbl_staff.staffName,tbl_department.department 
    FROM tbl_staff,tbl_logs,tbl_department 
    WHERE tbl_staff.userID = tbl_logs." + listStaff.SelectedValue + " and tbl_staff.idDepartment = tbl_department.idDepartment ";

SQLDataReader reader = command.executeReader();

while(reader.read())
{
 labelName.Text = reader.GetString(0);
 labelDepartment.Text = reader.GetString(1);
}

Upvotes: 0

Gregor Primar
Gregor Primar

Reputation: 6805

        string name = null;
        string department = null;
        string listStaff = "MylistStaff";

        string sql =  "SELECT tbl_staff.staffName,tbl_department.department " +
            "FROM tbl_staff,tbl_logs,tbl_department " +
            "WHERE tbl_staff.userID = tbl_logs." + listStaff + " and tbl_staff.idDepartment = tbl_department.idDepartment;";
        //change this connection string... visit www.connectionstrings.com
        string connString = "Server=localhost; Database=myDatabaseName; Trusted_Connection=Yes";
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            using (SqlCommand command = new SqlCommand(sql,conn))
            {
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    name = reader[0] as string;
                    department = reader[1] as string;
                    //break for single row or you can continue if you have multiple rows...
                    break;
                }
            }
            conn.Close();
        }

department and listStaff can then easily be applied to label text like:

DepartmentLabel.Text = department;

Upvotes: 2

SaschaW
SaschaW

Reputation: 58

you need to read the result via a SQLDataReader

SQLCommand command = new SQLCommand("your sql string here");
SQLDataReader reader = command.executeReader();
while(reader.read())
{
 set your label values here with reader["cloumn"]
}

Upvotes: 1

seeker
seeker

Reputation: 3333

You can read content of the row(s) that query returned using DataReader class. It has methods to get single value, or you can iterate for each row. Tell me how many rows youR query returns so I can provide the exact code.

Upvotes: 0

Related Questions