asked on

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

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?

Paul MacDonald

...
lstFilePaths.DataSource = objDataView
lstFilePaths.DisplayMember = "CIType"
lstFilePaths.DataBind()
I think.

David L. Hansen

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!

Karl_mark

ASKER

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?

ASKER CERTIFIED SOLUTION

David L. Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial