?
Solved

select count statement using form field as parameter

Posted on 2006-05-05
23
Medium Priority
?
283 Views
Last Modified: 2010-05-01
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
0
Comment
Question by:NCSO
  • 14
  • 8
23 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16613929
   Offense_Number.Text = System.Data.SqlClient.SqlDataAdapter("Select 1 + count (*) as offensecount from tbloffense where ARN = '" & ARN.Text & "';")
0
 

Author Comment

by:NCSO
ID: 16614189
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
0
 

Author Comment

by:NCSO
ID: 16614204
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)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:NCSO
ID: 16614425
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.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16615181
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)
0
 

Author Comment

by:NCSO
ID: 16616037
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?

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16616081
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.
0
 

Author Comment

by:NCSO
ID: 16616186
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
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16616289
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
0
 

Author Comment

by:NCSO
ID: 16616356
The mesagebox was too small to see the ARN it was using when load was called.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16616368
In that case, just put a breakpoint in your code and run it under the debugger to check the value.
0
 

Author Comment

by:NCSO
ID: 16616783
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?
0
 

Author Comment

by:NCSO
ID: 16616932
Ok, the sSQL messagebox is returning SELECT 1 + Count(*) AS offensecount FROM tbloffense WHERE ARN='.....'
0
 

Author Comment

by:NCSO
ID: 16616947
it is definately not pickup the ARN as the parameter.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16617008
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.
0
 

Author Comment

by:NCSO
ID: 16617176
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?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16617265
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.
0
 

Author Comment

by:NCSO
ID: 16617341
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?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16617534
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.
0
 

Author Comment

by:NCSO
ID: 16617615
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?
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 1600 total points
ID: 16617637
In that case the problem lies with the line:

    ARN.Text = ARN.Text  'This loads the ARN assigned from the previous form

If this is in the Load event of the form, and ARN, by default, contains "....." then this is simply assigning "....." back to ARN. It isn't taking a value from the previous form as the comment suggests it is.
0
 

Author Comment

by:NCSO
ID: 16617883
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.
0
 

Author Comment

by:NCSO
ID: 16618086
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)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question