Chuck Conway

In The craft

Connecting .NET (VB) to Mysql

January 14, 2004 · 4 minute read

This is an update of an older version I wrote pertaining to ByteFX version .65 this new version is for ByteFX version .73.

Introduction

The purpose for writing this tutorial is to help out developers trying to connect to the MySql database, bypassing hours of frustration.

What’s needed?

ASP.Net requires a data provider to connect to different flavors of databases, in this case ByteFX.Data (other data providers are available for a fee) is used with MySql, which is an open source project at http://www.sourceforge.net/ (There are other methods to connecting .NET to MySql, see links at the bottom of the page for more information). You will need to download the 0.73 binary build. Next I am assuming .NET version 1.0 or 1.1(available from Microsoft) is installed on your system (preferably 1.1). Finally I am assuming that MySql is installed on your system, if not it can be downloaded here. This tutorial has been tested with 3.23 and 4.0.16 on Windows 2000 server and Windows XP Professional using .Net 1.1.

Setting up your server

I am assuming a virtual application is setup on IIS if not this link will explain how. Once a virtual application is created, open up the virtual application directory and create a directory in the root of the application called bin. Unzip all the contents of the into (the zip file should be named “ByteFX.Data.73-2.zip”)the bin directory.

Setting up the Code

Now that the virtual application is created and MySql is installed and running (for more information on installing an administering MySql please refer to Kevin Yank’s superb article on PHP and MySql) it’s now time to write some code. First, the MySql .Net Native Provider needs to be imported, this is done with this line of code: Code:

<%@ import Namespace="ByteFX.Data.MySQLClient" %>

This is placed after the page language declaration. Next, the connection string must be configured. This is done by replacing the values example connection string below. Replace the pertinent information(user id, pwd, database) with values from your system. Most likely the “data source” attribute will not change. Here is the connection string:

Code:

 data source=localhost;user id=user;pwd=secret;database=website

The remaining tasks are declaring the MySqlConnection and MySqlCommand functions then passing it some SQL. Here is an example:

Code:

<%@ Page Language="vb" Debug="true" %>
<%@ import Namespace="ByteFX.Data.MySQLClient" %>
<script runat="server">
Dim strMysql as String = "data source=localhost;user id=user;pwd=secret;database=website"
Dim conMysql as MySqlConnection = new MysqlConnection(strMysql)
Sub Page_Load( s As Object, e As EventArgs )

Dim strInsert as String
Dim cmdInsert as MySQlCommand

strInsert = "INSERT INTO PHOTOS(TITLE, DESCRIPTION, FILENAME, DATETAKEN)
VALUES (’Johns’,'Myhouse’,'5405′,’06/07/2004′)"

cmdInsert = new MySQlCommand( strInsert, conMySql)

lblTest.Text="Data successfully entered!"

conMySql.Open()
cmdInsert.ExecuteNonQuery()
conMySql.Close()
End Sub
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<asp:label ID="lblTest" runat="server"></asp:label>
<!– Insert content here –>
</form>
</body>
</html>

Here is the SQL:

SQL:

CREATE TABLE PHOTOS(
PHOTOS_ID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
TITLE TEXT NOT NULL,
DESCRIPTION TEXT NOT NULL,
FILENAME TEXT NOT NULL,
DATETAKEN TEXT NOT NULL);

More Advanced Database Insertion

After creating a connection and inserting data into the database, data must be able to pass from the web page to the database. This is done by using .Net’s web control. A partial example of code that I used to insert data into MySql with Textboxes:

Code:

<%@ Page Language="vb" Debug="true" %>
<%@ import Namespace="ByteFX.Data.MySQLClient" %>
<script runat="server">
Sub Page_Load( s As Object, e As EventArgs )

End Sub

Sub Button_Click( s As Object, e As EventArgs )

Dim strMysql as String = “data source=localhost;user id=user;pwd=secret;database=website”
Dim conMysql as MySqlConnection = new MysqlConnection(strMysql)
Dim cmdInsert as MySqlCommand

Dim strInsert, strFilename as String

conMySql = new MySqlConnection(strMySQL)

strFilename = “my.jpg”
strInsert = “INSERT INTO PHOTOS(TITLE, DESCRIPTION, FILENAME, DATETAKEN) VALUES (@TITLE, @DESCRIPTION,
@FILENAME, @DATETAKEN)”
cmdInsert = new MySqlCommand( strInsert, conMySql)

cmdInsert.Parameters.Add( “@TITLE”, ctype(txtTitle.text,string))
cmdInsert.Parameters.Add( “@DESCRIPTION”, txtdescript.text )
cmdInsert.Parameters.Add( “@FILENAME”, strFilename )
cmdInsert.Parameters.Add( “@DATETAKEN”, txtDate.Text )

conMySql.Open()
cmdInsert.ExecuteNonQuery()
conMySql.Close()

lbltest.text="Data entered!”

txtTitle.text = “”
txtDescript.text = “”
txtDate.Text = “”

End sub
</script>

Here is the HTML:

HTML:

<html>
<head>
<title>Image Upload</title>
</head>

<body>
<form runat="server">

Title
<asp:TextBox id="txtTitle" runat="server"></asp:TextBox>

Description
<asp:TextBox id="txtdescript" runat="server" height="70" width="240"></asp:TextBox>

Genre
<asp:TextBox id="txtGenre" runat="server"></asp:TextBox>

Date Taken
<asp:TextBox id="txtDate" runat="server"></asp:TextBox>

<asp:Button id="Button1" onclick="Button_Click" Runat="Server" Text="Upload File!"></asp:Button>

<asp:Label id="lblError" runat="server"></asp:Label>

<asp:Label id="lbltest" runat="server"></asp:Label>**
</form>
</body>

</html>

Known Issues

At the time of the writing there are known issues with prior versions of the ByteFX.Data MySql data provider. Version 0.70 and above were complied with .Net 1.1 and have issues running on a system with .Net 1.0 installed. For more information visit the MySql .Net Native Provider support forum.

Helpful Resources

Here are some resources that are helpful when trying deploying an application in .Net with the MySql Database:

MySql Documentation ASP.Net Forum SoundForge MySql .Net Project