CODEWITHSUNDEEP
sqlsql-servervb.net
GCA
GCA

Reputation: 1

Retrieve SelectedValue from CheckedListBox and insert into SQL Server table

I've a problem with a CheckedListBox. How can I get the SelectedValues into an update query?

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim promozione As String

    dest = "INSERT INTO dbo.dest " &
               "([ID], [Cod], [SellIn], [SellOut]) " &
               "VALUES (@ID, @Cod, @SellIn, @SellOut)"

    Dim ins As New SqlDataAdapter(dest, con)
    Dim run As New SqlCommand(dest, con)
    con.Open()

    For Each Cod In CheckedListBox1.CheckedItems()
        run.Parameters.Add(New SqlParameter("@ID", ComboBox1.SelectedValue))
        run.Parameters.Add(New SqlParameter("@Cod", CheckedListBox1.SelectedValue))
        run.Parameters.Add(New SqlParameter("@SellIn", DateTimePicker1.Text))
        run.Parameters.Add(New SqlParameter("@SellOut", DateTimePicker2.Text))
        run.ExecuteNonQuery()
    Next

    con.Close()
End Sub

When I click Button2, an error is returned:

'System.Data.SqlClient.SqlException' in System.Data.dll the variable name '@ID' already declared.

Upvotes: 0

Views: 98

Answers (2)

GCA
GCA

Reputation: 1

HOW TO INSERT INTO SQL QUERY MULTIPLE SELECTED ITEMS FROM A CHECKEDLISTBOX! After tow days this is the solution:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim dest As String

    dest = "INSERT INTO dbo.dest " &
                 "([ID], [Cod], [SellIn], [SellOut]) " &
                 "VALUES (@ID, @Cod, @SellIn, @SellOut)"

    Dim ins As New SqlDataAdapter(dest, con)
    Dim run As New SqlCommand(dest, con)



        con.Open()

        For Each Cod As DataRowView In CheckedListBox1.CheckedItems()
            run.Parameters.Clear()
            run.Parameters.Add(New SqlParameter("@ID", ComboBox1.SelectedValue))
            run.Parameters.Add(New SqlParameter("@Cod", Cod("IDCod"))) '<------ IDCods are the CheckedListBox1.ValueMembers'
            run.Parameters.Add(New SqlParameter("@SellIn", DateTimePicker1.Text))
            run.Parameters.Add(New SqlParameter("@SellOut", DateTimePicker2.Text))
            run.ExecuteNonQuery()
        Next

        con.Close()

End Sub

Upvotes: 0

Harsh
Harsh

Reputation: 1319

You should clear parameters collections for each iteration, I believe as shown below. In fact, better way would be to create these parameters outside the loop and then just set their value in each iteration.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim promozione As String

    dest = "INSERT INTO dbo.dest " &
                 "([ID], [Cod], [SellIn], [SellOut]) " &
                 "VALUES (@ID, @Cod, @SellIn, @SellOut)"

    Dim ins As New SqlDataAdapter(dest, con)
    Dim run As New SqlCommand(dest, con)



        con.Open()

        For Each Cod In CheckedListBox1.CheckedItems()
            run.Parameters.Clear()
            run.Parameters.Add(New SqlParameter("@ID", ComboBox1.SelectedValue))
            run.Parameters.Add(New SqlParameter("@Cod", CheckedListBox1.SelectedValue))
            run.Parameters.Add(New SqlParameter("@SellIn", DateTimePicker1.Text))
            run.Parameters.Add(New SqlParameter("@SellOut", DateTimePicker2.Text))
            run.ExecuteNonQuery()
        Next

        con.Close()

End Sub

Upvotes: 2

Related Questions