Hai Experts,

I am having difficulty in saving/storing multiple values in a listbox. Briefly, I have a combobox to choose from, where the value selected will be added to a listbox. From the listbox(with multiple entries),I wanted to save it to a field in my SQL DB. I've been stuck for 2 days looking for solution,but nothing is working. Most of the example I found are Listbox items to save to txt files, or the SelectedItem methods. As for my issues, I don't have to select from the listbox,but to save/store the values in the listbox.
Thank you All.

Judd

I don't have to select from the listbox,but to save/store the values in the listbox.

If I understand, you select a value from a combobox and you want it added to a listbox. As well, you are doing soomething with a SQL database. Are you having problems populating the listbox or are you having problems saving listbox data to the database?

Hai Jim,
Thanks for your reply. Yes, I need to choose a few items from a combo box, then the selected items is added to a listbox. I managed to add the items selected from combobox but,Yes,I have problem saving the listbox(with SQL DB). It comes out empty.

My codes are as below. This code is working fine for all fields EXCEPT for the Work_ScopeListBox(supposed to be saved in WorkScope field in table JobDetails_Trans)

Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
Public Class frmVesselDetailTrans
    Public connstr As String = "Data Source=lse-fn\inforln61;Integrated Security=SSPI;Initial Catalog=SRDB;"
    Dim connect_to_db As New SqlConnection(connstr)


    Private Sub VesselDetailTrans_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WorkScope_SetupTableAdapter.Fill(Me.SRDBDataSet.WorkScope_Setup)

        Try
            Dim db As New SqlDataAdapter("select WS_Description from WorkScope_Setup", connect_to_db)
            Dim dtb As New DataTable
            db.Fill(dtb)
            WS_CodeComboBox.DataSource = dtb
            WS_CodeComboBox.DisplayMember = "WS_Description"
            WS_CodeComboBox.ResetText()

            Dim db1 As New SqlDataAdapter("select VesselType_Desc from VesselType_Setup", connect_to_db)
            Dim dtb1 As New DataTable
            db1.Fill(dtb1)
            VesselType_CodeComboBox.DataSource = dtb1
            VesselType_CodeComboBox.DisplayMember = "VesselType_Desc"
            VesselType_CodeComboBox.ResetText()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Work_ScopeListBox.Items.Clear()

        autonumber()
        btnAdd.Enabled = False
        btnDel.Enabled = True
    End Sub

    Public Sub autonumber()
        Dim myCommand As New SqlCommand
        Dim myAdapter As New SqlDataAdapter
        Dim mydatatable As New DataTable
        Dim dr As SqlDataReader
        Dim strsql As String
        Dim a As Integer

        connect_to_db.Open()
        strsql = "select * from JobDetails_Trans"
        myCommand = New SqlCommand(strsql, connect_to_db)
        dr = myCommand.ExecuteReader
        While dr.Read
            a = dr(0)
        End While
        Job_IDTextBox.Text = a + 1
        connect_to_db.Close()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim sql As New SqlCommand
        Dim SQL2 As New SqlCommand
        'Dim cmd As New SqlCommand

        Try
            connect_to_db.Open()

            Dim a As String
            a = "INSERT INTO VesselDetails_Trans(Job_NO, Vessel_Name,Captain, VesselType_Code, Draft, Length, Width, GRT, Remarks, ActDepartDate, ActUndockDate, ActDockDate, ActArrivalDate, PlanDepartDate, PlanUndockDate, PlanDockDate, PlanArrivalDate, Total_Duration, Dock_Duration) VALUES(@Job_NO, @Vessel_Name, @Captain, @VesselType_Code, @Draft, @Length, @Width, @GRT, @Remarks, @ActDepartDate, @ActUndockDate, @ActDockDate, @ActArrivalDate, @PlanDepartDate, @PlanUndockDate, @PlanDockDate, @PlanArrivalDate, @Total_Duration, @Dock_Duration)"

            sql = New SqlCommand(a, connect_to_db)
            sql.Parameters.AddWithValue("@Job_NO", Job_NOTextBox.Text)
            sql.Parameters.AddWithValue("@Vessel_Name", Vessel_NameTextBox.Text)
            sql.Parameters.AddWithValue("@Captain", CaptainTextBox.Text)
            sql.Parameters.AddWithValue("@VesselType_Code", VesselType_CodeComboBox.Text)
            sql.Parameters.AddWithValue("@Draft", DraftTextBox.Text)
            sql.Parameters.AddWithValue("@Length", LengthTextBox.Text)
            sql.Parameters.AddWithValue("@Width", WidthTextBox.Text)
            sql.Parameters.AddWithValue("@GRT", GRTTextBox.Text)
            sql.Parameters.AddWithValue("@Dock_Duration", Dock_DurationTextBox.Text)
            sql.Parameters.AddWithValue("@Total_Duration", Total_DurationTextBox.Text)
            sql.Parameters.AddWithValue("@PlanArrivalDate", PlanArrivalDateDateTimePicker.Value)
            sql.Parameters.AddWithValue("@PlanDockDate", PlanDockDateDateTimePicker.Value)
            sql.Parameters.AddWithValue("@PlanUndockDate", PlanUndockDateDateTimePicker.Value)
            sql.Parameters.AddWithValue("@PlanDepartDate", PlanDepartDateDateTimePicker.Value)
            sql.Parameters.AddWithValue("@ActArrivalDate", ActArrivalDateDateTimePicker.Value)
            sql.Parameters.AddWithValue("@ActDockDate", ActDockDateDateTimePicker.Value)
            sql.Parameters.AddWithValue("@ActUndockDate", ActUndockDateDateTimePicker.Value)
            sql.Parameters.AddWithValue("@ActDepartDate", ActDepartDateDateTimePicker.Value)
            sql.Parameters.AddWithValue("@Remarks", RemarksTextBox.Text)
            sql.Parameters.AddWithValue("@createdDate", DateTime.Now)
            sql.Parameters.AddWithValue("@modifiedDate", DateAndTime.Now)
            'sql.Parameters.AddWithValue("@modifiedDate", DateTime.Parse(ActArrivalDateDateTimePicker.Text))
            sql.ExecuteNonQuery()

            Dim b As String

            b = "INSERT INTO JobDetails_Trans(Job_ID, Job_NO, Upslip_Date, Downslip_Date, Coordinator, WorkScope, Location,Subcontractor, WorkDateFrom, WorkDateTo, Remarks) VALUES(@Job_ID, @Job_NO, @Upslip_Date, @Downslip_Date, @Coordinator, @WorkScope, @Location, @Subcontractor, @WorkDateFrom, @WorkDateTo, @Remarks)"

            SQL2 = New SqlCommand(b, connect_to_db)
            SQL2.Parameters.AddWithValue("@Job_ID", Job_IDTextBox.Text)
            SQL2.Parameters.AddWithValue("@Job_NO", Job_NOTextBox1.Text)
            SQL2.Parameters.AddWithValue("@Upslip_Date", Upslip_DateDateTimePicker.Value)
            SQL2.Parameters.AddWithValue("@Downslip_Date", Downslip_DateDateTimePicker.Value)
            SQL2.Parameters.AddWithValue("@Coordinator", CoordinatorTextBox.Text)
            SQL2.Parameters.AddWithValue("@WorkScope", Work_ScopeListBox.Text)
            SQL2.Parameters.AddWithValue("@Location", LocationTextBox.Text)
            SQL2.Parameters.AddWithValue("@Subcontractor", SubcontractorTextBox.Text)
            SQL2.Parameters.AddWithValue("@WorkDateFrom", WorkDateFromDateTimePicker.Value)
            SQL2.Parameters.AddWithValue("@WorkDateTo", WorkDateToDateTimePicker.Value)
            SQL2.Parameters.AddWithValue("@Remarks", RemarksTextBox.Text)
            SQL2.ExecuteNonQuery()

              MsgBox("Record saved!!")

            Job_NOTextBox.Text = ""
            Vessel_NameTextBox.Text = ""
            CaptainTextBox.Text = ""
            VesselType_CodeComboBox.Text = ""
            DraftTextBox.Text = ""
            LengthTextBox.Text = ""
            WidthTextBox.Text = ""
            GRTTextBox.Text = ""
            RemarksTextBox.Text = ""
            Job_NOTextBox1.Text = ""
            Upslip_DateDateTimePicker.Text = ""
            Downslip_DateDateTimePicker.Text = ""
            CoordinatorTextBox.Text = ""
            WS_CodeComboBox.Text = ""
            LocationTextBox.Text = ""
            RemarksTextBox.Text = ""
            SubcontractorTextBox.Text = ""
            WorkDateFromDateTimePicker.Text = ""
            WorkDateToDateTimePicker.Text = ""

        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

        connect_to_db.Close()

    End Sub


    Private Sub WS_CodeComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WS_CodeComboBox.SelectedIndexChanged
        Work_ScopeListBox.Items.Add(WS_CodeComboBox.Text)
    End Sub

    Private Sub Work_ScopeListBox_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Work_ScopeListBox.Click
        Work_ScopeListBox.Items.Clear()
    End Sub
End Class
Or Jim, could you give an example of checkbox? I have tried to create the WorkScope as checkbox(as WS1 as field1, WS2 as field2, WS3 as field3 in the JobDetails_Trans table), when the the it is checked, it will store the text value. But The below codes are saving all the text value eventhough I didn't check it.(* the codes below is having the same codes as above except this one I paste in the Save_Button. I had created a new table to try out the checkbox). Thanks.

SQL2.CommandText = "INSERT INTO **WorkScope_Trans**(Job_ID, Job_NO, Upslip_Date, Downslip_Date, Coordinator, SS_WS, BP_WS, ME_WS, EL_WS, PP_WS, OR_WS, Location,Subcontractor, WorkDateFrom, WorkDateTo, Remarks) VALUES('" & Job_IDTextBox.Text & "', '" & Job_NOTextBox1.Text & "', '" & Upslip_DateDateTimePicker.Value.Date & "', '" & Downslip_DateDateTimePicker.Value.Date & "', '" _
                              & CoordinatorTextBox.Text & "', '" & SS_WSCheckBox.Text & "', '" & BP_WSCheckBox.Text & "', '" & ME_WSCheckBox.Text & "', '" & EL_WSCheckBox.Text & "', '" & PP_WSCheckBox.Text & "', '" & OthersTxt.Text & "', '" & LocationTextBox.Text & "', '" & SubcontractorTextBox.Text & "', '" & WorkDateFromDateTimePicker.Value.Date & "', '" & WorkDateToDateTimePicker.Value.Date & "', '" & RemarksTextBox.Text & "')"

Dim sb As New System.Text.StringBuilder
            For Each SS_WSCheckBox As CheckBox In GroupBox2.Controls.OfType(Of CheckBox)()
                If SS_WSCheckBox.Checked Then
                    sb.Append(SS_WSCheckBox.Text)
                End If
            Next
            Dim sb1 As New System.Text.StringBuilder
            For Each BP_WSCheckBox As CheckBox In GroupBox2.Controls.OfType(Of CheckBox)()
                If BP_WSCheckBox.Checked Then
                    sb1.Append(BP_WSCheckBox.Text)
                End If
            Next
            Dim sb2 As New System.Text.StringBuilder
            For Each ME_WSCheckBox As CheckBox In GroupBox2.Controls.OfType(Of CheckBox)()
                If ME_WSCheckBox.Checked Then
                    sb2.Append(ME_WSCheckBox.Text)
                End If
            Next
    I had added this code but still not working.

    For i As Integer = 0 To Work_ScopeListBox.Items.Count - 1
                    Dim sqlcmd As String = "INSERT INTO JobDetails_Trans (WorkScope) values (@WorkScope)"
                    Dim cmd As New SqlClient.SqlCommand(sqlcmd, connect_to_db)
                    cmd.Parameters.AddWithValue("@WorkScope", Work_ScopeListBox.Items(i).ToString)
                    cmd.ExecuteNonQuery()
                Next

    FYI, the field WorkScope in JobDetails_Trans Table is of Data Type varchar(MAX).

In order to insert all of the text in the listbox you must first convert the text from a collection of strings to one string. You can do that as follows

   Dim s As String = ""

    For Each item As String In ListBox1.Items
        s &= item & vbCrLf
    Next

    MsgBox(s)

You can use s (or another appropriate variable name) to add the text to the insert query.

hai Jim,
I had done it these 2 ways,but still not working.Still comes out as Null. ARGGHH!!!

        (1)Dim s As String = ""
            For Each item As String In Work_ScopeListBox.Items
                s &= item & vbCrLf
                Dim sqlcmd As String = "INSERT INTO JobDetails_Trans (WorkScope) values (@WorkScope)"
                Dim cmd As New SqlClient.SqlCommand(sqlcmd, connect_to_db)
                cmd.Parameters.AddWithValue("@WorkScope", s.ToString)
                cmd.ExecuteNonQuery()
            Next
        *************************************** 
        (2)Dim s As String = ""
            For Each item As String In Work_ScopeListBox.Items
                s &= item & vbCrLf
                'Dim sqlcmd As String = "INSERT INTO JobDetails_Trans (WorkScope) values (@WorkScope)"
                Dim cmd As New SqlCommand
                cmd.Connection = connect_to_db
                cmd.CommandText = "Insert into JobDetails_Trans (WorkScope) values('" & s.ToString & "')"
                cmd.ExecuteNonQuery()
            Next

I think I have to go woth plan B then. I can't waste time doing this ListBox thing anymore..

Try

Dim s As String = ""

For Each item As String In ListBox1.Items
    s &= item & vbCrLf
Next

Dim sqlcmd As String = "INSERT INTO JobDetails_Trans (WorkScope) values (@WorkScope)"
Dim cmd As New SqlClient.SqlCommand(sqlcmd, connect_to_db)
cmd.Parameters.AddWithValue("@WorkScope", s)
cmd.ExecuteNonQuery()

Hi Jim,
Is it possible if I declare a field as char in SQL DB, but in VB.Net I display as checkbox? My intention is if user check it, then the Text will be saved in the DB.thanks

Other than by putting code in the CheckedChanged event to toggle the database value I can't say. I have little expertise in data bound controls so I can't be certain on this.

Its upto your design of database. You can store the data in multiple ways.
One way , you can put the multiple rows according to the selection.
Or you can also save it using some separator like comma in a single row.

This article has been dead for over six months. Start a new discussion instead.