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("S
Dim Command As New SqlClient.SqlCommand("Sele
Dim query As String = "Select Location from Locations where Location=@Location"
Connection.Open()
Command = New SqlCommand(query, Connection)
Command.Parameters.AddWith
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
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("S
Dim Command As New SqlClient.SqlCommand("Sele
Dim query As String = "Select Location from Locations where Location=@Location"
Connection.Open()
Command = New SqlCommand(query, Connection)
Command.Parameters.AddWith
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
How have you tied the combobox to the table?
Where and how are you filling the combo that needs to be refreshed? You basically need to "refresh" that combo after adding new records.
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.
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.
ASKER
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?
@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.
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.
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 -
Initial Load -
Opening Form2 and Adding Records -
Saving and Closing Form2 -
You could also choose to use ShowDialog (as opposed to show, and just update the combobox when the DialogResult is OK)
-saige-
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<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 ClassPublic 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<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 ClassModule 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 ClassInitial Load -
Opening Form2 and Adding Records -Saving and Closing Form2 -You could also choose to use ShowDialog (as opposed to show, and just update the combobox when the DialogResult is OK)
-saige-
ASKER
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.
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.
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.
ASKER
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("S
Dim Command As New SqlClient.SqlCommand("Sele
Dim query As String = "Select Location from Locations where Location=@Location"
Connection.Open()
Command = New SqlCommand(query, Connection)
Command.Parameters.AddWith
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
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("S
Dim Command As New SqlClient.SqlCommand("Sele
Dim query As String = "Select Location from Locations where Location=@Location"
Connection.Open()
Command = New SqlCommand(query, Connection)
Command.Parameters.AddWith
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
Can you post the code you are using to connect to the database and populate the location combobox on Form1?
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
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 ClassPublic 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 ClassASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.