Link to home
Start Free TrialLog in
Avatar of KennethSumerford1
KennethSumerford1Flag for United States of America

asked on

Unable to connect to any of the specified MySQL hosts --in VB.NET

I have tried about 2 hours to resolve this problem, which was not a problem about 10 days ago.  In VB.NET and VS 2008, the  VB.NET cannot find the MySQL DB.  Below is a connection string that does Not work but should work.  The program and DB are on my local PC running Windows 7.

Dim connStr As String = "Database=faith7;Data Source=localhost;User Id=faith7DB1;Password=kxxxxxxx"

    Try
        Dim dbconn1 As New MySqlConnection
         dbconn1.ConnectionString = connStr

          dbconn1.Open()

The error is always:  
  Unable to connect to any of the specified MySQL hosts.

How can I fix this?
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Hi.

Since this did work 10 days ago, what has changed? Have you loaded any new Microsoft updates or programs lately? I would check to see if the MySQL service is running from the command line or services management console.
Avatar of KennethSumerford1

ASKER

Who knows what Microsoft has uploaded to my PC to "make it better."

I can connect to the DB using  PhpAdmin but not in VS2008 and VB.NET.  It might be
a new port problem, but I do not know how to fix it.
You have PHPMyAdmin installed locally also? What about MySQL Workbench? Look at the configuration from either and see what port MySQL is listening on. The default is usually 3306. Additionally, the connection string does not look appropriate.

Check out this reference: http://www.connectionstrings.com/mysql

Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Note the differences to what you have above.
OK, give me about 10 minutes to try that.  It adds the port number, so that would probably help on my local PC.
Below is the new info, though I do Not know how to accurately determine the port
while IIS 7 is running with Visual Studio 2008.

connStr = Server=localhost;Port=3306;Database=faith7;Uid=faxxxx;Pwd=xxxxx;

| error = Unable to connect to any of the specified MySQL hosts.
Now I get the error msg below.

Input string was not in a correct format.

The input/connection string was:

 Dim connStr As String = "Server=localhost;Port=50003;Database=faith7;Uid=faixxxx;Pwd=kxxxx;"
check the configuration for mysql to see what ip addresses mysql is listening on.
I tried the IP address of my PC.

connStr = Server=192.168.1.xx;Port=50003;Database=faith7;Uid=faithxx;Pwd=kxxxx;

| error = Unable to connect to any of the specified MySQL hosts.
>>  check the configuration for mysql to see what ip addresses mysql is listening on. <<

IS that different than my regular IP address?  If so, how do I check that?
where is the 50003coming from? did you check the .ini (mysql configuration).
edit: if the ip address of your machine changed since 10 days ago and mysql is bound to a specific ip address this may be the issue.
From Firefox browser when I load the file that I am debugging in VS2008.

http://localhost:50003/VB-video-site/VideosShows/DB1test2012.aspx

Is this the php.ini file?  Here is some of it below.

; If mysqlnd is used: Number of cache slots for the internal result set cache
; http://php.net/mysqli.cache_size
mysqli.cache_size = 2000

; Default port number for mysqli_connect().  If unset, mysqli_connect() will use
; the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the
; compile-time value defined MYSQL_PORT (in that order).  Win32 will only look
; at MYSQL_PORT.
; http://php.net/mysqli.default-port
mysqli.default_port = 3306

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
; http://php.net/mysqli.default-socket
mysqli.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
; http://php.net/mysqli.default-host
mysqli.default_host =

; Default user for mysql_connect() (doesn't apply in safe mode).
; http://php.net/mysqli.default-user
mysqli.default_user =
Do you have any suggestions, or are you off this Website?
MySQL has what they call 'connectors'.  The two that you could use are the ODBC and the .NET connectors.  Listed here: http://www.mysql.com/products/connector/  You must have one of these to connect to a MySQL server.
Yes, that looks like a 'php.ini' file.  Are you using PHP also on that server to connect to MySQL?
I'm confused.  I did install the .NET MySQL connector.

>>    Are you using PHP also on that server to connect to MySQL? <<
I do not know what this means. I am running Visual Studio 2008 and
MS IIS to use VB.NET and MySQL, which worked fine about 10 days ago.
PHP is a different programming language.  You probably couldn't use Visual Studio to edit it properly because it requires plain text.  But it had to come from somewhere.  Is anyone else working on that server?  It is very odd that 'php.ini' code is in an ASPX file.

For general info:  ASP, ASP.NET, and PHP can coexist on an IIS web server and access the same MySQL database.  I have them running on this computer though I'm using a simplified ASP.NET program.  I have the ASP and the ASP.NET using the MySQL ODBC connector.  PHP has it's own built-in MySQL connector.
Yeah, I know that VB.NET and PHP are different languages.  I have at least 10 years experience in VB but only about 10 months in PHP.  

>>  It is very odd that 'php.ini' code is in an ASPX file. << 
They are Not in a ASPX file.

>>  I'm using a simplified ASP.NET program.  I have the ASP and the ASP.NET using the MySQL ODBC connector.  PHP has it's own built-in MySQL connector. <<
I still do Not know how to fix this problem.  Something has probably changed during the
last 10 days but all I did recently was add about 3 new Usernames and passwords to
the MySQL DB, but I cannot use them to connect to it.
You're confusing me a bit.  How did you add the new usernames?

In MySQL, the user names have connections associated with them to limit the source of the connection.  They can be 'localhost', '%' for 'anyhost' wildcard meaning any IP address, or they can be a specific IP address.  'localhost' means they have to be on the same computer that the MySQL server is on.

http://dev.mysql.com/doc/refman/5.1/en/adding-users.html
I used anyhost, but let me check it.
Did you also give those users privileges on the database you want them to access?
Yes, Any Host  for the new username and password for the faith7 MySQL DB.
How are you checking it?  You must be connecting thru something.
I put in localhost again.

connStr =  "Server=localhost;Port=3306;Database=faith7;Uid=faixxx;Pwd=kxxxx; "

  error = Unable to connect to any of the specified MySQL hosts.
I checked the screen capture when I set it up in PhpAdmin on my PC.
The you do have PHP running on your computer.  And it is connecting.  Try installing the ODBC connector to use with your .NET program.  Some people have had problems with the ADO.NET connector.  This page http://www.connectionstrings.com/mysql has all of the MySQL connection strings that I know about.  The ODBC strings are at the bottom of the page.
Is this what you are talking about?

"ODBC .NET Data Provider

The ODBC .NET Data Provider is an add-on component to the Microsoft .NET Framework Software Development Kit (SDK). It provides access to native ODBC drivers the same way that the OLE DB .NET Data Provider provides access to native OLE DB Providers. The ODBC .NET Data Provider is intended to work with all compliant ODBC drivers, but the Microsoft site states that it has only been tested with the Microsoft SQL ODBC Driver, Microsoft ODBC driver for Oracle, and the Microsoft Jet ODBC driver. "
ASKER CERTIFIED SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your help.  This seems to be a re-occuring problem between VB.NET, MS IIS and MySQL DB.