How to save multiple value of listbox

[judd]

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

[Reverend Jim]

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?

[judd]

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.

[Reverend Jim]

Can we please see your code? Please include all pertinent database info.

[judd]

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

[judd]

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

[judd]

    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).

[Reverend Jim]

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.

[judd]

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

[judd]

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

[Reverend Jim]

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()

[judd]

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

[Reverend Jim]

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.

[Nicholasamarq]

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.