?
Solved

How do I return the row count from a SgldataReader and Stored Procedure to my page?

Posted on 2004-11-18
13
Medium Priority
?
180 Views
Last Modified: 2010-04-24
Good afternoon!

I need to return the row count for the following to my .aspx page. Is this possible?

custom control code************************************

            Public Function GetReqOptionDetails(ByVal strrequiredoptions As String) As SqlDataReader

            Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As SqlCommand = New SqlCommand("spGetReqOptionDetails", myConnection)

            myCommand.CommandType = CommandType.StoredProcedure

             Dim parameterstrrequiredoptions As SqlParameter = New SqlParameter("@strrequiredoptions", SqlDbType.nvarchar, 250)
            parameterstrrequiredoptions.Value = strrequiredoptions
            myCommand.Parameters.Add(parameterstrrequiredoptions)
      
                  
            myConnection.Open()
            Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)  
            Return result
        End Function

STORED PROCEDURE CODE***********************

CREATE PROCEDURE spGetReqOptionDetails
 (
    @strrequiredoptions nvarchar(250)
)
  As

declare @SQL VARCHAR(8000)
SET @SQL =  'select  *  from CMRC_Products WHERE family in (''' + Replace(@strrequiredoptions,',',''',''') + ''') '

PRINT @SQL

EXEC (@SQL)
GO
0
Comment
Question by:dplsr
10 Comments
 
LVL 28

Expert Comment

by:iboutchkine
ID: 12618353
you can count in the loop

do while result.Read()
    i+=1
Loop

i will hold the quantity of records
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 12618367
Hello

 it's not possible with SQLDataReader, because it's forward only cursor and doesn't return all the records, so there's no way to know the records count using SqlDataReader

If you use Dataset you will be able to know that using DataSet1.Tables(0).Row.Count
, or use another SqlCommand with select count(*) to return the records


HTH
0
 
LVL 7

Expert Comment

by:natloz
ID: 12618424
What is the primary key of the table you are querying?

All of my tables have the PK starting at 1 so the rowcount is always equivilant to the primary key...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dplsr
ID: 12618478
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 12618683
try with ExecuteScalar. It is optimised to return a single value.
0
 

Author Comment

by:dplsr
ID: 12618842

hi emoreau

I would use Row Count(*)
 in the stored procedure

I found this example of ExecuteScalar in the .net docs

use someting like tis in my custom control?

Public Sub CreateMySqlCommand(myScalarQuery As String, myConnection As SqlConnection)
    Dim myCommand As New SqlCommand(myScalarQuery, myConnection)
    myCommand.Connection.Open()
    myCommand.ExecuteScalar()
    myConnection.Close()
End Sub 'CreateMySqlCommand
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 12618920
I don't think you can't get a row count on a SP.

You are going through a SP for a simple SELECT! Is there any reason why? You would need another SP (or add a parameter to your existing one) to execute a "SELECT COUNT(*)" instead of your "SELECT *"
0
 

Author Comment

by:dplsr
ID: 12618973


Thats what I need to know. Can I use SELECT COUNT(*) with my existing code? Can I add an output parameter to a SQLDataReader? How do I get the SELECT COUNT(*) onto my .aspx page?  

Been looking for examples with no luck.

dplsr
0
 

Author Comment

by:dplsr
ID: 12618992
I should have said SELECT COUNT(*) return value

sorry
0
 
LVL 20

Accepted Solution

by:
ihenry earned 2000 total points
ID: 12775015
You can get the number of records from sql server stored proc using @@rowcount statement and pass it's value to an output or return parameter.

CREATE PROCEDURE spGetReqOptionDetails
 (
    @strrequiredoptions nvarchar(250) ,
    @rowreturn int output
)
  As

declare @SQL VARCHAR(8000)
SET @SQL =  'select  *  from CMRC_Products WHERE family in (''' + Replace(@strrequiredoptions,',',''',''') + ''') '

PRINT @SQL

EXEC (@SQL)

set @rowreturn = @@rowcount

GO
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

862 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