NCSO
asked on
select count statement using form field as parameter
Hi Experts,
I have a form that is assigned a unique number (ARN) when the form loads, I need to use this number in a select count (*) as OffenseNumber where ARN = ARN.text (on the form)
How can I make the parameters for the query be the ARN.text value? Below are some of my thoughts and attempts.
Dim conn As New System.Data.SqlClient.SqlC onnection( "Persist Security Info=True;Server=xxx.xxx.x xx.xxx;Dat abase=Data base Name;uid=uid;network=dbmss ocn;passwo rd=passwor d")
Dim da As New System.Data.SqlClient.SqlD ataAdapter ("Select * from tblOffense", conn)
Dim ds As New System.Data.DataSet
Offense_Number.Text = System.Data.SqlClient.SqlD ataAdapter ("Select count (*) as offensecount from tbloffense where ARN = '" & ARN.Text & "';")
Additionally, when the select count statement runs, I need to add 1 to it. So if the query return a count of (2) for ARN 7777777777, then Offense_Number.text would be 3
If this makes any sense, please help!
Please be specific with your answers, I am a self taught newbie.
Thanks
I have a form that is assigned a unique number (ARN) when the form loads, I need to use this number in a select count (*) as OffenseNumber where ARN = ARN.text (on the form)
How can I make the parameters for the query be the ARN.text value? Below are some of my thoughts and attempts.
Dim conn As New System.Data.SqlClient.SqlC
Dim da As New System.Data.SqlClient.SqlD
Dim ds As New System.Data.DataSet
Offense_Number.Text = System.Data.SqlClient.SqlD
Additionally, when the select count statement runs, I need to add 1 to it. So if the query return a count of (2) for ARN 7777777777, then Offense_Number.text would be 3
If this makes any sense, please help!
Please be specific with your answers, I am a self taught newbie.
Thanks
Offense_Number.Text = System.Data.SqlClient.SqlD ataAdapter ("Select 1 + count (*) as offensecount from tbloffense where ARN = '" & ARN.Text & "';")
ASKER
When I use the System.Data.SqlClient.SqlD ataAdapter , I get the following error:
'SqlDataAdapter' is a type in 'SqlClient' and can not be used as an expression
Additionally, how does that statement know which connection to use for the count statement
'SqlDataAdapter' is a type in 'SqlClient' and can not be used as an expression
Additionally, how does that statement know which connection to use for the count statement
ASKER
I added the conn2 staement to address which connection to use
Offense_Number.Text = System.Data.SqlClient.SqlD ataAdapter (("Select 1 + count (*) as offensecount from tbloffense where ARN = '" & ARN.Text & "';"), conn2)
Offense_Number.Text = System.Data.SqlClient.SqlD
ASKER
I have tried the following as well with no luck:
Dim test As New System.Data.SqlClient.SqlD ataAdapter (("Select 1 + count (*) as offensecount from tbloffense where ARN = '" & ARN.Text & "';"), conn2)
offense_number.text = test
NO LUCK! There is something I am missing here.
Dim test As New System.Data.SqlClient.SqlD
offense_number.text = test
NO LUCK! There is something I am missing here.
If you're only retrieving a single value then you are better off using ExecuteScalar on a command object:
Dim cmd As New System.Data.SqlClient.SqlC ommand("SE LECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & ARN.Text & "'", conn2)
Offense_Number.Text = CType(cmd.ExecuteScalar(), String)
Dim cmd As New System.Data.SqlClient.SqlC
Offense_Number.Text = CType(cmd.ExecuteScalar(),
ASKER
We are getting there; however, the parameter is not working, I currently have the following:
Dim cmd3 As New System.Data.SqlClient.SqlC ommand("SE LECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & ARN.Text & "'", conn2)
this statement always returns a value of "1" even though there are multiple records for this ARN)
If I change it to
Dim cmd3 As New System.Data.SqlClient.SqlC ommand("SE LECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='7777777777'", conn2)
it matches the ARN "7777777777" and displays the appropriate counts
Any ideas?
Dim cmd3 As New System.Data.SqlClient.SqlC
this statement always returns a value of "1" even though there are multiple records for this ARN)
If I change it to
Dim cmd3 As New System.Data.SqlClient.SqlC
it matches the ARN "7777777777" and displays the appropriate counts
Any ideas?
Time to interogate what is going on. Change your code to:
Dim sSql As String = "SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & ARN.Text & "'"
MessageBox.Show(sSql)
Dim cmd3 As New System.Data.SqlClient.SqlC ommand(sSq l, conn2)
This will show what the SQL query contains and should show any problems with the value coming from ARN.Text.
Dim sSql As String = "SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & ARN.Text & "'"
MessageBox.Show(sSql)
Dim cmd3 As New System.Data.SqlClient.SqlC
This will show what the SQL query contains and should show any problems with the value coming from ARN.Text.
ASKER
The messagebox shows: SELECT 1 + Count(*) AS offensecount from tbloffense Where ARN='7777777777'
Here is the code for the page
Private Sub AddOffenses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ARN.Text = ARN.Text 'This loads the ARN assigned from the previous form
Incident_ID.Text = Incident_ID.Text 'This loads the Incident_ID from the previous form
OffenseID.Text = "O-" & CStr("T588") & "-" & Month(Now) & Year(Now) & "-" & Minute(Now) & Second(Now)
Dim conn2 As New System.Data.SqlClient.SqlC onnection( "Persist Security Info=True;Server=xxx.xxx.x xx.xxx;Dat abase=Repo rting;uid= uid;networ k=dbmssocn ;password= password")
Dim da2 As New System.Data.SqlClient.SqlD ataAdapter ("Select * from tblOffense", conn2)
Dim ds2 As New System.Data.DataSet
Dim cmd3 As New System.Data.SqlClient.SqlC ommand("SE LECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & ARN.Text & "'", conn2)
Try
conn2.Open()
Offense_Number.Text = CType(cmd3.ExecuteScalar() , String)
Catch ex As Exception
MessageBox.Show(ex.Message )
Finally
conn2.Close()
End Try
End Sub
Private Sub SubmitOffense_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitOffense.Click
Dim conn As New System.Data.SqlClient.SqlC onnection( "Persist Security Info=True;Server=xxx.xxx.x xx.xxx;Dat abase=Repo rting;uid= uid;networ k=dbmssocn ;password= password")
Dim da As New System.Data.SqlClient.SqlD ataAdapter ("Select * from tblOffense", conn)
Dim ds As New System.Data.DataSet
Dim cmd As New SqlCommand("insert into tbloffense(ARN,Incident_ID ,Offense_N umber,Offe nseID,Offe nseType,Of fenseDesc, Attempted_ commited,s tatuteViol ation,ncic _ucr_code, timestampa dded)value s(@ARN,@In cident_ID, @Offense_N umber,@Off enseID,@Of fenseType, @OffenseDe sc,@Attemp ted_commit ed,@statut eViolation ,@ncic_ucr _code,@tim estampadde d)", conn)
Dim cmd2 As New System.Data.SqlClient.SqlC ommand("SE LECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & ARN.Text & "'", conn)
Try
conn.Open()
cmd.Parameters.Add(New SqlParameter("@ARN", SqlDbType.NVarChar = 12)).Value = ARN.Text
cmd.Parameters.Add(New SqlParameter("@Incident_ID ", SqlDbType.NVarChar = 12)).Value = Incident_ID.Text
cmd.Parameters.Add(New SqlParameter("@OffenseID", SqlDbType.NVarChar = 12)).Value = OffenseID.Text
cmd.Parameters.Add(New SqlParameter("@Offense_Num ber", SqlDbType.NVarChar = 12)).Value = Offense_Number.Text
cmd.Parameters.Add(New SqlParameter("@OffenseType ", SqlDbType.NVarChar = 12)).Value = OffenseType.Text
cmd.Parameters.Add(New SqlParameter("@OffenseDesc ", SqlDbType.NVarChar = 12)).Value = OffenseDesc.Text
cmd.Parameters.Add(New SqlParameter("@Attempted_C ommited", SqlDbType.NVarChar = 12)).Value = Attempted_Commited.Text
cmd.Parameters.Add(New SqlParameter("@StatuteViol ation", SqlDbType.NVarChar = 12)).Value = StatuteViolation.Text
cmd.Parameters.Add(New SqlParameter("@NCIC_UCR_Co de", SqlDbType.NVarChar = 12)).Value = NCIC_UCR_Code.Text
cmd.Parameters.Add(New SqlParameter("@timestampad ded", SqlDbType.NVarChar = 12)).Value = Now()
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message )
Finally
conn.Close()
End Try
Me.Close()
End Sub
Here is the code for the page
Private Sub AddOffenses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ARN.Text = ARN.Text 'This loads the ARN assigned from the previous form
Incident_ID.Text = Incident_ID.Text 'This loads the Incident_ID from the previous form
OffenseID.Text = "O-" & CStr("T588") & "-" & Month(Now) & Year(Now) & "-" & Minute(Now) & Second(Now)
Dim conn2 As New System.Data.SqlClient.SqlC
Dim da2 As New System.Data.SqlClient.SqlD
Dim ds2 As New System.Data.DataSet
Dim cmd3 As New System.Data.SqlClient.SqlC
Try
conn2.Open()
Offense_Number.Text = CType(cmd3.ExecuteScalar()
Catch ex As Exception
MessageBox.Show(ex.Message
Finally
conn2.Close()
End Try
End Sub
Private Sub SubmitOffense_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitOffense.Click
Dim conn As New System.Data.SqlClient.SqlC
Dim da As New System.Data.SqlClient.SqlD
Dim ds As New System.Data.DataSet
Dim cmd As New SqlCommand("insert into tbloffense(ARN,Incident_ID
Dim cmd2 As New System.Data.SqlClient.SqlC
Try
conn.Open()
cmd.Parameters.Add(New SqlParameter("@ARN", SqlDbType.NVarChar = 12)).Value = ARN.Text
cmd.Parameters.Add(New SqlParameter("@Incident_ID
cmd.Parameters.Add(New SqlParameter("@OffenseID",
cmd.Parameters.Add(New SqlParameter("@Offense_Num
cmd.Parameters.Add(New SqlParameter("@OffenseType
cmd.Parameters.Add(New SqlParameter("@OffenseDesc
cmd.Parameters.Add(New SqlParameter("@Attempted_C
cmd.Parameters.Add(New SqlParameter("@StatuteViol
cmd.Parameters.Add(New SqlParameter("@NCIC_UCR_Co
cmd.Parameters.Add(New SqlParameter("@timestampad
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message
Finally
conn.Close()
End Try
Me.Close()
End Sub
Try changing you Load event to the following. Its the same as before with some additional debug message boxes.
Private Sub AddOffenses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ARN.Text = ARN.Text 'This loads the ARN assigned from the previous form
Incident_ID.Text = Incident_ID.Text 'This loads the Incident_ID from the previous form
OffenseID.Text = "O-" & CStr("T588") & "-" & Month(Now) & Year(Now) & "-" & Minute(Now) & Second(Now)
Dim conn2 As New System.Data.SqlClient.SqlC onnection( "Persist Security Info=True;Server=xxx.xxx.x xx.xxx;Dat abase=Repo rting;uid= uid;networ k=dbmssocn ;password= password")
Dim da2 As New System.Data.SqlClient.SqlD ataAdapter ("Select * from tblOffense", conn2)
Dim ds2 As New System.Data.DataSet
Dim sSql As String = "SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & ARN.Text & "'"
MessageBox.Show(sSql)
Dim cmd3 As New System.Data.SqlClient.SqlC ommand(sSq l, conn2)
Try
conn2.Open()
Dim i As Integer = cmd3.ExecuteScalar()
MessageBox.Show(i.ToString ())
Offense_Number.Text = i,ToString()
Catch ex As Exception
MessageBox.Show(ex.Message )
Finally
conn2.Close()
End Try
End Sub
Private Sub AddOffenses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ARN.Text = ARN.Text 'This loads the ARN assigned from the previous form
Incident_ID.Text = Incident_ID.Text 'This loads the Incident_ID from the previous form
OffenseID.Text = "O-" & CStr("T588") & "-" & Month(Now) & Year(Now) & "-" & Minute(Now) & Second(Now)
Dim conn2 As New System.Data.SqlClient.SqlC
Dim da2 As New System.Data.SqlClient.SqlD
Dim ds2 As New System.Data.DataSet
Dim sSql As String = "SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & ARN.Text & "'"
MessageBox.Show(sSql)
Dim cmd3 As New System.Data.SqlClient.SqlC
Try
conn2.Open()
Dim i As Integer = cmd3.ExecuteScalar()
MessageBox.Show(i.ToString
Offense_Number.Text = i,ToString()
Catch ex As Exception
MessageBox.Show(ex.Message
Finally
conn2.Close()
End Try
End Sub
ASKER
The mesagebox was too small to see the ARN it was using when load was called.
In that case, just put a breakpoint in your code and run it under the debugger to check the value.
ASKER
carl_tawn, call me stupid but how do I do that? I know how to put in the breakpoint but how do I use the debugger to check the value?
ASKER
Ok, the sSQL messagebox is returning SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='.....'
ASKER
it is definately not pickup the ARN as the parameter.
Is it literally putting the dots in there ? If so, then you need to track down why ARN.Text doesn't contain the correct value.
As far as using the debugger goes. If you set a breakpont and run the app it will switch to debug mode when it hits the breakpoint. You can then cursor over the value your interested in to see what it is set to.
As far as using the debugger goes. If you set a breakpont and run the app it will switch to debug mode when it hits the breakpoint. You can then cursor over the value your interested in to see what it is set to.
ASKER
Here's what I have found so far, on the form the ARN field is receiving the ARN number as it should. But for some reason the scalar query is using the ..... value, rather than the value assigned to the field at load.
My thinking is, if I can declare the ARN value for the entire form rather than just at load, I could dim the arn value and put the dim in the scalar query.
dim count as arn.text = arn.text
Dim sSql As String = "SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & count & "'"
Will something like this work?
My thinking is, if I can declare the ARN value for the entire form rather than just at load, I could dim the arn value and put the dim in the scalar query.
dim count as arn.text = arn.text
Dim sSql As String = "SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & count & "'"
Will something like this work?
Ok, what is this line:
ARN.Text = ARN.Text 'This loads the ARN assigned from the previous form
Supposed to do ?
What type of object is ARN ? I was assuming it was a textbox.
ARN.Text = ARN.Text 'This loads the ARN assigned from the previous form
Supposed to do ?
What type of object is ARN ? I was assuming it was a textbox.
ASKER
ARN.Text = ARN.Text defined
I have a form (OffenseIncident) that when opened prompts the user to input an ARN (Agency Report Number), on this form I have the following on the button click
AddOffenses.ARN.Text = AgencyReportNumber.Text
The form we are working with is the AddOffenses forms, so on load I have the ARN from the previous form populate a label (ARN). ARN is a label!
Did I cloud the water too much?
I have a form (OffenseIncident) that when opened prompts the user to input an ARN (Agency Report Number), on this form I have the following on the button click
AddOffenses.ARN.Text = AgencyReportNumber.Text
The form we are working with is the AddOffenses forms, so on load I have the ARN from the previous form populate a label (ARN). ARN is a label!
Did I cloud the water too much?
The line:
ARN.Text = ARN.Text
Actually just reassigns ARN.Text to itself, which doesn't make much sense. I think you need to be looking at ARN.Text, it seems like its not getting set properly; hence you end up with "....." rather than the number you were expecting.
ARN.Text = ARN.Text
Actually just reassigns ARN.Text to itself, which doesn't make much sense. I think you need to be looking at ARN.Text, it seems like its not getting set properly; hence you end up with "....." rather than the number you were expecting.
ASKER
Yeah, I asked a question on EE the other day about passing these values and was given that solution and it works great, except when we use the scalar query. The scalar query sees the original text value assigned to the object rather than the value assigned to the value at load. I wonder why this is?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, when you perform the button click on the 1st form and opens the 2nd form, the ARN is the ARN that was input on the previous form.
ASKER
I figured it out!!!! I had to reference the controlling form. (ARN='" & OffenseIncident.AgencyRepo rtNumber.T ext & "'", )
Dim cmd2 As New System.Data.SqlClient.SqlC ommand("SE LECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & OffenseIncident.AgencyRepo rtNumber.T ext & "'", conn2)
Dim cmd2 As New System.Data.SqlClient.SqlC