copy mysql table to ms access table

[ud2008]

I have this problem, which I hope you guys can help me with.

I use vs2010 and create a program using vb2010, with that I have a splashscreen which loads at program startup and should copy all the records from a mysql database table into a local ms access database table ( they are the same).
At startup the splashscreen should also check if the mysql database is available, this works, but when I add some code to copy from database to database it doesn't work and I get the message that the host isn't available (error message I've added).

Also with the error message, I added a button to it (the standard messagebox), when the message is shown, it closes the message after a couple of seconds but should remain until I click the OK button.

Here is the code I use, hope you can help me with this.

Imports MySql.Data.MySqlClient
Imports System.Data.OleDb

Public NotInheritable Class StartScreen1

    Dim con As New MySqlConnection("Server=" + My.Settings.OnlineHost + ";Database=" + My.Settings.OnlinedbName + ";Uid=" + My.Settings.OnlineUser + ";Pwd=" + My.Settings.Onlinepw)
    Dim accesscon As New OleDb.OleDbConnection
    Dim mycommand As New MySqlCommand
    Dim myadapter As New MySqlDataAdapter
    Dim Accessadapter As New OleDbDataAdapter
    Dim mydata As New DataTable
    Dim mydataset As New DataSet
    Dim SQL As String

    'TODO: This form can easily be set as the splash screen for the application by going to the "Application" tab
    '  of the Project Designer ("Properties" under the "Project" menu).

    Private Sub StartScreen1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            If My.Computer.Network.Ping("IP") Then
                'Set up the dialog text at runtime according to the application's assembly information.  

                'TODO: Customize the application's assembly information in the "Application" pane of the project 
                '  properties dialog (under the "Project" menu).
                accesscon.ConnectionString = ("Provider+" + My.Settings.Offlineprovider + ";Data Source=" + My.Settings.AccesssourceOff)
                SQL = "SELECT * FROM songs"
                Try
                    con.Open()
                    mycommand.Connection = con
                    mycommand.CommandText = SQL
                    myadapter.SelectCommand = mycommand
                    myadapter.Fill(mydataset, "songs")
                    accesscon.Open()
                    Accessadapter.Update(mydataset, "songs")
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
                'Application title
                If My.Application.Info.Title <> "" Then
                    ApplicationTitle.Text = My.Application.Info.Title
                Else
                    'If the application title is missing, use the application name, without the extension
                    ApplicationTitle.Text = System.IO.Path.GetFileNameWithoutExtension(My.Application.Info.AssemblyName)
                End If

                'Format the version information using the text set into the Version control at design time as the
                '  formatting string.  This allows for effective localization if desired.
                '  Build and revision information could be included by using the following code and changing the 
                '  Version control's designtime text to "Version {0}.{1:00}.{2}.{3}" or something similar.  See
                '  String.Format() in Help for more information.
                '
                '    Version.Text = System.String.Format(Version.Text, My.Application.Info.Version.Major, My.Application.Info.Version.Minor, My.Application.Info.Version.Build, My.Application.Info.Version.Revision)

                Version.Text = System.String.Format(Version.Text, My.Application.Info.Version.Major, My.Application.Info.Version.Minor, My.Application.Info.Version.Build, My.Application.Info.Version.Revision)

                'Copyright info
                Copyright.Text = My.Application.Info.Copyright
            End If
        Catch
            'MsgBox("Host is not available!" & vbNewLine & _
            '                           "Make sure you have an internet connection and try again later!")
            Dim button As DialogResult
            button = MessageBox.Show _
            ("Host is not available!" & vbNewLine & _
                                       "Make sure you have an internet connection and try again later!", _
            "Songlist Editor", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)
            If button = Windows.Forms.DialogResult.OK Then
                '    Application.Exit()
            Else
                'Do Nothing
            End If
            'Dim messge2 As DialogResult
            'messge2 = MessageBox.Show( _
            '   "The host is not available." & vbNewLine & _
            '   "Make sure you have an internet connection and try again later!", My.Application.Info.Title, _
            '                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            'Application.Exit()
        End Try
    End Sub
End Class

Thanks for any help.

 

[Menthos]

Have you set a breakpoint and stepped through your code to see what exception is being raised?

 

[Robert_SF]

Yea, I can't tell just by looking, either. Nothing jumps out at me. Like Menthos says, I'd suggest you step through the code to see exactly where it fails. Also, can you store your connection strings in your application settings? That way, they're tested and you don't have to build them on the fly in your code. You can create those connection strings by creating a data source in the data source window. It's the first step.

Also, for briefer code, you can create a data adapter in just two lines, like this:

Dim SQLquery As String = "Your SQL query string"
Dim myDataAdapter As New DataAdapter(qry, My.Settings.myConnectionString)

You don't have to separately create connection and command objects. The DataAdapter constructor can do it for you. In fact, the only reason you want a separate line of code for the query is that those strings tend to be long.

Ok, see if you can use the debugger to narrow the problem down and let us know.

 

[ud2008]

Also, can you store your connection strings in your application settings? That way, they're tested and you don't have to build them on the fly in your code. You can create those connection strings by creating a data source in the data source window. It's the first step.

I already store the connection string into the application settings (the way it is in my code now, is the way it should be).


I now use the application events to check if the online database is available:

Namespace My

    ' The following events are available for MyApplication:
    ' 
    ' Startup: Raised when the application starts, before the startup form is created.
    ' Shutdown: Raised after all application forms are closed.  This event is not raised if the application terminates abnormally.
    ' UnhandledException: Raised if the application encounters an unhandled exception.
    ' StartupNextInstance: Raised when launching a single-instance application and the application is already active. 
    ' NetworkAvailabilityChanged: Raised when the network connection is connected or disconnected.
    Partial Friend Class MyApplication

        Protected Overrides Function OnInitialize(commandLineArgs As System.Collections.ObjectModel.ReadOnlyCollection(Of String)) As Boolean
            Me.MinimumSplashScreenDisplayTime = 5000
            Return MyBase.OnInitialize(commandLineArgs)
        End Function

        Private Sub MyApplication_Startup(sender As Object, e As Microsoft.VisualBasic.ApplicationServices.StartupEventArgs) Handles Me.Startup
            If My.Computer.Network.IsAvailable And My.Computer.Network.Ping("ipaddress") Then
                'do nothing
            Else
                Dim msg As DialogResult = MessageBox.Show _
                ("Host is not available!" & vbNewLine & _
                                           "Make sure you have an internet connection and try again later!", _
                "Songlist Editor", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                e.Cancel = True
            End If
        End Sub
    End Class

End Namespace

Only the e.cancel = true won't work because in the messagebox there isn't any cancel button, I tried to use application.exit but exit is not recognised in the application event.

Then on the splashscreen I try to copy the complete table from MySQL to local MS Access database table.

SQL = "SELECT * FROM songs"
        Try
            con.Open()
            mycommand.Connection = con
            mycommand.CommandText = SQL
            myadapter.SelectCommand = mycommand
            myadapter.Fill(mydataset, "songs")
            accesscon.Open()
            Accessadapter.Update(mydataset, "songs")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

I get no error on the code, but it also won't work (it won't copy the records from the MySQL database into the MS Access database).


Any idea?

Thanks.

 

[JohnH]

Only the e.cancel = true won't work because in the messagebox there isn't any cancel button

There is no cancel button because you specified MessageBoxButtons.OK, there are other options: MessageBoxButtons Enumeration (System.Windows.Forms)
e.Cancel works regardless, when set to True it cancels the startup of the application.

 

[ud2008]

There is no cancel button because you specified MessageBoxButtons.OK, there are other options: MessageBoxButtons Enumeration (System.Windows.Forms)
e.Cancel works regardless, when set to True it cancels the startup of the application.

Yeah I knew this, but I tried to use the OK button, when the ip isn't pingable a messagebox pops up with a notification and when user click the OK button, the application should exit, the only thing is that Application.Exit is recognised in the applicationevents.vb

Any idea on how to do this anyway?

 

[JohnH]

Setting e.Cancel to True in Startup event will cancel application start up. No further code is necessary.

 

[ud2008]

Setting e.Cancel to True in Startup event will cancel application start up. No further code is necessary.

I tried it and it will close the application (without display any message), but I want to display a message for the user, so they know why the application isn't running (do I need to use unhandledexception to display the message?) and then when they click the OK button of the messagebox close the application.

 

[ud2008]

Ok, I've changed the code into the following:

Private Sub MyApplication_Startup(sender As Object, e As Microsoft.VisualBasic.ApplicationServices.StartupEventArgs) Handles Me.Startup
            If My.Computer.Network.IsAvailable Then
                If My.Computer.Network.Ping("195.128.174.38") Then
                    'Dim splash As StartScreen1 = CType(My.Application.SplashScreen, StartScreen1)
                Else
                    MessageBox.Show("Host is not available!" & vbNewLine & _
                                    "Make sure you have an internet connection and try again later!", _
                                    "Songlist Editor", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                    e.Cancel = True
                End If
            Else
                MessageBox.Show("There is no internet connection!" & vbNewLine & _
                                    "Make sure you have an internet connection and try again later!", _
                                    "Songlist Editor", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                e.Cancel = True
            End If
        End Sub

and now the messagebox is appearing when there is no internet connection available but the splashscreen is also appearing but after I click on the OK button of the messagebox the splashscreen won't close (the application is still running).

Any idea?

 

[ud2008]

I solved it, I've changed:

If result <> DialogResult.OK Then

into:

If result = DialogResult.OK Then

 

[JohnH]

but the splashscreen is also appearing but after I click on the OK button of the messagebox the splashscreen won't close (the application is still running).

Both suggestions here can be used, where Me.HideSplashScreen call is simplest: When using a Splash Screen, you can't cancel the Application.Start

 

[ud2008]

So the problem of the messagebox is solved, but the last question remains, about copying the online database to the locale MS Access database.

I'm stuck at that point.

 

[ud2008]

Ok, I've made some progress on the copy online database to local ms access database.

I am able to copy the complete database to the local database, but the problem is that the local database needs to be emptied before the new data is being copied.
And a progressbar needs to display the copy progress.
Here is the code what I have now:

Private Sub StartScreen1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim dt As New DataTable()

        'Get Data into DataTable from MySQL Server database    
        Using cnn As New MySqlConnection("Server=" + My.Settings.OnlineHost + ";Database=" + My.Settings.OnlinedbName + ";Uid=" + My.Settings.OnlineUser + ";Pwd=" + My.Settings.Onlinepw)
            Dim cmdSelect As MySqlCommand = New MySqlCommand("Select * From songs")
            cmdSelect.Connection = cnn
            Dim ad As New MySqlDataAdapter(cmdSelect)
            ad.AcceptChangesDuringFill = False
            ad.Fill(dt)
        End Using

        'Using cnn As New OleDbConnection("Provider=" + My.Settings.Offlineprovider + ";Data Source=" + My.Settings.AccesssourceOff)
        'Dim cmdDelete As OleDbCommand = New OleDbCommand("Delete * From songs")
        'cmdDelete.Connection = cnn
        'Dim ad As New OleDbDataAdapter(cmdDelete)
        'Dim cmdBuilder As New OleDbCommandBuilder(ad)
        'Dim cmd2 As OleDbCommand = cmdBuilder.GetDeleteCommand()
        'cmd2.Connection = cnn
        'ad.DeleteCommand = cmd2
        'End Using

        'Insert Data from DataTable into Access database  
        Using cnn As New OleDbConnection("Provider=" + My.Settings.Offlineprovider + ";Data Source=" + My.Settings.AccesssourceOff)
            Dim cmdSelect As OleDbCommand = New OleDbCommand("Select * From songs")
            cmdSelect.Connection = cnn
            Dim ad As New OleDbDataAdapter(cmdSelect)
            Dim cmdBuilder As New OleDbCommandBuilder(ad)
            Dim cmd As OleDbCommand = cmdBuilder.GetInsertCommand()
            cmd.Connection = cnn
            ad.InsertCommand = cmd
            ad.Update(dt)
            ProgressBar1.Value = ProgressBar1.Value + 1
        End Using
End Sub

Thanks again.

 

[ud2008]

anyone able to help me with my last message??