differences to Classic ASP

C

Comotose

Member
Joined
Mar 8, 2010
Messages
23
Programming Experience
1-3
Hi folks. I hope y'all haven't gone bush for the weekend and left me to my own devices. I definitely can't be trusted on my own!

I'm trying to transition from Classic Asp to VB.NET and I have to confess some confusion when it comes to using a MySQL database.

Here is the initial connection code that works fine for me on a classic asp page:

VB.NET: 
' Open a connection to the database.
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=databasename; UID=username; PWD=password")

I realize there are lots of different types of connections, but as I will eventually be trying to convert existing asp pages to aspx, it would be good if I could stick as close as possible to what I'm already using.

Anyway, trying to get a valid connection in VB.NET terms, this is what I've got so far:

VB.NET: 
<head runat="server">
  <title>MySQL Connection Test</title>
    
  <script runat="server" language="VB">

  Sub Page_Load()
    
     Dim objConn As OleDbConnection
              
     objConn = New OleDbConnection("Driver={MySQL ODBC 5.1 Driver}; Server=localhost; Database=databasename; _
                   User=username; Password=password; Option=3;")
     objConn.Open()

     ' Move along folks; nothing to see here!
        
     objConn.Close()

  End Sub
        
    </script>   
</head>

As you can see I'm not trying to do anything with the database yet, other than to get an error free connection.

That code results in Exception Detail:
System.ArgumentException: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

Realizing that the error message is only providing an example I tried it anyway:

VB.NET: 
objConn = New OleDbConnection("Provider=SQLOLEDB; Driver={MySQL ODBC 5.1 Driver};Server=localhost; _
                        Database=dlguard_caos; User=cache7admin;Password=pWw447944b;Option=3;")

But that's also clearly wrong, and as you can see I'm now just guessing, so can somebody please point me in the right direction with the simplest possible solution. I can add more complexity as I progress.

ALSO...

What DLL, if any, do I need to put in the cgi-bin, and where do I get it? I've been dropping DLLs in there like chook feed!

TIA
 
Sort by date Sort by votes
Well I finally got it sorted out after a struggle to find a connection string that would work.

I tried using the string from System.Data.SQLClient (which works fine in my classic asp code) but kept getting invalid login errors, despite being absolutely positive that the credentials were correct. Maybe this is something to do with MySQL access/permissions on the host's web server. Anyway, the ODBC alternative seems to work OK.

I'll post the full code below in case it helps someone else who is just getting started with VB.NET and trying to work with a MySQL database.

But again, if you're using Visual Web Developer 2008 (or 2005) Express, don't waste your time trying to use Connector/Net from the MySQL site, which is what a lot of people will recommend you try. There's simply no simple way to integrate it into the free Express version of VWD2008. If I'm wrong on that point a CLEAR & DETAILED explanation would be most welcome, but I don't think I am. It's a point that there's an awful lot of confusion about around the various forums, because a lot of people who use the full version of Visual Studio are recommending Connector/Net without warning (or knowing?) that their instructions won't work with the lower-end free product.

So long as you enter your correct database credentials on the 'myConn.ConnectionString =' line this example should work fine, and you can use it as a basis to extend from there.

VB.NET: 
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data.Odbc" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

	<title>MySQL Database & ODBC Connection String</title>
    
	<script runat="server" language="VB">
        
	    Dim myConn As New OdbcConnection
	    Dim objCmd As OdbcCommand
	    Dim objRdr As OdbcDataReader
	    
	    Sub Page_Load()
	        
	        myConn.ConnectionString = "Driver={MySQL ODBC 5.1 Driver}; " _ 
		& "Server=localhost; Database=databasename; " _ 
		& "User=username; Password=mypassword; Option=3;"

	        objCmd = New OdbcCommand("SELECT * FROM tablename", myConn)

	        myConn.Open()

	        ' Just to prove it works...
	        objRdr = objCmd.ExecuteReader()
	        While objRdr.Read()
	            Response.Write(objRdr.Item("columnx") & " :::: " & objRdr.Item("columny") & "<br />")
	        End While

		objRdr.Close()
	        myConn.Close()
            
	    End Sub
        
	</script>

</head>

<body>

</body>

</html>

Comments and/or suggestions for improvement welcome.
 
Upvote 0 Downvote
Comotose said:
It may well be, but I'm still none the wiser. It won't integrate with Visual Web Developer Express which is what started me on the manual coding path.

Sorry, but I need an example more specific to my particular circumstances.
Click to expand...
I'd still use the Connector/Net provider rather than the generic OleDB/ODBC one. Here's first match for search phrase "vb.net connector/net": VBMySQL.com » VB.NET-MySQL Tutorials
 
Upvote 0 Downvote
JohnH said:
I'd still use the Connector/Net provider rather than the generic OleDB one. Here's first match for search phrase "vb.net connector/net": VBMySQL.com » VB.NET-MySQL Tutorials
Click to expand...

I don't follow your reasoning John.

Why would I persevere with a provider that won't integrate with my editor (VWD 2008 Express), in preference to one that is already integrated?

Yes, I'm aware of the claims for Connector/Net, such as:

"Connector/net is one of the most feature-rich and best performing .NET providers for MySQL that is currently available" and...

"One advantage Connector/NET provides over other solutions is its use of the native MySQL protocol: many other solutions wrap the MySQL C client library and suffer a performance loss as a result."

But I still must ask: to what advantage to me? I doubt very much that performance will be an issue with a database of a few thousand simple records at most.

I could be wrong, but it seems to me the series of articles you referenced is dealing exclusively with "Visual Basic Express", as opposed to "Visual Web Developer Express", and the development of a standalone application as opposed to a website which must be uploaded to someone else's server.

As I said, I did try to use Connector/Net manually coded into my test application, but I failed to get it to work -- no doubt directly attributable to my own experience.

If someone would care to convert my sample code in the earlier message so as to use Connector/Net, I'd be more than happy to try it and become a convert.

I'm not trying to be argumentative and I do appreciate your recommendation. Just trying to arrive at the best solution, all practicalities considered.

Believe me, I've done enough searching to know that combining Connector/Net with VB.NET, and particularly with Visual Web Developer Express, has caused a lot of frustration for .NET novices.

I'd be delighted to get it working and share the solution to others.
 
Upvote 0 Downvote
r3plica said:
also ConnectionStrings.com - Forgot that connection string? Get it here! will help you, specifically MySQL Connection String Samples - ConnectionStrings.com
Click to expand...

I consulted those references before and during my earlier failed attempts to use Connector/Net. I don't know exactly why I failed (I didn't keep that code when I got the ODBC version working) but I think there's a bit more to it than just the connection string.

As I said, doubtless due to my own experience, but I still don't have a solution that utilizes Connector/Net, and I do have one that works using ODBC.
 
Upvote 0 Downvote

Similar threads

J
  • Question
Clickonce - Server connection timed out
Replies
0
Views
1K
joefa
J
Deva
  • Question
Is it safe method to connect database in online?
Replies
4
Views
2K
Deva
Deva
D
  • Question
Option to copy data from remote mySQL through the office firewall
Replies
0
Views
1K
divjoy
D
J
  • Question
Question Connecting individual programs to MySQL
Replies
1
Views
8K
jmcilhinney
jmcilhinney
V
  • Question
Question Get SQL Server Instances and Databases For A Selected Instance
Replies
1
Views
4K
jmcilhinney
jmcilhinney

Share this page

Latest posts

Back
Top