Please can someone help me here. I have been days trying to see where I am going wrong.
My VB-2010 Express / SQL Database application allows the user to select from ListBox1 a
number of items/requirements which are then listed in ListBox2.
The selection of these requirements is particular to the active record and so have to be
saved to the Database (TableName = Parts) field (FieldName = RequirementsList) so that when
the user returns to that record (ItemNo) the selection made is there to view or edit.
So, I am trying to save the items of a ListBox2 to ONE field (RequiremntsList) in an SQL
database and then have same ListBox2 refreshed from that database field.
My coding below allows me to do this but when I write the contents saved in the field back
to the ListBox all the items are displayed joined together as one string on one line which
is not the way it should be or was saved.
I have checked the way the items are saved to the field and displayed this in a MsgBox and
this looks ok with each item on it's own seperate line and also have output same to a
multiline TextBox and again each item is displayed as you would expect.
For debugging purposes my code is behind Buttons2 and 3.
This is my code to save to database...
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'SAVE BUTTON
'Add ListBox2 Items to RequirementsList field to Save in PARTS Table...
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
Try
cn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MYDBase.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
And This is my code to REFRESH the ListBox2 from the database...
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'REFRESH BUTTON
Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim str As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MYDBase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim con As New SqlConnection(str)
Dim com As String = "Select RequirementsList from Parts WHERE ItemNo ='" & ItemNoTextBox2.Text & "'"
Dim adpt As New SqlDataAdapter(com, con)
Dim QDoxDBaseDataSet As New DataSet()
adpt.Fill(QDoxDBaseDataSet, "Parts")
Dim Parts As DataTable = QDoxDBaseDataSet.Tables(0)
Dim tempRow As DataRow
ListBox2.Items.Clear()
For Each tempRow In Parts.Rows
ListBox2.Items.Add(tempRow("RequirementsList"))
MsgBox(tempRow("RequirementsList"))
Next
End Sub
I have tried saving and refreshing with & Chr(13) & Chr(10) and & Environment.NewLine to no avail.
Any help would be much appreciated
Thank You
My VB-2010 Express / SQL Database application allows the user to select from ListBox1 a
number of items/requirements which are then listed in ListBox2.
The selection of these requirements is particular to the active record and so have to be
saved to the Database (TableName = Parts) field (FieldName = RequirementsList) so that when
the user returns to that record (ItemNo) the selection made is there to view or edit.
So, I am trying to save the items of a ListBox2 to ONE field (RequiremntsList) in an SQL
database and then have same ListBox2 refreshed from that database field.
My coding below allows me to do this but when I write the contents saved in the field back
to the ListBox all the items are displayed joined together as one string on one line which
is not the way it should be or was saved.
I have checked the way the items are saved to the field and displayed this in a MsgBox and
this looks ok with each item on it's own seperate line and also have output same to a
multiline TextBox and again each item is displayed as you would expect.
For debugging purposes my code is behind Buttons2 and 3.
This is my code to save to database...
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'SAVE BUTTON
'Add ListBox2 Items to RequirementsList field to Save in PARTS Table...
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
Try
cn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MYDBase.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
And This is my code to REFRESH the ListBox2 from the database...
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'REFRESH BUTTON
Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim str As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MYDBase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim con As New SqlConnection(str)
Dim com As String = "Select RequirementsList from Parts WHERE ItemNo ='" & ItemNoTextBox2.Text & "'"
Dim adpt As New SqlDataAdapter(com, con)
Dim QDoxDBaseDataSet As New DataSet()
adpt.Fill(QDoxDBaseDataSet, "Parts")
Dim Parts As DataTable = QDoxDBaseDataSet.Tables(0)
Dim tempRow As DataRow
ListBox2.Items.Clear()
For Each tempRow In Parts.Rows
ListBox2.Items.Add(tempRow("RequirementsList"))
MsgBox(tempRow("RequirementsList"))
Next
End Sub
I have tried saving and refreshing with & Chr(13) & Chr(10) and & Environment.NewLine to no avail.
Any help would be much appreciated
Thank You