Rowcount and SQLDataReader

Hi, from what I can find, there isn't a way to get the number of rows returned from a SQLDataReader command. Is this correct? If so, is there a way around this? My SQLDataReader command is as follows:

Dim commandIndAsNew System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)

Dim commandSQLAsNew SqlCommand("GetAssetList2", connStringSQL)

Dim resultDSAsNew Data.DataSet()

'// Fill the dataset with values

commandInd.Fill(resultDS)

'// Get the XML values of the dataset to send to SQL server and run a new query

Dim strXMLAsString = resultDS.GetXml()

Dim xmlFileListAs SqlParameter

Dim strContainsClauseAs SqlParameter

'// Create and execute the search against SQL Server

connStringSQL.Open()

commandSQL.CommandType = Data.CommandType.StoredProcedure

commandSQL.Parameters.Add("@.xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXML

commandSQL.Parameters.Add("@.strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstruct

Dim sqlReaderSourceAs SqlDataReader = commandSQL.ExecuteReader()

results.DataSource = sqlReaderSource

results.DataBind()

connStringSQL.Close()

And the stored procedure is such:

DROPPROC dbo.GetAssetList2;

GO

CREATEPROC dbo.GetAssetList2

(

@.xmlFileListvarchar(1000),

@.strContainsClausevarchar(1000)

)

AS

BEGIN

SETNOCOUNTON

DECLARE @.intDocHandleint

EXECsp_xml_preparedocument @.intDocHandleOUTPUT, @.xmlFileList

SELECTDISTINCT

AssetsMaster.AssetMasterUID,

SupportedFiles.AssetPath,

FROM

AssetsMaster,

OPENXML(@.intDocHandle,'/NewDataSet/Table',2)WITH(FILENAMEvarchar(256))AS x,

SupportedFiles

WHERE

AssetsMaster.AssetFileName= x.FILENAME

AND AssetsMaster.Extension= SupportedFiles.Extension

UNION

SELECTDISTINCT

AssetsMaster.AssetMasterUID,

SupportedFiles.AssetPath,

FROM

AssetsMaster,

OPENXML(@.intDocHandle,'/NewDataSet/Table',2)WITH(FILENAMEvarchar(256))AS x,

SupportedFiles

WHERE

AssetsMaster.AssetFileName<> x.FILENAME

ANDCONTAINS((Description, Keywords), @.strContainsClause)

AND AssetsMaster.Extension= SupportedFiles.Extension

ORDERBY AssetsMaster.DownloadsDESC

EXECsp_xml_removedocument @.intDocHandle

END

GO

How can I access the number of rows returned by this stored procedure?

Thanks,

James

I would suggest changing your datareader into a dataset. Then you can bind to the dataset, and also check how many rows are in it.|||

Thanks Motley. What is wrong with this? I am calling a stored SELECT procedure. It says there is a syntax error.

Dim connStringSQLAsNew SqlConnection("Data Source=*;Database=*;User ID=*;Password=*;Trusted_Connection=*")

'// Create the new OLEDB connection to Indexing Service

Dim commandSQLAsNew SqlCommand("GetAssetList2", connStringSQL)

Dim resultDSAsNew Data.DataSet()

Dim resultDAAsNew SqlDataAdapter()

'// Fill the dataset with values from the previous query

commandInd.Fill(resultDS)

'// Get the XML values of the dataset to send to SQL server and run a new query

Dim strXMLAsString = resultDS.GetXml()

Dim xmlFileListAs SqlParameter

Dim strContainsClauseAs SqlParameter

'// Create and execute the search against SQL Server

commandSQL.Parameters.Add("@.xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXML

commandSQL.Parameters.Add("@.strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstruct

resultDA.SelectCommand = commandSQL

resultDA.Fill(resultDS)

Dim sourceAsNew Data.DataView(resultDS.Tables(0))

resultCount.Text = source.Count.ToString

results.DataSource = source

results.DataBind()

|||

Never mind, I got it by added

commandSQL.CommandType = Data.CommandType.StoredProcedure

But now I get one extra row returned per query. It's empty. Any ideas?