Link to home
Start Free TrialLog in
Avatar of Rog D
Rog DFlag for United States of America

asked on

SQLReader SQLStored Procedure always HAS rows???? Don't understand Need Help

I have a stored procedure I am calling to return resuts if there any to a
sqldatareader.

The problem I am having is my code thinks it is always returning results...

the While rdr2.read always passes and the rdr2.has rows is true even
if the stored procdure returns no results.

I have tested this by runing the stored procedure manually and it shows NO
results.

What could be my problem?   Here is my simple code and stored procedure....

Thanks,


*******   Code


                Dim cmd As New SqlClient.SqlCommand("R_GetSiphoned")
                Dim rdr2 As SqlClient.SqlDataReader
                cmd.Connection = cn
                If cmd.Connection.State = ConnectionState.Closed Then
                    cmd.Connection.Open()
                End If
                cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
                cmd.Parameters.Add("@Siteid", CStr(dtnew.Rows(i).Item(0)))
                cmd.Parameters.Add("@Date", CDate("1/1/2010"))
                rdr2 = sqlCmd.ExecuteReader()
                While rdr2.Read
                    If rdr2.HasRows Then
                        dtnew.Rows(i).Item(j + 1) = "X"
                    Else
                        dtnew.Rows(i).Item(j + 1) = "O"
                    End If
                End While


**************

Stored Procedure.....

ALTER PROCEDURE R_GetSiphoned

@SiteID varchar(10),
@Date datetime

AS
 SELECT CONVERT(varchar(10), siphonTransferAt, 101) AS Expr1, siteID
 FROM a_siphon
    WHERE  siteID = @SiteID AND (CONVERT(varchar(10), siphonTransferAt, 101)
= @date)
    return

ASKER CERTIFIED SOLUTION
Avatar of iboutchkine
iboutchkine

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rog D

ASKER

I do the checks as you have above, but I am confused by the StoredProcedure and the regular SQL execution of a query.

As you can see I do have the return in the stored procedure.  I just don't understand why anything is retured to the reader or why the reader has rows when the result is 0 rows?

THanks,

Roger
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rog D

ASKER


Yes.

I have manually ran the stored procedure with several different parameters in Enterprise Manager and saw that the result was zero.

I also tried your rdr2.Item("FieldName") Is System.DBNull.Value and was told there were results.

I am now going to try a different connection to the database.

I am wondering if there is a bug in the SQLDatareader?




Rog
Avatar of Rog D

ASKER

Problem ended up with the floowing line....


 cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
                cmd.Parameters.Add("@Siteid", CStr(dtnew.Rows(i).Item(0)))
                cmd.Parameters.Add("@Date", CDate("1/1/2010"))

*****      rdr2 = sqlCmd.ExecuteReader()

I was filling cmd not sqlCMD.  SQLCmd was from an earlier reader used above.


Thanks for the help though.