[RESOLVED] [Microsoft][ODBC Driver Manager] Data source name not found

[Nightwalker83]

Hi,

I'm trying to correct to an online database however, I keep receiving the message:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

regardless of which language I use VB6 or C#. This is how I have written the connect string:

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=http://75.126.1.33: DATABASE=aaronspe_test; USER=****; PASSWORD=****; OPTION=2;";

What am I missing?

Thanks,

Nightwalker


The following is the working solution I have uploaded.

VB6Online.zip
VB6Online 2.zip

[jmcilhinney]

Have you installed the MySQL ODBC driver? The latest version is 5.1 but you can still get 3.51 if that's what you want.

[Nightwalker83]

Yes, I have downloaded and installed the package to the default location:

C:\Program Files\MySQL\Connector ODBC 5.2\Unicode

Although, running myodbc-installer.exe I can't read what is on the output because it it only appears for a few milliseconds.

Looking at a VB.NET example I downloaded it didn't seem to reference the file in the project but it didn't have the above error when run.

[jmcilhinney]

I don't really use MySQL but if you have installed version 5.2 then should you be specifying version 3.51 in the connection string? Does that make a difference?

Also, are you able to connect to the database using a management tool? You may be able to get a connection string from that.

[Nightwalker83]

Nope, I still receive the same error even those I correct the oversight ODBC version. I am able to connect to the database when it is on my machine using:

 cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\Customers.mdb"

when the database is an Access database! However, when I change it from an access database to an sql database using the appropriate provider it gives me the above error.

[Nightwalker83]

Well, reading the MYSQL online reference it doesn't seem to be a coding issue but I have read about the ODBC Administrator during my research and maybe that is the solution to my problem. I just need to find a tutorial on how to configure it properly.

Edit:

Here is another example.

[techgnome]

It's your server address... you want the IP address... but not the HTTP protocol. I've connected to MySQL (and SQLServers) all the time via IP address.... never have I put the HTTP on it... and it's always worked for me.

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=75.126.1.33: DATABASE=aaronspe_test; USER=****; PASSWORD=****; OPTION=2;";

Also, I'd recommend you install the MySQL .NET Connector... and use the MySQL namespace rather than the generic OLEDB one... your connection string would then simplify down even further as you wouldn't have to specify the driver.

-tg

[akhileshbc]

If you are connecting to a remote db in a web server, setup the Remote MySQL option by adding your IP address from which you wish to connect to the remote db. Because I believe webservers won't allow external connections until we explicitly add our IP to the whitelist. I believe, it's because of security purposes.

If you have cPanel, have a look at the "Remote MySQL" option under the "Database" section. Add your IP address(from which you will be going to run your app from) to the whitelist. Then you are good to go.

For details, have a look at this one: http://support.hostgator.com/article...abase-remotely

[Nightwalker83]

Ah ok, I haven't actually looked into the Remote MySQL option because I thought it was on for web-service to web-service connections. I will have another look into it.

Edit:

I have added the ip to the "Remote MySQL" list by when I run my application I receive:

Lost connection to MySql server at 'waiting for initial communication packet', system error: 0

I can't locate the solution for the above problem. All the results pertain to "reading the initial packet".

[akhileshbc]

I have tested it and it worked fine without any issues.

First of all, I added my IP address to the whitelist, ie. in the Remote MySQL option from cPanel. I used this to get my IP address: http://www.whoislookup.com/ip-address-lookup.php

Then created a test page in PHP(in my WAMP server) with the following code:

<?php

    $settings = array(
        'dbhost'        => '1xx.xx.xx.xxxx', // IP Address of my server
        'dbuser'        => 'my_db_username',
        'dbpwd'        => 'my_db_password',
        'dbname'        => 'my_db_name',
        'dbport'        => '3306'  // port number. If your hosting provider had set any other port then, you should use that in here. I think, 3306 is the default port.
    );
    
    $db = new mysqli($settings['dbhost'],$settings['dbuser'],$settings['dbpwd'],$settings['dbname'], $settings['dbport']); //connection to server
    if($db->connect_errno) die("Failed to connect to MySQL: (" . $db->connect_errno . ") " . $db->connect_error);
    
    if($res=$db->query("SELECT * FROM tblUsers LIMIT 1")){  // trying to fetch a record
      $r=$res->fetch_row();
      print_r($r);
    }
?>

Here, I have used the IP address of my server as host and the port number is the default 3306.

It worked without any issues !!

The only downside is, if you wish to use this in an app that you would distribute to several people, then you have to add their IPs to the white list. So, a better option is to create a webservice and your app would interact with the webservice. And this webservice would interact with the db.

Hope it helps

BTW, next time when you post replies, avoid editing the post and appending to it. Because I didn't saw your edit which you made after 30 minutes of your initial reply !

[Nightwalker83]

@Akhileshbc,

I'm looking for vb6 not php but thanks anyway!

[akhileshbc]

@Akhileshbc,

I'm looking for vb6 not php but thanks anyway!

I have just posted it as an example. Since I am in PHP most of the time, I did that quickly and provided you a tested code. If you are looking for VB6 code, then check this one: http://stackoverflow.com/questions/1...nswer-10024882

[Nightwalker83]

Thanks! Now I guess I can resolve this thread since I have several examples to try.

Edit:

You may have read in my other that I managed to get the project working which, is true. However, that was only for about 5 minutes I managed to stuff everything up and both the vb6 and your code in post #10 return the following error, which, I confused as to how to solve it although, I managed to solved the problem the first time I didn't pay attention to what I was doing.

This is the error I am receiving:

Access denied for user 'aaronspe_1234567'@'ppp118-210-179-40.lns20.adl6.internode.on.net' (using password: YES)

Edit:

The above error occurred because I forgot to include "aaronspe_" (the hostname) as part of the username and password such as it did when I created the database and user. I have uploaded the working project to the first post.

[Nightwalker83]

I have uploaded a new version of the project which includes the ability to backup the database to a mysql script.

[ragortue]

Thanks for that version. I can see that this is working quite well already.

[Deaux]

I have the same problem and I don't understand your solution?!
'Data source name not found and no default driver specified'