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";
    }
}

}

When you say it's not working, in what way? Do you get an error? As a side issue (but important one), you are opening yourself up to SQL injection by building the SQL dynamically like that. You should use parameterised SQL instead. — AdaTheDev
– AdaTheDev, Commented Sep 22, 2010 at 11:22
I'm getting a null reference exception when trying to count the rows in the data table. The debugger says: Object reference not set to an instance of an object. — The Muffin Man
– The Muffin Man, Commented Sep 22, 2010 at 18:52

TalentTuner · Accepted Answer · 2010-09-22 11:24:46Z

1

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

Check for t and dt instances of datatable.

answered Sep 22, 2010 at 11:24

TalentTuner

17.6k6 gold badges40 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

abatishchev · Accepted Answer · 2010-09-22 11:29:04Z

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.

edited Sep 22, 2010 at 11:29

abatishchev

101k88 gold badges303 silver badges443 bronze badges

answered Sep 22, 2010 at 11:22

RPM1984

73.3k62 gold badges241 silver badges364 bronze badges

Comments

RPM1984 · Accepted Answer · 2010-09-22 11:32:02Z

1

You fill t:

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

but read dt:

if (dt.Rows.Count > 0)

edited Sep 22, 2010 at 11:32

RPM1984

73.3k62 gold badges241 silver badges364 bronze badges

answered Sep 22, 2010 at 11:28

abatishchev

101k88 gold badges303 silver badges443 bronze badges

4 Comments

RPM1984 Over a year ago

haha, didnt even notice that! you're probably right. let me guess, you were really good at those "where's wally" books?

The Muffin Man Over a year ago

I'm getting a null reference exception when trying to count the rows in the data table. The debugger says: Object reference not set to an instance of an object.

RPM1984 Over a year ago

@nick - did you make the change @abatishchev mentioned?

The Muffin Man Over a year ago

I did make the change, but the datatable kept coming up null. I decided to go with a datareader instead. Got it working that way.

The Muffin Man · Accepted Answer · 2010-09-23 15:36:08Z

1

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();

}

edited Sep 23, 2010 at 15:36

answered Sep 23, 2010 at 15:16

The Muffin Man

20k30 gold badges128 silver badges196 bronze badges

2 Comments

Dillie-O Over a year ago

Yes, that's the proper way to avoid SQL injection.

PriceCheaperton Over a year ago

This is no good for N tier apps.... you should use table adapters and code separation.

Anuja Pawar · Accepted Answer · 2010-09-22 12:01:58Z

0

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

c.Close();

answered Sep 22, 2010 at 12:01

Anuja Pawar

392 bronze badges

1 Comment

Fred Smith Over a year ago

You don't need to close c because a Using closes it automatically

Collectives™ on Stack Overflow

Problem filling data table with SQL adapter in ASP.NET

5 Answers 5

Comments

Comments

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related