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!
LVL 10
GlobaLevelProgrammerAsked:
Who is Participating?
 
quizwedgeConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
 
sarabhaiCommented:
call a sql server store procedure from your code.
store procedure can have output parameter or record set.


0
 
GlobaLevelProgrammerAuthor 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
 
GlobaLevelProgrammerAuthor Commented:
himanshut...

I get the following error with your code:

Error: Microsoft.VisualBasic: Public memeber 'CreateCommand' on type 'String' not found
0
 
GlobaLevelProgrammerAuthor 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:
@GlobaLevel, have you had a chance to try the tests that I suggested?
0
 
GlobaLevelProgrammerAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.