• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

ms sql server 2005/asp.net - find count of rows and roll that number into a varaible

I am writing a asp.net page in vb.net and I need to be able to run a query against a table to find the numbers of rows in the table and roll that number into a varaible...


so that I can do calculations on that number....so if there are 56 rows in the table...then I need to pass that so

label1.text = "You have " & total_number_rows & " in the table"

varaible is good too, if I need to use session

thanks!
0
GlobaLevel
Asked:
GlobaLevel
1 Solution
 
s_chilkuryCommented:
Write the SP which returns the row count as per the requirement:
http://support.microsoft.com/kb/306574
http://stackoverflow.com/questions/723352/creating-a-stored-procedure-to-return-the-rowcount-of-another-stored-procedure

Put that as a Out Parameter

Then, set the out parameter to variable after Executing the SP:
http://www.developerfusion.com/samplechapter/91/stored-procedures/4/

0
 
himanshutCommented:

1.
      Dim cmdCustomer As New SqlCommand
   2.
      Dim daCustomer As New SqlDataAdapter
   3.
      Dim dsCustomer As New DataSet
   4.
      Dim dtCustomer As New DataTable
   5.
       
   6.
      conn = GetConnect()
   7.
      Try
   8.
      cmdCustomer = conn.CreateCommand
   9.
      cmdCustomer.CommandText = "SELECT * FROM Customer where IDCustomer = '1' "
  10.
      daCustomer.SelectCommand = cmdCustomer
  11.
      daCustomer.Fill(dsCustomer, "Customer")
  12.
      dtCustomer = dsCustomer.Tables("Customer")
  13.
      MsgBox(dtCustomer.Rows.Count)
  14.
      Catch ex As Exception
  15.
      MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
  16.
      End Try

Open in new window

0
 
quizwedgeCommented:
Not sure what you have for code as far as calling the query, but assuming you do your query as follows

dim dt as new datatable
dim da as new SqlDataAdapter("SELECT * FROM tblUser", sqlconn.connectionstring)
da.fill(dt)

Open in new window


You can then just do
dt.Rows.Count and that will return the number of rows in your query.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sarabhaiCommented:
call a sql server store procedure from your code.
store procedure can have output parameter or record set.


0
 
GlobaLevelAuthor Commented:


        Dim camp_name As String
        camp_name = DropDownList1.Text
        Dim count_SMS_rows As String
        Dim sqlConnection As String
        SqlConnection = "Data Source=xx.xx.xx.xx;Initial Catalog=Teerrext;Trusted_connection=true;"
QuizWedge....I get a string/binary will be truncated issue here...the        

        Dim dt As New DataTable
        Using sqlConn As New SqlConnection
            sqlConn.ConnectionString = sqlConnection
            Dim da As New SqlDataAdapter("SELECT * FROM Casdgn where tc_id = '" & tc_id & "' and " & _
                    " camdsgn_name = '" & camp_name & "'", sqlConn.ConnectionString)
            da.Fill(dt)
            count_SMS_rows = dt.Rows.Count
        End Using
       
0
 
GlobaLevelAuthor Commented:
himanshut...

I get the following error with your code:

Error: Microsoft.VisualBasic: Public memeber 'CreateCommand' on type 'String' not found
0
 
GlobaLevelAuthor Commented:
QuizWedge....I get a string/binary will be truncated issue here...the        


     Dim camp_name As String
        camp_name = DropDownList1.Text
        Dim count_SMS_rows As String
        Dim sqlConnection As String
        SqlConnection = "Data Source=xx.xx.xx.xx;Initial Catalog=Teerrext;Trusted_connection=true;"
     

        Dim dt As New DataTable
        Using sqlConn As New SqlConnection
            sqlConn.ConnectionString = sqlConnection
            Dim da As New SqlDataAdapter("SELECT * FROM Casdgn where tc_id = '" & tc_id & "' and " & _
                    " camdsgn_name = '" & camp_name & "'", sqlConn.ConnectionString)
            da.Fill(dt)
            count_SMS_rows = dt.Rows.Count
        End Using
       
0
 
quizwedgeCommented:
Are you getting an error with that code before you run it or when you run it?

Can you try the following and see if the error goes away?

1. Change the SELECT query to "SELECT * FROM CASDGN"

2. Get rid of the line count_SMS_rows = dt.Rows.Count

3. Change "count_SMS_rows = dt.Rows.Count" to "count_SMS_rows = CStr(dt.Rows.Count)"

I'm just trying to figure out where in your code it is throwing an error. My best guess is that the tc_id you're passing in is too big for the SQL type that tc_id is, but I don't know what your data structure is or what the value of tc_id is at this point in the code.
0
 
quizwedgeCommented:
@GlobaLevel, have you had a chance to try the tests that I suggested?
0
 
GlobaLevelAuthor Commented:
Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now