Rog D
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_Ge tSiphoned" )
Dim rdr2 As SqlClient.SqlDataReader
cmd.Connection = cn
If cmd.Connection.State = ConnectionState.Closed Then
cmd.Connection.Open()
End If
cmd.CommandType = ADODB.CommandTypeEnum.adCm dStoredPro c
cmd.Parameters.Add("@Sitei d", 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
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_Ge
Dim rdr2 As SqlClient.SqlDataReader
cmd.Connection = cn
If cmd.Connection.State = ConnectionState.Closed Then
cmd.Connection.Open()
End If
cmd.CommandType = ADODB.CommandTypeEnum.adCm
cmd.Parameters.Add("@Sitei
cmd.Parameters.Add("@Date"
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Problem ended up with the floowing line....
cmd.CommandType = ADODB.CommandTypeEnum.adCm dStoredPro c
cmd.Parameters.Add("@Sitei d", 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.
cmd.CommandType = ADODB.CommandTypeEnum.adCm
cmd.Parameters.Add("@Sitei
cmd.Parameters.Add("@Date"
***** rdr2 = sqlCmd.ExecuteReader()
I was filling cmd not sqlCMD. SQLCmd was from an earlier reader used above.
Thanks for the help though.
ASKER
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