Parameters.Add in For Each Loop VB

[nickg21]

Hey everyone, my issue is a little strange, I have a form that has checkboxes, and depending on the selection, a textbox might appear for further info. I want to loop through the controls that have been selected, if it's only a checkbox than just add the Value of the checkbox, if there is also a textbox associated, add the text to the database as well. I keep getting the error that @chkText either has already been set, or that i have to declare the scalar variable @chkText. I dont know if this is even possible or if my approach is completely wrong, but any help is appreciated.

Also, I dont know if this will help, but when I comment out all of the database stuff, my message boxes that i have set up for testing work perfectly. They iterate through and they pop-up the Checkbox value, followed by corresponding textbox values in the exact right order;
Here is the code with the SQL stuff commented out;

Protected Sub Button7_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button7.Click
        InsertPatientInfo.Insert()
        InsInqInfo.Insert()
        InsOtherInfo.Insert()
        InsClinInfo.Insert()
        InsInquiryInfo.Insert()

        'Dim myConn As New SqlConnection("Data Source=W0121968;Initial Catalog=ResInquiryDev;Integrated Security=True;Pooling=False")
        'Dim queryCommand As SqlCommand = myConn.CreateCommand()
        'myConn.Open()
        'queryCommand.CommandText = "INSERT into tblReferral(RefInquiryID, RefSource, RefSourceText)VALUES(@InquiryID, @chkText, @txtText)"
        'queryCommand.Parameters.Add("@InquiryID", SqlDbType.Int)
        'queryCommand.Parameters("@InquiryID").Value = Convert.ToInt32(Session("NewInquiryID"))

            For Each ctrl As Control In Panel7.Controls
                If TypeOf ctrl Is CheckBox Then
                    If CType(ctrl, CheckBox).Checked = True Then
                        For i = 0 To ctrl.Controls.Count
                            Dim str As String
                            str = DirectCast(ctrl, CheckBox).Text
                            MsgBox(str, MsgBoxStyle.Critical)
                        'queryCommand.Parameters.Add("@chkText", SqlDbType.VarChar)
                        'queryCommand.Parameters("@chkText").Value = str.ToString
                        Next
                    End If
                End If
                If TypeOf ctrl Is TextBox Then
                    If CType(ctrl, TextBox).Text <> "" Then
                        For i = 0 To ctrl.Controls.Count
                            Dim txtstring As String
                            txtstring = DirectCast(ctrl, TextBox).Text
                            MsgBox(txtstring, MsgBoxStyle.Critical)
                        'queryCommand.Parameters.Add("@txtText", SqlDbType.VarChar)
                        'queryCommand.Parameters("@txtText").Value = txtstring.ToString
                        Next
                    End If
                End If
            'queryCommand.ExecuteNonQuery()
            Next
        'myConn.Close()
            MultiView1.ActiveViewIndex = 4
    End Sub

[Antenka]

Hello,
your code seems to be ok. But, one thing that I suspect .. what is the value of "ctrl.Controls.Count" when you get the

error that @chkText either has already been set

? Try to debug .. what conditions are, when your prog is trying to add this parameter more than once.

[DcFreefall]

I was doing the same thing and could not find a solution until I found this. Works great. Solved my problem in a few minutes.

donmateo12-28-2007, 10:53 PM

I figured it out!

Have to create and initialize the parameters outside the loop and assign them values inside the loop like so:

cnt = request.form("cnt")

set cmd = server.CreateObject("adodb.command")

cmd.ActiveConnection = getconn()
cmd.CommandType = adcmdStoredProc
cmd.CommandText = "spDISAUpdateSponsorStatus"

cmd.Parameters.Append cmd.CreateParameter("idpeople",adInteger,adParamInput,,0)
cmd.Parameters.Append cmd.CreateParameter("v50m",adVarChar,adParamInput,50,"")

for i = 1 to cnt
idpeople = request.Form("idpeople" & i)
v50m = request.Form("v50m" & i)

cmd.Parameters("idpeople") = idpeople
cmd.Parameters("v50m") = v50m
cmd.Execute

idpeople = ""
v50m = ""
next

cmd.ActiveConnection.Close
set cmd = nothing