I have a TabControl1 with 8 Pages.
On TabPage7 I have items listed in ListBox1 which when double clicked are copied over to ListBox2
and are saved to an SQL Database Table (TableName = Parts, FieldName = Requirements).
The Saving and Refreshing from the database works fine.
Now I require to add other Items selected from a ComboBox in TabPage1 to ListBox2 and save them as
before whilst preserving any existing items listed in ListBox2.
I can do this with code below and it looks fine with added item showing at bottom of list
However, my problem is when I refresh the list from the database anything I add to ListBox2 on
TabPage7 from TabPage1 overwrites the existing items.
Is there a way to achieve this, perhaps to Append items to ListBox2 ?
I have tried changing SQL Statement from Update to Insert without any success.
Any help would be much appreciated.
My codes is...
Private Sub MPIComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MPIComboBox.SelectedIndexChanged
'Add Requirement to ListBox2
If MPIComboBox.Text = "YES" Then
ListBox2.Items.Add("M.P.I Report")
'Save added Requirement to Database field...
Dim idNumber As String = ""
idNumber = ItemNoTextBox.Text
If idNumber = ItemNoTextBox5.Text Then
Call DO_SAVE_ListBox2_Main()
End If
End If
End Sub
And Main Saving code used within TabPage7 is...
Public Sub DO_SAVE_ListBox2_Main()
Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim s As String = ""
For Each item As String In ListBox2.Items
s &= item & vbCrLf
Next
'Advice from Tek-Tips...
'Use something other than vbCrLf as the delimiter between items in the database,
'perhaps a character like "|" or "~"
Try
cn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\QDoxDBase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
cn.Open()
cmd.CommandType = CommandType.Text
cmd.Connection = cn
cmd.CommandText = "UPDATE Parts SET RequirementsList = @RequirementsList WHERE ItemNo = '" & ItemNoTextBox2.Text & "'"
cmd.Parameters.AddWithValue("@RequirementsList", s)
cmd.ExecuteNonQuery()
cmd.Dispose()
Me.Validate()
Me.PartsBindingSource.EndEdit()
PartsTableAdapter.Update(Me.QDoxDBaseDataSet)
Catch ex As Exception
MessageBox.Show("Error while inserting record into table..." & ex.Message, "Insert Record")
Finally
cn.Close()
End Try
End Sub
On TabPage7 I have items listed in ListBox1 which when double clicked are copied over to ListBox2
and are saved to an SQL Database Table (TableName = Parts, FieldName = Requirements).
The Saving and Refreshing from the database works fine.
Now I require to add other Items selected from a ComboBox in TabPage1 to ListBox2 and save them as
before whilst preserving any existing items listed in ListBox2.
I can do this with code below and it looks fine with added item showing at bottom of list
However, my problem is when I refresh the list from the database anything I add to ListBox2 on
TabPage7 from TabPage1 overwrites the existing items.
Is there a way to achieve this, perhaps to Append items to ListBox2 ?
I have tried changing SQL Statement from Update to Insert without any success.
Any help would be much appreciated.
My codes is...
Private Sub MPIComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MPIComboBox.SelectedIndexChanged
'Add Requirement to ListBox2
If MPIComboBox.Text = "YES" Then
ListBox2.Items.Add("M.P.I Report")
'Save added Requirement to Database field...
Dim idNumber As String = ""
idNumber = ItemNoTextBox.Text
If idNumber = ItemNoTextBox5.Text Then
Call DO_SAVE_ListBox2_Main()
End If
End If
End Sub
And Main Saving code used within TabPage7 is...
Public Sub DO_SAVE_ListBox2_Main()
Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim s As String = ""
For Each item As String In ListBox2.Items
s &= item & vbCrLf
Next
'Advice from Tek-Tips...
'Use something other than vbCrLf as the delimiter between items in the database,
'perhaps a character like "|" or "~"
Try
cn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\QDoxDBase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
cn.Open()
cmd.CommandType = CommandType.Text
cmd.Connection = cn
cmd.CommandText = "UPDATE Parts SET RequirementsList = @RequirementsList WHERE ItemNo = '" & ItemNoTextBox2.Text & "'"
cmd.Parameters.AddWithValue("@RequirementsList", s)
cmd.ExecuteNonQuery()
cmd.Dispose()
Me.Validate()
Me.PartsBindingSource.EndEdit()
PartsTableAdapter.Update(Me.QDoxDBaseDataSet)
Catch ex As Exception
MessageBox.Show("Error while inserting record into table..." & ex.Message, "Insert Record")
Finally
cn.Close()
End Try
End Sub