Output Parameter not being returned correctly - DAAB in ASP.NET WAP

[Alec MacLean]

Hi,

I'm using the DAAB Ent Lib (Jan 2006) for .NET 2.0, with VS 2005 Pro. My project is a Web app project (using the WAP add in).

Background: I'm creating a survey system for our company, for which invites will target selected personnel among our customers via email. Each email will provide a custom hyperlink for each respondent using a SQL generated GUID value in the querystring. The GUID will be checked for validity before the user can proceed to the survey, and is used as part of the "use-once" process so that the respondent cannot re-submit multiple times.

I have a sproc that accepts one input parameter (the GUID), returns one output parameter, and may include a result set if the GUID is matched using a simple check. (Note that the GUID is not being used for a primary key - the table uses a normal Int identifier for the PK.)

Here's the SQL sproc:

CREATE PROCEDURE ...mySprocName...

@theGUID VARCHAR(36),
@Result INT OUTPUT

AS

SELECT @Result = COUNT(RecID) FROM Recipients WHERE GUID = @theGUID

IF @Result = 1
BEGIN
-- (Should only ever have one match across all records).
SELECT R.CampaignID, C.CloseDate, R.ResSaved
FROM Recipients AS R
JOIN Campaigns AS C ON R.CampaignID = C.CampaignID
WHERE R.GUID = @theGUID
END

The problem is that VS 2005 is interpreting the returned param (@result) as zero even when I know this isn't the case.

Here's the vb page code (for brevity I've omitted some of the obvious declarations and simple formatting checks, etc):

....
1 Me.dbCmd = db.GetStoredProcCommand("mySprocName")
2 Me.dbCmd.Parameters.Clear()
3 Me.db.AddInParameter(dbCmd, "@theGUID", DbType.String, theGUID)
4 Me.db.AddOutParameter(dbCmd, "@Result", DbType.Int32, 4)
5
6 Try
7 Me.lblErr.Visible = False
8 qRdr = db.ExecuteReader(dbCmd)
9 iRes = CInt(db.GetParameterValue(Me.dbCmd, "@Result"))
10
11 If iRes = 1 Then
12 'Exact match for GUID found
13 While qRdr.Read
14 Me.CampaignID = CInt(qRdr.Item("CampaignID"))
15 If Not IsDBNull(qRdr.Item("CloseDate")) Then
16 Me.CampaignCloseDate = CDate(qRdr.Item("CloseDate"))
17 End If
18 Me.ResponseAlreadyMade = CBool(qRdr.Item("ResSaved"))
19 End While
20
21 qRdr = Nothing
22 'Do some other stuff...
23 Return True
24 Else
25 'Invalid values of some sort.
26 Return False
27 End If
28
29 Catch ex As Exception
30 Me.lblErr.Visible = True
31 me.lblErr.Text = ex.Message.ToString
32 End Try
....

In the page code I have stepped through the code in debug and bypassed the check for iRes (shown here on line 11) as I know the test guid string passed in matches a test record. The data reader DOES have the results in it (one row of three columns) at this stage, but iRes is still zero, even though it has to be 1 in order for the results to be returned by the sproc!

I checked my syntax by changing the string input param to an int type and using an appropriate value (the test row PK in this case, 1). This worked as expected, giving the @Result param as 1 when the record match was made and zero when no match made.

I then tested by replacing the GUID with an email address (changing sproc statements accordingly). This also failed to return 1 when a known match was occuring.

I've also tried adding other fake params to the input list (e.g. @testInt INT) in order to see if the problem related to using just one string input param, but I got the same failure.

I also tried converting and passing the GUID value as a GUID type (sproc input then being uniqueidentifier), using the following syntax to replace line 3:

Dim myG As New Guid(theGUID)
Me.db.AddInParameter(dbCmd, "@theGUID", DbType.Guid, myG)

But again the same problem, result in VS2005 says iRes = 0, but using debug mode to step past the If statement on 11 directly to 13, we can successfuly step through the reader and read the results.

I should stress that I have tested my SQL in SQL Query Analyser and get the expected (correct) responses, so the issue seems to definitely be with VS 2005 + DAAB 2.0. (I have used the same process in VS 2003 with the previous DAAB and this worked fine with a string input and int output)


Has anyone else come across this issue? Is it just me?! Am I missing something?

Thanks for any help.

Al

 

[Guest]

Hi,
when using output parametes with data readers you have to read the whole
result first before you can access the value of the output parameter
Hope this helps.
Regards,
Mohamed Mosalem

 

[Alec MacLean]

Hi - found a way round the problem. If I use a dataset and the LoadDataset method to retrieve the results, the output param value operates correctly.

So, swapping out line 8 for the following line (having previously declared myDS as a new dataset) :
db.LoadDataSet(dbCmd, myDS, "Recipient")

Seems to do the trick.

In a peculiar twist of fate, this approach has the advantage of not actually needing to use the output param, as we can instead check the number of returned rows present in the dataset (we can't do this on a datareader)

So, the three lines:

Me.db.AddOutParameter(dbCmd, "@Result", DbType.Int32, 4) ...

iRes = CInt(db.GetParameterValue(Me.dbCmd, "@Result"))

If iRes > 0 Then...

Can all be replaced by the simpler

If myDS.Tables("Recipient").Rows.Count > 0 Then

....

Would still like to know why the datareader version has such a problem with output params though...

Al



Hi,

I'm using the DAAB Ent Lib (Jan 2006) for .NET 2.0, with VS 2005 Pro. My project is a Web app project (using the WAP add in).

Background: I'm creating a survey system for our company, for which invites will target selected personnel among our customers via email. Each email will provide a custom hyperlink for each respondent using a SQL generated GUID value in the querystring. The GUID will be checked for validity before the user can proceed to the survey, and is used as part of the "use-once" process so that the respondent cannot re-submit multiple times.

I have a sproc that accepts one input parameter (the GUID), returns one output parameter, and may include a result set if the GUID is matched using a simple check. (Note that the GUID is not being used for a primary key - the table uses a normal Int identifier for the PK.)

Here's the SQL sproc:

CREATE PROCEDURE ...mySprocName...

@theGUID VARCHAR(36),
@Result INT OUTPUT

AS

SELECT @Result = COUNT(RecID) FROM Recipients WHERE GUID = @theGUID

IF @Result = 1
BEGIN
-- (Should only ever have one match across all records).
SELECT R.CampaignID, C.CloseDate, R.ResSaved
FROM Recipients AS R
JOIN Campaigns AS C ON R.CampaignID = C.CampaignID
WHERE R.GUID = @theGUID
END

The problem is that VS 2005 is interpreting the returned param (@result) as zero even when I know this isn't the case.

Here's the vb page code (for brevity I've omitted some of the obvious declarations and simple formatting checks, etc):

...
1 Me.dbCmd = db.GetStoredProcCommand("mySprocName")
2 Me.dbCmd.Parameters.Clear()
3 Me.db.AddInParameter(dbCmd, "@theGUID", DbType.String, theGUID)
4 Me.db.AddOutParameter(dbCmd, "@Result", DbType.Int32, 4)
5
6 Try
7 Me.lblErr.Visible = False
8 qRdr = db.ExecuteReader(dbCmd)
9 iRes = CInt(db.GetParameterValue(Me.dbCmd, "@Result"))
10
11 If iRes = 1 Then
12 'Exact match for GUID found
13 While qRdr.Read
14 Me.CampaignID = CInt(qRdr.Item("CampaignID"))
15 If Not IsDBNull(qRdr.Item("CloseDate")) Then
16 Me.CampaignCloseDate = CDate(qRdr.Item("CloseDate"))
17 End If
18 Me.ResponseAlreadyMade = CBool(qRdr.Item("ResSaved"))
19 End While
20
21 qRdr = Nothing
22 'Do some other stuff...
23 Return True
24 Else
25 'Invalid values of some sort.
26 Return False
27 End If
28
29 Catch ex As Exception
30 Me.lblErr.Visible = True
31 me.lblErr.Text = ex.Message.ToString
32 End Try
...

In the page code I have stepped through the code in debug and bypassed the check for iRes (shown here on line 11) as I know the test guid string passed in matches a test record. The data reader DOES have the results in it (one row of three columns) at this stage, but iRes is still zero, even though it has to be 1 in order for the results to be returned by the sproc!

I checked my syntax by changing the string input param to an int type and using an appropriate value (the test row PK in this case, 1). This worked as expected, giving the @Result param as 1 when the record match was made and zero when no match made.

I then tested by replacing the GUID with an email address (changing sproc statements accordingly). This also failed to return 1 when a known match was occuring.

I've also tried adding other fake params to the input list (e.g. @testInt INT) in order to see if the problem related to using just one string input param, but I got the same failure.

I also tried converting and passing the GUID value as a GUID type (sproc input then being uniqueidentifier), using the following syntax to replace line 3:

Dim myG As New Guid(theGUID)
Me.db.AddInParameter(dbCmd, "@theGUID", DbType.Guid, myG)

But again the same problem, result in VS2005 says iRes = 0, but using debug mode to step past the If statement on 11 directly to 13, we can successfuly step through the reader and read the results.

I should stress that I have tested my SQL in SQL Query Analyser and get the expected (correct) responses, so the issue seems to definitely be with VS 2005 + DAAB 2.0. (I have used the same process in VS 2003 with the previous DAAB and this worked fine with a string input and int output)


Has anyone else come across this issue? Is it just me?! Am I missing something?

Thanks for any help.

Al

 

[Alec MacLean]

Thanks Mohamed.

Part of my reason for using the output param was to determine if there were
any results to be read and thus bypass the error caused by trying to read an
empty datareader. (Error text being "Invalid attempt to read when no data
is present")

Also, I'm not sure you are quite correct. In my tests I found that if the
input param was an integer (in my test case this represented a primary key
value), then the outout parameter operated correctly and as expected -
critically before any dataread operations occur.

The problem only seemed to manifest when a non-integer input was used in the
sproc logic as detailed in my original post.

Anyway, I've found a solution which I've posted separately to this thread as
another reply. I changed to use a dataset instead of a datareader. This
resolves the output parameter problem and offers some other control
advantages that obviate the need for the param in this particular case,
though I think the speed & memory efficiency of the datareader may be lost.

Al

 

[Guest]

Hi,
to check that the reader has values or not you can use the following
while(reader.Read())
{
// read the values
}

by doing so you will never read an empty reader,
if you want to return the number of records first then the actual rows, then
you can return from the stored procedure two result sets and reads them from
the datareader using the reader.NextResult() fundtion

Regards,
Mohaed Mosalem

 

[Alec MacLean]

Hi Mohamed,

Perhaps C# handles the reader.Read method differently from VB.NET, but in my
case, as soon as I try a loop that's using the empty reader, this will cause
the error to occur.

If you look again at my original posting, you'll see that this is what I was
trying to prevent by using the output parameter to indicate there were (or
not) records to read.

while myReader.Read 'If empty, error message occurs immediately here.
'Would normally read values here...
end while

Al

 

[William \(Bill\) Vaughn]

Ah, use MyDataReader.HasRows to determine if the DataReader returned a
rowset.
But no, you can't capture the OUTPUT parameters until the entire rowset has
been processed--it's not passed until the last row is returned no matter
where you put the SET in the stored procedure.
What exception are you getting on Read? If there is no DataReader, you might
not be looking at a resultset that returns a rowset. Try using the
NextResult method to step to the next resultset.

I describe all of these behaviors in my ADO.NET books.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

It's not really a "problem"--it's a behavior. The DataReader exposes the low-level data stream as it's made available from the server. Because of this, the server does not know how many rows qualify for the query (if any) until after it finds all the rows. It's like asking a farmer how many eggs he's going to gather before he goes into the chicken house. While your procedure might set the OUTPUT parameter early in the SP code, it's not appended into the data stream until last--after the rowset packets have all been sent.

Make sense?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi - found a way round the problem. If I use a dataset and the LoadDataset method to retrieve the results, the output param value operates correctly.

So, swapping out line 8 for the following line (having previously declared myDS as a new dataset) :
db.LoadDataSet(dbCmd, myDS, "Recipient")

Seems to do the trick.

In a peculiar twist of fate, this approach has the advantage of not actually needing to use the output param, as we can instead check the number of returned rows present in the dataset (we can't do this on a datareader)

So, the three lines:

Me.db.AddOutParameter(dbCmd, "@Result", DbType.Int32, 4) ...

iRes = CInt(db.GetParameterValue(Me.dbCmd, "@Result"))

If iRes > 0 Then...

Can all be replaced by the simpler

If myDS.Tables("Recipient").Rows.Count > 0 Then

...

Would still like to know why the datareader version has such a problem with output params though...

Al



Hi,

I'm using the DAAB Ent Lib (Jan 2006) for .NET 2.0, with VS 2005 Pro. My project is a Web app project (using the WAP add in).

Background: I'm creating a survey system for our company, for which invites will target selected personnel among our customers via email. Each email will provide a custom hyperlink for each respondent using a SQL generated GUID value in the querystring. The GUID will be checked for validity before the user can proceed to the survey, and is used as part of the "use-once" process so that the respondent cannot re-submit multiple times.

I have a sproc that accepts one input parameter (the GUID), returns one output parameter, and may include a result set if the GUID is matched using a simple check. (Note that the GUID is not being used for a primary key - the table uses a normal Int identifier for the PK.)

Here's the SQL sproc:

CREATE PROCEDURE ...mySprocName...

@theGUID VARCHAR(36),
@Result INT OUTPUT

AS

SELECT @Result = COUNT(RecID) FROM Recipients WHERE GUID = @theGUID

IF @Result = 1
BEGIN
-- (Should only ever have one match across all records).
SELECT R.CampaignID, C.CloseDate, R.ResSaved
FROM Recipients AS R
JOIN Campaigns AS C ON R.CampaignID = C.CampaignID
WHERE R.GUID = @theGUID
END

The problem is that VS 2005 is interpreting the returned param (@result) as zero even when I know this isn't the case.

Here's the vb page code (for brevity I've omitted some of the obvious declarations and simple formatting checks, etc):

...
1 Me.dbCmd = db.GetStoredProcCommand("mySprocName")
2 Me.dbCmd.Parameters.Clear()
3 Me.db.AddInParameter(dbCmd, "@theGUID", DbType.String, theGUID)
4 Me.db.AddOutParameter(dbCmd, "@Result", DbType.Int32, 4)
5
6 Try
7 Me.lblErr.Visible = False
8 qRdr = db.ExecuteReader(dbCmd)
9 iRes = CInt(db.GetParameterValue(Me.dbCmd, "@Result"))
10
11 If iRes = 1 Then
12 'Exact match for GUID found
13 While qRdr.Read
14 Me.CampaignID = CInt(qRdr.Item("CampaignID"))
15 If Not IsDBNull(qRdr.Item("CloseDate")) Then
16 Me.CampaignCloseDate = CDate(qRdr.Item("CloseDate"))
17 End If
18 Me.ResponseAlreadyMade = CBool(qRdr.Item("ResSaved"))
19 End While
20
21 qRdr = Nothing
22 'Do some other stuff...
23 Return True
24 Else
25 'Invalid values of some sort.
26 Return False
27 End If
28
29 Catch ex As Exception
30 Me.lblErr.Visible = True
31 me.lblErr.Text = ex.Message.ToString
32 End Try
...

In the page code I have stepped through the code in debug and bypassed the check for iRes (shown here on line 11) as I know the test guid string passed in matches a test record. The data reader DOES have the results in it (one row of three columns) at this stage, but iRes is still zero, even though it has to be 1 in order for the results to be returned by the sproc!

I checked my syntax by changing the string input param to an int type and using an appropriate value (the test row PK in this case, 1). This worked as expected, giving the @Result param as 1 when the record match was made and zero when no match made.

I then tested by replacing the GUID with an email address (changing sproc statements accordingly). This also failed to return 1 when a known match was occuring.

I've also tried adding other fake params to the input list (e.g. @testInt INT) in order to see if the problem related to using just one string input param, but I got the same failure.

I also tried converting and passing the GUID value as a GUID type (sproc input then being uniqueidentifier), using the following syntax to replace line 3:

Dim myG As New Guid(theGUID)
Me.db.AddInParameter(dbCmd, "@theGUID", DbType.Guid, myG)

But again the same problem, result in VS2005 says iRes = 0, but using debug mode to step past the If statement on 11 directly to 13, we can successfuly step through the reader and read the results.

I should stress that I have tested my SQL in SQL Query Analyser and get the expected (correct) responses, so the issue seems to definitely be with VS 2005 + DAAB 2.0. (I have used the same process in VS 2003 with the previous DAAB and this worked fine with a string input and int output)


Has anyone else come across this issue? Is it just me?! Am I missing something?

Thanks for any help.

Al

 

[Alec MacLean]

Hi Bill,

Thanks for both your postings. Ok, couple of things:

The datareader I was using is the base IDatareader class rather than the
SQLDataReader. As you'll know from writing your ADO books, this doesn't
have a HasRows property. I could of course cast it to the sqldatareader
type first, now you point that out - in fact I'd have to cast it if I wanted
to use sqldatareader at all with DAAB, as the DAAB ExecuteReader method only
returns an IDataReader.

Second thing is...
Why would the output result return correctly when I used an INT on the input
param instead of anything else (string, or GUID)? Try it - I'd be
interested to hear if you got different a result from me.

Anyway, my issue with the code was that the datareader DID have the full
resultset in it, but the output param was still indicating 0 when it had to
be 1 for there to _be_ any resultset, do you get me?

To use your simple egg collecting farmer analogy, I was trying to use the
output param to act like this:

Farmer enters shed, checks if there are any eggs to collect.
If there are no eggs, farmer exits shed and shouts "No eggs".
If there _are_ eggs, farmer exits shed carrying the eggs and shouts "I've
got eggs. By the way, here they are".

My sproc was effectively producing "I've got no eggs. But actually I'm
lying and I've stuffed them in my pockets to hide them from you".

Al