An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll in C#

Question

Code :

private void button2_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    SqlConnection CON = new SqlConnection(@"Data Source(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\stud\Documents\ronak.mdf;Integrated Security=True;Connect Timeout=30");

      
    SqlCommand cmd = new SqlCommand("Select * from Table where username= ' " + textBox1.Text + "' and password= ' " + textBox2.Text + "' ", CON);
         
    SqlDataReader sda = cmd.ExecuteReader();

    dt.Load(sda);
    if (dt.Rows[0][0].ToString() == "1")
     {

        this.Hide();
        login2 rk = new login2();
        rk.Show();
     }
     else
     {
        MessageBox.Show("please chack you username and password");
     }
 }

This code is totally true but I have face some problem.

Answer 1

First of all you forget = after Data source in sqlConnection Second you are using bad names for variables Third you dont need to use SqlCommand, you can replace it with SqlDataAdapter its more simple Fourth you must use ( Using ) to dispose connection

Here`s the full code

  private void button2_Click(object sender, EventArgs e)
        {
            DataTable table = new DataTable();
            using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\stud\Documents\ronak.mdf;Integrated Security=True;Connection  Timeout=30"))
            {
                connection.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter($"Select * from Table where username= '{textBox1.Text}' and password= '{textBox2.Text}' ", connection))
                {
                    adapter.Fill(table);

                    if (table.Rows.Count == 0)
                    {
                        MessageBox.Show("please chack you username and password");
                        return;
                    }
                    if (table.Rows[0][0].ToString() == "1")
                    {
                        this.Hide();
                        login2 rk = new login2();
                        rk.Show();
                    }
                }
            }

        }

Answer 2

Your code has quite a number of different issues:

• Your connection string is missing = after Data Source
• You need to actually open the connection.
• Do not use AttachDbFilename instead create and connect to a normal database.
• Do not store plain-text passwords. Salt and hash them instead. Then compare the hash server-side, do not return it to the client.
• You don't need a DataTable or DataAdapter, you can just use ExecuteScalar to retrieve a single value.
• Dispose the connection and command with using.
• Do not inject data into your queries. Use parameters instead.

const string query = @"
Select 1
from Table
where username= @username
  and password= @password
";

using (var CON = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=ronak;Integrated Security=True;Connect Timeout=30"))
using (var cmd = new SqlCommand(query, CON))
{
    cmd.Parameters.Add("@username", SqlDbType.NVarChar, 100).Value = textBox1.Text;
    cmd.Parameters.Add("@password", SqlDbType.VarBinary, 256).Value = SaltAndHashPassword(textBox2.Text, textBox1.Text);
    CON.Open();
    var exists = cmd.ExecuteScalar() == 1;
    CON.Close();

    if (exists)
    {
        this.Hide();
        login2 rk = new login2();
        rk.Show();
    }
    else
    {
        MessageBox.Show("please chack you username and password");
    }
}