CODEWITHSUNDEEP
c#sqldatareaderexecutereader
owenvoke
owenvoke

Reputation: 228

How can I make the SQL connect in a Windows Form?

I'm still working on my messenger programme, it will be more secure but I'm just testing stuff. I've got a login form which is meant to connect to the SQL database, I've set it up now with a local SQL as I was trying to get it working (it still doesn't work) and I was just wondering what I did wrong.

private void button1_Click(object sender, EventArgs e)
    {
        LoginInfo.un = textBox1.Text;
        LoginInfo.pw = textBox2.Text;

        SqlConnection con = new SqlConnection();
        con.ConnectionString = "Data Source=localhost:3306; User Id=PXgamer;Password=Password1; Initial Catalog=login;";
        try
        {
            con.Open();
        }
        catch (Exception)
        {
            MessageBox.Show("Error with the database connection");
        }
        string qry1 = "Select * from login where Password=@Password and Username=@Username";
        SqlCommand com = new SqlCommand(qry1, con);
        com.Parameters.AddWithValue("@Username", LoginInfo.un);
        com.Parameters.AddWithValue("@Password", LoginInfo.pw);
        SqlDataReader dr = com.ExecuteReader();
        while (dr.Read())
        {
            if (dr.HasRows == true)
            {
                MessageBox.Show("Login Successful", "Login Information");
            }
        }
        if (dr.HasRows == false)
        {
            MessageBox.Show("Access Denied", "Login Information");
        }

        this.Hide();
        var frmMain = new frmMain();
        frmMain.Closed += (s, args) => this.Close();
        frmMain.Show();
    }

So that's my code, the connection hangs at first, and then the "Error with the database connection" error appears. I tried looking that up, but it says it's when the connection hasn't opened. So obviously I'm guessing it's something wrong with the connection string.

In debugging, this is the part that gets highlighted:

SqlDataReader dr = com.ExecuteReader();

This is the error shown:

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

Additional information: ExecuteReader requires an open and available Connection. The connection's current state is closed.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Error without the catch:

Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Upvotes: 0

Views: 2289

Answers (3)

sstan
sstan

Reputation: 36483

You are trying to connect to a MySQL database using SQL Server-specific ADO.NET classes. That won't work.

You can see that your current code is trying to connect to a SQL Server instance from the error message that you got:

Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

You need to download the MySQL-specific drivers to connect to MySQL, and use that instead.

ADO.NET driver for MySQL

Upvotes: 1

Marco Sanfilippo
Marco Sanfilippo

Reputation: 295

Every line of code that need the 'con' object to work should stay inside the 'try' block of your method.

Also, if you can't open the connection, there are two possibilities:

  1. your connection string is wrong;
  2. your local SQL Server doesn't accept a connection from your application (misconfigured, not running, etc);
  3. both;

You can have a look here for connection strings for SQL Server: https://www.connectionstrings.com/sql-server-2008/

Finally... use a 'finally' block at the end for 'con.Close()'.

Upvotes: 0

S.Krishna
S.Krishna

Reputation: 876

Losing the port (:3306) from your connection string should work fine. If the SQL Server is not on default port, specify as below (using a comma, not a colon)

Data Source=server.ip.address,1433; Initial Catalog=myDataBase; 
User ID=myUsername; Password=myPassword;

Check this link for other variations of SQL Server connection strings

Upvotes: 0

Related Questions