We help IT Professionals succeed at work.

Trying to Refresh Data from SQL in List Box VB .Net

Karl_mark
Karl_mark asked
on
I have a window in VB.net which contains data from a table in SQL Server using an sqladaptor. The user has an option to add data to the table by clicking a button and selecting the source data. The question is, how do I refresh the data in the listbox to reflect the addition of the data?
The data is added by opening another form. I thought that I could refresh the data by reflling the dataset by calling the getfocus. However, this does not refresh the data and teh only way I have succeeded in seeing the new data is by physically closing the form at runtime and then reloading it. The code I have for refreshing the data is:
Private Sub FillDataSetAnd View()
  objAdaptor = Nothing
  objAdaptor = New SqlDataAdaptor("Select identifier, CI Type, FilePath FROM dbo.usr_importfilepaths", objConnection)
  objDataSet = New DataSet
  objAdaptor.Fill(objDataSet.Tables("FilePaths"))
  lstFilePaths.DataSource = objDataView
  lstFilePaths.DisplayMember = "CIType"
End Sub

Open in new window


I've tried adding this on the GotFocus and the Load events for the form. However, these do not seem to fire, mainly because the form will not close. Ideally I'd like the form to close when the add new data button is selected, but I can't seem to get this to work.
I have a private sub in the gorm called ExitForm() which empties all variables and has Me.Close() as the final line. This does not close the form however! I've tried several methods of closing the form, including having code to close the form as part of the code on the button which calls the next form:

'Call next form and close this form
Dim frmAddCITypes as Form
frmAddCITypes = frmAddCI  'This calls the code at the top of the frmAddCI form under Public Class
frmAddCITypes.ShowDialog(Me)
ExitForm() 'private sub to close existing form

Open in new window


Because I can't get the first form to close correctly I can't call the load event on the first form when the data has been successfully added. What is the best way of closing one form, launching the next form or indeed updating a dataset to show additions?
Comment
Watch Question

Paul MacDonaldDirector, Information Systems

Commented:
...
 lstFilePaths.DataSource = objDataView
 lstFilePaths.DisplayMember = "CIType"
 lstFilePaths.DataBind()
I think.
 
I always give my data-displaying-objects a datatable as its datasource.  If you do that, then when the user adds something just insert it into the sql server table and refresh the datatable.  With most controls using this approach the data will automatically be updated and shown in the control.  It works like a charm!

Author

Commented:
I'm already binding data in the listbox. The problem is getting the code to be called!
I haven't used datatables before. The current code Is:

Public Class frmAddCI

    Dim strConnectionString As String = ConfigurationManager.ConnectionStrings("WindowsApplication1.My.MySettings.BFWConnectionString").ConnectionString()
    Dim objConnection As New SqlConnection(strConnectionString)
    Dim objAdaptor As New SqlDataAdapter("SELECT md_guid, md_module_guid, md_title, md_client_class, md_name " & _
                                         "FROM dbo.md_class_type " & _
                                         "where md_client_class = '   Private Sub FillDataSetAndView()
        objDataset = New DataSet
        Try
            objAdaptor.Fill(objDataset, "AvailableCIs")
            objDataView = New DataView(objDataset.Tables("AvailableCIs"))
            lstAvailableCIs.DataSource = objDataView
            lstAvailableCIs.DisplayMember = "md_title"
        Catch NullReferenceExceptionErr As NullReferenceException
            ReloadConnections()
            objAdaptor.Fill(objDataset, "AvailableCIs")
            objDataView = New DataView(objDataset.Tables("AvailableCIs"))
            lstAvailableCIs.DataSource = objDataView
            lstAvailableCIs.DisplayMember = "md_title"
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
   
    Private Sub FillDataSetAndView()
        objDataset = New DataSet
        Try
            objAdaptor.Fill(objDataset, "AvailableCIs")
            objDataView = New DataView(objDataset.Tables("AvailableCIs"))
            lstAvailableCIs.DataSource = objDataView
            lstAvailableCIs.DisplayMember = "md_title"
        Catch NullReferenceExceptionErr As NullReferenceException
            ReloadConnections()
            objAdaptor.Fill(objDataset, "AvailableCIs")
            objDataView = New DataView(objDataset.Tables("AvailableCIs"))
            lstAvailableCIs.DataSource = objDataView
            lstAvailableCIs.DisplayMember = "md_title"
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
       

    End Sub


How would it differ using a datatable instead?

I think you'll be alright if you are using the FillLoadOption = LoadOption.OverwriteChanges in your objAdapter.  If the changes are making it to the database but are just not being displayed you can use "objAdapter.Fill(objDataSet)" to refresh the adapter (the sqladapter object uses 'Fill' not only to initially populate your dataset but also to refresh it).