VBMySQL.com

The VB.NET-MySQL Tutorial – Part 4

Purchase Tramadol Where To Get Diflucan Effects Generic Acomplia Free Samples Where To Get Soma Faq Order Inderal Lincoln Stromectol Effects Where Order Doxycycline NoRX Order Aldactone Tallahassee Lotrisone Dosage Price For Nexium Shop Neurontin Shipping Where Order Diflucan France Soma Tramadol Description Purchase Elimite Atlanta Flomax NoRX Generic Tramadol Horny Goat Weed Where Order Flomax Description Natural Seroquel Horny Goat Weed Where To Get Lotrisone Mexico Buy Toprol XL Tallahassee Order Tramadol Austin Nizoral NoRX Herbal Flomax Weight Where Order Ultram Drug Information Where Order Propecia Testimonials Nexium Delivery Price For Diflucan Swaziland Cytotec Tablets Where Order Seroquel Age Where Order Propecia High Doses Price For Clomid Pharmacy Where To Get Lasix Description Purchase Zyban Lincoln Purchase Toprol XL Annapolis Price For Aricept High Doses Buy Female Viagra BatonRouge Price For Zithromax Spain Where To Get Doxycycline Pharmacy Price For Antabuse For Add Where Order Clomid RX Purchase Prevacid DesMoines Natural Cipro Mail Buy Accutane LittleRock Discount Female Viagra Swaziland Generic Lotrisone Sacramento Nolvadex Forum Tramadol Price For Penisole Dosing Herbal Elimite Faq

Introduction

This article is part four of a series of articles on how to create a simple Windows application using Visual Basic.NET and MySQL. In previous articles we have designed our database, installed the software we need to create our application, and designed a simple login form. The previous three articles are mandatory reading before starting this article.

In this article I will demonstrate how to design queries, build forms, bind data to controls, and perform queries using the MySqlCommand, MySqlDataAdapter, and DataTable classes. Later I will demonstrate how to perform parameterized queries and update data in the MySQL tables.

Adding Users, Status Messages, and Events to the Database

Before we query the database to get the current status of our users, we should add extra users to the system so we have some context for our query. In addition, we will want to add some pre-defined status messages and a few events to the system.

To add the data, I will be using the MySQL Query Browser. Once the Query Browser is open, double-click on the user table, then click the Execute button. Click the Edit button at the bottom of the result set area, and add two new users to the database (see Article Two for more information). The user details are not vital, just set the administrator and deleted fields to False.

Once you have added two users, double-click on the status table and click Execute. We need to add some pre-defined status messages that our users can choose from. Click the Edit button and add three messages. Leave the user_id NULL, and set the deleted field to False. In my case I will be using the following three status messages:

Gone to Lunch
Gone for the Day
In Meeting

Once we have created users and status messages, we can add some events to the database. Double-click on the event table and click the Execute button.

The event table is the core of the tracking application and brings together the user and status tables to allow us to track the status of all our users. When a user signs in or out, the event is tracked with the appropriate user id, status message, and time. In addition, we track who created the event, in case a secretary creates an event on behalf of a user.

Let’s create a few events that can later be used when querying the database:

event_id	user_id	message_id	timestamp	status	creator
1	1	1	2005-01-17 11:50:00	Out	1
2	2	1	2005-01-17 11:57:00	Out	2
3	3	1	2005-01-17 11:59:00	Out	3
4	1	3	2005-01-17 13:30:00	In	1
5	2	3	2005-01-17 13:30:00	In	2
6	3	3	2005-01-17 13:30:00	In	3
7	2	2	2005-01-17 16:00:00	Out	2
8	3	2	2005-01-17 16:10:00	Out	3

These rows establish a chain of events of all three users going to lunch, returning to start a meeting, and then users 2 and 3 going home for the day.

Editing a Table Definition

While we are looking at our data, you may wonder if a user should be able to sign in or out with no message at all. This is actually a desirable feature, but our current table definition does not allow it because message_id is defined as NOT NULL.

To edit a table definition, right click on the table in the database browser and choose the Edit Table option.

Table Editor

As you can see, there is a check mark in the NOT NULL column for message_id. Click the check mark to remove the NOT NULL constraint and click the Apply Changes button to modify the table definition.

With the table definition modified, add a final row to the event table for user 1 with a NULL message_id, a timestamp of 2005-01-17 16:00:00, and a status of In. This shows that the meeting ended, but that user 1 has not signed out and has no status to report.

Building a Query

Now that our data is loaded into the database, we need to develop queries that can be used to retrieve the data in a format that is meaningful to our users. Lets begin with a simple SELECT * query of the event table, generated by double-clicking on the table in the MySQL Query Browser:

Select * Query

This does not present very useful information to an end user, so lets start by removing columns that our users do not benefit from:

SELECT e.user_id, e.message_id, e.timestamp, e.status FROM event e

user_id	message_id	timestamp	status	event_id
1	1	2005-01-17 11:50:00	Out	1
2	1	2005-01-17 11:57:00	Out	2
3	1	2005-01-17 11:59:00	Out	3
1	3	2005-01-17 13:30:00	In	4
2	3	2005-01-17 13:30:00	In	5
3	3	2005-01-17 13:30:00	In	6
2	2	2005-01-17 16:00:00	Out	7
3	2	2005-01-17 16:10:00	Out	8
1	NULL	2005-01-17 16:00:00	In	9

This removed the event_id and creator columns, which are not of interest to an end user. Note that the event table is represented by an alias of e. This alias is then used as a shorthand when listing the columns to be queried. For a video demonstration on how to select individual columns, see http://www.mysql.com/products/query-browser/tutorials/build_queries.html (Macromedia Flash required).

Joining Tables With an Inner Join

While the previous query is a bit more readable because it eliminates unnecessary rows, most end users will not like to identify their co-workers by number. The names of our users are stored in the user table, and can be used in our query by joining the event and user tables together. To join two tables together, we have to indicate to MySQL which columns can be used to relate the tables together. In this case, the user_id column of the user table is related to the user_id column of the event table.

In addition to establishing a relationship we need to select columns from the user table:

SELECT u.lastname, u.firstname, e.message_id, e.timestamp, e.status
FROM event e, user u
WHERE e.user_id = u.user_id

lastname	firstname	message_id	timestamp	status
Hillyer	Mike	1	2005-01-17 11:50:00	Out
Jones	Tom	1	2005-01-17 11:57:00	Out
Johnson	Julie	1	2005-01-17 11:59:00	Out
Hillyer	Mike	3	2005-01-17 13:30:00	In
Jones	Tom	3	2005-01-17 13:30:00	In
Johnson	Julie	3	2005-01-17 13:30:00	In
Jones	Tom	2	2005-01-17 16:00:00	Out
Johnson	Julie	2	2005-01-17 16:10:00	Out
Hillyer	Mike	NULL	2005-01-17 16:00:00	In

In the SELECT clause we added references to the lastname and firstname columns of the user table (and used the alias u to refer to the user table). In the FROM clause we added a reference to the user table. Finally, we added a WHERE clause to indicate that the user_id columns of both tables are related. With this relationship established, MySQL returned the first and last name of each user.

Concatenating Data

Many users are accustomed to seeing names displayed in a format of Lastname, Firstname. This formatting can be achieved by concatenating the fields within the query using the CONCAT() function:

SELECT CONCAT(u.lastname, ', ', u.firstname) AS name, e.message_id,
        e.timestamp, e.status
FROM event e, user u
WHERE e.user_id = u.user_id

name	message_id	timestamp	status
Hillyer, Mike	1	2005-01-17 11:50:00	Out
Jones, Tom	1	2005-01-17 11:57:00	Out
Johnson, Julie	1	2005-01-17 11:59:00	Out
Hillyer, Mike	3	2005-01-17 13:30:00	In
Jones, Tom	3	2005-01-17 13:30:00	In
Johnson, Julie	3	2005-01-17 13:30:00	In
Jones, Tom	2	2005-01-17 16:00:00	Out
Johnson, Julie	2	2005-01-17 16:10:00	Out
Hillyer, Mike	NULL	2005-01-17 16:00:00	In

Joining Tables with a Left Join

We can also join the status and event tables together to provide actual status messages instead of just message_id numbers. However, if we use the syntax we used previously we will get incorrect results:

SELECT CONCAT(u.lastname, ', ', u.firstname) AS name, s.message,
        e.timestamp, e.status
FROM event e, user u, status s
WHERE e.user_id = u.user_id
        AND e.message_id = s.message_id

name	message	timestamp	status
Hillyer, Mike	Gone to Lunch	2005-01-17 11:50:00	Out
Jones, Tom	Gone to Lunch	2005-01-17 11:57:00	Out
Johnson, Julie	Gone to Lunch	2005-01-17 11:59:00	Out
Hillyer, Mike	In Meeting	2005-01-17 13:30:00	In
Jones, Tom	In Meeting	2005-01-17 13:30:00	In
Johnson, Julie	In Meeting	2005-01-17 13:30:00	In
Jones, Tom	Gone For The Day	2005-01-17 16:00:00	Out
Johnson, Julie	Gone For The Day	2005-01-17 16:10:00	Out

While our data is more readable with the actual status messages displayed, we are now missing our final row, which has a message_id of NULL. The reason for this is that the relationship between the event and status tables is based on equality, and since the status table has no rows with NULL for a message_id, the NULL row from the event table is thrown out. This can be corrected by using a LEFT JOIN instead of an INNER JOIN.

In a LEFT JOIN, a single row is returned for every row contained in the left-hand table of the LEFT JOIN. When there is a matching row in the right-hand table it is returned, otherwise a row with all values set to NULL is returned instead. In our case, we will place the event table on the left and the status table on the right of the LEFT JOIN clause:

SELECT CONCAT(u.lastname, ', ', u.firstname) AS name, s.message, e.timestamp, e.status
FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u
WHERE e.user_id = u.user_id

name	message	timestamp	status
Hillyer, Mike	Gone to Lunch	2005-01-17 11:50:00	Out
Hillyer, Mike	In Meeting	2005-01-17 13:30:00	In
Hillyer, Mike	NULL	2005-01-17 15:19:49	In
Jones, Tom	Gone to Lunch	2005-01-17 11:57:00	Out
Jones, Tom	In Meeting	2005-01-17 13:30:00	In
Jones, Tom	Gone For The Day	2005-01-17 16:00:00	Out
Johnson, Julie	Gone to Lunch	2005-01-17 11:59:00	Out
Johnson, Julie	In Meeting	2005-01-17 13:30:00	In
Johnson, Julie	Gone For The Day	2005-01-17 16:10:00	Out

Note that our NULL row has returned. When we use a LEFT JOIN, the relationship information is moved from the WHERE clause to the FROM clause, using the ON keyword.

Formatting Dates

While the query is getting quite readable, the date could use improvement. We can format date columns using the DATE_FORMAT() function. The function is used by passing a date value and a format string:

SELECT CONCAT(u.lastname, ', ', u.firstname) AS name, s.message, DATE_FORMAT(e.timestamp,'%b %d %Y - %r'), e.status
FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u
WHERE e.user_id = u.user_id

name	message	DATE_FORMAT(e.timestamp,’%b %d %Y – %r’)	status
Hillyer, Mike	Gone to Lunch	Jan 17 2005 – 11:50:00 AM	Out
Hillyer, Mike	In Meeting	Jan 17 2005 – 01:30:00 PM	In
Hillyer, Mike		Jan 17 2005 – 03:19:49 PM	In
Jones, Tom	Gone to Lunch	Jan 17 2005 – 11:57:00 AM	Out
Jones, Tom	In Meeting	Jan 17 2005 – 01:30:00 PM	In
Jones, Tom	Gone For The Day	Jan 17 2005 – 04:00:00 PM	Out
Johnson, Julie	Gone to Lunch	Jan 17 2005 – 11:59:00 AM	Out
Johnson, Julie	In Meeting	Jan 17 2005 – 01:30:00 PM	In
Johnson, Julie	Gone For The Day	Jan 17 2005 – 04:10:00 PM	Out

Of course, most users cannot easily interpret DATE_FORMAT(e.timestamp,’%b %d %Y – %r’), so we can assign an alias to make it easier. While we are at it, we can assign aliases to the remaining columns to make the data easier to interpret:

SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status
FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u
WHERE e.user_id = u.user_id

Creating a Subquery

Our end users will not be interested in every single event that has occurred, but will instead want to know the current status of each user. To gather this information we must determine the latest event_id value from the event table for each user.

We can determine the highest event_id value by using the MAX() function:

SELECT MAX(event_id)
FROM event e

MAX(event_id)
9

This does not do us much good because we need the maximum value on a per-user basis. Aggregate functions like MAX(), MIN(), AVG(), and SUM() can be used with a GROUP BY clause to change the data that is aggregated:

SELECT e.user_id, MAX(e.event_id)
FROM event e
GROUP BY e.user_id

MAX(e.event_id)
9
7
8

Now we have the maximum event_id value for each user_id in the event table.

user_id	MAX(e.event_id)
1	9
2	7
3	8

We can now use this as part of a subquery to determine the current status of each user.

A subquery is a query within a query, contained within parenthesis. Subqueries can be used in place of tables in a FROM clause, or to generate a list of values for use in areas such as a WHERE clause. There is an excellent discussion of subqueries at the MySQL web site at http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html.

For the purposes of our subquery we will remove the user_id reference, leaving us with a list of maximum event_id values:

SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status
FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u
WHERE e.user_id = u.user_id
        AND event_id IN
       (
                SELECT MAX(e.event_id)
                FROM event e
                GROUP BY e.user_id
        )

Name	Message	DateTime	Status
Hillyer, Mike	NULL	2005-01-17 15:19:49	In
Jones, Tom	Gone For The Day	2005-01-17 16:00:00	Out
Johnson, Julie	Gone For The Day	2005-01-17 16:10:00	Out

Ordering Query Results

One final tweak we can perform on this query has to do with ordering the data. Our users are accustomed to reading information in alphabetical order, and we should aim to provide our data in the order they are expecting. We can sort the data returned from our query by using the ORDER BY clause. The ORDER BY clause can take multiple column names for sorting, along with the optional DESC keyword to indicate that data should be sorted in descending order. In our case we will sort by the name column:

SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status
FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u
WHERE e.user_id = u.user_id
        AND event_id IN
       (
                SELECT MAX(e.event_id)
                FROM event e
                GROUP BY e.user_id
       )
ORDER BY name

Name	Message	DateTime	Status
Hillyer, Mike	NULL	2005-01-17 15:19:49	In
Johnson, Julie	Gone For The Day	2005-01-17 16:10:00	Out
Jones, Tom	Gone For The Day	2005-01-17 16:00:00	Out

This query will now form the basis of our status form.

Creating a Status Form

Now that we have created some sample data and a query that can be used to show the current status of our users, we can start building a new form to display the query.

Start VB.NET and load the In-Out project. Right-click on the In-Out project name in the Solution Explorer and choose the Add > Windows Form… option. An Add New Item dialog will appear with the Windows Form template selected. Change the name of the form to frmMain.vb (we use frmMain instead of frmStatus because the is the primary form in our application) and click the Add button. Once Visual Studio successfully creates the new form, you should be presented with a new blank form to work with.

Designing the Status Form

In our initial version, this form will have three controls, a datagrid to display query results and two buttons to refresh the datagrid and update our status. First we will add a DataGridView control to our form; find the datagridview in the left-hand toolbox, click it, and then drag the shape you want the datagridview to be on your form (aim to have the datagridview occupy the bottom 85% of the form).

In the properties of the datagrid, change the name of the datagrid to dgvStatus, and while setting properties, set the text property of the form to In-Out – Status View.

In addition, add two buttons named cmdRefresh and cmdUpdate, and set their text properties to Refresh and Update Status, respectively.

Creating a Connection String Property

Our Connector/NET connection string was created in frmLogin, and we will need to pass it to frmMain when we change forms so that we can continue to connect to MySQL. We will do this by creating a public property in frmMain to store the connection string.

Click the View Code button to display the source code for frmMain.vb:

View Code Button

The View Code button is the highlighted button located at the top of the Solution Explorer.

At first this should be all that is shown in the code view:

Public Class frmMain

End Class

Within the class definition, type the following:

Public Class frmMain
        Private myConnString As String
End Class

This string will store the connection string generated by frmLogin. The Private keyword means that other forms cannot access or modify the connection string directly.

To allow access to the connection string for writing, we create a Property. Type Public WriteOnly Property connString As String after the string definition, and the following will be filled in for you:

Public WriteOnly Property connectionString() As String
        Set(ByVal value As String)

        End Set
End Property

This is a code template for a write only property, meaning an external form can assign the connection string, but the connection string cannot be read externally once assigned. Within the Set block we will add a single line of code:

Public Class frmMain
        Private myConnString As String
    Public WriteOnly Property connectionString() As String
                Set(ByVal value As String)
                        myConnString = value
                End Set
        End Property
End Class

Our form can now have a connection string assigned to it. Before we can assign a connection string, we first move the connection frmLogin connection string to a separate variable named myConnString:

        Dim myConnString As String

        myConnString = "server=" & txtServer.Text & ";" _
                 & "user id=" & txtUsername.Text & ";" _
                 & "password=" & txtPassword.Text & ";" _
                 & "database=in_out"

        conn.ConnectionString = myConnString

Instantiating and Showing the Form

Now that we can assign a connection string to frmMain, we need to update the code in frmLogin for a successful login. Instead of showing a message box congratulating the user on a successful login, we will want to create an instance of frmMain, pass the connection string to it, and then close frmLogin.

First we need to change the Shutdown mode of our application. By default an application closes when its startup form closes, but in our case we will be closing frmLogin early on and this will not work (there are alternatives such as having frmMain call frmLogin at startup and closing it upon a successful login). Choose the Properties option of the Project menu and make sure the Application tab is active.

Change the Shutdown mode from When startup form closes to When last form closes and close the properties window. Our application will now wait until the last form closes before ending. We need to make sure that all forms are closed when we are finished with them and not just hidden. One hidden but not closed form could leave our application running when the end user thinks the application has terminated.

Here is the new TRY – CATCH block for the Login button of our login form:

Try
        conn.Open()
        conn.Close()
        Dim mainForm As New frmMain
        mainForm.connectionString = myConnString
        mainForm.Show()
        Me.Hide()
        Me.Close()
Catch myerror As MySqlException
        MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        conn.Dispose()
End Try

First we open and close the connection to allow for an error to trigger the CATCH block. If we are still in the TRY block we instantiate the frmMain class and assign the myConnString variable.

Once the connectionString property is set, we show the main form, then hide and close the login form.

Querying Data from VB.NET

We have already dealt with the MySqlConnection class when creating our login form, and we will now be introduced to three additional classes: MySqlCommand, MySqlDataAdapter and DataTable.

The MySqlCommand Class

The MySqlCommand class contains the query or statement we will be sending to MySQL. The MySqlCommand object helps with performance by allowing us to build queries using parameters when our queries contain static and dynamic elements. After our query is created in the MySqlCommand object, we will pass the MySqlCommand object to the MySqlDataAdapter object for execution.

The MySqlDataAdapter Class

The MySqlDataAdapter class is used as an adapter between the MySqlConnection class and the DataTable class. The MySqlDataAdapter can query a database, then load the resulting information into a DataTable. It can later update the database with changes that have occurred in the DataTable. The MySqlDataAdapter is what enables us to bind data to a control on our form so that the control is automatically filled with our data.

The DataTable Class

The DataTable class is used to hold the contents of a single query. It is filled by the MySqlDataAdapter and can then be modified or bound to a control on your form.

Data Binding vs. Manual Data Loading

VB.NET provides data binding as a way to easily populate components on a form by linking the components directly to the data. With data binding we can quickly and easily populate a form of controls, and any changes we make to the data in the components can easily be applied back to the database.

While data binding allows for fast development, I often find that there is a greater degree of control available when I read the data and assign it to a form’s controls manually. That being said, I am first going to demonstrate data binding with the DataGridView class, and I will demonstrate manual data loading in a later article.

Adding References and Import Statements

The DataTable class is not part of the Connector/NET library but is part of the System.XML assembly. Because of this we need to add a new reference to our project. Choose the Add Reference… option of the Project menu. In the .NET tab, scroll down and choose the System.Xml.dll component and click the OK button.

To make it easier to work with our classes, lets add a pair of Imports statements to the top of our form code:

Imports MySql.Data.MySqlClient
Imports System.Data

As noted previously, these allow us to reference the MySqlDataAdapter without having to use the MySql.Data.MySqlClient.MySqlDataAdapter syntax.

Declaring Our Objects

We first need to declare (and instantiate) our database objects. This will be done within the Click event of the Refresh button, so start by double-clicking on the Refresh button to create a sub for the click event, then add this code:

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
        Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim myData As New DataTable
        Dim SQL As String
End Sub

We create a MySqlConnection to connect to the database, a MySqlCommand object to hold our query, a MySqlDataAdapter to run our query and load the data into a DataTable, and a SQL string to store our query. We instantiate the objects by using the New keyword so that we can use them in our code (consider a class to be a blueprint, and an object to be the home that is built from the blueprint). For the purpose of this tutorial, consider a class that is uninstantiated to be unusable (this is not entirely true, but we’ll get to that later).

Entering Our Query

Once we have declared our objects and variables, we can enter the SQL query we built earlier into the SQL string we declared:

SQL = "SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, " _
        & "DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status " _
        & "FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u " _
        & "WHERE(e.user_id = u.user_id) " _
        & "AND event_id IN( " _
        & "SELECT MAX(e.event_id) " _
        & "FROM event e " _
        & "GROUP BY e.user_id) " _
        & "ORDER BY name"

The underscore (_) character tells the compiler that the SQL = line is being continued on the next line, allowing us to keep the code readable. The ampersand (&) character is used to join the individual string together on each line. In the end SQL is filled with our query, and we still have neat, readable code (as opposed to if we had placed the entire query on a single line, requiring a lot of horizontal scrolling). In general I try to avoid having code wider than 80 characters, making my code easy to read and easy to print out.

Opening the Connection

Before we can query the database, we need to open our connection to the database by using the MySqlConnection.Open method. Before we can open the connection, we need to specify the connection string (stored in the MyConnString property as you recall):

conn.ConnectionString = myConnString

Try
        conn.Open()
Catch myerror As MySqlException
        MessageBox.Show("Error Connecting to Database: " & myerror.Message)
End Try

Once again, the opening of the connection is not guaranteed to work, so we place it within a TRY – CATCH block.

Configuring the MySqlCommand Object

The next step in the query process is to configure the command object. For a basic, static query all we need to do is assign the query text and tell the command object which connection object to use:

myCommand.Connection = conn
myCommand.CommandText = SQL

Our command is now ready to be passed to the MySqlDataAdapter object.

Using the MySqlDataAdapter Object

Once we have configured the connection object, opened the connection, and configured the command object, we are ready to perform the actual query. First we tell the MySqlDataAdapter object which command object to use to perform the query:

myAdapter.SelectCommand = myCommand

That is all the MySqlDataAdapter object needs to know to query the database. We perform the query by using the Fill method of the object, and we pass the name of our DataTable object so the adapter has something to place the query results into:

myAdapter.Fill(myData)

The results of our query are now located within the myData object (of the DataTable class). This can then be used to bind to the DataGridView control on our form.

Binding the Results

Now that our data is locally stored in memory, we can bind the data to our DataGridView control so that our users can see the data:

dgvStatus.DataSource = myData

That it, one line of code and our data is now visible to the user:

Bound data

You might notice that the timestamp column is a little truncated. This is caused by each column being the exact same width (notice the status column has far too much room). We can fix this by instructing the DataGridView to resize the columns after loading the data so that each column has the proper width:

dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

The command is a bit verbose, but the auto complete feature of Visual Studio means you actually have little typing to do, and you can select the proper option from a drop-down list. The results are much nicer:

Auto-sized

Error Catching

We want to also catch errors in the code that does the query, so we will place the preceding code in a TRY – CATCH block. Of course, we don’t want to try any of this if the attempt to open the connection fails, so we will actually embed this TRY – CATCH block within the previous one. Here is the entire procedure:

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
                Dim conn As New MySqlConnection
                Dim myCommand As New MySqlCommand
                Dim myAdapter As New MySqlDataAdapter
                Dim myData As New DataTable
                Dim SQL As String

                SQL = "SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, " _
       & "DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status " _
                 & "FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u " _
                 & "WHERE(e.user_id = u.user_id) " _
                 & "AND event_id IN( " _
                 & "SELECT MAX(e.event_id) " _
                 & "FROM event e " _
                 & "GROUP BY e.user_id) " _
                 & "ORDER BY name"

                conn.ConnectionString = myConnString

                Try
                        conn.Open()

                        Try
                                myCommand.Connection = conn
                                myCommand.CommandText = SQL

                                myAdapter.SelectCommand = myCommand
                                myAdapter.Fill(myData)

                                dgvStatus.DataSource = myData
                                dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                        Catch myerror As MySqlException
                                MsgBox("There was an error reading from the database: " & myerror.Message)
                        End Try
                Catch myerror As MySqlException
                        MessageBox.Show("Error connecting to the database: " & myerror.Message)
                Finally
                        If conn.State <> ConnectionState.Closed Then conn.Close()
                End Try
        End Sub

If there is an error connecting, the user will see a message box that reports an error connecting to the database. If there is an error executing the query, the user will see a message box that reports an error reading from the database.

Updating a User’s Status

Now that we can see what the status of our users is, it would be good to add the ability to update our own status. To do this we need to show the user a list of possible status messages using a combo box. In addition we should add a second combo box showing the two possible status messages. Our combo boxes will look something like this:

Creating the Status and Message Combobox Controls

Add the comboboxes to the form by dragging them from the toolbox. In the properties window, change the names of these comboboxes to cboStatus and cboMessage. You form should look something like the following:

combo boxes

On the left is cboStatus, on the right is cboMessage. We will populate these two comboboxes in the form load event, which can be created by double-clicking on the form background.

We populate the cboStatus combobox with the following code:

cboStatus.Items.Add("In")
cboStatus.Items.Add("Out")
cboStatus.SelectedIndex = 0

This adds two items (In and Out) and sets the combobox to pre-select the In item (the list starts at 0 and counts up, so In is 0 and Out is 1).

We will populate the cboMessage box using data binding. The principal is the same as we covered for the data grid in the previous section, so I am just going to display the code:

Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
                cboStatus.Items.Add("In")
                cboStatus.Items.Add("Out")
                cboStatus.SelectedIndex = 0

                dgvStatus.ReadOnly = True

                Dim conn As New MySqlConnection
                Dim myCommand As New MySqlCommand
                Dim myAdapter As New MySqlDataAdapter
                Dim myData As New DataTable
                Dim SQL As String

                SQL = "SELECT s.message_id, s.message " _
                 & "FROM in_out.status s " _
                 & "WHERE user_id IS NULL and deleted = 'False'"

                conn.ConnectionString = myConnString

                Try
                        conn.Open()

                        Try
                                myCommand.Connection = conn
                                myCommand.CommandText = SQL

                                myAdapter.SelectCommand = myCommand
                                myAdapter.Fill(myData)

                                cboMessage.DataSource = myData
                                cboMessage.DisplayMember = "message"
                                cboMessage.ValueMember = "message_id"
                        Catch myerror As MySqlException
                                MsgBox("There was an error reading from the database: " & myerror.Message)
                        End Try
                Catch myerror As MySqlException
                        MessageBox.Show("Error connecting to the database: " & myerror.Message)
                Finally
                        If conn.State <> ConnectionState.Closed Then conn.Close()
                End Try
        End Sub

The primary difference is the following two lines:

cboMessage.DisplayMember = "message"
cboMessage.ValueMember = "message_id"

After setting the source for data binding, we first specify that the message column of our query will be shown in the combobox, and that we can identify which item was selected through the message_id column, as I will later demonstrate. This does present one problem: in its current form, the combobox will not allow us to set a NULL status, because the combobox is only populated with entries from the status table (and only those messages that have no user_id and which are not marked as deleted). For the moment we will ignore this limitation and will address it in a future article.

The dgvStatus.ReadOnly = True line is added to prevent users from trying to edit the contents of the datagrid. With this code added, our comboboxes will be pre-populated and ready for us to use to update the status.

Retrieving the User ID With A Parameterized Query

To update a user’s status we need to create a new row in the event table by using an INSERT query.

In order to build the query we need to know the user_id value associated with our user. We can do this by querying the user table, and since we may use this multiple times in a single application run, lets perform the query as part of the login form. Here is our updated login form:

Public Class frmLogin
        Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
                Application.Exit()
        End Sub

        Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
                Dim conn As New MySqlConnection
                Dim myCommand As New MySqlCommand

                Dim myConnString As String
                Dim UserID As Integer

                myConnString = "server=" & txtServer.Text & ";" _
                 & "user id=" & txtUsername.Text & ";" _
                 & "password=" & txtPassword.Text & ";" _
                 & "database=in_out"

                conn.ConnectionString = myConnString

                Try
                        conn.Open()

                        myCommand.Connection = conn
                        myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username"
                        myCommand.Parameters.Add("?Username", txtUsername.Text)

                        UserID = myCommand.ExecuteScalar

                        conn.Close()

                        Dim mainForm As New frmMain
                        mainForm.UserID = UserID
                        mainForm.connectionString = myConnString
                        mainForm.Show()

                        Me.Hide()
                        Me.Close()
                Catch myerror As MySqlException
                        MessageBox.Show("Error Connecting to Database: " & myerror.Message)
                        conn.Dispose()
                End Try
        End Sub
End Class

Of interest are the following lines:

myCommand.Connection = conn
myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username"
myCommand.Parameters.Add("?Username", txtUsername.Text)

UserID = myCommand.ExecuteScalar

I added a command object to the code, set the conn object as it’s connection, and specified a query in the CommandText. This query is the first query in our application that incorporates user input, and as such it requires extra care. When we deal with data entered by the user we cannot blindly trust the user to provide information that is safe and syntactically correct, otherwise the user could compromise security through a method known as SQL Injection (see my article at http://www.vbmysql.com/articles/security/sqlinjection.html for more information). We cannot trust user input and we need to sanitize all user input before it reaches the MySQL server.

Connector/NET handles sanitizing of user input for us through the use of parameterized queries. Notice that within the query above the username is compared to a external value with the syntax WHERE BINARY username = ?Username. The ?Username element is a placeholder that we fill with a parameter that is derived from the txtUsername. When the query is executed the MySqlCommand object reads the text of the username textbox and fills in the query, sanitizing it in the process.

The query is executed using the ExecuteScalar method of the MySqlCommand object, which can be used to execute queries that will return only a single value. In this case we use it for user_id, it could also be used to return a query that counts rows in a table, or any other query that returns a single value.

The user id is passed to the frmMain by way of a parameter, just like the connection string. I have added a parameter to the frmMain to accommodate this:

Public Class frmMain
        Private myConnString As String
        Private myUserID As Integer

        Public WriteOnly Property connectionString() As String
                Set(ByVal value As String)
                        myConnString = value
                End Set
        End Property

        Public WriteOnly Property UserID() As Integer
                Set(ByVal value As Integer)
                        myUserID = value
                End Set
        End Property

The new property is the same as the connection string property, except the user id is stored as an integer instead of a string.

Inserting a New Row in the Event Table

Now that we have the user id of the logged in user, and the status and message information from our pair of combobox controls, we can insert a new row that will update the status of our user.

Here is an example of the INSERT statement we want to generate:

INSERT INTO event (user_id, message_id, timestamp, status, creator) VALUES(1, 1, NOW(), 'Out', 1)

We do not specify an event_id because, as an AUTO INCREMENT column, it will be filled in automatically. The user_id is 1 in this case, along with the creator because we are setting our own event. The timestamp is assigned to NOW(), which represents the current server time. Finally, status is set to ‘Out’ to indicate the user is signing out.

Of course, we cannot simply place this query into our application, otherwise everyone who ever hit the Update Status button would simply sign out user 1. Instead we will once again build a collection of parameters, bound to the controls on our form:

  Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
                Dim conn As New MySqlConnection
                Dim myCommand As New MySqlCommand

                conn.ConnectionString = myConnString

                myCommand.Connection = conn
                myCommand.CommandText = "INSERT INTO event(user_id, message_id, timestamp, status, creator)" _
                 & "VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)"

                myCommand.Parameters.Add("?UserID", myUserID)
                myCommand.Parameters.Add("?MessageID", cboMessage.SelectedValue)
                myCommand.Parameters.Add("?Status", cboStatus.SelectedItem)
                myCommand.Parameters.Add("?Creator", myUserID)

                Try
                        conn.Open()
                        myCommand.ExecuteNonQuery()
                Catch myerror As MySqlException
                        MsgBox("There was an error updating the database: " & myerror.Message)
                End Try
        End Sub

Remember when we set the ValueMember property of cboMessage to bind to the message_id of the status table? This allowed us to access the message_id through the SelectedValue property in order to use it as a parameter in our INSERT statement. cboStatus is not a bound control, so we use the SelectedItem property instead when binding it. Note that we can also variables as parameters (myUserID), and even use a variable more than once in a parameter set.

Testing the Application

Now that all code is in place, you can try a quick test. Start the application, enter your server IP, username, and password. Try entering the wrong IP, username, or password and make sure you see an error message (without your application crashing).

Once you are connected, click the Refresh button and make sure you can see the list of users. Choose a status and message and click the Update Status button, then click the Refresh button to see your status update.

Minor UI Tweaks

After testing the application you may have noticed a couple of improvements that we can make to the UI. First, when we update your status, a refresh should be fired automatically so we instantly see the new status. This can be accomplished by adding the following line to the bottom of your update code:

cmdRefresh.PerformClick()

This will fire the click event of the Refresh button, refreshing the data after the status is updated.

Another improvement that could be made is with regards to keyboard shortcuts on the login form. User expect that the Escape key will cancel out of a form, while the enter key will submit form information.

This requires very little coding:

Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 Me.AcceptButton = cmdLogin
 Me.CancelButton = cmdCancel
End Sub

By setting the AcceptButton and CancelButton properties of the form, VB.NET will now watch for Enter and Escape keystrokes and will fire the button events accordingly.

Application Improvements

There are a few things that need improving in this application. First of all, we still need a less tedious login that stores the database IP address and which remembers the last username entered. Our datagrid is not particularly attractive and could use cosmetic improvements, and we need to add support for having a blank status message or a custom status message. Finally, it would be nice is the data refreshed automatically rather than requiring our users to repeatedly click the Refresh button. These will all be addressed in future articles.

Conclusion

Our application is really moving along. We can now see a list of the users and their current status, and update our own status to one of the pre-defined status messages. We have populated some sample data to start off with, and added a new event through our application.

In our next article, we will improve the application by adding automatic data refreshing and persistent server information so that our users need not enter their server IP and username information every time they use the application.

The current project files and a dump of the database are available at http://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-part-4.zip.

129 Responses to “The VB.NET-MySQL Tutorial – Part 4”

Kevin Says:
November 9th, 2006 at 9:25 pm
Por Favor Podrian colocar la Traduccion en Español, Se lo agradeceria, es un tuturial muy bueno y vale la pena

Gracias
Bora Says:
November 10th, 2006 at 7:46 pm
Great articles,

I am trying to create a database, tables and Stored procedures in a client, and my challange is that I would like to hide my database structure and my data algorithm (implemented in SPs). Now, I am doing that through bat files running mysql admin with bunch of SQL statements but as you can imagine, since bat files and Sql statements are in ascii text, it is readable. I would like to create either console exe files or activex DLLs to wrap my work so it is not exposed. I would appreciate if you can help me.

Thank you
Ernest Bonat, Ph.D. Says:
November 20th, 2006 at 5:06 pm
Hi Bora,

You’re really in the right path for developing Windows applications using VB.NET/MySQL. As I explained before stored procedures (SP) and COM DLL components implementation is one of the best solutions in Windows application development today. Especially in MySQL 5.0 when we can develop database objects like stored procedures, functions, triggers and views. Why are people using dynamic and parameterized SQL statements in MySQL 5.0? I have no idea – bad programming practices! Many people that I know use Toad for MySQL or Navicat as GUI database management system for MySQL. Try them and let me know.

MS Windows platform is about COM components (DLL, EXE and OCX) everywhere. In VB.NET 2005 you can develop DLL components as a Class Library projects. I’ll write a tutorial soon about this topic, a lot of developer friends and students as me about it all the time.

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
Johnny F. Rojas G. Says:
January 27th, 2007 at 2:06 am
Por Favor Podrian colocar la Traduccion en Español, Se lo agradeceria, es un tuturial muy bueno y vale la pena

Gracias
Cammy Says:
January 31st, 2007 at 6:04 pm
Your tutorials are really fantastic!!

I am trying to delete an item in a combobox. The combobox is populated from my MySQL database using databinding. The corresponding row in the table in my database should be deleted as well.

How do I do this?

Some template code will be appreciated.
Ernest Bonat, Ph.D. Says:
January 31st, 2007 at 6:51 pm
Hi Cammy,

Are you trying to remove an item from the ComboBox or are you trying to delete a record from a table? Please explain. Both approaches are completing different.

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
Cammy Says:
February 1st, 2007 at 4:38 am
Hi Ernest

Ultimately I am actually trying to remove a record from the database. I can update the combobox after that by re-query-ing the database. The real thing that I am stuck on is selecting the correct record based on the item that is selected in the combobox (DropDownStyle in combobox is set to DropDownList so the user has to select one of the items in the list.)

Thank you for your reply.
ade Says:
February 16th, 2007 at 1:41 pm
can you please tell which connector am i to download from the mysql website when trying to use vb 2005 express with mysql, since u now have connector 1 and 5 available for download.
Ernest Bonat, Ph.D. Says:
February 16th, 2007 at 4:38 pm
Hi ade,

I have been using the MySQL Connector .NET 5.0.3 with VB.NET 2005 (Professional and Express Edition) without any problems at all. Please don’t forget to install the SP1 for VB.NET 2005 – very important!

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
Matthew Fox Says:
March 12th, 2007 at 1:06 am
Thanks for the helpful tutorial. I’m curious about your use of nested Try-Catch blocks. Is it not better to use multiple Catch statements, first trying to catch the most specific exceptions and then the most general exceptions (specifically, an object of type Exception)? This makes sense because a connection error in a try block halts execution of the entire block; you don’t need to worry about the following stuff running after a connection error.

However, if you have a reason for using nested blocks, I’d be curious to hear it.

Thanks for the good tutorial.
tumi Says:
March 20th, 2007 at 3:55 pm
i can only display on a datagrid but can noot save on a database
Ernest Bonat, Ph.D. Says:
March 20th, 2007 at 5:05 pm
Matthew,

I agree with you. Let’s wait for Mike, he may have some reasons why he used nested Try…Catch blocks in this tutorial.

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
ri3ka Says:
March 22nd, 2007 at 8:04 am
can we use a class to declare database connectionstring ? So that, if we want to connect to DB we just call that class.
Ernest Bonat, Ph.D. Says:
March 22nd, 2007 at 4:06 pm
Hi ri3ka,

You need to open an new post for this question in the VB.NET forum so everyone can help you with quickly feedbacks.

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
Hjalmar Says:
April 16th, 2007 at 12:01 pm
Hi.

I’m tring to access table fields directly instead of binding the data to a control (like a grid). How do I do that?

Thanks!
Steve Says:
April 24th, 2007 at 10:13 pm
I am using Visual Basic .net 2003 standard. The “Shutdown Mode” is not an option in .net 2003. Can you advise of or point to a workaround?

Thanks
evan Says:
April 28th, 2007 at 9:11 pm
Hiya,
I’ve read loads of tutorials on connecting VB.NEt to a MySQL database and this one is by far the best.
Thanks .
ORiGiN Says:
May 5th, 2007 at 8:06 pm
Conversion from string “INSERT INTO user_id, email, user” to type ‘Integer’ is not valid.

That is an error that I get when I try to ‘register’ a new member. I am using Visual Basic 2005 Express and the following code:

Dim trans As MySqlTransaction
Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim comm As MySqlCommand

conn = New MySqlConnection(”server=localhost;Initial Catalog=vbStuff;UID=root;PWD=qqqqqq”)
conn.Open()
comm = conn.CreateCommand()
trans = conn.BeginTransaction(”INSERT INTO user_id, email, username, password, administrator, deleted, created” _
& “VALUES(?UserID, ?Email, ?Name, ?Pass, ‘FALSE’, NOW())”)
comm.Connection = conn
comm.Transaction = trans
myCommand.CommandText = “INSERT INTO event(user_id, email, username, password, administrator, deleted, created)” _
& “VALUES(?UserID, ?Email, ?Name, ?Pass, ‘FALSE’, NOW())”

myCommand.Parameters.AddWithValue(”?UserID”, “1″)
myCommand.Parameters.AddWithValue(”?Email”, txtEM.Text)
myCommand.Parameters.AddWithValue(”?Pass”, txtPw1.Text)

Try
If Not (trans Is Nothing) Then
trans.Rollback()
trans.Commit()
Exit Sub
End If
Catch ex As MySqlException
Console.WriteLine(”Exception” + ex.GetType().ToString() + ” encountered while rolling back transaction.”)
MessageBox.Show(”Exception” + ex.GetType().ToString() + ” encountered while rolling back transaction.”)
End Try

I tried several different ways. I wish it just let you use regular MySQL commands.
Ernest Bonat, Ph.D. Says:
May 6th, 2007 at 6:01 am
Hi ORiGiN:

You need to open an new post for this question in the VB.NET forum so everyone can help you with quickly feedbacks.

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
CliffHanger Says:
June 4th, 2007 at 8:45 pm
Under your “Binding the Result” section, your example has nice Column headings while I used the same code and mine outputs the database table column name as the column heading:

customer_name_first vs “First Name”

How do you edit these column headings?

Cheers-
Javier Says:
June 8th, 2007 at 4:31 pm
Simplemente agradecer al autor, es un excelente tutorial, yo comencé hace poco a mudar desde Vb6 y este es el unico tutorial que explica bien las cosas. saludos!
Ernest Bonat, Ph.D. Says:
June 8th, 2007 at 5:52 pm
Hey Javier,

May be this forum is in English. I think we have a forum in Español.

Muchas Gracias!

Ernest Bonat, Ph.D.
Norm Hogan Says:
June 8th, 2007 at 6:20 pm
I’m sure everyone has found this already but the method Add is now obsolete(or deprecated I got both error versions in Visual Basic Express 2005) and has been replaced with AddWithValue
example myCommand.Parameters.Add(”?UserID”, myUserID)
is now
myCommand.Parameters.AddWithValue(”?UserID”, myUserID)

Not sure if it is the interest of the larger audience to have this code updated but thought I’d mention it anyway
Ernest Bonat, Ph.D. Says:
June 8th, 2007 at 6:41 pm
Hi Norm,

You’re right. Most of the code for this tutorial belongs to VB.NET 2002 and 2003. It could be nice if we can find some one to migrate this tutorial to VB.NET 2005. Just in case, below is code to add a MySQL parameter to the Command object:

Dim MySqlParameterUserID As New MySqlParameter

With MySqlParameterUserID
.ParameterName = “?par_UserID”
.Direction = ParameterDirection.Input
.MySqlDbType = MySqlDbType.VarChar
.Size = 20
.Value = pUserIDString
End With
.Parameters.Add(MySqlParameterUserID)

Thanks

Ernest Bonat, Ph.D.
Nascarnick Says:
July 3rd, 2007 at 1:57 pm
This doesn’t have a lot to do with this article but I can’t seem to find an answer anywhere else.
How can I bind a label to data in the datagrid? I wan’t to be able to click on the different rows in my datagrid and have that row’s data move to labels in my application.
I have set up my application’s code the same way it is set up in this tutorial.
Thanx
Nascarnick Says:
July 3rd, 2007 at 3:45 pm
Also, I have a column in my MySQL table that I want to bind data to but I don’t want it to be shown in the datagrid. Is this possible? I need it but I want it to be invisible.
Ernest Bonat, Ph.D. Says:
July 3rd, 2007 at 4:58 pm
Hi Nascarnick,

You don’t need to bind the label to the DataGridView. When you select a row on grid the CurrentCellChanged() event is triggered. Set the current cell of the grid as:

Dim CurrentCell As DataGridViewName
CurrentCell = DataGridViewName.CurrentCell

Than, get the value of any column by:

ColumnNameValue = CriteriaDataGridView(SelectedRowNumber, CurrentCell.RowIndex).Value

At this point pass this value to the label

LabelName.Text = ColumnNameValue.ToString

That’s all.

To make a Column invisible make the Visible property to False as:

ColumnName.Visible = False

Note: Be careful with data binding in VB.NET 2005 -> this has been no proofed in real production environment today???

I hope this helps

Thanks

Ernest Bonat, Ph.D.
503.730.4556
http://www.evisualwww.com
Nascarnick Says:
July 4th, 2007 at 4:11 am
Thank you very much!
About that ColumnName.Visible = False
Where should I put this code? Whenever I put the column name, (example. Title.visible = False) It says Title isn’t declared.

Thanx
George Says:
July 7th, 2007 at 10:06 am
Hi,

It works quite well to connect to a mysql database from a vb.net express edition environment. However, it is quite uncomftable to play around with queries in the source code, since it takes a long time to finish a query and even longer to debug it. Is there a way to connect to my mysql db using datasets etc. (aka using a xsd file where I just display my tables and pick what I actually need and process the data from there). I have already done that at Siemens where I worked before. Thing is that we had a MSSQL db, thus I was able to connect without any problems to the DB, since I can choose (standard) between a MSSQL DB and a Access DB in the Application – Unfortunately not a MYSQL.

Can anyone help me?

(Yes, I already ordered a free trial of a MSSQL environment).

Georg

PS.: Just in case anyone knows. Is it possible to inherit windows forms in VB. NET express edition?
Nascarnick Says:
July 9th, 2007 at 5:50 am
I really need an answer to this question.
ColumnName.Visible = False
Where do I put this code? When I change the ColumnName to the name of the column, it says it is not declared. What do I do?

Thanx in advance
Ernest Bonat Says:
July 9th, 2007 at 5:04 pm
Hi Georg!

Fast development SQL statements for MySQL are not a problem any more. Here is some database management system that you can get online:

1. Toad for MySQL (my prefer)
2. Navicat
MySQL Maestro
MySQLDirect.NET
Many more…

About: .: Just in case anyone knows. Is it possible to inherit windows forms in VB. NET express edition?

VB.NET 2005 Express Edition is full OOP capable. As you can see you can add class file to your solution project.

Let me now for any more questions?

Thanks

Ernest Bonat, Ph.D.
503.730.4556
http://www.evisualwww.com
Ernest Bonat Says:
July 9th, 2007 at 5:27 pm
Hi Nascarnick,

Here is an example for you:

Dim ColumnContactID As New DataGridViewTextBoxColumn
With ColumnContactID
.DataPropertyName = “id”
.Name = “id”
.HeaderText = “ID”
.AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader
.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
End With
DataGridViewControl.Columns.Add(ColumnContactID)
DataGridViewControl.ColumnContactID.Visible = False

As you can see this column ColumnContactID is bound to the ID of the Contact table. After the column is setup I set the visible property to False. We don’t want to show the ID of the records to the users. Let me know is that is clear so far!

Thanks

Ernest Bonat, Ph.D.
503.730.4556
wwww.evisualwww.com
Nascarnick Says:
July 10th, 2007 at 2:44 pm
That code makes complete sense but I can’t seem to apply it to a textboxcolumn that is already there. I tried to start it with
Dim ColumnContactID As DataGridViewTextBoxColumn

thats without the new.
Now how do I tell it which textboxcolumn I want?

Sorry for causing you to do all of this. Your such a big help to me.

Thanx
Georg Says:
July 10th, 2007 at 3:38 pm
Dear Ernest,

Thanks a lot ! That was what I was looking for~
Works perfectly … !

Georg
Nascarnick Says:
July 11th, 2007 at 11:04 pm
Sorry for bothering you Ernest but I’m working on this project and I don’t have much time.
I posted earlier.
http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-4#comment-8332

Thank you so much!!
Ernest Bonat Says:
July 12th, 2007 at 3:06 pm
Hi Nascarnick,

You don’t need to be sorry. We’re here to help each other -> That is the main reasons of the our forum. Your question is not really clear for me at this point. Send me your partial code and an easy explanation so I can position you in the right direction. I’ll be checking my e-mail very often waiting for you!

Ernest Bonat, Ph.D.
http://www.evisualwww.com
Nascarnick Says:
July 12th, 2007 at 7:11 pm
Here is just the important parts

Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String
Dim myConnString As String

SQL = “SELECT R.`dlink`, R.`ddesc`, R.`slink`, R.`scount`, R.`dtitle` As `Title`, R.`dauth` As `Author`, R.`dsize` As `File Size`, R.`ddate` As Date, R.`dtype` As `Ride Type`, R.`drcty` As `Expansion`, R.`dcount` As `Number of Downloads` FROM rctgo_downloads.Roller_Coasters R;”

myConnString = “server=” & “*********” & “;” _
& “user id=” & “***********” & “;” _
& “password=” & “***********” & “;” _
& “database= *********”

conn.ConnectionString = myConnString

myCommand.Connection = conn
myCommand.CommandText = SQL
myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)
DataGridView1.DataSource = myData

See I have columns like dlink, slink, ddesc, and scount that I need to bind a label to but I don’t want them to be viewed in the datagrid. How do I make those columns invisible to the user viewing the datagrid.

I hope this makes sense.

Thanx so much!!
Ernest Bonat Says:
July 13th, 2007 at 5:02 pm
Hi Nascarnick.

Here is the idea: before you bound the DataGridView to the DataSet,

DataGridView1.DataSource = myData

You created a format DataGridView function as following:

Call DataGridViewFormat(DataGridView1)
DataGridView1.DataSource = myData

Here is the code of DataGridViewFormat() function:

Private Sub DataGridViewFormat(ByRef pDataGridViewControl As DataGridView)
With pDataGridViewControl
‘ This general grid settings may be chanced !!!
.AlternatingRowsDefaultCellStyle.BackColor = Color.LightCyan
.SelectionMode = DataGridViewSelectionMode.FullRowSelect
.AllowUserToOrderColumns = True
.AllowUserToDeleteRows = False
.AllowUserToAddRows = False
.ReadOnly = True
.MultiSelect = False
.Columns.Clear()

Dim ColumnDlink As New DataGridViewTextBoxColumn
With ColumnDlink
.DataPropertyName = “dlink”
.Name = “dlink”
.HeaderText = “dlink”
.AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader
.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
End With
.Columns.Add(ColumnDlink)
ColumnDlink.Visible = False

‘ Here you write the same code for slink, ddesc, and scount columns

‘ For the other columns remove the line

‘ ColumnDlink.Visible = False

‘ So you can see all of them. That’s all!

End With
End Sub

Let me know if this helps a litle bit?

Ernest Bonat, Ph.D.
503.730.4556
http://www.evisualwww.com
Nascarnick Says:
July 15th, 2007 at 10:09 pm
Dear Ernest,

That code worked perfect! It did exactly what I needed. Thank you so much for your help.

I was just wondering, is it possible to have the application download all the data that will go into the datagrid the first time it loads up and then save it into like an XML file or something and then just load the file on start up? Then every week it reloads the data to keep the data up-to-date. The server it downloads data from isn’t a very fast server and there is a lot of data to download.
I’m only asking this because it takes a while to download all of the data on startup.
If this isn’t easy to do then don’t worry about it. It’s not that much of a problem.
Thanks
darr Says:
July 18th, 2007 at 6:42 pm
i did your project just as you have it there but when i run the program i am getting an error at

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim mydata As New DataTable

Dim SQL As String

SQL = “SELECT CONCAT(u.lastname, ‘, ‘, u.firstname) AS Name, s.message AS Message, ” _
& “DATE_FORMAT(e.timestamp,’%b %d %Y – %r’) AS DateTime, e.status AS Status ” _
& “FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u ” _
& “WHERE(e.user_id = u.user_id) ” _
& “AND event_id IN( ” _
& “SELECT MAX(e.event_id) ” _
& “FROM event e ” _
& “GROUP BY e.user_id) ” _
& “ORDER BY name”

conn.ConnectionString = myConnString

Try
conn.Open()
Try

Catch ex As Exception

End Try

Catch myerror As MySqlException
MessageBox.Show(”Error Connecting to Database: ” & myerror.Message)
End Try
myCommand.Connection = conn
myCommand.CommandText = SQL
myAdapter.SelectCommand = myCommand
myAdapter.Fill(mydata)**********************************************ERROR:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘%râ€™) AS DateTime, e.status AS Status FROM event e LEFT JOIN status s ON e.mess’ at line 1

dgvStatus.DataSource = mydata

Try

Catch myerror As MySqlException
MsgBox(”ERROR CONNECTING TO THE DATABASE:” & myerror.Message)
Finally
If conn.State ConnectionState.Closed Then conn.Close()

End Try

End Sub
End Class

PLEASE HELP URGENT
darr Says:
July 19th, 2007 at 12:16 pm
hey can anyone tell me how do display information form my database in text boxes on a form. My database is build in MYSQL and VB.NET for the user interface.
Ernest Bonat, Ph.D. Says:
July 19th, 2007 at 8:06 pm
Hi darr,

Here are some ideas for you:

1. Connect to you MySQL databse server.

Private Function MySQLADOConnOpen(ByVal pServerString As String, _
ByVal pDatabaseString As String, _
ByVal pUserIDString As String, _
ByVal pPasswordString As String, _
ByRef pErrorMsg As String) As Boolean
Dim ADOConnString As String
Try
mDataConnection = New MySqlConnection
ADOConnString = “Server=” & pServerString & “;” & _
“Database=” & pDatabaseString & “;” & _
“Uid=” & pUserIDString & “;” & _
“Pwd=” & pPasswordString & “;”
mDataConnection.ConnectionString = ADOConnString
mDataConnection.Open()
Return (True)
Catch ex As MySqlException
pErrorMsg = ex.Message
Return (False)
End Try
End Function

2. Get the MySQLDataReader and loop it:

mSQLString = “SELECT * FROM your_table”

mDataCommand = mDataConnection.CreateCommand()

mDataCommand.CommandType = CommandType.Text

mDataCommand.CommandText = mSQLString

mDataDataReader = mDataCommand.ExecuteReader(CommandBehavior.SingleRow)

mDataDataReader.Read()

If mDataDataReader.HasRows Then

mObjectValue = mDataDataReader(”name”)

If Not IsDBNull(mObjectValue) Then

TextBoxName = Convert.ToString(mObjectValue)

Else

TextBoxName = String.Empty

End If

‘ more fields

End If

3. Close the MySQL ADO.NET objects

If Not mDataDataReader Is Nothing Then
mDataDataReader.Close()
mDataDataReader = Nothing
End If

If Not mDataCommand Is Nothing Then
mDataCommand.Dispose()
mDataCommand = Nothing
End If

4. Close MySQL ADO.NET conneection object:

Public Function MySQLADOConnClose(ByRef pErrorMsg As String) As Boolean
Try
If Not mDataConnection Is Nothing Then
With mDataConnection
If .State = ConnectionState.Open Then
.Close()
End If
.Dispose()
End With
mDataConnection = Nothing
End If
Return (True)
Catch ex As MySqlException
pErrorMsg = ex.Message
Return (False)
End Try
End Function

Let me know if this helps?

Thanks

Ernest Bonat, Ph.D.
503.730.4556
http://www.evisualwww.com
darr Says:
July 25th, 2007 at 1:54 pm
hi Mr. Bonat

i am not getting through with this code i don’t know if i am not setting up the statement and my variables correct can you send me a full example showing how to use the loop and all objects used. I need to see how the data reader is set up. I have been struggling with this since you posted it for me. Can you pls offer some more help, i really need this code. I am new to vb.net and i am really trying to learn it if you know of any reference books or video cd’s that can be of help please let me know.

My best regards

darr
Ernest Bonat, Ph.D. Says:
July 25th, 2007 at 3:38 pm
Hi darr,

No problem at all. I published two papers so far at MySQL Developer Zone. Here are the links:

1. “Define and Store MySQL ADO Connection String in VB.NET 2005” (http://dev.mysql.com/tech-resources/articles/ebonat-connection-string-vb-net.html)

2. “MySQL Data Loading with Lookup Tables” (http://dev.mysql.com/tech-resources/articles/ebonat-sql-data-loading-with-lookup-tables.html)

Read these two papers and download the source code and the doc from my website (http://www.evisualwww.com/project_download.php)

I hope this will help. Let me know for any questions?

Thanks

Ernest Bonat, Ph.D.
503.730.4556
http://www.evisualwww.com
darr Says:
July 26th, 2007 at 11:43 am
Hi Mr. Bonat

Thanks for all those resources but i am still a bit confused, with that code you gave me i am still unable to get what i want done because it is a bit different from what i want. Let me tell you what i am doing, i have a simple registration form and i am trying to create a query button to retrieve all the registration data stored in mysql database and populate the text boxes in my form. So can you please help me out there, i have been stuck here for months with out any progress. Another thing i have already created a login to the database and connected, do i have to repeat the ‘open connection’ string behind my buttons every time i am accessing the database?. The source code you directed me to does not come up check the page, here it comes up as a set of symbols.

Best Regards
Darr
Ernest Bonat, Ph.D. Says:
July 26th, 2007 at 4:14 pm
Hi Darr,

The two papers are general for any MySQL/VB.NET developer. You can’t hardcore application settings in your Internet or Windows applications programming code. Every thing must be store and update (control) in the application configuration files, so we can maintain them without applications deployment. This is the main meaning. You can’t repeat your code over the places in your application, no one will maintain that. In this case your Connection String parameters are stored in your config file and you get pass this string every where in your application. I checked my download page this morning and every thing is working well. So, try it one more time and let me know.

“Define and Store MySQL ADO Connection String in VB.NET 2005” (http://dev.mysql.com/tech-resources/articles/ebonat-connection-string-vb-net.html)

In my website:

http://www.evisualwww.com/project_download.php

Thanks

Ernest Bonat, Ph.D.
503.730.4556
http://www.evisualwww.com
darr Says:
July 27th, 2007 at 12:49 pm
Mr. Bonat

I would like if you can send me some sample code that i need to place behind the query button in my form to retrieve all data from the mysql database. I think includes binding the data to fields on the form

Regards
Darr
darr Says:
August 15th, 2007 at 3:09 pm
Mr. Bonat

I am creating some mysql queries using six fields. I have to come up with all the possible combinations of how the the user might query. The user can query by any of the six fields or my all fields with in the six however they choose. I have to write all the possible ways the user may choose to query. Is there any easy way to know how many queries i must create, a formula or something. Please let me know so that it will save me the time of having to write all the possible patterns without repetition.
Ernest Bonat, Ph.D. Says:
August 15th, 2007 at 3:24 pm
Hi,

Yes, I did that before for SQL Server 2000 using stored procedures. Let me look at the code and I’ll get back to you soon. A question for you: Do you want to user MySQL dynamic SQL or stored procedures? Let me know?

Thanks

Ernest Bonat, Ph.D.
503.730.4556
http://www.evisualwww.com
Rudner Says:
August 24th, 2007 at 6:33 am
Thank you very much for sharing your ideas in this forum.. I am new in vb.net, but it makes me feel aggresive to continue this thing..

God bless!!!!!
Ernest Bonat, Ph.D. Says:
August 24th, 2007 at 3:50 pm
Hi Rudner,

Thank you very much for your kind e-mail. Feel free to post any questions in this forum. I’m connected to it practically all day long from Monday to Friday.

Many blessing for you and all your family!

Ernest Bonat, Ph.D.
503.730.4556
http://www.evisualwww.com
Mark R Says:
September 10th, 2007 at 2:00 am
I’ve enjoyed this tutorial and was doing great up to this (part 4). Tried to copy your code very carefully and I’ve checked and rechecked the copying, all appears right. Created an account “mike” with password “guru” in my database. When I either log in as mike or as myself, nothing happens after the login screen. I’ve watched the code in DEBUG and the program executes the frmMain_Load section and pops open a window, but nothing ever gets displayed in it. The nested TRY and TRY both execute correctly but nothing gets displayed. Further, the main window does not stay open. As I watch the code execute through frmMain_Load, when it hits the END SUB, it goes right back to the login, which then closes everything and the program ends.

Not sure how to unzip your files to give this a try. I also created the database as “in-out” instead of “in_out” (although I’ve adjusted accordingly), so I can’t unzip your files because there is imbeded “in_out” in some of the VB generated code.

Also, your code seems to have been generated a couple of years ago, so it doesn’t seem to work quite right with the existing, available version of VB.NET that can be downloaded from Microsoft today. I did fix the
myCommand.Parameters.Add(”?UserID”, myUserID) –> myCommand.Parameters.AddWithValue(”?UserID”, myUserID), although that seems minor overall.

I think you are on a great track here and that this could be very useful for anyone trying to create any database. I’ve searched the web high and low and haven’t seen anything close to a true data entry program. I think what you have shown here could easily be converted to such a program — although I’m still fuzzy about the datagrids.

Here is my frmMain.vb code:

Imports MySql.Data.MySqlClient
Imports System.Data

Public Class frmMain
Private myConnString As String
Private myUserID As Integer

Public WriteOnly Property connectionString() As String
Set(ByVal value As String)
myConnString = value
End Set
End Property

Public WriteOnly Property UserID() As Integer
Set(ByVal value As Integer)
myUserID = value
End Set
End Property

Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
cboStatus.Items.Add(”In”)
cboStatus.Items.Add(”Out”)
cboStatus.SelectedIndex = 0

dgvStatus.ReadOnly = True

Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String

SQL = “SELECT s.message_id, s.message ” _
& “FROM status s WHERE user_id IS NULL and deleted = ‘False’”

conn.ConnectionString = myConnString

Try
conn.Open()

Try
myCommand.Connection = conn
myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)

cboMessage.DataSource = myData
cboMessage.DisplayMember = “message”
cboMessage.ValueMember = “message_id”
Catch myerror As MySqlException
MsgBox(”There was an error reading from the database: ” & myerror.Message)
End Try
Catch myerror As MySqlException
MessageBox.Show(”Error connecting to the database: ” & myerror.Message)
Finally
If conn.State ConnectionState.Closed Then conn.Close()
End Try
End Sub

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String

SQL = “SELECT CONCAT(u.lastname, ‘, ‘, u.firstname) AS Name, s.message As Message, ” _
& “DATE_FORMAT(e.timestamp, ‘%b %d %Y – %r’) AS DateTime, e.status AS Status” _
& “FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u ” _
& “WHERE(e.user_id = u.user_id)” _
& “AND event_id IN (” _
& “SELECT MAX(e.event_id)” _
& “FROM event e” _
& “GROUP BY e.user_id)” _
& “ORDER BY name”

conn.ConnectionString = myConnString

Try
conn.Open()

Try
myCommand.Connection = conn
myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand

myAdapter.Fill(myData)

dgvStatus.DataSource = myData
dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

Catch myerror As MySqlException
MsgBox(”There was an error reading from the database: ” & myerror.Message)
End Try

Catch myerror As MySqlException
MessageBox.Show(”Error Connecting to Database: ” & myerror.Message)
Finally
If conn.State ConnectionState.Closed Then conn.Close()
End Try
End Sub

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand

conn.ConnectionString = myConnString

myCommand.Connection = conn
myCommand.CommandText = “INSERT INTO event(user_id, message_id, timestamp, status, creator)” _
& “VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)”

myCommand.Parameters.AddWithValue(”?UserID”, myUserID)
myCommand.Parameters.AddWithValue(”?MessageID”, cboMessage.SelectedValue)
myCommand.Parameters.AddWithValue(”?Status”, cboStatus.SelectedItem)
myCommand.Parameters.AddWithValue(”?Creator”, myUserID)

Try
conn.Open()
myCommand.ExecuteNonQuery()
Catch myerror As MySqlException
MsgBox(”There was an error updating the database: ” & myerror.Message)
End Try
cmdRefresh.PerformClick()
End Sub
End Class

What the HECK am I missing? If you want, I’ll ZIP my whole project and e-mail to you.

Many thanks for your help!
Ernest Bonat, Ph.D. Says:
September 10th, 2007 at 4:30 pm
Mark,

Yes, please send the entire project with the database script files to look at it. Mike Hillyer (former forum admin) developed this tutorials couple of years ago. They may be old as you mentioned, but no problem at all, I’ll be happy to help you. I’ll be in charge of this forum for now on. Here is my business e-mail address: ebonat@evisualwww.com or ebonath@yahoo.com

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
Meis Says:
September 14th, 2007 at 10:16 pm
These tutorials have been useful, but could anyone instruct me on how to display the output of non select commands to a text box, such as
DELETE FROM Users WHERE UserName = “John Doe”;
and the output is
Query OK, 5 rows affected (0.05 sec)
Any help would be appreciated.
Ernest Bonat, Ph.D. Says:
September 15th, 2007 at 4:21 pm
Meis,

Here is the solution for you. To get the rows affected you’ll need to use the ExecuteNonQuery() method of the MySQLCommand object. You may go the MySQL ADO.NET help file at C:\Program Files\MySQL\MySQL Connector Net 5.0.8\Documentation and learn how to do that. It’s not very hard. Let me know for any more help.

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
Meis Says:
September 18th, 2007 at 4:38 pm
Dr. Ernest, that’s close to what I am wanting, but I was looking for the server’s output more than a flat integer of affected rows.
By the way, I am also trying to bind a TreeView object to a “Show tables” command in a way that the top node says the database name, the sub nodes show table names and their sub nodes list the columns and then a tooltip to on mouse hover to show the columns properties. Is this possible?

Thanks for the assistance you have given so far.
Ernest Bonat, Ph.D. Says:
September 19th, 2007 at 5:43 pm
Meis,

I don’t any ADO.NET objects or any API functions that can return the server’s output. In fact, I never have any request for any client about this output value. Why the developers need to know this value. Let me ask some friends about it?

About the TreeView data loading? Please don’t use data binding at all in VB.NET 2005, we have no prove about this development on real production environment. Here is an idea: I think you may use the ADO.NET mapping objects to get the structure of the database. We did that in VB 6.0, check it out in VB.NET and let us know.

Thank you very much for all your questions!

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
Vladan Says:
September 24th, 2007 at 7:11 am
Thank you for help.
This is the best tutorial.
Osvaldo Armas Says:
September 26th, 2007 at 6:28 pm
Your work is excellent, Thank you
Meis Says:
September 26th, 2007 at 7:18 pm
Dr. Ernest, I have never used ADO, this is fairly new territory for me. I am experienced with SQL and VB .Net but not using them together. Any leads would be appreciated.
Ernest Bonat, Ph.D. Says:
September 26th, 2007 at 11:02 pm
Meis,

This is very simple. Go to Google and type in ADO.NET. You’ll find many good places about ADO.NET. A good starting point should the MySQL ADO.NET help file at C:\Program Files\MySQL\MySQL Connector Net 5.0.8\Documentation\ MySql.Data.chm. Let me know?

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
Osvaldo Armas Says:
October 1st, 2007 at 8:07 pm
I think this sentences is wrong: SQL = “SELECT s.message_id, s.message ” _
& “FROM status s WHERE user_id IS NULL and deleted = ‘False’”

The ‘NOT’ is missing

The correct sentences must be (I think): SQL = “SELECT s.message_id, s.message ” _
& “FROM status s WHERE user_id IS NOT NULL and deleted = ‘False’”

the next moment, i’m reading the all tutorial
Philippe Spileers Says:
November 3rd, 2007 at 12:52 pm
Hello,

the option dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells seems not to be available in Visual Studio 2003. Is there a simmilar instruction available in VStudio 2003 in order to obtain the same result of resizing the column width ?

Furthermore, after executing all the code mentioned in this tutorial, the Refresh option seems to fail. When I look into the event table, a new record is creatad as a result of the INSERT instruction , but the user_id and the creator_id have a value of zeo, causing a failure of the update. Can anybody direct me to the solution to this problem ? Thanks !
Mark R Says:
November 6th, 2007 at 3:19 pm
I would like to use the Data Grid View (dgv) for data entry purposes instead of just displaying data. If I had a scrolled area for data entry of invoice detail, for example, is there a way to (1) protect some columns as display only and (2) update those display only fields as other data is entered. For example, consider this scrolled area:

Quantity ProductCode ProductName UnitCost ExtendedCost Notes
8 ABC123 Widgets 8.23 65.84 Ship All or None

In the above example, the user would enter the Quantity and the Product Code. The user could not get into the fields ProductName, UnitCost or ExtendedCost. When the user presses TAB after entering the ProductCode, the computer would look up in a database the product code and get the Product name and Unit Cost. The computer would display these immediately, as well as multiplying Quantity by Unit Cost, giving Extended Cost. The TAB key would then put the user in the Notes field, where he would enter as necessary, then the next tab would place him in the next row, in the quantity field.

Is such a structure possible? I’ve been able to update the Product Name, Unit Cost and Extended Cost, but VB won’t display it until the user leaves the row, after entering in the notes field. By creating a subroutine that handles dgv.CellEnter and calling a routine called UpdateScrolledArea(), this marginally works. In the UpdateScrolledArea() routine, I scan the entire table and lookup the product code (using an in-memory table). When I find the entered product code, I set the dgv to the product name with the statement:

dgv.DataSource.Rows.Item(RowSub).Item(2) =

The problem is that the cell does not update until I leave the row.

Ideas?
Nico Says:
November 10th, 2007 at 1:08 pm
Hi there,
I try to follow this course. Great stuff. Thanks.
I use Visual Basic 2005 Version 8.0.50727.42. Maybe it has something to do with it?

I have add “dgvStatus.DataSource = myData” as last and try then to run the program.

I get an error on “conn.open()”, “Key cannot be null.”

I don’t know how this come can somebody help me out here…..

Thanks in advance, Nico
Mark R Says:
November 10th, 2007 at 4:39 pm
Nico,

Set a breakpoint on the conn.open() line that fails. Try running the program up to the breakpoint. Then, using QuickWatch, see what the value is of “myConnString”. Make sure it looks properly formed. Also, make sure that before the conn.open() the program did this:

conn.ConnectionString = myConnString

Sounds to me like you aren’t getting the right connection string into conn.ConnectionString.
Nico Says:
November 11th, 2007 at 10:49 pm
As you can see, I have placed two times a message box. The first time it give back my server string, the second it doesn’t, it is empty.

Public WriteOnly Property connectionString() As String
Set(ByVal value As String)
myConnString = value
MsgBox(”first ” & myConnString)
End Set
End Property

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click

MsgBox(”second ” & myConnString)

Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String = “SELECT CONCAT(u.lastname, ‘, ‘, u.firstname) AS Name, s.message AS Message, ” _
& “DATE_FORMAT(e.timestamp,’%b %d %Y – %r’) AS DateTime, e.status AS Status ” _
& “FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u ” _
& “WHERE(e.user_id = u.user_id) ” _
& “AND event_id IN( ” _
& “SELECT MAX(e.event_id) ” _
& “FROM event e ” _
& “GROUP BY e.user_id) ” _
& “ORDER BY name”
conn.ConnectionString = myConnString

Try
conn.Open()
Catch myerror As MySqlException
MessageBox.Show(”Error Connecting to Database: ” & myerror.Message)
End Try

myCommand.Connection = conn
myCommand.CommandText = SQL
myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)
dgvStatus.DataSource = myData
End Sub
Ernest Bonat Says:
November 12th, 2007 at 1:40 am
Hi Nico,

Try this code and let me know in what line you got the crash!

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click

MsgBox(”second ” & myConnString)

Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String = “SELECT CONCAT(u.lastname, ‘, ‘, u.firstname) AS Name, s.message AS Message, ” _
& “DATE_FORMAT(e.timestamp,’%b %d %Y – %r’) AS DateTime, e.status AS Status ” _
& “FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u ” _
& “WHERE(e.user_id = u.user_id) ” _
& “AND event_id IN( ” _
& “SELECT MAX(e.event_id) ” _
& “FROM event e ” _
& “GROUP BY e.user_id) ” _
& “ORDER BY name”
Try
conn.ConnectionString = myConnString
conn.Open()
myCommand.Connection = conn
myCommand.CommandText = SQL
myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)
dgvStatus.DataSource = myData
Catch myerror As MySqlException
MessageBox.Show(”Error Connecting to Database: ” & myerror.Message)
Finally
myAdapter.Dispose()
myCommand.Dispose()
conn.Close()
conn.Dispose()
End Try

End Sub

Ernest Bonat, Ph.D.
President and CEO
Visual WWW, Inc.
Cell:503.730.4556
Fax:503.715.4888
ebonat@evisualwww.com
http://www.evisualwww.com
Visual WWW is an Open Source
Application Development and Training Shop
Andrew Says:
November 12th, 2007 at 7:24 pm
Great tutorial so far – still working through it but spotted what I think might be a minor typo ….

You suggest using

dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

in order to have the columns fit the data nicely but I think it should actually be

dgvStatus.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

It worked okay using my idea and the columns stayed narrow and even with yours. Seemed to make more sense as well given we were interested in columnwidth rather than row height.
Nico Says:
November 12th, 2007 at 10:25 pm
Dear Ernest,
I have changed as you ask. Second message is still empty. On conn.open() is still an error (logic is the string is empty).

How is it possible that the string is empty????

Nico
Nico Says:
November 12th, 2007 at 10:46 pm
I think it is maybe a good idea to print out the peace that is calling the mainform.

Try
conn.Open()
conn.Close()
Dim mainForm As New frmMain
frmMain.connectionString = myConnString
mainForm.Show()
Me.Hide()
Me.Close()
Catch myerror As MySqlException
MessageBox.Show(”Error Connecting to Database: ” & myerror.Message)
conn.Dispose()
End Try

If I place a breakpoint on Me.close() and I check all variables they are all filled with the serverstring. One step on and the variables are empty.

Nico
Ernest Bonat, Ph.D. Says:
November 12th, 2007 at 11:57 pm
Hey Nico,

You can try to comment the following line:

‘Me.Close()

Let me know if the ConnectionString myConnString is still empty.

Thanks

Ernest Bonat, Ph.D.
http://www.evisualwww.com
Nico Berg Says:
November 13th, 2007 at 9:23 am
Yes, I allready tried that. It is still empty. Do I have a ghostcomputer?

Nico
Ernest Bonat, Ph.D. Says:
November 13th, 2007 at 4:15 pm
Hey Nico,

Zip your entire project folder and send it to me. I’ll check it out for you tonight and let you know were the problem.

Thanks

Ernest Bonat, Ph.D.
President and CEO
Visual WWW, Inc.
Cell:503.730.4556
Fax:503.715.4888
ebonat@evisualwww.com
http://www.evisualwww.com
Visual WWW is an Open Source
Application Development and Training Shop
Nico Says:
November 14th, 2007 at 6:24 pm
Ernest has repaired the code for me. I have seen the changes, I understand them but I still don’t know why the old situation didn’t work.

On frmLogin
Try
conn.ConnectionString = myConnString
conn.Open()
conn.Close()
Dim mainForm As New frmMain(myConnString)
‘frmMain.connectionString = myConnString
mainForm.Show()
Me.Hide()
Me.Close()
Catch myerror As MySqlException
MessageBox.Show(”Error Connecting to Database: ” & myerror.Message)
conn.Dispose()
End Try

On frmMain

Public Class frmMain
Private myConnString As String

‘Public WriteOnly Property connectionString() As String
‘ Set(ByVal value As String)
‘ myConnString = value
‘ End Set
‘End Property

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
‘MsgBox(”second ” & myConnString)

Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String = “SELECT CONCAT(u.lastname, ‘, ‘, u.firstname) AS Name, s.message AS Message, ” _
& “DATE_FORMAT(e.timestamp,’%b %d %Y – %r’) AS DateTime, e.status AS Status ” _
& “FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u ” _
& “WHERE(e.user_id = u.user_id) ” _
& “AND event_id IN( ” _
& “SELECT MAX(e.event_id) ” _
& “FROM event e ” _
& “GROUP BY e.user_id) ” _
& “ORDER BY name”
Try
conn.ConnectionString = myConnString
conn.Open()
myCommand.Connection = conn
myCommand.CommandText = SQL
myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)
dgvStatus.DataSource = myData
dgvStatus.Refresh()
Catch myerror As MySqlException
MessageBox.Show(”Error Connecting to Database: ” & myerror.Message)
Finally
myAdapter.Dispose()
myCommand.Dispose()
conn.Close()
conn.Dispose()
End Try
End Sub

Public Sub New(ByVal pmyConnString As String)
InitializeComponent()
myConnString = pmyConnString
End Sub

End Class

For all the people having the same problem or if the writers of this tutorial want to fix. This is the working code.

Nico
Irfan Says:
February 9th, 2008 at 12:44 pm
Hi ernest,
I am from indonesia and i am new to VB 2005,as information that My english is no good. I have 2 problems :

1. When i try to make connection from mysql database then bind to combobox, but there is a problem occures because first row from tblpenerbit not display it. The same as problem also when it bind to listview control. This is the foolowing its code.

Private Sub Form3_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
cmd = New MySqlCommand
da = New MySqlDataAdapter
conn = New MySqlConnection
conn.ConnectionString = “Server=localhost;user id=root;password=salafy; database= ” & My.Settings.database & “”

strpilih = “Select p.id_penerbit,p.Penerbit from tblpenerbit p”
Try
conn.Open()
‘isicboPenerbit()
Try
cmd = New MySqlCommand(strpilih, conn)
cmd.CommandType = CommandType.Text
dr = cmd.ExecuteReader
dr.Read()
Do While dr.Read()
Me.cboPenerbit.Items.Add(dr(”Penerbit”))
Loop
conn.Close()
Me.cboPenerbit.SelectedIndex = 0

Catch ex As MySqlException
MsgBox(”There was an error reading from the database: ” + ex.Message)
End Try

Catch ex As Exception
MsgBox(”Error connecting to the database:” + ex.Message)
Finally
If conn.State ConnectionState.Closed Then conn.Close()

End Try

End Sub

2. I try use your code from tutorial 4 but when I debugg, oh..there are error message fire,”Error connecting to the database : value cannot be null.Parameter name : datatable” and this the code

Private Sub Form3_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

cmd = New MySqlCommand
da = New MySqlDataAdapter
conn = New MySqlConnection
conn.ConnectionString = “Server=localhost;user id=root;password=salafy; database= ” & My.Settings.database & “”

strpilih = “Select p.id_penerbit,p.Penerbit from tblpenerbit p”
Try
conn.Open()
Try
cmd.Connection = conn
cmd.CommandText = strpilih
da.SelectCommand = cmd
da.Fill(mydata)
With cboPenerbit
.DataSource = mydata
.DisplayMember = “Penerbit”
.ValueMember = “id_penerbit”
End With
Catch ex As MySqlException
MsgBox(”There was an error reading from the database ;” + ex.Message)
End Try
Catch ex As Exception
MsgBox(”Error Connecting to Database :” + ex.Message)
Finally
If conn.State ConnectionState.Closed Then conn.Close()

End Try

I hope you can give me troubleshooting from my problem, finally i tell to all people in the world that your tutorial is very useful. and dont make born to teach us about Mysql and VB.Net 2005.

Thank’s greatfully
Ernest Bonat, Ph.D. Says:
February 12th, 2008 at 6:23 pm
Hi Irfan,

Error 1: You wrote the SQL statement as:

strpilih = “Select p.id_penerbit,p.Penerbit from tblpenerbit p”

Try this one:

strpilih = “Select p.id_penerbit, p.Penerbit From tblpenerbit p Order By p.Penerbit”

Them, change the field name in the looping as:

Me.cboPenerbit.Items.Add(dr(”p.Penerbit”))

Let me know if these changes help you?

Error 2:

You have:

.DisplayMember = “Penerbit”
.ValueMember = “id_penerbit”

May be it should be:

.DisplayMember = “p.Penerbit”
.ValueMember = “p.id_penerbit”

Try that and let me know.

Both errors may be because the Select fields name. Just in case this tutorail was written in VB.NET 2002. You need to use VB.NET 2005 0r 2008 code. You may go to my website and learn more. Check out this link:

http://www.evisualwww.com/project_download.php

Let me kmow for any more helps?

Thanks

Ernest Bonat, Ph.D.
President | Visual WWW, Inc.
Phone:503.730.4556 | Fax:503.715.4888
ebonat@evisualwww.com | http://www.evisualwww.com
Visual WWW is an Open Source Application
Development and Training Shop
Tony Jose Says:
February 12th, 2008 at 8:16 pm
Imports Imports System.ServiceProcess.ServiceController
Imports System.IO
Imports Microsoft.Win32

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

‘
‘Service Controller is used to stop and start the MySqlService
‘Refernce added from Solution Explorer
‘Name space : System.ServiceExplorer()
‘
Dim mySqlServiceController As New ServiceProcess.ServiceController
mySqlServiceController.ServiceName = “Mysql”

‘If status=1 means service is stopped.
If mySqlServiceController.Status = 1 Then
Else
mySqlServiceController.Stop()
End If

‘chage passwod qurery as String
Dim chpwdqry As String

‘The qurey used to change change the password encolsed in single quotes
chpwdqry = “SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD(’jobing’);”

‘The file name in which the above query is saved is C:\mysql-init.txt
If File.Exists(”C:\mysql-init.txt”) Then
File.Delete(”C:\mysql-init.txt”)

Dim filestream As New FileStream(”C:\mysql-init.txt”, FileMode.Create)
Dim ipstream As New StreamWriter(filestream) ‘create reading stream
ipstream.Write(chpwdqry) ‘Write a to file
ipstream.Close() ‘ close reading stream
filestream.Close() ‘ close filestream
Else
Dim filestream As New FileStream(”C:\mysql-init.txt”, FileMode.Create)
Dim ipstream As New StreamWriter(filestream) ‘create reading stream
ipstream.Write(chpwdqry) ‘Write a to file
ipstream.Close() ‘ close reading stream
filestream.Close() ‘ close filestream

End If

‘The retPointer shows the ProcesID
Dim retPointer As Integer

‘Shell Open the application here cmd.exe the MSDOS command prompt
retPointer = Shell(”cmd.exe”, AppWinStyle.MinimizedFocus)

MsgBox(retPointer)

Dim strCommand As String ‘The value is passed to cmd
AppActivate(retPointer) ‘Activated cmd

‘ Define the RegistryKey objects for the registry hives.
Dim regClasses As RegistryKey = Registry.LocalMachine

‘ Check whether MySql 5.0 is installed on this computer,
‘ by searching the HKEY_CLASSES_LocalMachine
‘ Local Machine expands as Software Hardware etc. Select SOFTWARE
‘ Among the SOFTWARE LISTING select the MYSQL AB which shows the Company Name
‘ Among the list of MySql AB softwares select MySQL Server 5.0
‘ MySQL Server 5.0 Now has 3 fields
‘ 1. Default: Not needed here
‘ 2. Location : Specifies the location in which the software is Installed
‘ 3. Version : Specifies the MySQL Server Version.

Dim regWord As RegistryKey = regClasses.OpenSubKey(”SOFTWARE”)
Dim regMySqlFld As RegistryKey = regWord.OpenSubKey(”MYSQL AB”)
Dim regMySqlVersion As RegistryKey = regMySqlFld.OpenSubKey(”MySQL Server 5.0″)

‘The below array stores the Location and Version details of MySql Server
Dim siz(2) As String
siz(0) = regMySqlVersion.GetValue(”Location”)
siz(1) = regMySqlVersion.GetValue(”Version”)

‘ Always close Registry keys after using them.
regClasses.Close()
regWord.Close()
regMySqlFld.Close()
regMySqlVersion.Close()

‘The below string execInDOS is the final Command in executing the application
Dim execInDOS As String

‘ The “”"” is used to get A double quote(”) in the string.
execInDOS = “”"”

‘The final string is obtained below
execInDOS = execInDOS + siz(0) + “bin\mysqld-nt.exe” + execInDOS + ” ” + “–defaults-file=” + execInDOS + siz(0) + “my.ini” + execInDOS + ” ” + “–init-file=C:\mysql-init.txt”

If regMySqlVersion Is Nothing Then
MsgBox(”MySql Is Not Installed”)
Else
‘ MsgBox(”MySql Is Installed”)
End If

‘ Passes value to the strCommand
strCommand = execInDOS

‘The the execution command is written to the check.txt
If Not File.Exists(”C:\check.txt”) Then

Dim filestream1 As New FileStream(”C:\check.txt”, FileMode.Create)
Dim ipstream1 As New StreamWriter(filestream1) ‘create reading stream
ipstream1.Write(strCommand) ‘Write a to file
ipstream1.Close()
filestream1.Close()
Else
File.Delete(”C:\check.txt”)
Dim filestream1 As New FileStream(”C:\check.txt”, FileMode.Create)
Dim ipstream1 As New StreamWriter(filestream1) ‘create reading stream
ipstream1.Write(strCommand) ‘Write a to file
ipstream1.Close()
filestream1.Close()

End If

‘Activates the cmd using the ProcessID
AppActivate(retPointer)

‘Flushes the exiting Commands
‘SendKeys.Flush()

‘The most important command
‘Send the command to the cmd
SendKeys.SendWait(strCommand)

‘Enter Option in the DOS
SendKeys.SendWait(”{ENTER}”)

‘The EXIT Command
SendKeys.SendWait(”EXIT”)

‘The Enter Option
SendKeys.SendWait(”{ENTER}”)

‘Force remove the mysqld-nt.exe
Dim myProcesses() As Process
Dim myProcess As Process
Dim teller As Integer = 0

‘ Returns array containing all instances of mysqld-nt.exe”.
myProcesses = Process.GetProcessesByName(”mysqld-nt”)
For Each myProcess In myProcesses
myProcess.Kill()
teller += 1
Next
If teller > 0 Then
MessageBox.Show(”closed”)
Else
MessageBox.Show(”Nothing to be closed”)
End If

‘Deletes the mysql-ini.txt if it exits
If File.Exists(”C:\mysql-init.txt”) Then
File.Delete(”C:\mysql-init.txt”)
End If

If mySqlServiceController.Status = 3 Then
MsgBox(”Wait”)
ElseIf mySqlServiceController.Status = 4 Then
‘mySqlServiceController.Stop()
MsgBox(”Running”)
ElseIf mySqlServiceController.Status = 1 Then
MsgBox(”Stopped”)
Else
mySqlServiceController.Stop()
End If

‘Restart the service again for working.
mySqlServiceController.Start()

End Sub
Tony Jose Says:
February 12th, 2008 at 8:18 pm
‘Here is the source code to change the root password from Windows. I havebeen trying a lot.
It halts evenly, works evenly.

Imports Imports System.ServiceProcess.ServiceController
Imports System.IO
Imports Microsoft.Win32

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

‘
‘Service Controller is used to stop and start the MySqlService
‘Refernce added from Solution Explorer
‘Name space : System.ServiceExplorer()
‘
Dim mySqlServiceController As New ServiceProcess.ServiceController
mySqlServiceController.ServiceName = “Mysql”

‘If status=1 means service is stopped.
If mySqlServiceController.Status = 1 Then
Else
mySqlServiceController.Stop()
End If

‘chage passwod qurery as String
Dim chpwdqry As String

‘The qurey used to change change the password encolsed in single quotes
chpwdqry = “SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD(’jobing’);”

‘The file name in which the above query is saved is C:\mysql-init.txt
If File.Exists(”C:\mysql-init.txt”) Then
File.Delete(”C:\mysql-init.txt”)

Dim filestream As New FileStream(”C:\mysql-init.txt”, FileMode.Create)
Dim ipstream As New StreamWriter(filestream) ‘create reading stream
ipstream.Write(chpwdqry) ‘Write a to file
ipstream.Close() ‘ close reading stream
filestream.Close() ‘ close filestream
Else
Dim filestream As New FileStream(”C:\mysql-init.txt”, FileMode.Create)
Dim ipstream As New StreamWriter(filestream) ‘create reading stream
ipstream.Write(chpwdqry) ‘Write a to file
ipstream.Close() ‘ close reading stream
filestream.Close() ‘ close filestream

End If

‘The retPointer shows the ProcesID
Dim retPointer As Integer

‘Shell Open the application here cmd.exe the MSDOS command prompt
retPointer = Shell(”cmd.exe”, AppWinStyle.MinimizedFocus)

MsgBox(retPointer)

Dim strCommand As String ‘The value is passed to cmd
AppActivate(retPointer) ‘Activated cmd

‘ Define the RegistryKey objects for the registry hives.
Dim regClasses As RegistryKey = Registry.LocalMachine

‘ Check whether MySql 5.0 is installed on this computer,
‘ by searching the HKEY_CLASSES_LocalMachine
‘ Local Machine expands as Software Hardware etc. Select SOFTWARE
‘ Among the SOFTWARE LISTING select the MYSQL AB which shows the Company Name
‘ Among the list of MySql AB softwares select MySQL Server 5.0
‘ MySQL Server 5.0 Now has 3 fields
‘ 1. Default: Not needed here
‘ 2. Location : Specifies the location in which the software is Installed
‘ 3. Version : Specifies the MySQL Server Version.

Dim regWord As RegistryKey = regClasses.OpenSubKey(”SOFTWARE”)
Dim regMySqlFld As RegistryKey = regWord.OpenSubKey(”MYSQL AB”)
Dim regMySqlVersion As RegistryKey = regMySqlFld.OpenSubKey(”MySQL Server 5.0″)

‘The below array stores the Location and Version details of MySql Server
Dim siz(2) As String
siz(0) = regMySqlVersion.GetValue(”Location”)
siz(1) = regMySqlVersion.GetValue(”Version”)

‘ Always close Registry keys after using them.
regClasses.Close()
regWord.Close()
regMySqlFld.Close()
regMySqlVersion.Close()

‘The below string execInDOS is the final Command in executing the application
Dim execInDOS As String

‘ The “”"” is used to get A double quote(”) in the string.
execInDOS = “”"”

‘The final string is obtained below
execInDOS = execInDOS + siz(0) + “bin\mysqld-nt.exe” + execInDOS + ” ” + “–defaults-file=” + execInDOS + siz(0) + “my.ini” + execInDOS + ” ” + “–init-file=C:\mysql-init.txt”

If regMySqlVersion Is Nothing Then
MsgBox(”MySql Is Not Installed”)
Else
‘ MsgBox(”MySql Is Installed”)
End If

‘ Passes value to the strCommand
strCommand = execInDOS

‘The the execution command is written to the check.txt
If Not File.Exists(”C:\check.txt”) Then

Dim filestream1 As New FileStream(”C:\check.txt”, FileMode.Create)
Dim ipstream1 As New StreamWriter(filestream1) ‘create reading stream
ipstream1.Write(strCommand) ‘Write a to file
ipstream1.Close()
filestream1.Close()
Else
File.Delete(”C:\check.txt”)
Dim filestream1 As New FileStream(”C:\check.txt”, FileMode.Create)
Dim ipstream1 As New StreamWriter(filestream1) ‘create reading stream
ipstream1.Write(strCommand) ‘Write a to file
ipstream1.Close()
filestream1.Close()

End If

‘Activates the cmd using the ProcessID
AppActivate(retPointer)

‘Flushes the exiting Commands
‘SendKeys.Flush()

‘The most important command
‘Send the command to the cmd
SendKeys.SendWait(strCommand)

‘Enter Option in the DOS
SendKeys.SendWait(”{ENTER}”)

‘The EXIT Command
SendKeys.SendWait(”EXIT”)

‘The Enter Option
SendKeys.SendWait(”{ENTER}”)

‘Force remove the mysqld-nt.exe
Dim myProcesses() As Process
Dim myProcess As Process
Dim teller As Integer = 0

‘ Returns array containing all instances of mysqld-nt.exe”.
myProcesses = Process.GetProcessesByName(”mysqld-nt”)
For Each myProcess In myProcesses
myProcess.Kill()
teller += 1
Next
If teller > 0 Then
MessageBox.Show(”closed”)
Else
MessageBox.Show(”Nothing to be closed”)
End If

‘Deletes the mysql-ini.txt if it exits
If File.Exists(”C:\mysql-init.txt”) Then
File.Delete(”C:\mysql-init.txt”)
End If

If mySqlServiceController.Status = 3 Then
MsgBox(”Wait”)
ElseIf mySqlServiceController.Status = 4 Then
‘mySqlServiceController.Stop()
MsgBox(”Running”)
ElseIf mySqlServiceController.Status = 1 Then
MsgBox(”Stopped”)
Else
mySqlServiceController.Stop()
End If

‘Restart the service again for working.
mySqlServiceController.Start()

End Sub
Tony Jose Says:
February 12th, 2008 at 8:25 pm
What I am looking forward is a completely working Root pwd changer
Ernest Bonat, Ph.D. Says:
February 12th, 2008 at 8:35 pm
Hi Tony,

I did not understand your question. Please let me know so I can help you!

Thanks

Ernest Bonat, Ph.D.
President | Visual WWW, Inc.
Phone:503.730.4556 | Fax:503.715.4888
ebonat@evisualwww.com | http://www.evisualwww.com
Visual WWW is an Open Source Application
Development and Training Shop
Tony Jose Says:
February 12th, 2008 at 8:43 pm
‘This was what I was working out the code is still in this forum, The code is not working properly,

A.4. Administration-Related Issues

A.4.1. How to Reset the Root Password

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. However, it is recommended to set a password for each account. See Section 5.6.1, “General Security Guidelines”.

If you set a root password previously, but have forgotten what it was, you can set a new password. The following procedure is for Windows systems. The procedure for Unix systems is given later in this section.

The procedure under Windows:

Log on to your system as Administrator.

Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager:

Start Menu -> Control Panel -> Administrative Tools -> Services

Then find the MySQL service in the list, and stop it.

If your server is not running as a service, you may need to use the Task Manager to force it to stop.

Create a text file and place the following command within it on a single line:

SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD(’MyNewPassword’);

Save the file with any name. For this example the file will be C:\mysql-init.txt.

Open a console window to get to the DOS command prompt:

Start Menu -> Run -> cmd

We are assuming that you installed MySQL to C:\mysql. If you installed MySQL to another location, adjust the following commands accordingly.

At the DOS command prompt, execute this command:

C:\> C:\mysql\bin\mysqld-nt –init-file=C:\mysql-init.txt

The contents of the file named by the –init-file option are executed at server startup, changing the root password. After the server has started successfully, you should delete C:\mysql-init.txt.

Users of MySQL 4.1 and higher who install MySQL using the MySQL Installation Wizard may need to specify a –defaults-file option:

C:\> “C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt.exe”

–defaults-file=”C:\Program Files\MySQL\MySQL Server 4.1\my.ini”

–init-file=C:\mysql-init.txt

The appropriate –defaults-file setting can be found using the Services Manager:

Start Menu -> Control Panel -> Administrative Tools -> Services

Find the MySQL service in the list, right-click on it, and choose the Properties option. The Path to executable field contains the –defaults-file setting.

Stop the MySQL server, then restart it in normal mode again. If you run the server as a service, start it from the Windows Services window. If you start the server manually, use whatever command you normally use.

You should be able to connect using the new password.
Tadpole Says:
March 4th, 2008 at 8:08 pm
Dear Mr. Bonat,
First I wanted to say that I don’t need help with anything.
Secondly, I wanted to say that this is one of the best tutorials I have EVER had the pleasure of seeing in ALL my years on the internet! Not only have
you provided an exceptionally well documented tutorial that is written for *everyday users*, but you have gone through the trouble of answering all
the quesitons that the users have tossed at you. I think I speak for MANY others out there when I say THANK YOU! THANK YOU! THANK YOU!

Most sincerely and respectfully,
Jay Schienle
Ernest Bonat, Ph.D. Says:
March 4th, 2008 at 8:44 pm
Dear Jay,

These VB.NET/MySQL tutorials were written by Mike Hillyer (a former MySQL AB Technical Writer). He did unbelievable job for our VB.NET community. I’m helping him right now to keep our forum running. Thank you very much for your kind words. Feel free to contact me at any time you need.

Thanks again!
Kifli Says:
April 24th, 2008 at 2:01 pm
Dear Mr. Bonat,

I’m sorry if my English isn’t good, but with the help of a dictionary I want to extend gratitude to you and Mr. Hillyer for the articles and your effort to answer the questions.
The step by step explanation helps me more to understand the languages, especially in the connection’s declarations, entity design, and the implementations.
I hope you have no objections if I print these complete tutorials out. Some times reading is softer on my eyes than staring at the monitor for hours…
Thanks again, terima kasih and matur nuwun…

Regards from Batam, Indonesia..
Ernest Bonat, Ph.D. Says:
April 24th, 2008 at 2:45 pm
Hi Terima and Matur,

Feel free to print this tutorial and use it in your VB/MySQL projects. Thank you very much for asking. Let me know for any futher questions.

Thanks again,

Ernest Bonat, Ph.D.
President | Visual WWW, Inc.
Phone:503.730.4556 | Fax:503.715.4888
ebonat@evisualwww.com | http://www.evisualwww.com
Visual WWW is an Open Source Application
Development and Training Shop
Shuvo Says:
May 4th, 2008 at 2:02 pm
I’ve faced 1 error & 5 warnings with these frmLogin & frmMain codes. I’ve downloaded the tutorial files of part4 & checked but still has that error. I’m using VB.NET 2008, is that a problem?
Ernest Bonat, Ph.D. Says:
May 4th, 2008 at 5:42 pm
Hi Shuvo,

No, VB.NET 2008 is not the problem for sure! Zip the entire project and send to me for review. I’ll do that for you tonight!

Thanks

Ernest Bonat, Ph.D.
ebonat@evisualwww.com
http://www.evisualwww.com
Shuvo Says:
May 7th, 2008 at 5:25 am
Hi Ernest Bonat Sir, I’ve sent the mail two days ago with my projects. Please check your spam box also.

Thanks
Shuvo Says:
May 7th, 2008 at 9:48 am
Dear Ernest Bonat Sir, I’ve found the probelm actually. Now its work. I’ll let you know later if any problem.

Another thing is, In my case ‘dgvStatus.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells’ is works perfect instead of ‘dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells’.
Ernest Bonat, Ph.D. Says:
May 7th, 2008 at 3:46 pm
Glad that every thing works for you!
Shuvo Says:
May 8th, 2008 at 7:25 am
Dear Ernest Bonat Sir, I dont understand two things about this part4. Those are below,

1. If conn.State ConnectionState.Closed Then conn.Close()

2. myCommand.ExecuteNonQuery()

If you please explain about those commands line I’ll be glad. Otherwise everything I understand so well

Thanks
Ernest Bonat, Ph.D. Says:
May 8th, 2008 at 2:50 pm
Hi Shuvo!

Hi Shuvo!

1. The ConnectionState property of the Connection object checks for open and close connection. Before to close the connection you need to check the status. So, if the connection is not close you can close it, otherwise you can.

2. The ExecuteNonQuery() method of the command object is used to execute INSERT, UPDATE and DELETE SQL statements.

Please go to the MySQL Connector .NET help file and find all required info to read and understand more about it.

Thanks

Ernest Bonat, Ph.D.
President | Visual WWW, Inc.
Phone:503.730.4556 | Fax:503.715.4888
ebonat@evisualwww.com | http://www.evisualwww.com
Visual WWW is an Open Source Application
Development and Training Shop
Joshua Says:
June 9th, 2008 at 12:50 pm
Hi,

How do I export the results of the query to an excel sheet?

Please reply back

Thanks
~Josh
Ernest Bonat, Ph.D. Says:
June 9th, 2008 at 3:28 pm
Josh,

Your question is very simple. Go to Google and type in: “How do I export the results of the query to an excel sheet in VB.NET?” You’ll get many links with very good VB.NET code from many developers, get whatever you like it and change it for your program.

Thanks

Ernest Bonat, Ph.D.
President | Visual WWW, Inc.
Phone:503.730.4556 | Fax:503.715.4888
ebonat@evisualwww.com | http://www.evisualwww.com
Visual WWW is an Open Source Application
Development and Training Shop
Sh_oK Says:
July 21st, 2008 at 4:41 pm
One of the best tutorials regarding MYSQL and vb.net. I learned a lot from it.
Congratualtions
Ridwan Says:
July 23rd, 2008 at 6:52 am
Hi Mr.Borat
Firts of all, I would like tohank you very very much for your articles. It helps me to connect my VB.NET program to Mysql.
However, there is somthing that I want to ask. I usually use ADO in VB, and I get used to use a recordset.

Please guide me how to create recorset in VB.NET via Mysql ( I already connected to Database by your code)
It is simple question,, How to show data or a field into Textbox or Combo Box
Thanks

Rgds/ Ridwan
Ernest Bonat, Ph.D. Says:
July 23rd, 2008 at 2:25 pm
Hi Rgds/ Ridwan,

Very simple! Go to my website downloads (http://www.evisualwww.com/project_download.php) and get the docs and source code of the MySQL/VB.NET programs. Every thing you need is there. Happy VB.NET code!

Thanks

Ernest Bonat, Ph.D.
President | Visual WWW, Inc.
Phone:503.730.4556 | Fax:503.715.4888
ebonat@evisualwww.com | http://www.evisualwww.com
Visual WWW is an Open Source Application
Development and Training Shop
Ridwan Says:
July 24th, 2008 at 1:51 am
Hi Bonat,
Thank you very much for your tutorial and code.
You help me very much.

Rgds/ Ridwan
Ernest Bonat, Ph.D. Says:
July 24th, 2008 at 4:10 am
Any time. Just ask!

Thanks

Ernest Bonat, Ph.D.
President | Visual WWW, Inc.
Phone:503.730.4556 | Fax:503.715.4888
ebonat@evisualwww.com | http://www.evisualwww.com
Visual WWW is an Open Source Application
Development and Training Shop
Fernando Says:
September 12th, 2008 at 2:36 am
what about using a database like MySql?

Let’s say the database name is MyDatabase (MySql)

Relationship(One Client Many Orders; One Order Many Products)

table1(ClientTable) has ClientId , FName and LastName fields

table2(OrderTable) has OrderId,ClientID,ProductId,Date,Quantity

table3(ProductTable) has ProductId,Description,Price

There will be a form showing table one in form style and tables 1 and 2 in a datagrid

showing the only the following fields:

Quantity(t2) Description(t3) Price(t3) ExtendedPrice(added)

how can that be approached
Rozie Says:
October 7th, 2008 at 3:28 am
im using vbnet 2003..
1) my prob is when i click the save buton it will save null value in the sql server.. why? can u explain and give me an example of save buton..

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim strInsert As String
Dim intAddCount As Integer
Dim cmdinsert As OdbcCommand
Dim MyconString = New OdbcConnection(”Driver={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=hhdatabase; USER=root;PASSWORD=root;OPTION=3;”)

strInsert = “Insert into meter_reader(HandHeldId,StaffNo,Name)” & _
“Values(@HandHeldId,@StaffNo,@Name)”
MyconString.Open()

cmdinsert = New OdbcCommand(strInsert, MyconString)
intAddCount = cmdinsert.ExecuteNonQuery()
cmdinsert.Parameters.Add(”?HandHeldId”, txtHand.Text)
cmdinsert.Parameters.Add(”?StaffNo”, txtStaff.Text)
cmdinsert.Parameters.Add(”?Name”, txtName.Text)
MyconString.Close()

Label1.Text = intAddCount & ” records Added!”
End Sub

2) when i finish download the connecter i cant run the connecter coz all the file in the connecter folder is unread if must open with other program… so how i want to run the program..

thanks..
Ernest Bonat, Ph.D. Says:
October 7th, 2008 at 5:11 pm
Hi Rozie,

believe you’re missing the Execute method of the OdbcCommand object. Find out which one is the right method to do Insert, Update and Delete records. One more thing, in .NET we don’t use any more the ODBC driver to talk to SQL Server, we use the native SQL Client instead.

Thanks

Ernest Bonat, Ph.D.
Visual WWW, Inc.
http://www.evisualwww.com
Rozie Says:
October 8th, 2008 at 3:04 am
do u have tutorials on search button using odbc not sql
Sopheap Pen Says:
November 7th, 2008 at 6:24 pm
Hi. Thank you very much for the tutorials. It helps me in a great way.

I would like to know if there is a way to reset the auto-increment field of a primary key back to 1. Because I have done some tests on my DB and I deleted all the rows in a table that I have create but when I create new rows for the same table, the auto-increment primary key doesn’t start back to number 1.

Also, is it possible to change an alias of a table in the Query Browser? A lot of my entity name start with the S letter. And then, I have a lot of table who has the alias S. Ok, these questions sound basic maybe but I am new to MySQL. I have only worked with Lotus Notes before! lolll

Sopheap
Ernest Bonat, Ph.D. Says:
November 7th, 2008 at 6:48 pm
Hey Sopheap,

Here you go…

I would like to know if there is a way to reset the auto-increment field of a primary key back to 1. Because I have done some tests on my DB and I deleted all the rows in a table that I have create but when I create new rows for the same table, the auto-increment primary key doesn’t start back to number 1.

“Yes, you can that by truncating the table. Look for the Table Truncate SQL statement”

Also, is it possible to change an alias of a table in the Query Browser? A lot of my entity name start with the S letter. And then, I have a lot of table who has the alias S. Ok, these questions sound basic maybe but I am new to MySQL. I have only worked with Lotus Notes before! lolll

“I’m not sure about in Query Browser. I use Toad for MySQL.”

Let me know for any more questions?

Thanks

Ernest Bonat, Ph.D.
President | CEO
Visual WWW, Inc.
Phone:503.730.4556 | Fax:503.715.4888
ebonat@evisualwww.com | http://www.evisualwww.com
Visual WWW is an Open Source
Application Development and Training Shop
Sopheap Pen Says:
November 7th, 2008 at 7:37 pm
Thank You very much Dr. Bonat. I will do my research with the hints you gave me. Have a nice day!

Greetings,
Sopheap
confused Programmer Says:
December 5th, 2008 at 7:01 pm
dr.bonat

i am trying to create an application that will add data to any odbc compliant database. based on what i was reading i will have to have a driver for each database to be able to write code for each database connection. ex: i will have to use this ADO.Net/MySqlConnector for MySql Connections, OleDB objects for SQL Server, and etc. I thought the point of ODBC was to have somewhat of a SQL standard.

I was planning on reading the connection string from a UDL but that is only used for OleDB and since the OleDB povider for ODBC drivers is not supported by the .net framework i dont know what my workaround for reading the UDL connection string file will be.

But in any case isnt there an object that I could use to create a connectionstring from and use that for db access regardless of type of database i am using i.e. regardless of provider. i hope all this is clear but i must be sure that im understanding everything correctly so i can know how to proceed. please an explanation would be greatly appreciated.
Junnjun Mendoza Says:
January 3rd, 2009 at 2:17 pm
Hi This tutorial is really great. When i read what mike had written it seems i have a teacher with me it is very clear. Now i have a quetion, can you give me a simple example of a program like this that has an MDI form because i dont know to get the connection string from the frmLOGIN when im like say in a third form from frmLOGIN.

Junnjun
Pankaj Modi Says:
January 5th, 2009 at 9:19 pm
hey, Dude Thanx for guid line
I like UR approch to make such tutorial for the people like Me,who have egarness to do something new
I am glad to take ur guide line and thanku once again

With Regards,
Pankaj modi
Helvin Says:
February 2nd, 2009 at 2:28 am
I am writing an asp.net page and the following code is in a sub which is activated upon the click of a submit button. Somehow, the browser says that there is a syntax error and the ExecuteNonQuery line is highlighted. txt_ref_no and txt_part_no are both textboxes that the user fills in before hitting the submit button.

‘Open database and store values
Using myConnection As New MySqlConnection(”server=localhost; user id=myuser; database=database1; pooling=false;”)
myConnection.Open
Dim str = New String(”INSERT INTO table_ecn(ref_no) VALUES (txt_ref_no.Text) WHERE part_no= @search_part_no;”)
Dim sqlComm As New MySqlCommand(str, myConnection)
sqlComm.Parameters.AddWithValue(”@search_part_no”, txt_part_no.Text)
sqlComm.ExecuteNonQuery()

myConnection.Close

End Using
Tekhlu Tesfay Says:
February 16th, 2009 at 4:14 am
On form load generate 30 integer numbers and populate the first list box. The second list box is invisible on form load, when one of the option buttons are selected the second list box becomes visible. If Option1 is selected the second list box is populated with even numbers filtered from the first list box. If option2 is selected similar thing happens, the previous contents of second list box is cleared and populated with odd numbers. When Button1 is clicked an in formation is displayed on a text box on how many even and odd numbers are generated.
Mark Bunds Says:
May 1st, 2009 at 6:19 pm
(Using Visual Studio 2008) For some reason, my main form closes when my frm.login closes for any reason. If I disable the .hide and .close statements, both windows stay open, and everything works, until I close the login form with exit or the cancel button, then both forms close. What did I do wrong?
Mark Bunds Says:
May 1st, 2009 at 6:47 pm
I discovered the cause of the problem with both forms closing. In visual Studio 2008, the properties for the project will allow you to select the “Shutdown Mode” of the program to be either “When startup form closes”, or “When last form closes”. I changed the property to the latter, and now the program works as expected.
Ernest Bonat, Ph.D. Says:
May 1st, 2009 at 9:01 pm
Hey Mark,

I’m glass that you found the problem. Feel free to contact us a any time you need!
JhonQ Says:
October 7th, 2009 at 6:43 am
Great tutorial! thanks for your help
Charlie Childs Says:
October 17th, 2009 at 12:02 am
I have got as far as adding the “cbo.message” code to the Private Sub frmMain but I encounter a problem and wonder if anyone can help?

I add

cboMessage.DataSource = myData
cboMessage.DisplayMember = “message”
cboMessage.ValueMember = “message_id”

and when running the program, after adding this code, the login box appears as expected. But after filling it in and clicking the login button I get the following pop up box message on the code page of VB —

pop up box heading – “!ArgumentException was unhandled
Line underneath – Cannot bind to the new value member. Parameter name: value”

— and frmMain will not appear.

There are no errors listed in the lower pain of my Visual Basic 2008 UI.

If I remove — cboMessage.ValueMember = “message_id” — (which is the line of code the error message is attached to ) – I do not get the pop up error message and the program runs apparently well.

From what is written in the tutorial about adding that code —
EXTRACT — (After setting the source for data binding, we first specify that the message column of our query will be shown in the combobox, and that we can identify which item was selected through the message_id column, as I will later demonstrate. This does present one problem: in its current form, the combobox will not allow us to set a NULL status, because the combobox is only populated with entries from the status table (and only those messages that have no user_id and which are not marked as deleted). For the moment we will ignore this limitation and will address it in a future article.)
END EXTRACT — am I to believe I have missed setting the source for data binding ? or is the problem I am having the same probelm that is mentioned in this extract which will be dealt with later or is it something else?

I want to continue the tutorial in the morning because I have been held up by this so for the moment I will leave that line removed so that I can keep running the program as I progress but I would be grateful if anyone could let me know if they can see what I have done wrong. The complete private sub, up to and including that code, is below:

Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cboStatus.Items.Add(”In”)
cboStatus.Items.Add(”Out”)
cboStatus.SelectedIndex = 0

dgvStatus.ReadOnly = True

Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String

SQL = “SELECT CONCAT(u.lastname, ‘, ‘, u.firstname) AS Name, s.message AS Message, ” _
& “DATE_FORMAT(e.timestamp,’%b %d %Y – %r’) AS DateTime, e.status AS Status ” _
& “FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u ” _
& “WHERE(e.user_id = u.user_id) ” _
& “AND event_id IN( ” _
& “SELECT MAX(e.event_id) ” _
& “FROM event e ” _
& “GROUP BY e.user_id) ” _
& “ORDER BY name”

conn.ConnectionString = myConnString

Try
conn.Open()

Try
myCommand.Connection = conn
myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)

cboMessage.DataSource = myData
cboMessage.DisplayMember = “message”
cboMessage.ValueMember = “message_id”

Catch myerror As MySqlException
MsgBox(”There was an error reading from the database: ” & myerror.Message)
End Try
Catch myerror As MySqlException
MessageBox.Show(”Error connecting to the database: ” & myerror.Message)
Finally
If conn.State ConnectionState.Closed Then conn.Close()
End Try
End Sub

Would be very grateful for assistance with understanding this problem
Charlie Childs Says:
October 17th, 2009 at 9:34 am
Scratch the above message – what a difference a good sleep makes . .. .
Charlie Childs Says:
October 18th, 2009 at 9:02 pm
These tutorials are absolutely brilliant for the likes of me – that is as an absolute beginner with databases. I feel that I am doing quite well and have reached tutorial 5 and set up the automated refresh and timer but I have come back to tutorial 4 because although my program is working apparently well in every other respect new stauses are simply over-writing the existing lines in the in_out status view form. There is one blank line underneath the user status rows and it has the star in the first collumn.
I have checked and checked and checked and I cannot see what I have done wrong. I feel that some where I am missing the instruction to provide the new row

I have one other issue if anyone can shed any light: I added 4 other users when buiding the database and I cannot login to frmMain as anyone but mike with password 12345- the other users are a mixture of ordinary users and administrators. Two of the other users are appearing in the status form as expected and it seems a bit peculiar that I cannot login as them – I am not sure if organising that is coming up in a future tutorial . All other users p/w are words rather than numbers.

Does anyone have any ideas which might help? My frmLogin Code is below followed by my frmMain

—————

Imports MySql.Data.MySqlClient

Public Class frmLogin

Private Sub cmdcancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdcancel.Click
Application.Exit()
End Sub

Private Sub cmdlogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdlogin.Click
Dim conn = New MySqlConnection
Dim myCommand As New MySqlCommand

Dim myConnString As String
Dim UserID As Integer

myConnString = “server=” & txtServer.Text & “;” _
& “user id=” & txtUsername.Text & “;” _
& “password=” & txtPassword.Text & “;” _
& “database=in_out”

conn.ConnectionString = myConnString

Try
conn.Open()

myCommand.Connection = conn
myCommand.CommandText = “SELECT user_id FROM user WHERE BINARY username = ?Username”
myCommand.Parameters.AddWithValue(”?Username”, txtUsername.Text)

UserID = myCommand.ExecuteScalar

conn.Close()

Dim mainForm As New frmMain
mainForm.UserID = UserID
mainForm.connectionString = myConnString
mainForm.Show()

Me.Hide()
Me.Close()
Catch myerror As MySqlException
MessageBox.Show(”Error Connecting to Database: ” & myerror.Message)
conn.Dispose()
End Try

End Sub

Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.AcceptButton = cmdlogin
Me.CancelButton = cmdcancel
End Sub
End Class

—————-

Imports MySql.Data.MySqlClient
Imports System.Data

Public Class frmMain
Private myConnString As String
Private myUserID As Integer

Public WriteOnly Property connectionString() As String
Set(ByVal value As String)
myConnString = value
End Set
End Property

Public WriteOnly Property UserID() As Integer
Set(ByVal value As Integer)
myUserID = value
End Set
End Property

Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cboStatus.Items.Add(”In”)
cboStatus.Items.Add(”Out”)
cboStatus.SelectedIndex = 0

dgvStatus.ReadOnly = True

Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String

SQL = “SELECT s.message_id, s.message ” _
& “FROM in_out.status s ” _
& “WHERE user_id IS NULL and deleted = ‘False’”

conn.ConnectionString = myConnString

Try
conn.Open()

Try
myCommand.Connection = conn
myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)

cboMessage.DataSource = myData
cboMessage.DisplayMember = “message”
cboMessage.ValueMember = “message_id”
Catch myerror As MySqlException
MsgBox(”There was an error reading from the database: ” & myerror.Message)
End Try
Catch myerror As MySqlException
MessageBox.Show(”Error connecting to the database: ” & myerror.Message)
Finally
If conn.State ConnectionState.Closed Then conn.Close()
End Try

refreshStatus(dgvStatus)

End Sub

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
refreshStatus(dgvStatus)
End Sub

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand

conn.ConnectionString = myConnString

myCommand.Connection = conn
myCommand.CommandText = “INSERT INTO event(user_id, message_id, timestamp, status, creator)” _
& “VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)”

myCommand.Parameters.AddWithValue(”?UserID”, myUserID)
myCommand.Parameters.AddWithValue(”?MessageID”, cboMessage.SelectedValue)
myCommand.Parameters.AddWithValue(”?Status”, cboStatus.SelectedItem)
myCommand.Parameters.AddWithValue(”?Creator”, myUserID)

Try
conn.Open()
myCommand.ExecuteNonQuery()
Catch myerror As MySqlException
MsgBox(”There was an error updating the database: ” & myerror.Message)
End Try

refreshStatus(dgvStatus)

End Sub

Private Sub refreshStatus(ByRef statusView As DataGridView)
Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String

SQL = “SELECT CONCAT(u.lastname, ‘, ‘, u.firstname) AS Name, s.message AS Message, ” _
& “DATE_FORMAT(e.timestamp,’%b %d %Y – %r’) AS DateTime, e.status AS Status ” _
& “FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u ” _
& “WHERE(e.user_id = u.user_id) ” _
& “AND event_id IN( ” _
& “SELECT MAX(e.event_id) ” _
& “FROM event e ” _
& “GROUP BY e.user_id) ” _
& “ORDER BY name”

conn.ConnectionString = myConnString

Try
conn.Open()

Try
myCommand.Connection = conn
myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)

statusView.DataSource = myData
dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
Catch myerror As MySqlException
MsgBox(”There was an error reading from the database: ” & myerror.Message)
End Try
Catch myerror As MySqlException
MessageBox.Show(”Error connecting to the database: ” & myerror.Message)
Finally
If conn.State ConnectionState.Closed Then conn.Close()
End Try
End Sub

Private Sub tmrRefresh_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tmrRefresh.Tick
refreshStatus(dgvStatus)
End Sub
End Class

———-

I will be doing the tutorial again and again until I am absolutely clear with everything but I would be very grateful for any assistance with those to questions in the near future if there is any assistance out their.
Charlie Childs Says:
November 3rd, 2009 at 10:34 pm
I have completed to end of part 6 of this tutorial but I am still not getting a new row inserted as described in this part of the tutorial. Everything is working fine apart from that. I have 3 users who I can login. The focus on the login form is set to password. I can write personal status messages and update my form with new status and messages but the new overwrites the existing line for the user in the form. Can anyone offer any advice as to why the insert query :

myCommand.CommandText = “INSERT INTO event(user_id, message_id, timestamp, status, creator)” _
& “VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)”

.. . . is not producing a new row??

I am using Visual Basic 2008 Express with MySQL 5.1. I have checked the MySQL manual and “INSERT INTO” is a valid command although I have found that the project still does its job even if I leave out the “INTO” part.

Although the form is working exactly as it should to the end of part six in every other way, this has been a probelm for me ever since part four ( this part) of the tutorials and I cannot for the life of me see where I am going wrong. Is anyone able to offer any advice or likely / possible explanation?? I would be very grateful for assistance XX
rey p Says:
December 22nd, 2009 at 8:16 pm
Dim myCommand As New MySqlCommand
Dim myConnString As String

myConnString = “server=” & txtServer.Text & “;” _
& “user id=” & txtUserName.Text & “;” _
& “password=” & txtPassword.Text & “;” _
& “database=in_out”

conn.ConnectionString = myConnString <<<<<<<<<<<<<<< hi i receive an error message when executing the program (nullreferrenceexception was
unhandled-im using visual studio 2008 to run the codes)

Try
conn.Open()

myCommand.Connection = conn
myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username"
myCommand.Parameters.Add("?Username", txtUserName.Text)

conn.Close()

Dim mainForm As New frmMain

mainForm.connectionString = myConnString
mainForm.Show()

Me.Hide()
Me.Close()
Catch myerror As MySqlException
MessageBox.Show("Error Connecting to Database: " & myerror.Message)
conn.Dispose()
End Try

End Sub

Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.AcceptButton = cmdLogin
Me.CancelButton = cmdCancel
End Sub
End Class
rey p Says:
December 22nd, 2009 at 8:22 pm
hi thanks for the tutorials,

hope you have codes for visual studio 2008, i’m encountering errors everytime i ran the codes i got hanged up with nullreferenceexception errors. hope you can send me, tnx
Ernest Bonat. Ph.D. Says:
December 22nd, 2009 at 8:30 pm
Hi,

I have no code for VB.NET 2008. This site will be down very soon!

Thank you for asking.
Colino Says:
December 25th, 2009 at 3:28 pm
Hi,
i try to connect to an italian server “aruba” but it gime an error that is impossible stabilish a connection.
In local it works great.
I need to insert some components or is necessary set someting??
help me please!
p.s. very very thx
Vic Says:
January 6th, 2010 at 9:10 am
Hi,

How do I execute a mysql script thru vb.net using commandline.

The following is my code and i can’t get it to work. If i type the command manually in the command line, it’s working fine so it must be something with vb.net that’s causing the error.

Pls advice. THanks
————–
Dim pmysql As System.Diagnostics.Process
Dim p As New System.Diagnostics.ProcessStartInfo

p.WorkingDirectory = “C:\PROGRA~1\MySQL\MYSQLS~1.0\bin\”
p.FileName = “mysql.exe”
p.Arguments = “-v –host=hostname –user=username –password=password targetDB < C:\sample_MYSQL.sql"

p.WindowStyle = ProcessWindowStyle.Hidden
p.RedirectStandardOutput = True
p.UseShellExecute = False

Dim fs As New System.IO.FileStream("C:\testMySQLOutput.txt", IO.FileMode.Create, IO.FileAccess.Write)
Dim sw As New System.IO.StreamWriter(fs)

pmysql = Process.Start(p)
sw.Write(pmysql.StandardOutput.ReadToEnd())
sw.Flush()
sw.Close()
pmysql.WaitForExit()
Arnaud Says:
January 8th, 2010 at 3:42 pm
Hi and thank you for this useful tutorial.
For some reason, the
“me.close”
statement in the class “Class frmLogin”

causes my application to close rather than closing only the login window.
Have you got any idea what am I doing wrong?

Thank you and regards

Here is my code:

Public Class frmLogin
Dim conn As MySqlConnection

Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
Application.Exit()
End Sub

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
Dim conn = New MySqlConnection()
Dim myConnString As String
myConnString = “server=pricediscovery.XBZ.local;” _
& “user id=” & txtUsername.Text & “;” _
& “password=” & TxtPassword.Text & “;” _
& “database=masteratwork”
‘conn.ConnectionString = “server=TESTING.XBZ.local;” _
conn.ConnectionString = myConnString

Try
conn.Open()
conn.Close()
Dim mainForm As New frmMain

mainForm.connectionString = myConnString
mainForm.Show()

Me.Hide()

Catch myerror As MySqlException
MessageBox.Show(”Error Connecting to Database: ” & myerror.Message)
conn.Dispose()
End Try

End Sub

Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.AcceptButton = cmdLogin
Me.CancelButton = cmdCancel
End Sub

End Class
Arnaud Says:
January 9th, 2010 at 5:15 pm
Hi,
Forget this question, I needed to change parameter of my project so that it doesn’t exit the project when the start form closes.
Thanks
mhars Says:
January 18th, 2010 at 8:04 pm
you help a lot of students like me… thanks for your very excellent tutorial… hoping this site will stay forever thanks ^^
Touch Up Paint Kit Says:
January 29th, 2010 at 9:16 am
I’ve been searching for this exact information on this subject for a long time.