CODEWITHSUNDEEP
delphidelphi-7
MADDOG5910
MADDOG5910

Reputation: 11

Any reason why this should not work? SQL will not add record to database

so I have another problem that blows my mind why it will not work, but you all are smarter than me so..

the code is supposed to add new records to my database, it is a budgeting program and it asks the user the date of the income they have received, the user's ID, the name and category of the income and the amount that was received, and I am using SQL to add it into a ms access database. Take a look and please help!

Here is the code:

procedure TForm1.BitBtn8Click(Sender: TObject);
var
 iNameOfIncome, iCategory, iDate :integer;
 sDate, sAmount :string;
 dDate :TDate;
begin
 with dmRecords do
  begin
   sAmount := Edit7.Text;
   iNameOfIncome := ComboBox10.ItemIndex + 1;
   iCategory := ComboBox2.ItemIndex + 1;
   dDate := DateTimePicker1.Date;
   sDate := DateToStr(dDate);

   qryRecords.SQL.Clear;
   qryRecords.Active := False;
   qryRecords.SQL.Add('INSERT INTO [Income (Ledger)](DateofIncome, [User ID], [Income ID], [Category ID], [Income Amount]');
   qryRecords.SQL.Add('VALUES (' + sDate + ',' + IntToStr(SpinEdit2.Value) + ',' + IntToStr(iNameOfIncome) + ',' + IntToStr(iCategory) + ',' + sAmount + ')');
   qryRecords.ExecSQL;
   qryRecords.SQL.Clear;
   qryRecords.SQL.Add('SELECT * FROM [Income (Ledger)] ORDER BY IncomeNo');
   qryRecords.Active := True;
  end;

Thank you in advance!

Upvotes: 0

Views: 209

Answers (1)

Imants Gulbis
Imants Gulbis

Reputation: 87

I think you should rewrite it like this

  qryRecords.Close;
  qryRecords.SQL.Text :=
  'INSERT INTO [Income (Ledger)](DateofIncome, [User ID], [Income ID], [Category ID], [Income Amount]) ' +
  'VALUES (:DateofIncome,:User_ID,:Category_ID,:Income_ID,:Income_Amount)';
  qryRecords.ParamByName('DateofIncome').AsDateTIme := dDate;
  qryRecords.ParamByName('User_ID').AsInteger := SpinEdit2.Value;
  qryRecords.ParamByname('Category_ID').AsInteger := iCategory;
  qryRecords.ParamByname('Income_ID').AsInteger := iNameOfIncome;  
  qryRecords.ParamByname('Income_Amount').AsFloat:= sAmount;
  qryRecords.ExecSQL;

  qryRecords.SQL.Text := 'SELECT * FROM [Income (Ledger)] ORDER BY IncomeNo';
  qryRecords.Open;

This way you will pass right types to DB. Otherwise you do not know how db will interpret what you are sending to it.

Upvotes: 1

Related Questions