[RESOLVED] from CheckedListbox to Parameters.AddWithValue

Hi want to update a record in an access table with the values of a CheckedListbox

I use this code to update the record but don't know how to loop through the CheckedListbox and get the values of the items so I can update the record.


CheckedListBox1.Items.Add("11X", row.Item("send_11"))
CheckedListBox1.Items.Add("43X", row.Item("send_43"))
CheckedListBox1.Items.Add("13X", row.Item("send_13"))...etc


Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
'the record has to be stored in the database
Dim Command As OleDbCommand
Dim SQLstr As String

SQLstr = "UPDATE Vergelijk SET [Vergelijk Name] = @name, regexp = @regx, [Document Name] = @doc_name, send_11XSTAF = @11 WHERE OldName = @oldname"
Command = New OleDbCommand(SQLstr, con)
With Command
.CommandText = SQLstr
.Parameters.AddWithValue("@name", DbNullOrStringValue(DocName.Text))
.Parameters.AddWithValue("@regx", DbNullOrStringValue(RegeXp.Text))
.Parameters.AddWithValue("@11", ????)
.Parameters.AddWithValue("@oldname", DbNullOrStringValue(DocName.Text))
.Connection = con
.ExecuteNonQuery()
End With

End Sub

This is a path to follow with a mock-up

Extension method to get checked items

Code:

---

Public Module CheckedListBoxExtensions

    <Runtime.CompilerServices.Extension>
    Public Function CheckedStringList(sender As CheckedListBox) As List(Of String)
        Return sender.Items.Cast(Of String)().
            Where(Function(stringToken, index) sender.GetItemChecked(index)).
            Select(Function(stringToken) stringToken).Select(Function(x) x).
            ToList()
    End Function

End Module

---

Class to populate CheckedListBox for demo'ing iterating checke

Code:

---

Public Class Helpers
    Public Shared Function MonthNames() As List(Of String)
        Return Enumerable.Range(1, 12).
            Select(Function(index) DateTimeFormatInfo.CurrentInfo.GetMonthName(index)).
            ToList()
    End Function
End Class

---

Displays checked months in a ListBox

Code:

---

Public Class Form1
    Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        CheckedListBox1.DataSource = Helpers.MonthNames()

        CheckedListBox1.SetItemCheckState(
            CheckedListBox1.FindString(DateTime.Now.ToString("MMMM")),
            CheckState.Checked)

        ShowSelectedMonthNames()

    End Sub

    Private Sub SelectedMonthsButton_Click(sender As Object, e As EventArgs) _
        Handles SelectedMonthsButton.Click

        ShowSelectedMonthNames()

    End Sub

    Private Sub ShowSelectedMonthNames()

        Dim checkedMonths = CheckedListBox1.CheckedStringList()

        If checkedMonths.Count > 0 Then
            ListBox1.DataSource = checkedMonths
        Else
            ListBox1.DataSource = Nothing
        End If

    End Sub
End Class

---

I found a solution to set my datatable. There is one problem. Its updating the wrong fields with True or False

Code:

---

Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
        'the record has to be stored in the database
        Dim Command As OleDbCommand
        Dim SQLstr As String

        Dim b As Boolean

        SQLstr = "UPDATE Vergelijk SET [Vergelijk Name] = @name, regexp = @regx, send_11XSTAF = @11XSTAF, send_13XSTAF = @13XSTAF, send_43XSTAF = @43XSTAF"
        SQLstr &= ", send_OOCLHQ = @OOCLHQ, send_CLAS = @CLAS, send_DIVI = @DIVI, send_JISTARC = @JISTARC, send_KCTSTAF = @KCTSTAF, send_DGLC = @DGLC"
        SQLstr &= ", send_1GNC = @1GNC, send_EODD = @EODD, send_SVV = @SVV, send_LWC = @LWC, can_have_add_text = @add, Activated = @act"
        SQLstr &= " WHERE [Vergelijk Name] = @oldname"
        Console.WriteLine(SQLstr)
        Command = New OleDbCommand(SQLstr, con)
        With Command
            .CommandText = SQLstr
            .Parameters.AddWithValue("@name", DbNullOrStringValue(DocName.Text))
            .Parameters.AddWithValue("@regx", DbNullOrStringValue(RegeXp.Text))
            For Each item In CheckedListBox1.Items
                b = CheckStatus(item.ToString)
                .Parameters.AddWithValue("@" & item.ToString, OleDbType.Boolean).Value = b
            Next
            .Parameters.AddWithValue("@add", OleDbType.Boolean).Value = ChkBoxAddText.CheckState
            .Parameters.AddWithValue("@act", OleDbType.Boolean).Value = ChkBoxActive.CheckState
            .Parameters.AddWithValue("@oldname", DbNullOrStringValue(DocName.Text))
            .Connection = con
            .ExecuteNonQuery()
        End With

End Sub

  Private Function CheckStatus(sItem As String) As Boolean

        Dim itemChecked As Object
        CheckStatus = False

        For Each itemChecked In CheckedListBox1.CheckedItems
            If sItem = itemChecked.ToString() Then
                CheckStatus = True
                Exit For
            End If
        Next

    End Function

---

Remember, Access database doesn't care about the parameter name, it only cares about the order of the parameters. You don't even need names, you could just use "?". So, are you adding the parameters in the right order?

Thanks Wes.
So I need to change the order in the query to match the database table.

Quote:

---

Originally Posted by clausowitz

Thanks Wes.
So I need to change the order in the query to match the database table.

---

No...you need to change the order that you add the parameters to match the order in which they appear in the query. Access doesn't care about named parameters... only the order in which they appear.

-tg