too many arguments specified...

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")

EltonW
Tue Jan 03 20:20:03 CST 2006

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")

jonefer
Wed Jan 04 03:30:03 CST 2006

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.

"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")