[Error] - No value given to one or more required parameters.

[hisok]

I have this function named NotYetInRecord that checks the data if it is already existing in database(MS Access 2007).
Whenever I try to run my windows app, I always getting exception "No value given to one or more required parameters.".


Public Function NotYetInRecord(ByVal _patient As Object) As Boolean
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
Dim selectRecord As String = "SELECT RTRIM(LTRIM(PatientID)) FROM PersonnalDetail " & _
" WHERE LastNm = ? AND MiddleNm = ? AND FirstNm = ? AND BirthDate = ?"

Dim cmd As New OleDb.OleDbCommand(selectRecord, con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("LastNm", _patient.LastNm)
cmd.Parameters.AddWithValue("FirstNm", _patient.MiddleNm)
cmd.Parameters.AddWithValue("MiddleNm", _patient.FirstNm)
cmd.Parameters.AddWithValue("BirthDate", _patient.BirthDate)

If cmd.ExecuteScalar = 0 Then
con.Close()
Return True
Else
con.Close()
_patient.ErrorMessage = "Record is already exists. Cannot add duplicate record."
Return False
End If

Catch ex As Exception
con.Close()
MsgBox(ex.Message)
End Try
End Function

Does anyone can help me to solve this error. Thanks a lot.

 

[cjard]

Add a primary key to your table so that duplicates cannot be added
Do your data access as described in the DW2 link in my signature; set of microsoft tutorials - follow the "Create a Simple Data App" one


There's nothing immediately jumping out at me as being wrong with the code; what are the values of all the parameters? The advice given above will start you off on a route of doing your data access that is very easy, achieves a well designed structure and likely will never encounter this problem, because VS will set up the parameters etc for you.

Additionally; I don't recommend checking if a record exists before running an insert in the way you do here because it is possible that two apps will check simultaneously, and then insert simultaneously

Youre better off having the DB control this, with primary keys. Plus you get a performance boost. Simply try to insert the record and if it fails because it exists, then you know it exists, so update it
If you think the app will be 90% update, 10% insert, perform the update first and if 0 records are updated, then insert it

 

[InertiaM]

It's probably caused by adding the parameters in the wrong order. Swop the MiddleNm and FirstNm parameters around, as per your query.

Dim selectRecord As String = "SELECT RTRIM(LTRIM(PatientID)) FROM PersonnalDetail " & _
" WHERE LastNm = ? AND MiddleNm = ? AND FirstNm = ? AND BirthDate = ?"

Dim cmd As New OleDb.OleDbCommand(selectRecord, con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("LastNm", _patient.LastNm)
[B]cmd.Parameters.AddWithValue("MiddleNm", _patient.FirstNm)
cmd.Parameters.AddWithValue("FirstNm", _patient.MiddleNm)[/B]
cmd.Parameters.AddWithValue("BirthDate", _patient.BirthDate)

See this thread for more information.

 

[cjard]

Good catch! I'm ready to be amazed if this is the root cause though, because access doesnt care about the parameter names.. You can call those params anything you like, it's the order of addition that matters and as such, (IMHO) access isn't going to look at the column names, and the parameter names and work out that theyre the same and hence realise that the order of addition was wrong..
It would just return the patient ID for Mr Middle First Last instead of Mr First Middle Last (i.e. John Michael Smith when the user wants Michael John Smith)

 

[cjard]

Just a thought.. Does the access driver take anything that looks like a column, but isn't, as a parameter?

e.g. in this case if you said:

SELECT * FROM table WHERE [FsirtNm] = ?

Yes the fsirtnm is deliberately misspelled; does access think youre trying to add a parameter called fsirtnm (and hence you havent given a value for it) ?

Are all your column names in your query correctly spelled? I know this has tripped me up in Access (the GUI from Microsoft) itself, when working with mdbs before..

 

[InertiaM]

I hope it's the fact that IMHO MiddleName would not be a required field, but LastName would. Supplying the values the wrong way round would mean *potentially* supplying a empty value for the LastName parameter when _patient.MiddleNm is empty.