Zenyl Zenyl - 7 months ago 37
SQL Question

Error: "No value given for one or more required parameters" when updating Database

I am currently working with a small program where a field must be selected of a DBGrid and then with the help of an inputbox, the value can be updated.

The Database has a field called

that has the primary key. The code must identify the field name of the field and the case number of the selected row, with that information, the database can be updated.

Here is a copy of my code:

procedure TfrmManagePatientInformation.btnChangeClick(Sender: TObject);
var NewValue, Fieldname : String;
CaseNumber : Integer;
// Make sure that the Case Number will not be changed
if DBGridPatients.SelectedField.FieldName = 'CaseNumber' then
MessageDlg('The Case Number of a patient cannot be changed!',mtError,[mbOK],0);
// Get Fieldname
Fieldname := DBGridPatients.SelectedField.FieldName;

// Get Case Number of the selected row
CaseNumber := StrToInt(DBGridPatients.Fields[0].Value);

// Get new value
NewValue := InputBox('New Value','Please enter the new value!','');

// Change the value permanently
PatientQuery.Active := False;
PatientQuery.SQL.Text := 'Update CurrentPatients SET ' + QuotedStr(FieldName) + ' = ' + QuotedStr(NewValue) + ' where CaseNumber = :CaseNumber';
PatientQuery.Parameters.ParamByName('CaseNumber').Value := CaseNumber;

MessageDlg('Information changed successfully!',mtConfirmation,[mbOK],0);

Each time I run it, it gives me an error:

No value given for one or more required parameters.

What can I do to solve this?


You must not use QuotedStr with FieldName.
You should also consider using a parameter for NewValue e.g.:

PatientQuery.SQL.Text := 'Update CurrentPatients SET ' + FieldName + ' = :NewValue where CaseNumber = :CaseNumber';
PatientQuery.Parameters.ParamByName('CaseNumber').Value := CaseNumber;
PatientQuery.Parameters.ParamByName('NewValue').Value := NewValue;

This will also keep you safe from a potential sql injection