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.
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.Item(1).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.Item(2).Value = txtstring.ToString
Next
End If
End If
queryCommand.ExecuteNonQuery()
Next
myConn.Close
MultiView1.ActiveViewIndex = 4
Thanks in advance to everyone who even takes the time to look at this. Also, unless I wrap the @chkText and @txtText parameters in single quotes, the query will not work, and after that I obviously just put the actual values ‘@chkText’ and ‘@txtText’ in the DB rows, which is not what i want either.
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;
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
If it’s a syntax error or if im out of scope jumping into the loops than please let me know the proper syntax and scope, im fairly new to ASP and VB.Net and i’m not entirely sure what else to do i’ve been “googling” for days
Here, I mocked this up and made some changes, with comments. It should work for you, but as always, please step through it yourself, double checking my code (rusty in VB) and make sure the assertions I made in my comments are correct!
Imports System.Data.SqlClient
Imports System.Linq
Public Class _Default
Inherits System.Web.UI.Page
' first: make your connection class level
Private myConn As SqlConnection
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' second: initialize your connection here
myConn = New SqlConnection("Data Source=W0121968;Initial Catalog=ResInquiryDev;Integrated Security=True;Pooling=False")
myConn.Open()
End Sub
Private Sub Page_Unload(sender As Object, e As System.EventArgs) Handles Me.Unload
' third: terminate you connection here
myConn.Close()
myConn.Dispose()
End Sub
Private Sub Commit_Transaction(ByVal id As Integer, ByVal checked As Boolean, ByVal text As String)
' fourth: isolate the transaction commit
Dim queryCommand As SqlCommand = myConn.CreateCommand()
queryCommand.CommandText = "INSERT into tblReferral(RefInquiryID, RefSource, RefSourceText)VALUES(@InquiryID, @chkText, @txtText)"
queryCommand.Parameters.AddWithValue("@InquiryID", id)
queryCommand.Parameters.AddWithValue("@chkText", checked)
queryCommand.Parameters.AddWithValue("@txtText", text)
queryCommand.ExecuteNonQuery()
End Sub
Private Function GetValueFromCheckBox(ByVal ctrl As CheckBox)
' fifth: write an helper method to cast a control and get the checked state all in one shot
Return ctrl.Checked
End Function
Private Function GetValueFromTextBox(ByVal ctrl As TextBox)
' sixth: write an helper method to cast a control and get the text value all in one shot
Return ctrl.Text
End Function
Protected Sub Button7_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button7.Click
' not sure what these do, are they relavent?
InsertPatientInfo.Insert()
InsInqInfo.Insert()
InsOtherInfo.Insert()
InsClinInfo.Insert()
InsInquiryInfo.Insert()
' your loop was failing because you were handling each ctrl
' one at a time, when in reality they should be handled in pairs
' since the controls are in pairs, we take two at a time
' I am assuming the check box is first, then the text box
' you should step through this and double check correctness
' I am also assuming text box is there, but not visible at times
For index As Integer = 0 To Panel7.Controls.Count - 1 Step 2
Dim id As Integer = Convert.ToInt32(Session("NewInquiryID"))
Dim checked As Boolean = GetValueFromCheckBox(Panel7.Controls(index))
Dim text As String = GetValueFromTextBox(Panel7.Controls(index + 1))
Commit_Transaction(id, checked, text)
Next
' not sure what this does, is it relavent?
MultiView1.ActiveViewIndex = 4
End Sub
End Class
Thank you very much for your help on this; I have gone through trying to implement your code, and have set everything up as you stated. I was trying to think of what to write for the functions but I am really unsure, I am not very advanced in functions or VB/ASP.Net for that matter. I think what is confusing me, is that aren’t we sending the value of “index” to the function, which is going to be a numeric value depending on the step?
If that is the case, how exactly do I bind that to the corresponding control in the panel? I also changed it to Panel8, just as a heads up. Is there an Index property for the submitted areas of the panel? If this is too vague please just let me know, i’m not sure how else to phrase it.
Thank you for all of your help,
NickG
You should just be able to change Panel7 to Panel8 in my example code. You don’t need to pass a control index into Commit_Transaction method. You handle that when passing in an indexed control to the GetValueFromX methods.