troubleshooting Question

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

Avatar of Rog D
Rog DFlag for United States of America asked on
Visual Basic.NET
5 Comments2 Solutions226 ViewsLast Modified:
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
iboutchkine

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros