too many arguments specified...

4 Jan 2006 1:49 AM

jonefer

I have an ASP.NET app that searches for members using 5 parameters.
I finally wrote a nifty Stored procedure to account for all or anything in
between - parameters - that could be used out of 5.

And the stored procedure works fine while using Query Analyzer in SQL
Server...

but now in trying to implement it using session variables because
the textboxes for the parameters are on a different page:
I get a "too many arguments specified" error when trying to implement this
code in VB.NET/ADO.NET - how can I make sure it works?

(this is the code I have on the Results page which is a DataGrid of the
result from whatever the use selected on the search page)

daMembers_SQL.SelectCommand.Parameters.Add("@MRN", SqlDbType.Int).Value =
Session("ssMRN")
                    daMembers_SQL.SelectCommand.Parameters.Add("@MemNAME",
SqlDbType.NVarChar, 40).Value = Session("ssNAME")

                    daMembers_SQL.SelectCommand.Parameters.Add("@DOB",
SqlDbType.DateTime).Value = IIf(Len(Session("ssDOB")) = 0, DBNull.Value,
Session("ssDOB"))
                    daMembers_SQL.SelectCommand.Parameters.Add("@SSN",
SqlDbType.NVarChar, 9).Value = Session("ssSSN")
                    daMembers_SQL.SelectCommand.Parameters.Add("@SEX",
SqlDbType.NVarChar, 1).Value = Session("ssSEX")
                    'daMembers_SQL.SelectCommand.Parameters.Add("@Debug",
SqlDbType.Bit).Value = 0
                    daMembers_SQL.Fill(dsMembers, "dbo.qMemberSelect")

4 Jan 2006 2:20 AM

Elton W

You might add other parameters to the SelectCommand in some way. One thing
you can try is that before adding those 5 parameters, clear parameter:

daMembers_SQL.SelectCommand.Parameters.Clear();

HTH

Elton Wang

Show quote

"jonefer" wrote:

> I have an ASP.NET app that searches for members using 5 parameters.
> I finally wrote a nifty Stored procedure to account for all or anything in
> between - parameters - that could be used out of 5.
>
> And the stored procedure works fine while using Query Analyzer in SQL
> Server...
>
> but now in trying to implement it using session variables because
> the textboxes for the parameters are on a different page:
> I get a "too many arguments specified" error when trying to implement this
> code in VB.NET/ADO.NET - how can I make sure it works?
>
> (this is the code I have on the Results page which is a DataGrid of the
> result from whatever the use selected on the search page)
>
>
> daMembers_SQL.SelectCommand.Parameters.Add("@MRN", SqlDbType.Int).Value =
> Session("ssMRN")
>                     daMembers_SQL.SelectCommand.Parameters.Add("@MemNAME",
> SqlDbType.NVarChar, 40).Value = Session("ssNAME")
>
>
>                     daMembers_SQL.SelectCommand.Parameters.Add("@DOB",
> SqlDbType.DateTime).Value = IIf(Len(Session("ssDOB")) = 0, DBNull.Value,
> Session("ssDOB"))
>                     daMembers_SQL.SelectCommand.Parameters.Add("@SSN",
> SqlDbType.NVarChar, 9).Value = Session("ssSSN")
>                     daMembers_SQL.SelectCommand.Parameters.Add("@SEX",
> SqlDbType.NVarChar, 1).Value = Session("ssSEX")
>                     'daMembers_SQL.SelectCommand.Parameters.Add("@Debug",
> SqlDbType.Bit).Value = 0
>                     daMembers_SQL.Fill(dsMembers, "dbo.qMemberSelect")

4 Jan 2006 9:30 AM

jonefer

That took away the error, but now I am returning no rows no matter what I type.

It seems that the parameters.add works in a way that is verry convenient
for missing parameters so that you could do something like:

    if len(Session("ssMRN")) <> 0 then
           daMembers_SQL.SelectCommand.Parameters.Add("@MRN",Sql.Int).value
= _        Session ("ssMRN")

and not even add the parameter if it was missing
which is how I am now coding it. But I can't understand why it is not
producing any results.

Show quote

"Elton W" wrote:

> You might add other parameters to the SelectCommand in some way. One thing
> you can try is that before adding those 5 parameters, clear parameter:
>
> daMembers_SQL.SelectCommand.Parameters.Clear();
>
> HTH
>
> Elton Wang
>
> "jonefer" wrote:
>
> > I have an ASP.NET app that searches for members using 5 parameters.
> > I finally wrote a nifty Stored procedure to account for all or anything in
> > between - parameters - that could be used out of 5.
> >
> > And the stored procedure works fine while using Query Analyzer in SQL
> > Server...
> >
> > but now in trying to implement it using session variables because
> > the textboxes for the parameters are on a different page:
> > I get a "too many arguments specified" error when trying to implement this
> > code in VB.NET/ADO.NET - how can I make sure it works?
> >
> > (this is the code I have on the Results page which is a DataGrid of the
> > result from whatever the use selected on the search page)
> >
> >
> > daMembers_SQL.SelectCommand.Parameters.Add("@MRN", SqlDbType.Int).Value =
> > Session("ssMRN")
> >                     daMembers_SQL.SelectCommand.Parameters.Add("@MemNAME",
> > SqlDbType.NVarChar, 40).Value = Session("ssNAME")
> >
> >
> >                     daMembers_SQL.SelectCommand.Parameters.Add("@DOB",
> > SqlDbType.DateTime).Value = IIf(Len(Session("ssDOB")) = 0, DBNull.Value,
> > Session("ssDOB"))
> >                     daMembers_SQL.SelectCommand.Parameters.Add("@SSN",
> > SqlDbType.NVarChar, 9).Value = Session("ssSSN")
> >                     daMembers_SQL.SelectCommand.Parameters.Add("@SEX",
> > SqlDbType.NVarChar, 1).Value = Session("ssSEX")
> >                     'daMembers_SQL.SelectCommand.Parameters.Add("@Debug",
> > SqlDbType.Bit).Value = 0
> >                     daMembers_SQL.Fill(dsMembers, "dbo.qMemberSelect")