6 Replies - 940 Views - Last Post: 24 May 2013 - 11:25 AM Rate Topic: -----

#1 pharylon  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 83
  • Joined: 01-September 12

Can't Connect to mySQL Server

Posted 24 May 2013 - 09:10 AM

I'm having issues connecting to a mySQL server. The program runs fine when I connect to a MSSQL server I have (it's hosted remotely using Azure) but it won't connect to a mySQL server I have running on my local network.

Here's some sample code:

static void Main(string[] args)
        {
            string connectionString = "server=192.168.0.201;User Id=REDACTED;password=REDACTED;database=test";
            using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                connection.Open();
                string commandString = @"create table test_table_3 (id INT NOT NULL auto_increment PRIMARY KEY, household_num varchar(5));";
                var command = new System.Data.SqlClient.SqlCommand(commandString, connection);
                command.CommandType = System.Data.CommandType.Text;
                command.ExecuteNonQuery();
            }
        }


This code works with MSSQL, but not with mySQL, with the only difference being the connection string. In both cases I'm using the connection string auto-generated when I go to "Project -> Add New Data Source..." and go through the wizard.

The error happens at connection.Open() and this is what it throws:

Quote

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)


I know I'm missing something basic, but I'm not sure what. When I go through the wizard and hit the "test connection" button it works just fine and I can see the list of tables and make a Dataset (though I'm not using one right now) so I don't *think* it's an issue with the server, and I've turned off Windows Firewall on the local machine just to be sure.

Any suggestions?

This post has been edited by pharylon: 24 May 2013 - 09:14 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Can't Connect to mySQL Server

#2 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 355
  • View blog
  • Posts: 1,524
  • Joined: 08-April 09

Re: Can't Connect to mySQL Server

Posted 24 May 2013 - 09:14 AM

Do you have MySql .net connector
http://dev.mysql.com.../connector/net/

and here are connection strings for MySql
http://www.connectionstrings.com/mysql

and last suggestion, check if your mysql service is running.

This post has been edited by Michael26: 24 May 2013 - 09:17 AM

Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,875
  • Joined: 12-December 12

Re: Can't Connect to mySQL Server

Posted 24 May 2013 - 09:16 AM

You are using SqlConnection rather than the (imported) MySqlConnection object.
Was This Post Helpful? 0
  • +
  • -

#4 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 355
  • View blog
  • Posts: 1,524
  • Joined: 08-April 09

Re: Can't Connect to mySQL Server

Posted 24 May 2013 - 09:20 AM

Good one andrewsw, i just skimed over the code, didn't notice the sqlConnection error.
Was This Post Helpful? 0
  • +
  • -

#5 pharylon  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 83
  • Joined: 01-September 12

Re: Can't Connect to mySQL Server

Posted 24 May 2013 - 10:12 AM

Whoops! OK, well, I knew it was something basic. :\

However, as I play with figuring out how to get my application to support both MSSQL and mySQL, is there an easy way to create a connection that does both? Like, maybe in the constructor I'd tell it which one it was connecting to so I could do myConnection.Open() and stuff like that without having a lot of code duplication?
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,875
  • Joined: 12-December 12

Re: Can't Connect to mySQL Server

Posted 24 May 2013 - 10:20 AM

I recommend that you investigate provider factories. MSDN reference

It is possible to achieve this yourself by creating and instantiating your own class (or to create your own factory class) but if you follow the Provider Factory model then VS will do a lot of the grunt work for you.

Factory Model Overview (MSDN)

This post has been edited by andrewsw: 24 May 2013 - 10:30 AM

Was This Post Helpful? 0
  • +
  • -

#7 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4490
  • View blog
  • Posts: 7,822
  • Joined: 08-June 10

Re: Can't Connect to mySQL Server

Posted 24 May 2013 - 11:25 AM

Enterprise Library makes it pretty easy to do this as well.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1