Connecting to a MySQL database via C#

[riahc3]

Hey

I have the following code

private void buttonInicio_Click(object sender, EventArgs e)
        {
            MySql.Data.MySqlClient.MySqlConnection conn;

            String constring = "server=localhost;database=bd;uid=user;password=pass;";
            try
            {
                conn = new MySql.Data.MySqlClient.MySqlConnection(constring);
                string query = "COUNT (*) FROM table WHERE usuario=" + Globales.usuario +
                               " AND pass=" + Globales.contra;
                MySqlCommand cmd = new MySqlCommand(query,conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();
                int num = (int) cmd.ExecuteScalar();
                MessageBox.Show("Trying");


                conn.Open();

                MessageBox.Show(num.ToString());
                MessageBox.Show("Fin");
                conn.Close();

            }
            catch
            {
                MessageBox.Show("Error");
            }
        }

This code gives me a

"A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll"

and the MessageBox showing Error pops up.

What could be some possibilities that are happening? All the parameters such as connection to DB and the tables exist so....

Thanks!

[rotemorb]

Hi there.
First, put your connection string in your web.config.
After that, use this code to access it and connect to the db:

       string query = string.Empty;
        String con1 = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
        MySqlConnection con = new MySqlConnection(con1);
        query = "Your sql query";

        MySqlDataAdapter da = new MySqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        da.Fill(ds);

one more thing:

rewrite your catch block to look like :

catch(exception ex)
{
    messageBox.show(ex.message);
}

Please tell me what is the reported error.

Rotem

[riahc3]

Hi there.
First, put your connection string in your web.config.
After that, use this code to access it and connect to the db:
1. string query = string.Empty;2. String con1 = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();3. MySqlConnection con = new MySqlConnection(con1);4. query = "Your sql query";5.6. MySqlDataAdapter da = new MySqlDataAdapter(query, con);7. DataSet ds = new DataSet();8. da.Fill(ds);
one more thing:

rewrite your catch block to look like :
1.catch(exception ex)2.{3. messageBox.show(ex.message);4.}
Please tell me what is the reported error.

Rotem

My web.config? This isnt a ASP.net project.....

Maybe you ment something else.

[thines01]

You're calling conn.Open() twice.

[riahc3]

The exception is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT (*) FROM table WHERE usuario=usuario AND pass=pass' at line 1

Now that I think of it.........isnt it SELECT COUNT(*)....

Trying now....

[riahc3]

I feel stupid because Im trying:

SELECT COUNT (*) from table;

In MySQL workbench and it says it is invalid too. That line is perfectly valid right?

[riahc3]

Confirming it is more of a MySQL error as I did a simply select and it worked.

[riahc3]

Marking this as unsolved because I cant connect to a PC on my same network.....

I can ping it and it can ping me. None of us have firewalls install on the PC. What can be wrong. The exception says:

Unable to connect to any of the specified MySQL hosts.GUIProject.exe Error: 0 : Unable to connect to any of the specified MySQL hosts.
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
Unable to connect to any of the specified MySQL hosts.The thread '<No Name>' (0x14694) has exited with code 0 (0x0).

[thines01]

Take out the space between the word count and the left parenthesis.
Also, your table name really isn't "table", is it? That's a reserved word.

Also, in order to diagnose your connection string, I would need to see it.

[riahc3]

Take out the space between the word count and the left parenthesis.
Also, your table name really isn't "table", is it? That's a reserved word. Also, in order to diagnose your connection string, I would need to see it.

The count works. What doesnt work is connecting to another IP in my local network.

And no; "table" is a example.

My connection string as you request:

String constring = "server=192.168.100.171;database=bd;uid=user;password=pass;";

Replacing the IP with 127.0.0.1 works (I have a MySQL server working on my local machine too). Putting "localhost" also works (just tests, I know there is no difference). Putting my internal IP on the network also works which is 192.168.100.73 (this is very important and glad it works). I once again tried pinging to and from that 171 IP and it works. Just this C# that doesnt want to.

Here is something intresting I discovered with the command line:

Connecting to .73 works. Thats my IP
Connecting to .90 (non existing host) doesnt work and gives a 10060 error.
Connecting to .171 (the host with the MySQL server that I want to connect to) gives the same error message but a 10061 error
Connecting to .71 (a existing host but with no MySQL server installed) gives a 10060 error.

[riahc3]

String constring = "server=192.168.100.73;database=bd;uid=user;password=pass;";

Oh and before anyone says anything about the ";" after pass, I removed it and like that I can still connect to my database on my local PC.

[thines01]

Do you have Microsoft Query installed or any tool with which you can test the remote connection?
Your code might not be the problem. It could be your firewall.

[riahc3]

Do you have Microsoft Query installed or any tool with which you can test the remote connection?
Your code might not be the problem. It could be your firewall.

Never heard of "Microsoft Query". Where can I find it?

Thanks

[riahc3]

I dont think its a firewall as currently there are non running on that PC.

Im trying this now Windows to Windows (it was Windows to Linux) and still nothing.

The permission is set correctly with a user created to connect from anywhere....Still nothing.

[riahc3]

Tried with MySQL Workbench and cant either.....There is no firewall installed on the other PC (and mine doesnt have one either)

[riahc3]

OK, reverse works.

The PC I want to connect from, A, cannot connect to the PC I want to connect to, B.
But B can connect to A.

B runs XP, A runs 7.

Any more information? So strange....