4 Replies - 4726 Views - Last Post: 14 June 2012 - 03:48 AM Rate Topic: -----

#1 purvabhilare1587  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-May 12

transfer items selected in listbox to access database

Posted 12 June 2012 - 10:30 PM

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




Is This A Good Question/Topic? 0
  • +

Replies To: transfer items selected in listbox to access database

#2 zeeshanef  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 76
  • Joined: 14-April 12

Re: transfer items selected in listbox to access database

Posted 13 June 2012 - 01:25 AM

Do you want to store "LanguagesKnownListBox" selected values to "LanguagesKnown" column with comma separated?
Was This Post Helpful? 0
  • +
  • -

#3 purvabhilare1587  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-May 12

Re: transfer items selected in listbox to access database

Posted 13 June 2012 - 03:14 AM

View Postzeeshanef, on 13 June 2012 - 01:25 AM, said:

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



YES.
Was This Post Helpful? 0
  • +
  • -

#4 zeeshanef  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 76
  • Joined: 14-April 12

Re: transfer items selected in listbox to access database

Posted 13 June 2012 - 04:13 AM

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

Was This Post Helpful? 0
  • +
  • -

#5 purvabhilare1587  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-May 12

Re: transfer items selected in listbox to access database

Posted 14 June 2012 - 03:48 AM

View Postzeeshanef, 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1