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

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
dplsrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iboutchkineCommented:
you can count in the loop

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

i will hold the quantity of records
0
Mohammed NasmanSoftware DeveloperCommented:
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
natlozCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

dplsrAuthor Commented:
0
Éric MoreauSenior .Net ConsultantCommented:
try with ExecuteScalar. It is optimised to return a single value.
0
dplsrAuthor Commented:

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
Éric MoreauSenior .Net ConsultantCommented:
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
dplsrAuthor Commented:


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
dplsrAuthor Commented:
I should have said SELECT COUNT(*) return value

sorry
0
ihenryCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.