windows forms startup issue

[ud2008]

Hi there,

I'm having an issue with the startup of my build app with vb2010.

Let me explain what the situation is at the moment.

I have a form and a splashscreen. I use the application events (startup event) to check if there is a network connection, if so the splashscreen will run and it will check if the database is available, if this is the case, it will copy the online database to the local ms access database.
If the startup check of the network is false, it the user gets a message that there isn't any network available and the program will try to run normal, then when the user clicks the ok button of that message, it will check if the local database has any records. If so the splashscreen will run and the program starts normal (without copy the database, because there isn't any network available).

The issue that I am having is, that when there isn't a network connection and the local database is empty, (I do get the message that the local database is empty), but the program still runs, while it should exit.

Here is the code of the application event:

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
            Dim topmessage As New TopMostMessage
            Dim result As DialogResult
            Dim sql As String
            Dim ds As New DataSet
            Dim oledbAdapter As OleDbDataAdapter
            Dim connetionString As String
            Dim connection As OleDbConnection
            If My.Computer.Network.IsAvailable Then
                Dim splash As SplashScreen1 = CType(My.Application.SplashScreen, SplashScreen1)
            Else
                result = topmessage.Show("There is no internet connection available!" & vbNewLine & _
                                        "Songlist Editor 2 will try to run normal!", _
                                        "Songlist Editor 2", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                If result = DialogResult.OK Then
                    connetionString = "Provider=" + My.Settings.dbaccess + "Data Source = |DataDirectory|\" + My.Settings.msaccess
                    sql = "SELECT * FROM songs"
                    connection = New OleDbConnection(connetionString)
                    Try
                        connection.Open()
                        oledbAdapter = New OleDbDataAdapter(sql, connection)
                        oledbAdapter.Fill(ds, "songs")
                        oledbAdapter.Dispose()
                        connection.Close()
                        If ds.Tables(0).Rows.Count > 0 Then
                            'Do nothing
                        Else
                            result = topmessage.Show("Host is not available!" & vbNewLine & _
                                            "Songlist Editor 2 cannot use the local database and will exit!" & vbNewLine & _
                                            "Make sure you have an active internet connection and try again later", _
                                            "Songlist Editor", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                            If result = DialogResult.OK Then
                                e.Cancel = False
                            End If
                        End If
                    Catch ex As Exception
                        MsgBox("Can not open connection ! ")
                    End Try
                End If
            End If
        End Sub

And the splashscreen:

Sub New()
        InitializeComponent()
    End Sub

    Public Overrides Sub ProcessCommand(ByVal cmd As System.Enum, ByVal arg As Object)
        MyBase.ProcessCommand(cmd, arg)
    End Sub

    Public Enum SplashScreenCommand
        SomeCommandId
    End Enum

Private Sub SplashScreen1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If DataBase.DBConnectionStatus = True Then
            'copy database from online to local
            'dbc.CopyDatabase()
            Dim dt As New DataSet()
            Try
                'Get Data into DataTable from MySQL Server database    
                Using cnn As New MySqlConnection("Server=" + My.Settings.host + ";Database=" + My.Settings.database + ";Uid=" + My.Settings.username + ";Pwd=" + My.Settings.mysqlpw)
                    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
                Try

                    DeleteRecords()
                    Try
                        'Insert Data from DataTable into Access database  
                        Using cnn As New OleDbConnection("Provider=" + My.Settings.dbaccess + "Data Source = |DataDirectory|\" + My.Settings.msaccess)
                            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
                            For Each row As DataRow In dt.Tables(0).Rows
                                ad.InsertCommand = cmd
                            Next
                            ad.Update(dt)
                        End Using
                    Catch ex3 As Exception
                        MessageBox.Show(ex3.Message)
                    End Try
                Catch ex2 As Exception
                    MessageBox.Show(ex2.Message)
                End Try
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        Else
            Application.Exit()
        End If
        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)
    End Sub

    Public Sub DeleteRecords()
        MScmd.Connection = MScon
        MScmd.CommandType = CommandType.Text
        MScmd.CommandText = "DELETE FROM songs"
        Using MScon
            MScon.Open()
            MScmd.ExecuteNonQuery()
        End Using
    End Sub

The "DBConnectionStatus" is a function in a class:

Public Shared Function DBConnectionStatus() As Boolean
        Try
            Using sqlConn As New MySqlConnection("Server=" + My.Settings.host + ";Database=" + My.Settings.database + ";Uid=" + My.Settings.username + ";Pwd=" + My.Settings.mysqlpw)
                sqlConn.Open()
                Return (sqlConn.State = ConnectionState.Open)
            End Using
        Catch e1 As MySqlException
            Return False
        Catch e2 As Exception
            Return False
        End Try
    End Function

I hope someone can help me with this.

If I need to clarify something, please let me know.

Thanks in advanced.

 

[ud2008]

I've checked the sourceadapter.fill and the destinationadapter.update for the record count and the source gave me an item of 313, while the destination gave me zero. So nothing is updated into the ms access database, what I would like to know is, are the OledbType important for the insert, because they are not really the same as I configured in ms access.

Thanks

 

[jmcilhinney]

Ah, sorry. It was my mistake but it's probably something that you should have picked up. I said to set the AcceptChangesDuringFill property to False. You're calling Fill on the source adapter and Update on the destination adapter so obviously you must set that property on the source adapter, not the destination adapter.

 

[ud2008]

Thanks, that works, but there is a small problem, the records inserted are multiplied, so of each record is inserted double.

 

[jmcilhinney]

Then you did it wrong.

 

[ud2008]

Ok, I solved the error on the duplicate records, by creating a deletecommand before inserting the records in the access database.
It all works for 1 table, but when I try to add the code for the second table, only the first table is being updated, the second remains empty and even the count (which I've added to check) is zero.

Here is the code I use (hopefully you can see the differents in the names):

Using sourceConnection As New MySqlConnection("Server=" + My.Settings.host + ";Database=" + My.Settings.database + ";Uid=" + My.Settings.username + ";Pwd=" + My.Settings.mysqlpw),
                    destinationConnection As New OleDbConnection("Provider=" + My.Settings.dbaccess + "Data Source = |DataDirectory|\" + My.Settings.msaccess),
                    sourceAdapter As New MySqlDataAdapter("SELECT * FROM songs", sourceConnection),
                    songsAdapter As New OleDbDataAdapter,
                    songsDelete As New OleDbCommand("DELETE FROM songs", destinationConnection),
                    songsCommand As New OleDbCommand("INSERT INTO songs (Id, Songtitle, Genre, Songtext, Audio, Scores) VALUES (@Id, @Songtitle, @Genre, @Songtext, @Audio, @Scores)", destinationConnection),
                    SourceEmailAdapter As New MySqlDataAdapter("SELECT * FROM email", sourceConnection),
                    EmailAdapter As New OleDbDataAdapter,
                    emailDelete As New OleDbCommand("DELETE FROM email", destinationConnection),
                    emailCommand As New OleDbCommand("INSERT INTO email (Id, Groupname, Realname, Emailaddress) VALUES (@Id, @Groupname, @Realname, @Emailaddress)", destinationConnection)
                    EmailAdapter.DeleteCommand = emailDelete
                    EmailAdapter.InsertCommand = emailCommand
                    EmailAdapter.AcceptChangesDuringFill = False
                    songsAdapter.DeleteCommand = songsDelete
                    songsAdapter.InsertCommand = songsCommand
                    sourceAdapter.AcceptChangesDuringFill = False

                    destinationConnection.Open()
                    songsDelete.ExecuteNonQuery()
                    emailDelete.ExecuteNonQuery()

                    songsCommand.Parameters.Add("@Id", OleDbType.Integer, 11, "Id")
                    songsCommand.Parameters.Add("@Songtitle", OleDbType.VarChar, 200, "Songtitle")
                    songsCommand.Parameters.Add("@Genre", OleDbType.VarChar, 200, "Genre")
                    songsCommand.Parameters.Add("@Songtext", OleDbType.VarChar, 200, "Songtext")
                    songsCommand.Parameters.Add("@Audio", OleDbType.VarChar, 200, "Audio")
                    songsCommand.Parameters.Add("@Scores", OleDbType.VarChar, 200, "Scores")

                    emailCommand.Parameters.Add("@Id", OleDbType.Integer, 11, "Id")
                    emailCommand.Parameters.Add("@Groupname", OleDbType.VarChar, 200, "Groupname")
                    emailCommand.Parameters.Add("@Realname", OleDbType.VarChar, 200, "Realname")
                    emailCommand.Parameters.Add("@Emailaddress", OleDbType.VarChar, 200, "Emailaddress")

                    Dim table As New DataTable
                    Dim table2 As New DataTable

                    sourceAdapter.Fill(table)
                    SourceEmailAdapter.Fill(table2)
                    Dim count As Integer = SourceEmailAdapter.Update(table2)
                    MsgBox(count)

                    songsAdapter.Update(table)
                    EmailAdapter.Update(table2)
end using

I wonder if I should use multiple using instead of one.

 

[ud2008]

Anyone an idea?