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

Hi,

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

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

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!

Show quoteHide quote

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

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

Show quoteHide quote

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

Selecting, Inserting and Updating Relational Data (SQLServer/ADO.NET)
Updating - let me count the ways
Connect to Foxpro Data (*.DBF) in VB.Net
2nd layer of filtering?
Problem connecting to sql server