Problem filling data table with SQL adapter in ASP.NET

Question

I have a username db table that I'm trying to connect with to compare the username/pass.

Here is my code, it's not working, what am I doing wrong?

DataTable dt = null;

protected void btn_Click_Login(object sender, EventArgs e)
{
    string query = string.Format("SELECT * FROM Users WHERE Username='{0}' AND Password='{1}'", txtUsername.Text, txtPassword.Text);

    using (SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["RBConnectionString"].ConnectionString))
    {
        c.Open();

        using (SqlDataAdapter a = new SqlDataAdapter(query, c))
        {
            DataTable t = new DataTable();
            a.Fill(t);
        }
    }
    if (dt.Rows.Count > 0)
    {
        Session["Username"] = txtUsername.Text;
        Session["Password"] = txtPassword.Text;
        Response.Redirect("main.aspx");
        lblError.Text = "success";
    }
    else
    {
        lblError.Text = "Wrong Username/Password combination";
    }
} 

}

Answer 1

Try creating a SqlCommand to hold your query.

SqlCommand cmd = new SqlCommand(query, c);

using (SqlDataAdapter a = new SqlDataAdapter(cmd))
{
    DataTable t = new DataTable();
    a.Fill(t);
}

I'm not 100% sure that's your issue, but back in the days when i used to use ADO.NET (before L2SQL/EF, dark days indeed), i seem to remember an issue with DataTable's and SqlDataAdapter.

From what i remember - you can't fill a DataTable with a SqlDataAdapter based on a raw query string - you need to use SqlCommand. But i believe this can be accomplished with DataSet.

So either change to SqlCommand, or change to DataSet.

Answer 2

most probably you are using wrong datatable to check no of rows returned.

Check for t and dt instances of datatable.

Answer 3

You fill t:

DataTable t = new DataTable();
a.Fill(t);

but read dt:

if (dt.Rows.Count > 0)

Answer 4

What error you are getting is not clear. But i feel your connection is open and is never closed. Try

c.Close();

Answer 5

I decided to try the data reader and got it working:

protected void btn_Click_Login(object sender, EventArgs e)
{

   SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RbConnectionString"].ConnectionString);
    conn.Open();
    string queryString = "SELECT * FROM [Users] WHERE Username=@username AND Password= @password";
   SqlCommand command = new SqlCommand(queryString, conn);
   command.Parameters.AddWithValue("@username", txtUsername.Text);
   command.Parameters.AddWithValue("@password", txtPassword.Text);

   SqlDataReader reader = null;
   reader = command.ExecuteReader();

   if (reader.Read())
   {
       Session["Username"] = txtUsername.Text;
       Session["Password"] = txtPassword.Text;
       Response.Redirect("main.aspx");
   }
   else
   {
       lblError.Visible = true;
       lblError.Text = "Incorrect Username/Password Combination";
   }
    conn.Close();

}