Multiple Parameters with data adapters

[Idethrad]

Hi all hope someone can help me with this I am totally stuck. Basically I am trying to filter a single table containing a mix of empty cells and cells with data in them. I have constructed an SQL statement to pass through an OleDBSelectCommand and a DataAdapter, which I have checked out many times and in Access and the Query builder so am pretty sure it should work.

The problem comes when I try to integrate it into my code. Testing of my code produces only the "Parameter *** has no default value." Error, the value of which is the name of the next empty parameter that I passed to the .fill method.

"
Essentially I am looking to create a function that can take a variable number of parameters (1-5) and filter the table for a row that matches these parameters exactly.
"

Initially I had hoped to simply use a SELECT statement that compares each parameter to each field (column1 = @param1) AND ...
but the NULL values didn't seem to mix and I couldnt find a way around it.
If this comparison can be done it would also solve the above problem.

I have been wrestling with this for 2 days now with no success, any help would be much appreciated.

(I am trying to do this in the data tier of my application.)

 

[cjard]

Read this:
http://www.vbdotnetforums.com/showthread.php?t=22373&highlight=dynamic+sql

It will give the germ of an idea for how to make your variable parameter search..

BUT

youre using access, which aside from being crap.. Is crap.

When you make an access sql statement:

Select * From Table Where (column = ? or ? is null) and (column2 = ? or ? is null)

It is the order of addition that matters. Access doesnt use named parameters, so you CANNOT reuse parameters like in real databases like SQL server

As such, once you make your SQL, you should:

clear the parameters collection (in case in added a load for you.. look in the debugger, because it might well have done so)
add each parameter twice:

Public Sub Search(name As String, age as Integer)
cmd.Parameters.AddWithValue("nameVal", name)
cmd.Parameters.AddWithValue("nameNull", name)
cmd.Parameters.AddWithValue("ageVal", age)
cmd.Parameters.AddWithValue("ageNull", age)

Get it? your SQL contains four question marks so you need 4 params but they work in pairs. I gave them names (xxxVal /xxxNull) though they wont be used, just so you can see which one goes with which

Now if we pass Nothing in for the values:

Search(Nothing, 23)

the SQL will theoretically end up like:



Select * From Table Where (column = NULL or NULL is null) and (column2 = 23 or 23 is null)


See how this works? In real DB like Oracle, sqlserver you can reuse the params as per that link i posted.. Like I said, access is crap