Get values from data table and pass it to label

Question

I want to get the FullName when the user login succesfully and put their FullName to the label

string conn = ConfigurationManager.ConnectionStrings["SystemDatabase"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(conn);
sqlconn.Open();
string query = "Select * from UserAccount where Username = '" + txtUsername.Text.Trim() + "' and Password = '" + txtPassword.Text.Trim() + "'";
SqlDataAdapter sda = new SqlDataAdapter(query,sqlconn);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count == 1) 
{
    FrmPOS frm = new FrmPOS();
    frm.Show();
    frm.lblCashierName.Text = ?//here i want to display the fullname from the UserAccount table.
    this.Hide();
}

I don't know how to get the data from the DataTable. Or is there any other way to do it?

DataTable consists of multiple rows, you need to select single [DataRow](https://learn.microsoft.com/en-us/dotnet/api/system.data.datarow?view=net-5.0) and mention column name in Square bracket to get the text you wanted. Note: *You should mention correct given column name of sql in DataRow* see [this](https://stackoverflow.com/questions/7119993/datarow-select-cell-value-by-a-given-column-name) — Abdul Wahab, Mar 23 '21 at 05:20
Does this answer your question? [Get data from datatable into a string in asp.net](https://stackoverflow.com/questions/6640441/get-data-from-datatable-into-a-string-in-asp-net) — Dale K, Mar 23 '21 at 05:22
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Mar 23 '21 at 06:10

Yong Shun · Accepted Answer · 2021-03-23T10:10:32.713

You might use dt.Rows[0]["FullName"].ToString() to get the FullName column from first record.

using (SqlConnection sqlconn = new SqlConnection (conn))
{
    sqlconn.Open();
    string query = "SELECT * FROM UseAccount WHERE Username = @Username AND Password = @Password";
    using (SqlCommand cmd = new SqlCommand(query, sqlconn)
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Username", txtUsername.Text.Trim());
        cmd.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());
    
        using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            adp.Fill(dt);
            if (dt != null || dt.Rows.Count > 0)
            {
                FrmPOS frm = new FrmPOS();
                frm.Show();
                frm.lblCashierName.Text = dt.Rows[0]["FullName"].ToString();
                this.Hide();
            }
        }
    }
}

Anyway, I would like to enhance your source code for some best practices

Apply using block to SqlConnection, SqlCommand, and SqlDataAdapter. Thus when each process ends, the using block will perform Dispose to release resources.
Apply SqlCommand with Parameters (a.k.a Parameterized query) as your way could lead to SQL Injection by concatenating the value into command.
Checking DataTable to ensure it has record(s) then only proceed; aims to solve possible NullReferenceException happened when no data return.

Andrei Solero · Answer 2 · 2021-03-23T06:03:10.247

0

You can try this

string conn = ConfigurationManager.ConnectionStrings["SystemDatabase"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(conn);
sqlconn.Open();
string query = "Select * from UserAccount where Username = '" + txtUsername.Text.Trim() + "' and Password = '" + txtPassword.Text.Trim() + "'";
SqlCommand command = new SqlCommand(query,sqlconn);
SqlDataReader reader = command.ExecuteReader();

if (reader.Read() == true) 
{
    FrmPOS frm = new FrmPOS();
    frm.Show();
    frm.lblCashierName.Text = reader["FullName"].ToString();
    this.Hide();
}

edited Mar 23 '21 at 06:03

answered Mar 23 '21 at 05:33

Andrei Solero

802
1
4
12

You are welcome @SimSim, but I forgot to change the variable name for SqlCommand, it should be `command`, lol :) – Andrei Solero Mar 23 '21 at 06:02
3

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) – marc_s Mar 23 '21 at 06:10
Yep, I agree with @marc_s suggestions – Andrei Solero Mar 23 '21 at 06:13
@marc_s yes sir, i will make a `stored procedure` out of it – Sim Sim Mar 23 '21 at 09:43

Get values from data table and pass it to label

2 Answers2