?
Solved

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

Posted on 2011-02-12
10
Medium Priority
?
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

718 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