Select problem using Parameters for multiple Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Get Error "No value given for one or more required parameters."

Code:
SELECT * from PatientSummary WHERE PatientSummaryDate=(SELECT
max(PatientSummaryDate) FROM PatientSummary WHERE MasterPatientID=?) AND
MasterPatientID=?
Then:
OleDbSelectCommand1.Parameters("MasterPatientID").Value = MasterPatientID

Appears that Parameter value not applied to both "?"

Thanks
 
Nope, that counts as two parameters. If you check the paramaters.count
property, you'll see there's just one, but the query is expecting two
because it can't differentiate and know that the one param is supposed to
work for both- otherwise it could cause some real trouble. If you forgot to
add a param to the collection and you actually were looking to use two or
more params - then you wouldn't have any indication that something was wrong
but it would use the same value for both parameters and your query would
return stuff you weren't expecting.
 
Thanks,
i used the "Configure Data Adapter" Wizard that as you correctely state
only generated one parameter.
I added a second parameter and all worked well.

much appreciated
 
Użytkownik "gavin said:
Get Error "No value given for one or more required parameters."

Code:
SELECT * from PatientSummary WHERE PatientSummaryDate=(SELECT
max(PatientSummaryDate) FROM PatientSummary WHERE MasterPatientID=?) AND
MasterPatientID=?
Then:
OleDbSelectCommand1.Parameters("MasterPatientID").Value = MasterPatientID

Appears that Parameter value not applied to both "?"
Click to expand...

Another solution than adding two parameters is to change sql, for example:

SELECT
P.*
From
PatientSummary As P
Inner Join
(SELECT
MasterPatientId ,
max(PatientSummaryDate) As MaxD
FROM PatientSummary
GROUP BY MasterPatientId) As M
ON
P.PatientSummaryDate = M.MaxD
And
P.MasterPatientId = M.MasterPatientID
WHERE
MasterPatientID=?

You can change Where clause to:

WHERE
MasterPatientID=?
Or
MasterPatientID=0

After this modification if you pass 0 to the command, you will get all
clients.

Regards,
Grzegorz
 
Back
Top