Solved

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

Posted on 2011-02-12
10
372 Views
Last Modified: 2012-05-11
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
Comment
Question by:GlobaLevel
10 Comments
 
LVL 9

Expert Comment

by:s_chilkury
Comment Utility
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
 
LVL 10

Expert Comment

by:himanshut
Comment Utility

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
 
LVL 14

Expert Comment

by:quizwedge
Comment Utility
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
 
LVL 9

Expert Comment

by:sarabhai
Comment Utility
call a sql server store procedure from your code.
store procedure can have output parameter or record set.


0
 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility


        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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
himanshut...

I get the following error with your code:

Error: Microsoft.VisualBasic: Public memeber 'CreateCommand' on type 'String' not found
0
 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
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
 
LVL 14

Accepted Solution

by:
quizwedge earned 500 total points
Comment Utility
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
 
LVL 14

Expert Comment

by:quizwedge
Comment Utility
@GlobaLevel, have you had a chance to try the tests that I suggested?
0
 
LVL 10

Author Closing Comment

by:GlobaLevel
Comment Utility
Thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now