Update a combobox on form 1 based on data entry update from form 2

Hello experts. I have two forms I created in vb.net the connects to a sql database. form 1 contains various text and combo boxes. One of my combobox's is tied to a table in my database that contains locations (building 1, building 2, etc). On my second form (form 2) I created a text box and save button to add any additional locations to the table in my database. As you can see from my code below there is some basic error checking for records that already exist. Here is my issue. Everything appears to work correctly however, what ever new record I have added does not show up in my form 1 combobox. They appear in form 1 only after I close and re-open. I thought I could resolve by placing a Form1.Refresh() at the end of my code but that appears to have no affect. How do I get form 1 combobox to populate after the new record has been added to the table?

 Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        If txtlocation.Text = "" Then
            MsgBox("Field Cannot Be Blank", MsgBoxStyle.Information)

            Exit Sub
        End If

        'Check for duplicate values
        Dim Connection As New SqlClient.SqlConnection("Server=xxxx=xxxx;Integrated Security=True")
        Dim Command As New SqlClient.SqlCommand("Select * from Locations order by Location ASC", Connection)
        Dim query As String = "Select Location from Locations where Location=@Location"


        Connection.Open()
        Command = New SqlCommand(query, Connection)
        Command.Parameters.AddWithValue("@Location", txtlocation.Text)
        Dim dr As SqlDataReader
        dr = Command.ExecuteReader()
        If dr.HasRows Then
            MsgBox("Location Already Exists!", MsgBoxStyle.Exclamation, "Add New Location!")

        Else

            'Insert Data
            SQL.AddParam("@Location", txtlocation.Text)

            SQL.ExecQuery("Insert Into Locations (Location) " &
                      "Values (@Location);", True)

            'Report & Abort On Errors
            If SQL.hasException(True) Then Exit Sub
            If SQL.DBDT.Rows.Count > 0 Then
                Dim r As DataRow = SQL.DBDT.Rows(0)
                'MsgBox(r("").ToString)
            End If


            txtlocation.Clear()
            Form1.Refresh()

            Connection.Close()
        End If
DJ PAsked:
Who is Participating?
 
ArkConnect With a Mentor Commented:
If u'r using bindingsource to fill datagridview just call
bindingSource1.ResetBindings(False)

Open in new window

Otherwise after closing form2 call your LoadGrid procedure:
Using f As New Form2
    If f.ShowDialog = Windows.Forms.DialogResult.OK Then
        LoadGrid()
    End If
End Using

Open in new window

0
 
NorieVBA ExpertCommented:
How have you tied the combobox to the table?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Where and how are you filling the combo that needs to be refreshed? You basically need to "refresh" that combo after adding new records.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Fernando SotoRetiredCommented:
Hi DJ P;

The issue is that inserting new records from another form via another connection will not update the data that is being displayed from the database in form1. When you query data from a database the data retrieved is a disconnected from the database itself so therefore need to get the data again from the database and refresh the control on the form. This is why it works when you restart the app.
0
 
DJ PAuthor Commented:
Scott, If you look at my code on my second form I am refreshing the entire form 1 after the records have been added. AM I missing something?
0
 
Fernando SotoRetiredCommented:
@DJ P;

You will need to re-query the database before the new records can show up on Form1. You are working with a disconnected database and no new records will show up in your Form1 until you go back to the database and query again.
0
 
it_saigeDeveloperCommented:
As has already been stated, you will need to re-query the database and rebind that results to the combobox.  I would suggest that you use an event to signal from Form2 to Form1 that the save has completed.  Example:

Form1.vb -
Public Class Form1
	Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
		ComboBox1.DataSource = MockDatabase.MockRecords.Select(Function(x) New With {.DisplayMember = x.Value, .ValueMember = x.ID}).ToList()
		ComboBox1.DisplayMember = "DisplayMember"
		ComboBox1.ValueMember = "ValueMember"
	End Sub

	Private Sub OnClick(sender As Object, e As EventArgs) Handles btnAddRecords.Click
		Dim frm2 = New Form2()
		AddHandler frm2.RecordSaved, AddressOf OnRecordSaved
		frm2.Show()
	End Sub
	Private Sub OnRecordSaved(ByVal sender As Object, ByVal e As EventArgs)
		ComboBox1.DataSource = MockDatabase.MockRecords.Select(Function(x) New With {.DisplayMember = x.Value, .ValueMember = x.ID}).ToList()
		ComboBox1.DisplayMember = "DisplayMember"
		ComboBox1.ValueMember = "ValueMember"
	End Sub
End Class

Open in new window

Form1.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
	Inherits System.Windows.Forms.Form

	'Form overrides dispose to clean up the component list.
	<System.Diagnostics.DebuggerNonUserCode()> _
	Protected Overrides Sub Dispose(ByVal disposing As Boolean)
		Try
			If disposing AndAlso components IsNot Nothing Then
				components.Dispose()
			End If
		Finally
			MyBase.Dispose(disposing)
		End Try
	End Sub

	'Required by the Windows Form Designer
	Private components As System.ComponentModel.IContainer

	'NOTE: The following procedure is required by the Windows Form Designer
	'It can be modified using the Windows Form Designer.  
	'Do not modify it using the code editor.
	<System.Diagnostics.DebuggerStepThrough()> _
	Private Sub InitializeComponent()
		Me.ComboBox1 = New System.Windows.Forms.ComboBox()
		Me.btnAddRecords = New System.Windows.Forms.Button()
		Me.SuspendLayout()
		'
		'ComboBox1
		'
		Me.ComboBox1.FormattingEnabled = True
		Me.ComboBox1.Location = New System.Drawing.Point(13, 13)
		Me.ComboBox1.Name = "ComboBox1"
		Me.ComboBox1.Size = New System.Drawing.Size(259, 21)
		Me.ComboBox1.TabIndex = 0
		'
		'btnAddRecords
		'
		Me.btnAddRecords.Location = New System.Drawing.Point(150, 41)
		Me.btnAddRecords.Name = "btnAddRecords"
		Me.btnAddRecords.Size = New System.Drawing.Size(121, 23)
		Me.btnAddRecords.TabIndex = 1
		Me.btnAddRecords.Text = "Add Records"
		Me.btnAddRecords.UseVisualStyleBackColor = True
		'
		'Form1
		'
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.ClientSize = New System.Drawing.Size(284, 79)
		Me.Controls.Add(Me.btnAddRecords)
		Me.Controls.Add(Me.ComboBox1)
		Me.Name = "Form1"
		Me.Text = "Form1"
		Me.ResumeLayout(False)

	End Sub

	Friend WithEvents ComboBox1 As ComboBox
	Friend WithEvents btnAddRecords As Button
End Class

Open in new window

Form2.vb -
Public Class Form2
	Private ReadOnly _recordSaved As New List(Of EventHandler)
	Public Custom Event RecordSaved As EventHandler
		AddHandler(value As EventHandler)
			_recordSaved.Add(value)
		End AddHandler
		RemoveHandler(value As EventHandler)
			_recordSaved.Remove(value)
		End RemoveHandler
		RaiseEvent(sender As Object, e As EventArgs)
			For Each handler As EventHandler In _recordSaved
				Try
					handler.Invoke(sender, e)
				Catch ex As Exception

				End Try
			Next
		End RaiseEvent
	End Event
	Private Sub OnSelectedValueChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedValueChanged
		Dim lb = CType(sender, ListBox)
		btnRemove.Enabled = lb.SelectedValue IsNot Nothing
	End Sub

	Private Sub OnLeave(sender As Object, e As EventArgs) Handles TextBox1.Leave
		Dim tb = CType(sender, TextBox)
		btnAdd.Enabled = Not String.IsNullOrWhiteSpace(tb.Text)
	End Sub

	Private Sub OnClick(sender As Object, e As EventArgs) Handles btnAdd.Click, btnRemove.Click, btnSaveAndClose.Click
		Dim btn = CType(sender, Button)
		If btn.Equals(btnAdd) Then
			Dim matched = MockDatabase.MockRecords.Where(Function(x) x.Value.Equals(TextBox1.Text)).FirstOrDefault()
			If matched Is Nothing Then
				Dim id = If(MockDatabase.MockRecords.Count > 0, MockDatabase.MockRecords.Last().ID + 1, 0)
				MockDatabase.MockRecords.Add(New MockRecord With {.ID = id, .Value = TextBox1.Text})
			End If
			ListBox1.DataSource = MockDatabase.MockRecords.Select(Function(x) New With {.DisplayMember = x.Value, .ValueMember = x.ID}).ToList()
			ListBox1.DisplayMember = "DisplayMember"
			ListBox1.ValueMember = "ValueMember"
			TextBox1.Clear()
		ElseIf btn.Equals(btnRemove) Then
			Dim matched = MockDatabase.MockRecords.Where(Function(x) x.Value.Equals(ListBox1.SelectedValue)).FirstOrDefault()
			If matched IsNot Nothing Then
				MockDatabase.MockRecords.Remove(matched)
			End If
			ListBox1.SelectedValue = Nothing
			btn.Enabled = False
		ElseIf btn.Equals(btnSaveAndClose) Then
			RaiseEvent RecordSaved(sender, e)
			Close()
		End If
	End Sub

	Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
		ListBox1.DataSource = MockDatabase.MockRecords.Select(Function(x) New With {.DisplayMember = x.Value, .ValueMember = x.ID}).ToList()
		ListBox1.DisplayMember = "DisplayMember"
		ListBox1.ValueMember = "ValueMember"
		'		btnAdd.Enabled = btnRemove.Enabled = False
	End Sub
End Class

Open in new window

Form2.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form2
	Inherits System.Windows.Forms.Form

	'Form overrides dispose to clean up the component list.
	<System.Diagnostics.DebuggerNonUserCode()> _
	Protected Overrides Sub Dispose(ByVal disposing As Boolean)
		Try
			If disposing AndAlso components IsNot Nothing Then
				components.Dispose()
			End If
		Finally
			MyBase.Dispose(disposing)
		End Try
	End Sub

	'Required by the Windows Form Designer
	Private components As System.ComponentModel.IContainer

	'NOTE: The following procedure is required by the Windows Form Designer
	'It can be modified using the Windows Form Designer.  
	'Do not modify it using the code editor.
	<System.Diagnostics.DebuggerStepThrough()> _
	Private Sub InitializeComponent()
		Me.ListBox1 = New System.Windows.Forms.ListBox()
		Me.btnRemove = New System.Windows.Forms.Button()
		Me.btnAdd = New System.Windows.Forms.Button()
		Me.TextBox1 = New System.Windows.Forms.TextBox()
		Me.btnSaveAndClose = New System.Windows.Forms.Button()
		Me.SuspendLayout()
		'
		'ListBox1
		'
		Me.ListBox1.FormattingEnabled = True
		Me.ListBox1.Location = New System.Drawing.Point(270, 13)
		Me.ListBox1.Name = "ListBox1"
		Me.ListBox1.Size = New System.Drawing.Size(249, 303)
		Me.ListBox1.TabIndex = 0
		'
		'btnRemove
		'
		Me.btnRemove.Location = New System.Drawing.Point(188, 39)
		Me.btnRemove.Name = "btnRemove"
		Me.btnRemove.Size = New System.Drawing.Size(75, 23)
		Me.btnRemove.TabIndex = 1
		Me.btnRemove.Text = "Remove"
		Me.btnRemove.UseVisualStyleBackColor = True
		'
		'btnAdd
		'
		Me.btnAdd.Location = New System.Drawing.Point(107, 39)
		Me.btnAdd.Name = "btnAdd"
		Me.btnAdd.Size = New System.Drawing.Size(75, 23)
		Me.btnAdd.TabIndex = 2
		Me.btnAdd.Text = "Add"
		Me.btnAdd.UseVisualStyleBackColor = True
		'
		'TextBox1
		'
		Me.TextBox1.Location = New System.Drawing.Point(12, 13)
		Me.TextBox1.Name = "TextBox1"
		Me.TextBox1.Size = New System.Drawing.Size(251, 20)
		Me.TextBox1.TabIndex = 3
		'
		'btnSaveAndClose
		'
		Me.btnSaveAndClose.Location = New System.Drawing.Point(12, 292)
		Me.btnSaveAndClose.Name = "btnSaveAndClose"
		Me.btnSaveAndClose.Size = New System.Drawing.Size(251, 23)
		Me.btnSaveAndClose.TabIndex = 4
		Me.btnSaveAndClose.Text = "Save and Close"
		Me.btnSaveAndClose.UseVisualStyleBackColor = True
		'
		'Form2
		'
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.ClientSize = New System.Drawing.Size(531, 331)
		Me.Controls.Add(Me.btnSaveAndClose)
		Me.Controls.Add(Me.TextBox1)
		Me.Controls.Add(Me.btnAdd)
		Me.Controls.Add(Me.btnRemove)
		Me.Controls.Add(Me.ListBox1)
		Me.Name = "Form2"
		Me.Text = "Form2"
		Me.ResumeLayout(False)
		Me.PerformLayout()

	End Sub

	Friend WithEvents ListBox1 As ListBox
	Friend WithEvents btnRemove As Button
	Friend WithEvents btnAdd As Button
	Friend WithEvents TextBox1 As TextBox
	Friend WithEvents btnSaveAndClose As Button
End Class

Open in new window

SupportingObjects.vb -
Module MockDatabase
	Public ReadOnly Property MockRecords() As List(Of MockRecord) = New List(Of MockRecord)
End Module

Class MockRecord
	Public Property ID() As Integer
	Public Property Value() As String
End Class

Open in new window

Which produces the following results:

Initial Load -Capture.PNGOpening Form2 and Adding Records -Capture.PNGSaving and Closing Form2 -Capture.PNG
You could also choose to use ShowDialog (as opposed to show, and just update the combobox when the DialogResult is OK)

-saige-
0
 
DJ PAuthor Commented:
Wow all that code for something that should be so simple? For myself being a newbie, This will take me some time to figure out.
0
 
NorieVBA ExpertCommented:
DJ P

As far as I can see the code it_saige has posted is the entire code for an example project.

A lot of the code, e.g. Form1.Designer.vb,  would be generated automatically when you created your forms and added controls to them.
0
 
DJ PAuthor Commented:
Norie, For a newbie like myself I cannot make the correlation between my code and the example code that was posted. I understand why the problem is taking place but not experienced enough to know where to place code. Don't know if this helps but here is the code to my second form to add locations.


 Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        If txtlocation.Text = "" Then
            MsgBox("Field Cannot Be Blank", MsgBoxStyle.Information)

            Exit Sub
        End If

        'Check for duplicate values
        Dim Connection As New SqlClient.SqlConnection("Server=xxx;Database=xxx Security=True")
        Dim Command As New SqlClient.SqlCommand("Select * from Locations order by Location ASC", Connection)
        Dim query As String = "Select Location from Locations where Location=@Location"


        Connection.Open()
        Command = New SqlCommand(query, Connection)
        Command.Parameters.AddWithValue("@Location", txtlocation.Text)
        Dim dr As SqlDataReader
        dr = Command.ExecuteReader()
        If dr.HasRows Then
            MsgBox("Location Already Exists!", MsgBoxStyle.Exclamation, "Add New Location!")

        Else

            'Insert Data
            SQL.AddParam("@Location", txtlocation.Text)

            SQL.ExecQuery("Insert Into Locations (Location) " &
                      "Values (@Location);", True)

            'Report & Abort On Errors
            If SQL.hasException(True) Then Exit Sub
            If SQL.DBDT.Rows.Count > 0 Then
                Dim r As DataRow = SQL.DBDT.Rows(0)
                'MsgBox(r("").ToString)
            End If



            txtlocation.Clear()


            Connection.Close()
        End If



    End Sub
0
 
NorieVBA ExpertCommented:
Can you post the code you are using to connect to the database and populate the location combobox on Form1?
0
 
NorieVBA ExpertCommented:
This probably isn't ideal code, mainly because it relies on the Activated event of Form1 to populate/update the combobox, but it works for me.
Form1.vb
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' show Form2
        Me.Hide()
        Form2.Show()
    End Sub

    Private Sub Form1_Activated(sender As Object, e As EventArgs) Handles Me.Activated
        Dim conn As New SqlConnection("Server=xxx;Database=xxx;Integrated Security=True")
        Dim adp As New SqlDataAdapter
        Dim ds As New DataSet()

        Dim qry As String = "Select Location from Locations ORDER BY Location"

        adp.SelectCommand = New SqlCommand(qry, conn)

        adp.Fill(ds)

        With ComboBox1
            .DataSource = ds.Tables(0)
            .DisplayMember = "Location"
        End With

        conn.Close()
    End Sub
End Class

Open in new window

Form2.vb
Public Class Form2
    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        If txtlocation.Text = "" Then
            MsgBox("Field Cannot Be Blank", MsgBoxStyle.Information)
            Exit Sub
        End If

        'Check for duplicate values
        Dim Connection As New SqlClient.SqlConnection("Server=NO2\SQLEXPRESS;Database=TestDB;Integrated Security=True")
        Dim Command As New SqlClient.SqlCommand("Select * from Locations order by Location ASC", Connection)
        Dim query As String = "Select Location from Locations where Location=@Location"

        Connection.Open()

        ' check if location already exists
        Command = New SqlCommand(query, Connection)
        Command.Parameters.AddWithValue("@Location", txtlocation.Text)

        Dim dr As SqlDataReader
        dr = Command.ExecuteReader()
        If dr.HasRows Then
            MsgBox("Location Already Exists!", MsgBoxStyle.Exclamation, "Add New Location!")
            Connection.Close()
        Else

            dr.Close()

            'Insert Data
            query = "Insert Into Locations (Location) " &
                     "Values (@Location);"
            Command.CommandText = query

            Command.ExecuteNonQuery()

            Connection.Close()

            Me.Close()
            Form1.Show()

        End If

    End Sub

    Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
        Me.Close()
        Form1.Show()
    End Sub
End Class

Open in new window

0
 
DJ PAuthor Commented:
Norie, Per your request I have attached my code.
Code.docx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.