ListBox items displayed on ONE line Error.

[VBeric]

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

 

[jebenson]

Just having a line break is not enough to make the item appear as two items in the listbox. You need to split the field on the line break, then add each item to the listbox individually.

Dim sItems() As String 'string array to hold items

Dim sFieldData As String

sFieldData = tempRow("RequirementsList")

sItems = sFieldData.Split(vbCrLF)

For Each s As String in sItems
ListBox2.Items.Add(s)
Next


I would use something other than vbCrLf as the delimiter between items in the database, perhaps a character like "|" or "~".

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[VBeric]

Thank you jebenson your coding has fixed my problem.
I had tried using "Split" but being unfamiliar with it I was getting nowhere.
Now you have shown me the correct way I am up and running again.
Many Thanks
VBeric