Looping Through Checkbox List and adding selected items to MS SQL Database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Looping Through Checkbox List and adding selected items to MS SQL Database

  1. #1
    Join Date
    May 2009
    Posts
    8

    Looping Through Checkbox List and adding selected items to MS SQL Database

    Hi All,

    Not sure if anyone out there can help but I've been trying to get my code to work for days and days. I'm an old VB 6 user and am very rusty and trying to learn the .NET and am using Visual Studio and VB 2008 with MS SQL 2000.

    My project goal is to loop through a check box list and take the selected values and add them to a database using parameters.

    The table that I would like to write the selected items to only contains the the ID of the display field which works fine and also, I have the values go to a message box one at a time and that worked fine too.

    The problem is that I can loop once and the data writes to the table but when it goes around again, I get the following error:

    The variable name '@CourtID' has already been declared. Variable names must be unique within a query batch or stored procedure.

    My code is below: Any thoughts or help would be very much appreciated.


    Dim connectionString As String = "Data Source=TestServer;Initial Catalog=OutcomeMeasures;Persist Security Info=True;User ID=test;Password=test1234"

    Dim Outcomes As New SqlClient.SqlConnection(connectionString)
    Outcomes.Open()

    'create the command
    Dim theCommand As New SqlClient.SqlCommand()



    'loop through checkbox list to see what was chosen and find the CourtID

    Dim tmp As String = ""
    Dim MyItem As ListItem



    For Each MyItem In cblCourts.Items
    If MyItem.Selected = True Then
    tmp = MyItem.Value
    theCommand.CommandText = "INSERT INTO CourtResults(CourtID) VALUES (@CourtID)"
    theCommand.Connection = Outcomes

    'message box for testing
    MsgBox(tmp)

    'perform update to database
    theCommand.Parameters.AddWithValue("@CourtID", tmp)
    theCommand.ExecuteNonQuery()

    End If
    Next
    Outcomes.close

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Try something like this.
    Code:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim i As Integer
            For i = 0 To cblCourts.Items.Count
                If MyItem.Selected = True Then
                    tmp = MyItem.Value
                    theCommand.CommandText = "INSERT INTO CourtResults(CourtID) VALUES (@CourtID_" & i & ") "
                    theCommand.Connection = Outcomes
    
                    'message box for testing
                    MsgBox(tmp)
    
                    'perform update to database
                    theCommand.Parameters.AddWithValue("@CourtID_" & i & ", tmp)
                    theCommand.ExecuteNonQuery()
                End If
            Next
    End Sub
    NOTE: This is untested but the theory is sound.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    May 2009
    Posts
    8

    Looping through Check box list

    Hi Hack,

    Thank you so much for your quick response! I tried that code and am still getting an error but it could be that I have not declared the parameter.???
    Anyway, when I run it, I get the following

    Error 1
    Argument not specified for parameter 'value' of 'Public Function AddWithValue(parameterName As String, value As Object) As System.Data.SqlClient.SqlParameter'

    Error 1 refers to the line of code:
    theCommand.Parameters.AddWithValue("@CourtID_" & i & ", tmp)

    any light you can shed on this would be most apprecaited

    and

    Error 2 String constants must end with a double quote.

    Error two refers to the line of code:

    theCommand.Parameters.AddWithValue("@CourtID_" & i & ", tmp)

  4. #4
    Join Date
    May 2009
    Posts
    8
    Hi Again Hack,

    I figured it out. On my original code, i just added
    theCommand.Parameters.AddWithValue("@CourtID", tmp)
    theCommand.ExecuteNonQuery()
    theCommand.Parameters.Clear()

    The a clear for the parameter and now it loops great.

    Thanks though for all your help!

Similar Threads

  1. Adding checkbox and textbox to gridview
    By luvdairish in forum ASP.NET
    Replies: 0
    Last Post: 01-04-2007, 10:36 AM
  2. adding items to multiple column list box
    By br3athe in forum VB Classic
    Replies: 1
    Last Post: 05-14-2006, 02:43 PM
  3. adding dupe entries within a SQL query
    By Mike B in forum Database
    Replies: 1
    Last Post: 07-03-2003, 11:56 AM
  4. Adding a new item to the list box
    By krallabandi in forum Web
    Replies: 0
    Last Post: 09-03-2001, 11:49 PM
  5. Adding Items to a SELECT list
    By Brent in forum Web
    Replies: 1
    Last Post: 09-17-2000, 08:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center