0

Im a beginner, and new to C#, i don't know how to fix this bug, please help SQL Service is running, VS2019 is updated to newest and i am Windows 11. It only said Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll Code:

string cs = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\LoginDB.mdf;Integrated Security=True;";
            private void Loginbutton_Click(object sender, EventArgs e)
            {
                if (LoginTextBox.Text == "" || PassTextBox.Text == "") // Nếu 1 trong 2 ô trống thì
                {
                    MessageBox.Show("Missing login name or password");
                    return;
                }
                try
                {
                    SqlConnection con = new SqlConnection(cs);
                    SqlCommand cmd = new SqlCommand("Select * from LoginDB where UserName=@username and Password=@password", con);
                    cmd.Parameters.AddWithValue("@username", LoginTextBox.Text);
                    cmd.Parameters.AddWithValue("@password", PassTextBox.Text);
                    con.Open();
                    SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapt.Fill(ds);
                    con.Close();
                    int count = ds.Tables[0].Rows.Count;
                    //If count =1 thì hiện f2(Menu)
                    if (count == 1)
                    {
                        MessageBox.Show("Login Successfully!");
                        this.Visible = false;
                        Menu f2 = new Menu();
                        f2.Show();
                    }
                    else
                    {
                        MessageBox.Show("Login Name or password is wrong");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
Jim G.
  • 15,141
  • 22
  • 103
  • 166
Th1nhhdk
  • 9
  • 3
  • 1
    What does the exception say and on what line is it thrown? – kaffekopp Jul 15 '21 at 13:19
  • Sql Service? Or SQL Server? – Jim G. Jul 15 '21 at 13:21
  • per my experience: dataadapter will automatically open and close connection. so avoid use sqlcommand and dataadapter in one connection. – Lei Yang Jul 15 '21 at 13:23
  • It only said Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll – Th1nhhdk Jul 15 '21 at 13:26
  • It's Sql Service – Th1nhhdk Jul 15 '21 at 13:32
  • If you run this in the debugger, it will stop at the exception and you can view the whole message. Further notes: Connection, command and adapter need to be in `using` blocks. Don't do `select *` if you don't have to, just `select` the columns you need. Here you only need a yes/no, so you can do `select 1 from...` and use `ExecuteScalar` to get a single result. Don't store or pass plain-text passwords, use salt-and-hash instead. See [Bad Habits : Using AttachDBFileName](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-attachdbfilename/). ..... – Charlieface Jul 15 '21 at 13:49
  • ..... Don't hard-code the connection string, put it in a settings file. [AddWithValue is Evil](https://www.dbdelta.com/addwithvalue-is-evil/), specify parameter lengths and types explicitly. – Charlieface Jul 15 '21 at 13:49
  • "put it in a settings file", how can i do it, im new. – Th1nhhdk Jul 15 '21 at 14:02
  • often `app.config` file. – Lei Yang Jul 15 '21 at 14:10
  • @Th1nhhdk, based on my test, your code is no problem. I have two suggestions about your problem. First, you could refer to the [link](https://stackoverflow.com/questions/45492679/how-to-connect-to-sql-server-database-file-in-my-solution) to use the correct connection string to connect to the DB file. Second, I don't suggest that you use the same name between your table name and database name. – Jack J Jun Jul 16 '21 at 01:39

2 Answers2

0

Change your catch block to

    catch (SqlException ex)
    {
        StringBuilder errorMessages = new StringBuilder();
        for (int i = 0; i < ex.Errors.Count; i++)
        {
            errorMessages.Append("Index #" + i + "\n" +
                "Message: " + ex.Errors[i].Message + "\n" +
                "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                "Source: " + ex.Errors[i].Source + "\n" +
                "Procedure: " + ex.Errors[i].Procedure + "\n");
        }
        MessageBox.Show(errorMessages.ToString());
    }

It will give you a descriptive error message which is usually self explanatory.

Reference : https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlexception?view=dotnet-plat-ext-5.0

Shinva
  • 1,899
  • 18
  • 25
  • Looks like an issue with the connection string. Try string cs = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\.mdf;Integrated Security=True;Initial Catalog=LoginDB"; – Shinva Jul 15 '21 at 13:35
-1

SQL is just too hard for me, may be i will go .xlsx, yes i know its very inseccure but I quit, thanks anyway

Th1nhhdk
  • 9
  • 3