The VB.NET-MySQL Tutorial – Part 5

October 27th, 2006 Leave a comment Go to comments

Introduction

This article is fifth in a series of articles describing how to create a simple Windows application that queries and updates a MySQL database. So far we have designed our application and our database, installed MySQL and VB.NET, created our database, and created a basic application. This application is capable of querying event data from the database and updating it.

At the end of the fourth article of this series, I listed some improvements that could be made to our application. These included automatic refreshing of the data and storage of the MySQL server IP address in a configuration file.

Automating Data Refresh

One of the drawbacks of our current application is that it requires our user to manually click the Refresh button every time they want to see the latest information. This means that a change in status can go unnoticed if the user does not click the refresh button. In addition, we cannot allow for one user to set a watch for another user to login if the first user has to regularly click the Refresh button.

Well will be automating the refreshing of our status information through the use of a VB.NET Timer control. A timer fires at a configurable interval and executes a block of code. Timers are very useful in application programming and can be used for a variety of tasks.

Abstracting the Refresh Code

Before we create a Timer, we should make our refresh code more generic. Currently the code for refreshing the status data is located directly within the event handler for a click of the Refresh button. We can move this code to a function, whttp://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-part-5.zip
vb-mysql-tutorial-part-5.ziphere it can then be called from the click event and the timer.

The new function will contain all the code that was previously in the Refresh button click event, with one small change:

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

In the declaration of the function we pass an argument named statusView that will point to the DataGridView on our form. In the code, we change the code that binds the DataGridView to bind against the statusView. This way we can change the name of the DataGridView without changing the code within the function.

Our click event is also changed to call this function:

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

Clicking the Refresh button now calls the refreshStatus function. In addition, we can change the following line of our update code:

cmdRefresh.PerformClick()

to

refreshStatus(dgvStatus)

This is a much better approach when the same code is executed in multiple places. In addition, add the call to refreshStatus to your frmMain_load event so that the user is presented with a view of the status from the beginning.

Adding the Timer

Now that our refresh code is moved, we can call it from a timer. The Timer control is located in the toolbox:

Timer control

Click and drag the timer control onto your form. Unlike other controls, the Timer control does not stay on your form, but is automatically moved to a special area below the form:

Placed Timer

This area is reserved for controls that are not visible, such as the timer and common dialogs. Change the name of the timer to tmrRefresh using the properties window and set the Enabled property to True.

Choosing an Interval

The Interval property of the timer determines how often the timer will trigger an event. The Interval is measured in milliseconds, with 1000 milliseconds to a second. We do not want to fire this event too often, or we produce excessive load on our server. We also do not want to wait too long before firing our event, or our users may not see updated status information fast enough. Ideally we want to have an interval of about one minute, or 60,000 milliseconds. Set the Interval property of our timer to 60000.

Catching the Tick Event

When the Interval set previously is reached, the timer triggers the Tick event. We can catch the Tick event and add code to it, allowing that code to be run every time the interval is reached, or once every minute.

Double-click on the timer and the following code template is generated:

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

End Sub

We can add a call to our refreshStatus function within this function to cause the status information to refresh automatically:

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

This will cause our status information to be refreshed once per minute, regardless of whether the user clicks the Refresh button or not. You can test this by launching the application, then adding a row to the event table using the MySQL Query Browser. Within one minute the status display will change.

Storing Application Settings

Now that we have added a timer to frmMain, let’s make some improvements to frmLogin. Currently, our users need to manually enter the MySQL server IP address every time they use our application. In addition, there is no option to remember a username for future use. To solve both of these problems we need a way to persist information for future use. There are a variety of options for persisting data, including INI files, the registry, and XML files.

Most of the options mentioned above require either the use of API calls or file handling code to set and retrieve data. With VB.NET 2005 a new My.Settings syntax has been added that greatly simplifies the storage and retrieval of application settings.

The new My.Settings Syntax

With My.Settings, we can application and user-level settings to our application and easily access them without using any API or file handling code. Settings are stored in XML files located either with the application executable or in the Documents and Settings/Username directory, depending on the scope of the setting. Setting can have either a User or Application scope, depending on whether an setting applies to the application in general or to a specific user.

To use My.Settings, we first create the setting within the VB.NET IDE, then access the setting through code.

Storing the Host IP with an Application Setting

The first step in using an application-level setting is to create is using the setting designer. Choose Properties from the Project menu and choose the Settings tab. A grid with available settings is displayed:

Settings Dialog

Within this grid we create a new HostIP setting as a String, with an Application scope. Set the value to the IP address of your MySQL server machine. Once your setting is in place you can close the Project Properties window. After closing the window you will be prompted to save the application settings file, click Yes and you will be returned to the form designer.

The following is added to the App.Config file:

<applicationSettings>
    <In_Out.MySettings>
      <setting name="HostIP" serializeAs="String">
        <value>192.168.1.10</value>
      </setting>
    </In_Out.MySettings>
  </applicationSettings>

This file will be compiled into a file named in-out.exe.config file when you build your project, and will be located in the bin folder of your project.

Accessing the Host IP Information in frmLogin

After the HostIP setting is stored we can access it from within our code. First change the form design by deleting the controls related to the server IP address:

Updated Login Form

Once the form is redesigned, we can modify the login code, specifically the connection string::

SQL = "server="  &  My.Settings.HostIP  &  ";" _
                  &  "user id="  &  txtUsername.Text  &  ";" _
                  &  "password="  &  txtPassword.Text  &  ";" _
                  &  "database=in_out"

Our login code will now use the stored IP address when connecting to the server.

Storing the User Name with a User Setting

Now that our host IP is taken care of, let’s look at optionally storing the username to save time for our users. We can store the username in a User scope setting, which will allow it to be read and written to a file in the Documents and Settings directory (Application scope settings are read-only though the My.Settings interface). One advantage of this approach is that even when our application is used with multiple Windows user accounts, each user can have their username stored without overwriting the previous user’s information.

We create a User scope setting with the same interface as we used to create a Application scope setting. When creating the UserName setting, create it as a string, set a User scope, and leave the Value blank.

The Settings section of the App.Config file will look like this:

<applicationSettings>
    <In_Out.MySettings>
      <setting name="HostIP" serializeAs="String">
        <value>192.168.1.10</value>
      </setting>
    </In_Out.MySettings>
  </applicationSettings>
  <userSettings>
    <In_Out.MySettings>
      <setting name="Username" serializeAs="String">
        <value />
      </setting>
    </In_Out.MySettings>
  </userSettings>

Accessing the User Name in frmLogin

As an added convenience to our user, let’s allow the username to be optionally stored by means of a checkbox. This will save time when the user logs in. First lets redesign our login form and add a CheckBox control:

Remember Me Checkbox

Name the CheckBox chkRemember and leave it unchecked by default (set the text property ‘Remember Me’).

First let’s add code to the form_load event to check if we have a saved username:

If My.Settings.Username <> "" Then
        txtUsername.Text = My.Settings.Username
        chkRemember.Checked = True
End If

This code checks for a blank username. If the Username setting is not blank, it fills the txtUsername Text property and checks chkRemember.

Next we edit the login code for the form. We only save the username if chkRemember is checked and the username is successfully used to login. If chkRemember is not checked, we blank out the Username setting:

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=" & My.Settings.HostIP & ";" _
         & "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()

                If chkRemember.Checked Then
                        My.Settings.Username = txtUsername.Text
                        My.Settings.Save()
                Else
                        My.Settings.Username = ""
                End If

                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

Our frmLogin class should now look like this:

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=" & My.Settings.HostIP & ";" _
                 & "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()

                        If chkRemember.Checked Then
                                My.Settings.Username = txtUsername.Text
                                My.Settings.Save()
                        Else
                                My.Settings.Username = ""
                        End If

                        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

                If My.Settings.Username <> "" Then
                        txtUsername.Text = My.Settings.Username
                        chkRemember.Checked = True
                End If
        End Sub
End Class

You can test our setting code by running the application, checking the Remember Me box, and logging in. Close the application and restart it, your username should be present.

Hiding the Password

One last tweak we can make before leaving frmLogin has to do with txtPassword. At present, any password entered is in plain sight, and could be read by an observer of the user.

To hide the text of a textbox, add the following line to the form_load event:

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

        If My.Settings.Username <> "" Then
                txtUsername.Text = My.Settings.Username
                chkRemember.Checked = True
        End If

                txtPassword.PasswordChar = "*"
End Sub

All text entered is replaced with *, preventing casual observers from seeing sensitive information.

Conclusion

In this article we have tweaked our application to improve its ease of use. Specifically, we have added a Timer control to frmMain to allow for automatic refreshing of event data, and made use of the My.Settings system to store IP and username information. Username information is stored in a file that is unique to each Windows user account, allowing multiple users to store username information without overwriting each other.

In our next article we will improve the event display and generation code to allow for the creation of custom status messages and to support blank status messages. We will also explore the creation of custom classes and data binding to an object.

An archive of the code so far can be found at http://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-part-5.zip.

  1. Yesid Hernandez
    December 11th, 2006 at 03:30 | #1

    Hello,

    I would like to know if this tutorial work using a server client app, I;m using and when I tried with localhost it works. But then I changed localhost for the IP address of the localhost for this case 192.168.0.2 and that give the following error: Unable to connect to any of the specified MySQL host.

    Thanks for your help.

  2. Yesid Hernandez
    December 11th, 2006 at 03:34 | #2

    Hi, I just realize the following. From the MySQL Query browser I try to connect using localhost and works but then I changed for the IP address of the PC and do not connect. I saw the IP using the ipconfig from a command line. Do you know anything about this??. Thanks in advance for any help.

    Bye.

  3. December 11th, 2006 at 03:39 | #3

    When you connect to localhost you are connecting to a socket, when you connect to the IP address you are connecting via TCP/IP. Odds are you have port 3306 behind a firewall and therefore your TCP/IP connection is blocked.

  4. Yesid Hernandez
    December 11th, 2006 at 13:12 | #4

    Thanks for the answer, I have checked that the firewall it is not blocking me that port and added to the exceptions using the 3306 port and a name but it not works. Any other idea with this issue?. Another question is if you think I will be able to connect from a remote computer with this version of VB 2005 Express?.

    Bye.

  5. December 11th, 2006 at 14:27 | #5

    Hi Yesid,
    You need to explicitly allow the userid@IP/hostname in mySQL…then it will work. Add hostname in mySQL

    GRANT ALL PRIVILEGES ON *.* TO ‘someuserid’@'hostnameorIPyouwish’
    -> IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION;

    you can replace ‘someuserid’@'hostnameorIPyouwish’ with ‘someuserid’@'%’ to allow userid to connect from host. (Warning: May cause security risk if your machine is accessible from outside world)

    Regds
    Inderjeet

  6. December 11th, 2006 at 14:30 | #6

    Small typo:

    you can replace ’someuserid’@’hostnameorIPyouwish’ with ’someuserid’@’%’ to allow userid to connect from **ANY** host. (Warning: May cause security risk if your machine is accessible from outside world)

  7. Keyston
    December 28th, 2006 at 19:38 | #7

    @Yesid Hernandez i take it you have a routher since you have that 192.168.x.x ip. in that cause you need to foward ports to that pc you want to connect.. in this case it will be 192.168.0.2

  8. Barb
    January 19th, 2007 at 19:10 | #8

    Thanks!

  9. Sonia
    February 8th, 2007 at 13:41 | #9

    Hello, I would like to know of it is possible to display data from a recordset in a textbox in visual basic 2005 using MYSQL

  10. Inderjeet
    February 8th, 2007 at 14:11 | #10

    Hello Sonia,
    Somewhat strange but this (excellent) article mentioned everything from A-Z about your question. Right from creating a connection to Once you have the data in the recordset, you can do anything with it.

    Read the article or clarify your question if you’re asking something else.

    Regds
    Inderjeet

  11. Tosa
    February 27th, 2007 at 19:56 | #11

    How can I prevent doing login without username and password fields filled? Currently, as soon as user hit , it is executed even though username and password are empty.

  12. Inderjeet
    February 27th, 2007 at 20:18 | #12

    Tosa,
    It seems you’ve just started coding. I’m not going to explain how but will give you a hint: you need to do either client side validation using javascript OR server side validations (if isblank(username) then priny somemessage etc)

    Look on your favorite search enginbe and youi’ll get a thousand options.

    regds
    IJ

  13. tosa
    February 27th, 2007 at 22:37 | #13

    You misunderstood. What I wanted is to intercept OK press and run validation for zero text string. If not valid (zero text length), then pop messagebox and won’t close the form.

    I found a way to my question myself: Add my own CancelEventHandler to Closing event of the Form.
    In the handler routine, if not valid, I set e->Cancel = true (otherwise e->Cancel = false). Then the form does not close but stays up for requesting valid inputs. (A small point: You have to call Form.BringToTop after setting Cancel = true, so that the Form comes to the front.

  14. Sarah
    March 1st, 2007 at 13:56 | #14

    How do I put an input mask to a colum in MYSQL

  15. Riz
    March 12th, 2007 at 07:50 | #15

    Hey… I have a VB.Net Application which connects and retirieves Data from a MySQL Database. The code works perfectly fine when the MySQL database is located on my LAN… But when the MySQL Database is remote,the application freezes… Any Idea wht might be the reason??

  16. Jeff
    April 23rd, 2007 at 02:30 | #16

    To those trying to connect remotely, you might want to check with your host provider (assuming you are doing a monthly rental type of arrangement).
    My host says they typically don’t allow remote requests for shared hosting, but said they could do it on a dedicated leased machine (at substantially higher monthly costs).

  17. April 27th, 2007 at 12:10 | #17

    am using vs2005 and mysql 5.0 the private sub refresh status isnt working the error being stated is datagridview is not defined what could possibly be the problem

  18. private21
    May 8th, 2007 at 20:26 | #18

    Monqare and any others that are having his problem. i had this problem too and found that the datagridview control was throwing the exception when it was processing the DATETIME format. My removing of the DATE_FORMAT command from the SQL line allow it to process with no exception error. This fixes it and the Date and time is nicely translated into the expected string except for the month indicator being numeric instead of alpha. Has anyone else investigated this and found where the problem is…is something in the DATE_FORMAT function returning a value that is special to datagridview control. The exception that is thrown seems (14 lines long so you have to screen really carefully) that this might be the case. I’ll try and do a little more playing around and let you know but if someone already knows…..

  19. kiran
    August 1st, 2007 at 05:55 | #19

    ya it is fin……….but i have one problem……any one will help me/….
    We need a small VB.net tray application (running similar to Google Desktop, or Norton, or something like that….where when you shrink the application it goes down under the far right taskbar).

    We need it to have a simple grid, line-by-line and it will pull from an already created view in SQL 2005. 99.99999% of the work will be on the pulling of the data so it pulls it almost instantly. And, when a row is updated in the table on SQL server…the grid must be updated in the app.

    All coding must be done in VB.NET using Visual Basic (no C#) .NET and VS 2005.

    Platform:
    Vb.NET
    Visual Basic .NET
    .aspx
    Visual Studio 2005
    Microsoft SQL Server 2005

  20. August 16th, 2007 at 17:29 | #20

    I have created a simple registration form using VB.net and a database using mysql. I have already connected to the database using a mysql connection. The data has already been bind to the text boxes. I am now doing the record navigation controls for the form however i am not getting the required results. Here is the code that i used for the next button and is the only navigation code that i am trying out:

    Me.BindingContext(myData, “table name”).Position = Me.BindingContext(myData, “ablename”).Position + 1

    and the following err is displayed: ‘child list for ‘field name’ cannot be created.

    I have used a MySqlDataAdapter a Data table and Binding source to bind the data to the textboxes

  21. August 20th, 2007 at 10:52 | #21

    hi
    i create one web service and windows application in vb.net full function control and accessing the webservices how to use this coding please reply me

  22. Rudner
    August 24th, 2007 at 07:54 | #22

    hello…

    i tried to put an * to passwordchar properties in my txtpassword label but as i run the form as if there is nothing change as i enter my password..what should i do..did i forget something?

  23. Ben
    September 26th, 2007 at 00:20 | #23

    Hey! I love your tutorials man, I’ve followed right through to part 5 and I’ve never really done any serious VB in my life, everything works great. When is the next part of your tutorial coming out?

  24. Greg Cushing
    November 1st, 2007 at 13:11 | #24

    Love the tutorials but when i paste your code that remembers the username i get an error saying the Username Property is read only how do i change this?

  25. Andrew
    November 12th, 2007 at 21:42 | #25

    First class – it all worked wonderfully and makes sense. Very well put together.

  26. Chaz
    December 30th, 2007 at 22:17 | #26

    Really great job taking the time and effort into making these guides! Big Big Kudos for you! heh You saved me a lot of headaches and I appreciate that.

    TheChazm

  27. pradeep
    February 16th, 2008 at 12:19 | #27

    thanks for avoiding my headace

  28. Nataraj.SM
    July 29th, 2008 at 12:34 | #28

    Hi, Its very useful and clear tutorial friend. I was planning to migrate from MS-access to other. After this tutorial I decided to configure in Mysql with your tutorial help. Thank you very much . I may need your guidelines , if I will get any configure problems.

    Thank you. Al the best
    Nataraj.S.M

  29. Imtiaz Cassamally
    August 14th, 2008 at 07:44 | #29

    Very useful code. I’ve been able to learn great things from your code. Keep it up. You did a great job.

  30. Imtiaz Cassamally
    August 14th, 2008 at 07:45 | #30

    Hi, I’ve a question. I need to retrive data from mysql database and publish it in excel.

    Any idea?

    Thanks

  31. petitmamouth
    August 25th, 2008 at 17:38 | #31

    HI
    nice article, and i’m french, i often use MYsql 5.0 and VB.net.

    Well now, i’ve a got a good question.
    I have made an application in VB 2005 express connecting wtih MYsql . All the data come from other database like INGRES II or MS SQL 2005.
    Even i have no problem to import data from ingres II (i have build a simple exe to export data in txt file) it is not the same with MS SQL 2005.
    I would like to now if it’s possible to “catch” the data from a table in a database SQL 2005 and insert them in a table with the same definition but in a MYSQL database using VB.NET ?

    I know how to connect on a MS 2005 DATABASE and MYSQL DATABASE.
    I know how to fill a dataset or datable with the rows coming from the MS 2005 DATABASE
    But i dont know how to use them to insert them in the table of MYSQL.

    I can’t use SQLBULKCOPY because it doesn’t accept a MYSQL connection ?
    I can install BCP on the client and run it to generate txt files, but i “want” to create a ‘bridge’ between these two database which are on differents servers !!

    If someone have an idea.

    Thanks

  32. Priyu
    October 3rd, 2008 at 18:34 | #32

    Its rocking man……..
    Thanks for your tutorials……………….

  33. Rozie
    October 10th, 2008 at 08:28 | #33

    do u no how to hide title bar in vb net…
    some of my friend said we select at the properties..

  34. Rozie
    October 10th, 2008 at 08:29 | #34

    do u no how to hide title bar in vb net…
    some of my friend said we select at the properties..
    could u help me?

  35. Mario
    January 9th, 2009 at 22:51 | #35

    This is a very very usefull tutorial; I am an “old school” programmer and I was facing a couple of problems in visual and OO thechnologies, this tutorial not only is very well explained but it´s components are very good planned to be didactic.

    Good Look man and Thanks for this excellent tutorial.

  36. Tomas
    January 19th, 2009 at 16:31 | #36

    Many Thanks for whole tutorial, very very useful.

    I’m looking for some example (tutorial like above) which will describe working with BindingNavigator, BindingSource and ComboBox + Text items associated with that BindingSource.
    + bellow functionality would be great:

    1.) Add new record + reflect changes into ComboBox
    2.) Remove current record + reflect changes into ComboBox
    3.) Save data to database => COMMIT functionality + reflect changes into ComboBox
    4.) Rollback functionality
    5.) Multiple data change => and save to mySQL db + commit
    6.) Data Search functionality (with mask)

    Many Thanks,
    Tomas

  37. ccw
    March 17th, 2009 at 11:20 | #37

    hi,

    can help me solve some problem???
    i had connected a datagridview to MySQL and the datagridview has shown me the data from MySQL.
    But now my problem is what i insert, update and delete from the datagridview , it will not edit in the data in MySQL…
    so can help me solve this problem ???

    THX…..

  38. Ketan
    May 27th, 2009 at 06:35 | #38

    These tutorials are great,
    Awaiting next part….

    Ketan

  39. JOHNV
    September 22nd, 2009 at 14:47 | #39

    hello.. i would like to ask a question.. this is problem i had encountered. i am a beginner and i want to have some help from anyone here. please give me an idea or help on how i will formulate the source code of my program i am creating using vb.net and mysql. the problem is on how to INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS… having a primary key and a foreign key.. thanks guys i hope and need your help..

  40. Evilson
    October 12th, 2009 at 12:30 | #40

    Hi,

    I build an application that uses VB.net 2008 and mysql and it works fine in one module. Now i’m planning to build ten modules that uses five different tables. If i’ll make one MySqlDataReader, DataTable, MySqlCommand and MySqlDataAdapter and use one function to create, modify, delete and update database, will it encounter a problem.

    This is the sample program

    Imports MySql.Data.MySqlClient
    Module modVariable
    Friend Adapter As New MySqlDataAdapter
    Friend Command As New MySqlCommand
    Friend DataTable As New DataTable
    Friend Reader As MySqlDataReader
    Private Function SQLtrans(ByVal cond as integer, ByVal commandline as string)
    Connection has already been establish
    If conn.State = ConnectionState.Closed Then conn.Open()
    DataTable.Clear()
    Command = New MySqlCommand
    Command = conn.CreateCommand
    Command.Connection = conn
    Command.CommandText = “”
    If cond = 1 Then ‘For find and filling datagrid
    Adapter.SelectCommand = commandline
    Adapter.Fill(DataTable)
    Do Until counter = findregDataTable.Rows.Count
    If DataTable.Rows.Count 0 Then
    assigning table rows and collums to a variable
    End If
    Loop
    Else If cond = 2 Then ‘For insert, delete and update
    Command.CommandText = commandline
    Command.ExecuteNonQuery()
    End If
    End Module

  41. Charlie Childs
    October 19th, 2009 at 14:43 | #41

    At the end of tutorial 4 I had two probelms. I am now at the end of stage 5 and I only have one problem. Progress is being made but this problem is seriously niggling.

    My frmMain rows are being over written with the new status’s rather than in a new row. If anyone receives this or sees this call for help, please have a heart and see if you can tell me why my place holder is not working. My frmLogin and frmMain code were posted yesterday in tutrial 4.

  42. Alfredo Garza
    October 23rd, 2009 at 22:06 | #42

    Hi,

    I noticed that all of the connections to the database are open and closed right after finishing the data access. Is there any reason why not to leave the connection open until the aplication exits.

  43. Fernando
    November 4th, 2009 at 07:51 | #43

    HI,

    I’ve the same doubt as Alfredo Garza. Please, anyone ?

  44. Charlie Childs
    November 5th, 2009 at 12:01 | #44

    Alfredo and Fernando,

    I am a beginner but have read this tutorial quite a lot and I think I have read two things that may be of interest to you:

    1) Somewhere in this tutorial it sates that it is best practice to close connections as soon as they are finished with in order to save resourses.

    2)I think I have read that the closing of the connection is used to trigger something. If I am right about that I think I remember it being in a TRY CATCH block but I cannot be certain.

    I think that you will probably find a more definate answer to your question by reading through lesson 3 or 4.

    Might you know of any reason that may be causing my form to overwrite existing rows rather than provide a new row.

    Good luck

  45. Charlie Childs
    November 5th, 2009 at 12:09 | #45

    Alfredo and Ferando – just remembered that it also says that if the connection is left open, the database may be left open when the user thinks they have finished with it and closed it.

  46. November 30th, 2009 at 06:51 | #46

    I will try to do this but the demand now is Java. Is it possible to work if my knowledge is only VB

  47. November 30th, 2009 at 08:18 | #47

    How can i connect vb.net desktop application with remote mysql server?????????????????

  48. April 6th, 2010 at 14:54 | #48

    Try & Preserve your iPad for Free! -> http://bit.ly/cFBuis

  49. Nokia6230ivn
    April 27th, 2010 at 14:54 | #49

    Hi,

    I have 2 matters in this lession.

    1. I connected to my localhost many times and loading database is ok. But if I change it to my host, it has an error at section “MyAdapt.Fill(myDataTable)”.

    2. If I connect to my database by localhost, “Refresh” button working very good, all chages of database update immediately (even frmMain had time control). But when the timer control working (automatic), it has an error: “Error connecting to the database: Access denied for user “@’localhost’ (using password: NO)

    Please tell me how to correct these errors???

  50. methodman3130
    May 13th, 2010 at 23:34 | #50

    HOW TO DO CRYSTAL REPORTS WITH VB.NET AND MYSQL USING XML DATA? PLEASE HELP

  51. Bongs
    June 7th, 2010 at 19:27 | #51

    I am connected to MySQL database using localhost, I keep getting an error that says CONNECTION MUST BE VALID AND OPEN when I am trying to add a new record. I am using INSERT INTO statement. I need help. The exception is on the Executenonquery statement, here is the code:

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
    Dim ConnStr As String = “server=localhost;user id=bongani;Password=bongs;persist security info=True;database=world”
    Dim connection As New MySqlConnection(ConnStr)
    connection.Open()
    MyCommand = New MySqlCommand(“INSERT INTO pals values (‘” & txtName.Text & “‘, ‘” & txtSur.Text & “‘ ,’” & txtTel.Text & “‘)”)
    If connection.State = ConnectionState.Open Then
    MsgBox(“Connection is open”)
    MyCommand.ExecuteNonQuery()
    Else
    MsgBox(“Connection is closed”)
    End If

    myData = New DataSet
    myAdapter.Fill(myData, “pals”)
    myAdapter.Update(myData, “pals”)
    End Sub

  1. No trackbacks yet.