[purvabhilare1587]

I want to transfer multiple items selected from a listbox to an access 2007 database using a parameterized query.
When I select certain items from the listbox, only first item selected gets stored in the database.Other values selected are not written to the database.My code is as follows.Is my approach towards transferring multiple values correct?Someone
help please.

Private Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click

 Dim arr As New List(Of String)
 Dim cmdText As String = "INSERT INTO Infotable (LastName, FirstName, MiddleName, Address,PhoneNo, AlternativeNumber, LanguagesKnown)" & "Values(?,?,?,?,?,?,?)"
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con)
        cmd.CommandType = CommandType.Text


        With cmd.Parameters
            .Add("@LastName", OleDb.OleDbType.VarChar).Value = LastNameTextBox.Text
            .Add("@FirstName", OleDb.OleDbType.VarChar).Value = FirstNameTextBox.Text
            .Add("@MiddleName", OleDb.OleDbType.VarChar).Value = MiddleNameTextBox.Text
            .Add("@Address", OleDb.OleDbType.VarChar).Value = AddressRichTextBox.Text
            .Add("@PhoneNo", OleDb.OleDbType.Numeric).Value = PhoneNoTextBox.Text
            .Add("@AlternativeNumber", OleDb.OleDbType.Numeric).Value = AlternativePhoneNoTextBox.Text
            For i = 0 To LanguagesKnownListBox.Items.Count

                arr.Add(LanguagesKnownListBox.SelectedItem)

                .Add("@LanguagesKnown", OleDb.OleDbType.VarChar).Value = arr(i)

            Next
           End With
        cmd.ExecuteNonQuery()
        MsgBox("New record added to the database")
 End Sub

[zeeshanef]

Do you want to store "LanguagesKnownListBox" selected values to "LanguagesKnown" column with comma separated?

[purvabhilare1587]

zeeshanef, on 13 June 2012 - 01:25 AM, said:

Do you want to store "LanguagesKnownListBox" selected values to "LanguagesKnown" column with comma separated?

YES.

[zeeshanef]

First of all loop through selectedItems of "LanguagesKnownListBox" to store all selected values in string variable, but not in string list.

        Dim SelectedLang As String = ""
        Dim UseComma As String = ""

        For i As Integer = 0 To LanguagesKnownListBox.SelectedItems.Count - 1

            If Not (i = LanguagesKnownListBox.SelectedItems.Count - 1) Then 'this will not add comma in last item or only one
                UseComma = ", "
            Else
                UseComma = ""
            End If

            SelectedLang = SelectedLang + LanguagesKnownListBox.SelectedItems(i) + UseComma
        Next

Now you can add "SelectedLang" value to the @LanguagesKnown parameter, like this:

.Add("@LanguagesKnown", OleDb.OleDbType.VarChar).Value = SelectedLang

[purvabhilare1587]

zeeshanef, on 13 June 2012 - 04:13 AM, said:

First of all loop through selectedItems of "LanguagesKnownListBox" to store all selected values in string variable, but not in string list.

        Dim SelectedLang As String = ""
        Dim UseComma As String = ""

        For i As Integer = 0 To LanguagesKnownListBox.SelectedItems.Count - 1

            If Not (i = LanguagesKnownListBox.SelectedItems.Count - 1) Then 'this will not add comma in last item or only one
                UseComma = ", "
            Else
                UseComma = ""
            End If

            SelectedLang = SelectedLang + LanguagesKnownListBox.SelectedItems(i) + UseComma
        Next

Now you can add "SelectedLang" value to the @LanguagesKnown parameter, like this:

.Add("@LanguagesKnown", OleDb.OleDbType.VarChar).Value = SelectedLang

Hey thanks.It worked.