SqlDataAdapter.Fill(dt) unexpected errror

Question

private void button1_Click(object sender, EventArgs e)
{
   SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C: \Users\mohamma ali\Documents\Visual Studio 2015\Projects\WindowsFormsApplication4\WindowsFormsApplication4\MyLib_DB.mdf ;Integrated Security=True;Connect Timeout=30");
   string query =  "Select *  From User_Registration where UserID = '" + username_textbox.Text.Trim() + "' & Password = '" + password_text.Text.Trim() + "'";
   SqlDataAdapter sda = new SqlDataAdapter(query, con);
   DataTable dt = new DataTable ();
   sda.Fill(dt);

   if (dt.Rows.Count == 1)
   {
       mainmenu main = new mainmenu();
       this.Hide();              
       main.Show();
    }
    else
    {
          MessageBox.Show("Please Check usename and password");
    }
}

it is returning unexpected error at sda.fill(dt)?

Do not use AttachDbFilename when you are using integrated Security=True. The SQL serve owns the mdf filename and will not allow access. — jdweng, Nov 29 '18 at 21:27
Hi, I see you're new to SO. If you feel an answer solved the problem, please mark it as 'accepted' by clicking the gray check mark beside the answer. Check this link to know How does accepting an answer work: https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work — Salah Akbari, Nov 30 '18 at 18:22

Salah Akbari · Answer 1 · 2018-11-30T10:54:29.417

In SQL you should use and instead of &. Also you should always use parameterized queries to avoid SQL Injection. So your query should be something like this:

  string query =  "Select *  From User_Registration where UserID = @userName and Password = @password";
  sda.SelectCommand.Parameters.AddWithValue("@userName ", username_textbox.Text.Trim());
  sda.SelectCommand.Parameters.AddWithValue("@password", password_text.Text.Trim());

score 1 · Answer 2 · edited Nov 30 '18 at 14:32

private void button1_Click(object sender, EventArgs e)
{
    {
        string commandText = "Select * From User_Registration where UserID = @UserID  and Password = @Password ";

        using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C: \Users\mohamma ali\Documents\Visual Studio 2015\Projects\WindowsFormsApplication4\WindowsFormsApplication4\MyLib_DB.mdf ;Integrated Security=True;Connect Timeout=30"))
        {
            SqlDataAdapter sda = new SqlDataAdapter();
            SqlCommand command = new SqlCommand(commandText, connection);
            command.Parameters.AddWithValue("@UserID", username_textbox.Text.Trim());
            command.Parameters.AddWithValue("@Password", password_text.Text.Trim());

            try
            {
                connection.Open();
                sda.SelectCommand = command;

                DataTable dt = new DataTable();
                sda.Fill(dt);

                if (dt.Rows.Count == 1)
                {
                    mainmenu main = new mainmenu();
                    this.Hide();
                    main.Show();
                }
                else
                {
                    MessageBox.Show("Please Check usename and password");
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }

SqlDataAdapter.Fill(dt) unexpected errror

2 Answers2