Avatar of Rog D
Rog D
Flag 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

Visual Basic.NET

Avatar of undefined
Last Comment
Rog D

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
iboutchkine

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
iboutchkine

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
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.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck