Reloading database content in the application

[Monster Killer]

Hello.

I am new to using databases in applications and i have a code to select data from the database. However, the first time the application starts, new data is inserted into the database which is needed after. But, the data does not appear until the whole application is restarted.

I need some help on how i can refresh the database content.
The current code for selecting the data is:

Imports System.Data.SqlServerCe

Public Class MSSUI
    Dim con As SqlCeConnection = New SqlCeConnection(My.Settings.DB1ConnectionString)
    Dim cmd As SqlCeCommand
    Dim myDA As New SqlCeDataAdapter
    Dim myDataSet As New DataSet

   Private Sub MSSUI_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cmd = New SqlCeCommand("SELECT * FROM srcdspath", con)
        If con.State = ConnectionState.Closed Then con.Open()
        Dim sdr1 As SqlCeDataReader = cmd.ExecuteReader()
        While sdr1.Read = True
            srcds21 = (sdr1.Item("path"))
            extracmds.Text = (sdr1.Item("path"))
        End While
        cmd.Dispose()
        sdr1.Close()
End Sub

'other codes doing things
End Class

Thank's in advance

[kvprajapati]

>But, the data does not appear until the whole application is restarted.

Open app.config and change the path of .sdf

<add name="WindowsFormsApplication6.Properties.Settings.Database1ConnectionString"
            connectionString="Data Source=C:\your_folder\bin\Debug\Database1.sdf"
            providerName="Microsoft.SqlServerCe.Client.3.5" />

[Monster Killer]

>But, the data does not appear until the whole application is restarted.

Open app.config and change the path of .sdf

<add name="WindowsFormsApplication6.Properties.Settings.Database1ConnectionString"
            connectionString="Data Source=C:\your_folder\bin\Debug\Database1.sdf"
            providerName="Microsoft.SqlServerCe.Client.3.5" />

Thank you for your reply. I tried this but it unfortunately doesn't solve my problem.

Basically, The first time the application is opened, a different form window will pop up where the user selects a directory. When they click a button the data is written to the database and that form is closed and the main form is enabled. I need a way to refresh the database or to restart the application.

[Monster Killer]

Anyone have any ideas?

[cellus205]

Can't you just create a function that calls the DataAdapter from that class and updates the datatable in your main form?

Dim newForm As MSSUI
newForm = MSSUI
MSSUI.MyDA.Update(mainFormTable)
newForm.Close()

oomething like that? What version of .NET are you using?

[Monster Killer]

Can't you just create a function that calls the DataAdapter from that class and updates the datatable in your main form?

Dim newForm As MSSUI
newForm = MSSUI
MSSUI.MyDA.Update(mainFormTable)
newForm.Close()

oomething like that? What version of .NET are you using?

Im using 2008 Express

[kvprajapati]

Post your complete code. You can use attachment feature to post .zip of your code.

[Monster Killer]

This code is in the zip file.

As you will see i tried restarting the application to see if it would reload the database but it didn't work.

[kvprajapati]

Create a procedure/sub and call it whenever it is required.

public Sub Test()
      
      Dim adp as new SqlCeDataAdapter("SELECT * FROM srcdspath", con)
     Dim dt  as new DataTable
     adp.Fill(dt)

     dataGridView1.DataSource=dt
End sub

 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        sql = "INSERT INTO srcdspath(path) " + "VALUES (@path1) "
        cmd = New SqlCeCommand(sql, con)
        If con.State = ConnectionState.Closed Then con.Open()
        cmd.Parameters.Add("@path1", srcdsdir.SelectedPath)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        con.Dispose()
        Me.Close()

        Test()

    End Sub

[Monster Killer]

Create a procedure/sub and call it whenever it is required.

public Sub Test()
      
      Dim adp as new SqlCeDataAdapter("SELECT * FROM srcdspath", con)
     Dim dt  as new DataTable
     adp.Fill(dt)

     dataGridView1.DataSource=dt
End sub

 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        sql = "INSERT INTO srcdspath(path) " + "VALUES (@path1) "
        cmd = New SqlCeCommand(sql, con)
        If con.State = ConnectionState.Closed Then con.Open()
        cmd.Parameters.Add("@path1", srcdsdir.SelectedPath)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        con.Dispose()
        Me.Close()

        Test()

    End Sub

Thanks.
How do i get the data from that, there will be only 1 record in the database and i need to get that one out to use. An example could be showing it in a text box.

[kvprajapati]

>How do i get the data from that, there will be only 1 record

Write following code in click handler of button.

Dim Con as New SqlCeConnection(".....")
        cmd = New SqlCeCommand("SELECT * FROM srcdspath where colname='value_here'", con)
        con.Open()
        Dim sdr1 As SqlCeDataReader = cmd.ExecuteReader()
        if sdr1.Read() Then
            extracmds.Text = sdr1.Item("path").ToString()
        End While
        sdr1.Close()
        con.Close()