Zenyl Zenyl - 3 months ago 8
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

CaseNumber
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;
begin
// Make sure that the Case Number will not be changed
if DBGridPatients.SelectedField.FieldName = 'CaseNumber' then
begin
MessageDlg('The Case Number of a patient cannot be changed!',mtError,[mbOK],0);
Exit;
end
else
begin
// 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;
PatientQuery.ExecSQL;

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?

Answer

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

Comments