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

Question

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)

You are swallowing the significant error. Remove the `try catch` around your `con.Open()`, and then post back your complete error message that occurs when you call `con.Open()`. — sstan, Jul 13 '15 at 16:15
I believe there's an error in your connection string. Check out [this site](http://www.connectionstrings.com/sql-server/) for further instructions. — Saragis, Jul 13 '15 at 16:15
This is just a guess, but is your database named something other than `login`? If so, the Initial Catalog portion of the connection string is the database name, not the table you want to query. — entropic, Jul 13 '15 at 16:16
I know you said this is your testing app but you might want to read this. http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ Also, you really need to hash your passwords and not store them in plain text. — Sean Lange, Jul 13 '15 at 16:18
@sstan Done, I hope that helps. Seems like it's just not connecting to my SQL. But it works when I connect from anything else. — owenvoke, Jul 13 '15 at 16:19
@PXGamer: How do you connect to your database when you do it through SSMS? What is the server name? Do you use windows authentication vs SQL Server Authentication? What network protocol do you use? You have to try it the same way you are doing it in code. — sstan, Jul 13 '15 at 16:26
@sstan When I'm normally connecting, I go in through PHPMyAdmin or I connect using my local website. Which works perfectly fine. I just have a username and password so not sure about the Windows Auth, or SQL Server Auth. — owenvoke, Jul 13 '15 at 16:35
@PXGamer: Hold on a sec. Which database are you connecting to? Is it really a SQL Server database? Or is it a MySQL database? — sstan, Jul 13 '15 at 16:38
@PXGamer: You bet there is. And you tagged your question `sql-server`. So we were all mislead. — sstan, Jul 13 '15 at 16:41

sstan · Accepted Answer · 2015-07-13T16:46:48.643

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

score 0 · Answer 2 · answered Jul 13 '15 at 16:24

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

score 0 · Answer 3 · edited Jul 13 '15 at 16:33

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:

your connection string is wrong;
your local SQL Server doesn't accept a connection from your application (misconfigured, not running, etc);
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()'.

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

3 Answers3