Link to home
Start Free TrialLog in
Avatar of NCSO
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.SqlConnection("Persist Security Info=True;Server=xxx.xxx.xxx.xxx;Database=Database Name;uid=uid;network=dbmssocn;password=password")
        Dim da As New System.Data.SqlClient.SqlDataAdapter("Select * from tblOffense", conn)
        Dim ds As New System.Data.DataSet
        Offense_Number.Text = System.Data.SqlClient.SqlDataAdapter("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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

   Offense_Number.Text = System.Data.SqlClient.SqlDataAdapter("Select 1 + count (*) as offensecount from tbloffense where ARN = '" & ARN.Text & "';")
Avatar of NCSO
NCSO

ASKER

When I use the System.Data.SqlClient.SqlDataAdapter, 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
Avatar of NCSO

ASKER

I added the conn2 staement to address which connection to use

        Offense_Number.Text = System.Data.SqlClient.SqlDataAdapter(("Select 1 + count (*) as offensecount from tbloffense where ARN = '" & ARN.Text & "';"), conn2)
Avatar of NCSO

ASKER

I have tried the following as well with no luck:

        Dim test As New System.Data.SqlClient.SqlDataAdapter(("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.
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.SqlCommand("SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & ARN.Text & "'", conn2)
    Offense_Number.Text = CType(cmd.ExecuteScalar(), String)
Avatar of NCSO

ASKER

We are getting there; however, the parameter is not working, I currently have the following:    

Dim cmd3 As New System.Data.SqlClient.SqlCommand("SELECT 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.SqlCommand("SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='7777777777'", conn2)
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.SqlCommand(sSql, conn2)


This will show what the SQL query contains and should show any problems with the value coming from ARN.Text.
Avatar of NCSO

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.SqlConnection("Persist Security Info=True;Server=xxx.xxx.xxx.xxx;Database=Reporting;uid=uid;network=dbmssocn;password=password")
        Dim da2 As New System.Data.SqlClient.SqlDataAdapter("Select * from tblOffense", conn2)
        Dim ds2 As New System.Data.DataSet
        Dim cmd3 As New System.Data.SqlClient.SqlCommand("SELECT 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.SqlConnection("Persist Security Info=True;Server=xxx.xxx.xxx.xxx;Database=Reporting;uid=uid;network=dbmssocn;password=password")
        Dim da As New System.Data.SqlClient.SqlDataAdapter("Select * from tblOffense", conn)
        Dim ds As New System.Data.DataSet
        Dim cmd As New SqlCommand("insert into tbloffense(ARN,Incident_ID,Offense_Number,OffenseID,OffenseType,OffenseDesc,Attempted_commited,statuteViolation,ncic_ucr_code,timestampadded)values(@ARN,@Incident_ID,@Offense_Number,@OffenseID,@OffenseType,@OffenseDesc,@Attempted_commited,@statuteViolation,@ncic_ucr_code,@timestampadded)", conn)
        Dim cmd2 As New System.Data.SqlClient.SqlCommand("SELECT 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_Number", 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_Commited", SqlDbType.NVarChar = 12)).Value = Attempted_Commited.Text
            cmd.Parameters.Add(New SqlParameter("@StatuteViolation", SqlDbType.NVarChar = 12)).Value = StatuteViolation.Text
            cmd.Parameters.Add(New SqlParameter("@NCIC_UCR_Code", SqlDbType.NVarChar = 12)).Value = NCIC_UCR_Code.Text
            cmd.Parameters.Add(New SqlParameter("@timestampadded", SqlDbType.NVarChar = 12)).Value = Now()
            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.SqlConnection("Persist Security Info=True;Server=xxx.xxx.xxx.xxx;Database=Reporting;uid=uid;network=dbmssocn;password=password")
        Dim da2 As New System.Data.SqlClient.SqlDataAdapter("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.SqlCommand(sSql, 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
Avatar of NCSO

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.
Avatar of NCSO

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?
Avatar of NCSO

ASKER

Ok, the sSQL messagebox is returning SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='.....'
Avatar of NCSO

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.
Avatar of NCSO

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?
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.
Avatar of NCSO

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?
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.
Avatar of NCSO

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
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NCSO

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.
Avatar of NCSO

ASKER

I figured it out!!!!  I had to reference the controlling form. (ARN='" & OffenseIncident.AgencyReportNumber.Text & "'", )


Dim cmd2 As New System.Data.SqlClient.SqlCommand("SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='" & OffenseIncident.AgencyReportNumber.Text & "'", conn2)