Solved

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

Posted on 2011-02-12
10
382 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
ID: 34881520
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
ID: 34881521

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
ID: 34881524
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 9

Expert Comment

by:sarabhai
ID: 34881540
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
ID: 34894146


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

Author Comment

by:GlobaLevel
ID: 34894148
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
ID: 34894180
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
ID: 34903044
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
ID: 34940923
@GlobaLevel, have you had a chance to try the tests that I suggested?
0
 
LVL 10

Author Closing Comment

by:GlobaLevel
ID: 34943397
Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
"Emulate" TAB key when press Enter Key 3 49
ASP.NET (VB) return a record 2 40
SSRS Deployment problem 5 67
Google Maps with Webforms 1 14
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 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