something about stored procedure not returning the result

[Guest]

I have succesfully passed parameters to the stored procedure...
but i keep getting no rows.


I notice that if I generate a dataset with the wizard it creates a
'ReturnValue' parameter
Do I need to be using this @ReturnValue parameter somehow?

If I preview the dataadapter, I get results.
But when I use the following code, I get no results:
(further below is the stored procedure)

SqlConnection1.Open()
'daMembers_SQL.SelectCommand.CommandType =
CommandType.Text
'daMembers_SQL.SelectCommand.CommandText = strMemberSQL
If Len(Session("ssMRN")) <> 0 Then
daMembers_SQL.SelectCommand.Parameters("@MRN").Value
= Session("ssMRN")
End If

If Len(Session("ssNAME")) <> 0 Then

daMembers_SQL.SelectCommand.Parameters("@MemNAME").Value = Session("ssNAME")
End If

If Len(Session("ssDOB")) <> 0 Then
daMembers_SQL.SelectCommand.Parameters("@DOB").Value
= Session("ssDOB")
End If

If Len(Session("ssSSN")) <> 0 Then
daMembers_SQL.SelectCommand.Parameters("@SSN").Value
= Session("ssSSN")
End If

If Len(Session("ssSEX")) <> 0 Then
daMembers_SQL.SelectCommand.Parameters("@SEX").Value
= Session("ssSEX")
End If
daMembers_SQL.Fill(dsMembers, "qMemberSelect")

'Stored procedure ================================
CREATE PROCEDURE qMemberSelect
@MRN int = NULL,
@MemNAME nvarchar(40) = NULL,
@DOB datetime = NULL,
@SSN nvarchar(9) = NULL,
@SEX nvarchar(1) = NULL,
@debug bit = 0 AS

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)

SELECT @sql =
'SELECT
[MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT] FROM
dbo.MEMBERSHIP AS Y
WHERE 1=1'


if @MRN IS NOT NULL
SELECT @sql = @sql + ' AND MRN = @xMRN'

if @MemNAME IS NOT NULL
SELECT @sql = @sql + ' AND MemName like @xMemNAME + ''%'''

if @DOB IS NOT NULL
SELECT @sql = @sql + ' AND DOB = @xDOB'

if @SSN IS NOT NULL
SELECT @sql = @sql + ' AND SSN = @xSSN'

if @SEX IS NOT NULL
SELECT @sql = @sql + ' AND SEX = @xSEX'

if @debug = 1
PRINT @sql

SELECT @sql = @sql + ' GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR,
[FROM-DT], [THRU-DT]
HAVING len([THRU-DT])= 0
Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
dbo.Membership As X Where X.MRN = Y.MRN And X.MRN
Not in(Select Z.MRN
From dbo.Membership As Z Where len(Z.[THRU-DT])=0))
ORDER BY MemNAME, [FROM-DT] DESC'

SELECT @paramlist = '@xMRN int,
@xMemName nvarchar(40),
@xDOB datetime,
@xSSN nvarchar(9),
@xSEX nvarchar(1)'

EXEC sp_executesql @sql, @paramlist,
@MRN, @MemNAME, @DOB, @SSN, @SEX

 

[Chris Priede]

Hi,

I have succesfully passed parameters to the stored procedure...
'daMembers_SQL.SelectCommand.CommandType =
CommandType.Text

Try CommandType.StoredProcedure; that is what it's for.

I assume you have tested the stored procedure and know it works correctly.

 

[Guest]

Chris,
If you'll notice below - (sorry I pasted it - didn't mean to)
The lines you are referring to are commented out.

That is because I used to write my own custom SQL from scratch and pass it
to this procedure in the form of strMemberSQL

but I changed that when I wrote the stored procedure.
The command isn't in this code because it's in the dataAdapter object which
is on the page. - That and reference to the stored procedure name
[qMemberSelect]

that is how I am testing this stored procedure and know that it works. I
use - preview data and provide some parameters.

I provided all the code in hopes that someone may see what is wrong, because
I feel have carefully looked at it.

And you got my hopes up. Please carefully (or anyone else) look through
anything else that I may have done wrong.

This is usually what i fear when I provide all the code - but when I provide
short explanations - I'll get someone who asks me to provide all the code!

 

[Guest]

Check out using "SET NOCOUNT ON" in your stored procedure.

 

[Guest]

I did it...
But still no results - what exactly was that supposed to have accomplished?

 

[Guest]

I do something very similar and can't get it to work directly either. What I
do is configure my Table Adapter first by using a simple select statement.

In your case that would be:
SELECT
[MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT] FROM
dbo.MEMBERSHIP AS Y

After the fields populate, I go back and configure it to use the stored
procedure. I'll still get an error (in my case it's Incorrect syntax near
'AND') but click finish and everything else seems to work fine. The key is
that the select statement must return the same fields the stored procedure
will ultimately. I think it's a bug, but no idea if it's reported or how to
report it.

Larry