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("@MemNA ME",
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("@Debu g",
SqlDbType.Bit).Value = 0
daMembers_SQL.Fill(dsMembers, "dbo.qMemberSelect")

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:

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: