4 Replies - 41187 Views - Last Post: 17 April 2009 - 05:52 AM Rate Topic: -----

#1 macman007   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 57
  • Joined: 11-November 07

count records in query

Posted 16 April 2009 - 06:36 AM

Hi,

I am using VB2005 ASP/VB. I have a Gridview placed on a web form which is popluated by a query based on user input.

select * from view_main where initials = ddlfilter.text

This works fine. However I want to display a realtime record count based on that query and dump the info in a label called 'recordcount' which appears at the bottom of the grid.

If I run the folloing query in SQL query monitor it gives me a count of 20
SELECT COUNT( * ) FROM "view_main" where initials='jjj'
But how to I amalgamate the count function with my query?
:D

Is This A Good Question/Topic? 0
  • +

Replies To: count records in query

#2 Goethals   User is offline

  • D.I.C Head

Reputation: 25
  • View blog
  • Posts: 145
  • Joined: 14-April 09

Re: count records in query

Posted 16 April 2009 - 06:58 AM

ExecuteScalar will return it.
(Or alternatively you could read the first column from the first (and only) row returned by a reader)

You could also assign a name to it.

SELECT COUNT( * ) AS X FROM "view_main" where initials='jjj'


Was This Post Helpful? 0
  • +
  • -

#3 macman007   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 57
  • Joined: 11-November 07

Re: count records in query

Posted 17 April 2009 - 01:39 AM

Hi,

Thanks for the reply. Could you show me how to implement your proposal. Here is my code for populating the Gridview:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click


		Dim myconn As SqlConnection
		Dim mystring As String

		Dim flag As Integer

	

		Dim connectionstring As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
		myconn = New SqlConnection(connectionstring)
		myconn.Open()
   
		If DDLFilter.Text = "Code" Then
			flag = 1

		ElseIf DDLFilter.Text = "Date" Then
			flag = 2


		Else
			flag = 3
		End If

		If flag = 1 Then mystring = "SELECT * FROM view_main where code1 " & "='" & DDLfilter2.Text & "'" & " or code2" & "='" & DDLfilter2.Text & "'" & " or code3" & "='" & DDLfilter2.Text & "'"
		If flag = 2 Then mystring = "SELECT * FROM view_main where date" & "='" & Split(DDLfilter2.Text, "/")(2) & "-" & Split(DDLfilter2.Text, "/")(1) & "-" & Split(DDLfilter2.Text, "/")(0) & "'"
		If flag = 3 Then mystring = "SELECT * FROM view_main where " & DDLFilter.Text & "='" & DDLfilter2.Text & "'"


		Dim ds As New DataSet

		Dim myda As SqlDataAdapter = New SqlDataAdapter(mystring, myconn)
		myda.Fill(ds, "Table")
		Dim dv As New DataView(ds.Tables(0))
		GridView1.DataSource = dv
		GridView1.DataBind()

	End Sub



TIA
:D
Was This Post Helpful? 0
  • +
  • -

#4 Goethals   User is offline

  • D.I.C Head

Reputation: 25
  • View blog
  • Posts: 145
  • Joined: 14-April 09

Re: count records in query

Posted 17 April 2009 - 02:39 AM

By using the command object.

 
		Dim ConStr As String = ""
		Dim Query As String = "SELECT COUNT(*) FROM view_main where initials='jjj';"
		Dim Con As New System.Data.SqlClient.SqlConnection(ConStr)
		Con.Open()
		Dim Command As New System.Data.SqlClient.SqlCommand(Query, Con)
		Dim Count As Integer = Command.ExecuteScalar()
		Con.Close()



ExecuteScalar returns the first column of the first row returned from the db, in this case the count you did.
Was This Post Helpful? 1
  • +
  • -

#5 macman007   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 57
  • Joined: 11-November 07

Re: count records in query

Posted 17 April 2009 - 05:52 AM

Hi,

Ok got it to work in test mode

Dim mycmd As SqlCommand

		myconn = New SqlConnection(connectionstring)

		myconn.Open()
		'mycmd = New SqlCommand("SELECT COUNT(*) FROM view_main where initials='ada'", myconn)

		mycmd = New SqlCommand(mystring, myconn)
		Dim RecordCount As Integer
		RecordCount = mycmd.ExecuteScalar
		LblRecCount.Text = RecordCount




However, the select command used above was for testing purposes, I need to utilise the contents of 'mystring' - see above

So I tried:

mycmd = New SqlCommand(mystring, myconn)

The only trouble here is that this string does not contain 'SELECT COUNT(*)'
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1