asked on

Insert items from listview into database.

I am trying to add items from my listview to my data base. I do not know how to do this.
Listview colums
0- ID
1- Description
2- Labor
3- Length
4- Width
5- Depth
6- Volume OF Dent
7- Price

var
  sum, cpi : Single;
  l,w,d : Single;
  ListItem: TListItem;
  i: integer;
  s: double;
begin
  if edit1.Text ='' then
   ShowMessage('Please Input A Value For Length')
  else
  begin
    l := strtofloat(edit1.Text);
    w := strtofloat(edit2.Text);
    d := strtofloat(edit3.Text);
    sum := (2*l*w) + (2*w*d) + (2*l*d);
    labelededit1.Text:='$' + (floattostr(sum* strtoint(frmsetup.Edit1.Text)));
 
     ListItem := form1.ListView1.Items.Add;
     with ListItem do begin
        datamodule1.AQGlobalID.SQL.Clear;
        Datamodule1.AQGlobalID.SQL.Add('Select * From GlobalEstID');
        datamodule1.AQGlobalID.Open;
        DataModule1.AQGlobalID.Edit;
     Caption := Datamodule1.AQGlobalID.fieldbyname('GlobalID').AsString;// first column
     SubItems.Add(Form1.Label1.Caption);
     SubItems.Add('0.0');
     SubItems.Add(edit1.Text);
     SubItems.Add(Edit2.Text);
     SubItems.Add(Edit3.Text);
     SubItems.Add(floattostr(sum)) ;
     SubItems.Add(floatToStr(sum* StrToInt(Frmsetup.Edit1.Text)));
      begin
        with datamodule1.AQEstimates do
        try
        if active then
        close;
        SQL.Text := 'INSERT INTO Estimates (EstimateID) VALUES (:EstimateID)';
        Parameters.ParamByName('EstimateID').Value := Datamodule1.AQGlobalID.fieldbyname('GlobalID').AsString;
        SQL.Text := 'INSERT INTO Estimates (Description) VALUES (:Description)';
        Parameters.ParamByName('Description').Value := 0 subitem
        SQL.Text := 'INSERT INTO Estimates (Labor) VALUES (:Labor)';
        Parameters.ParamByName('Labor').Value := 1st subitem
        SQL.Text := 'INSERT INTO Estimates (Length) VALUES (:Length)';
        Parameters.ParamByName('Length').Value := 2nd subitem
        SQL.Text := 'INSERT INTO Estimates (Width) VALUES (:Width)';
        Parameters.ParamByName('Width').Value := 3rd subitem
        SQL.Text := 'INSERT INTO Estimates (Depth) VALUES (:Depth)';
        Parameters.ParamByName('Depth').Value := 4th subitem
        SQL.Text := 'INSERT INTO Estimates (VolumOfDent) VALUES (:VolumeOfDent)';
        Parameters.ParamByName('VolumeOfDent').Value := 5th subitem
        SQL.Text := 'INSERT INTO Estimates (Price) VALUES (:Price)';
        Parameters.ParamByName('Price').Value := 5th subitem
        ExecSQL
        finally
        Close
      end;
     frmDentRepair.Close;
 
     end;
     form1.calctotals;
  end;
 
end;

Open in new window

ASKER CERTIFIED SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Grant Fullen

ASKER

Thanks for help formating the SQL but that does not help me learn how to assign the correct value from each listview subitem so i can insert it into the database.
Thanks for your reading tips.

Grant Fullen

ASKER

Do you really anticipate only getting one row back from that initial SELECT query? You are only processing one row.

Yes one row is all i need to do. It is one line item of an estimate.......

Geert G

davizinx, you really need to see a doctor about your indentation ...

li: TListItem;

Parameters.ParamByName('EstimateID').Value := li.Caption;
Parameters.ParamByName('Description').Value := li.SubItems[0];
Parameters.ParamByName('Labor').Value := li.SubItems[1];
Parameters.ParamByName('Length').Value := li.SubItems[2];
Parameters.ParamByName('Width').Value := li.SubItems[3];
Parameters.ParamByName('Depth').Value := li.SubItems[4];
Parameters.ParamByName('VolumeOfDent').Value := li.SubItems[5];
Parameters.ParamByName('Price').Value := li.SubItems[6];

Grant Fullen

ASKER

I am geting access violation with this code what am I doing wrong here.

var
  sum, cpi : Single;
  l,w,d : Single;
  ListItem: TListItem;
  i: integer;
  s: double;
  li: TListItem;
begin
  if edit1.Text ='' then
   ShowMessage('Please Input A Value For Length')
  else
  begin
    l := strtofloat(edit1.Text);
    w := strtofloat(edit2.Text);
    d := strtofloat(edit3.Text);
    sum := (2*l*w) + (2*w*d) + (2*l*d);
    labelededit1.Text:='$' + (floattostr(sum* strtoint(frmsetup.Edit1.Text)));
 
     ListItem := form1.ListView1.Items.Add;
     with ListItem do begin
        datamodule1.AQGlobalID.SQL.Clear;
        Datamodule1.AQGlobalID.SQL.Add('Select * From GlobalEstID');
        datamodule1.AQGlobalID.Open;
//        DataModule1.AQGlobalID.Edit;  // Why are you doing this?
     Caption := Datamodule1.AQGlobalID.fieldbyname('GlobalID').AsString;// first column
     SubItems.Add(Form1.Label1.Caption);
     SubItems.Add('0.0');
     SubItems.Add(edit1.Text);
     SubItems.Add(Edit2.Text);
     SubItems.Add(Edit3.Text);
     SubItems.Add(floattostr(sum)) ;
     SubItems.Add(floatToStr(sum* StrToInt(Frmsetup.Edit1.Text)));
      begin
        with datamodule1.AQEstimates do
        try
        if active then close;
        SQL.CLEAR;
        SQL.Add('INSERT INTO Estimates ');
        SQL.Add('( ');
        SQL.Add(' EstimateID, ');
        SQL.Add(' Description, ');
        SQL.Add(' Labor, ');
        SQL.Add(' Length, ');
        SQL.Add(' Width, ');
        SQL.Add(' Depth, ');
        SQL.Add(' VolumOfDent, ');
        SQL.Add(' Price ');
        SQL.Add(') ');
        SQL.Add('VALUES ');
        SQL.Add('( ');
        SQL.Add(' :EstimateID)');
        SQL.Add(' :Description)');
        SQL.Add(' :Labor)');
        SQL.Add(' :Length)');
        SQL.Add(' :Width)');
        SQL.Add(' :Depth)');
        SQL.Add(' :VolumeOfDent)');
        SQL.Add(' :Price)');
        SQL.Add('}; ');
        Parameters.ParamByName('EstimateID').Value := li.Caption;
        Parameters.ParamByName('Description').Value := li.SubItems[0];
        Parameters.ParamByName('Labor').Value := li.SubItems[1];
        Parameters.ParamByName('Length').Value := li.SubItems[2];
        Parameters.ParamByName('Width').Value := li.SubItems[3];
        Parameters.ParamByName('Depth').Value := li.SubItems[4];
        Parameters.ParamByName('VolumeOfDent').Value := li.SubItems[5];
        Parameters.ParamByName('Price').Value := li.SubItems[6];
        ExecSQL
        finally
        Close
      end;
     frmDentRepair.Close;
 
     end;
     form1.calctotals;
  end;
  end;
end;

Open in new window

8080_Diver

Try putting "Prepare;" between lines a 59 and 60. Also, step through the code (put a breakpoint at line 34) and make sure that the TADOQUERY is assigned (i.e. not Nil).

Grant Fullen

ASKER

I set the breakpoint. I run it and step through it and it breaks at line 60 .
I tried puting Prepare; and it gives undeclared Identifier;
Thanks for your patience.

Grant Fullen

ASKER

I am able to put Prepared; there but still same error.

Grant Fullen

ASKER

Anyone have an ideal why i am getting this error ?

8080_Diver

You may need to add a Para
You may need to add the parameters to the query one you have put the SQL Text into it. If so, you would first need to clear the Parameters so that you don't wind up just adding the new parameters to the already existing list.
You also might want to make sure that the ParamCheck setting is set to True on the TADOQuery.

Finally, another option might be to access the Parameters based upon their order in the SQL Statement using Parameters.Items[0].Valu, for instance, to set the value for the EstimateID parameter.

Grant Fullen

ASKER

After changing the code some I have this error .

Number of query values and desanation fields are not the same.

Any thoughts ??????

var
  sum, cpi : Single;
  l,w,d : Single;
  ListItem: TListItem;
  i: integer;
  s: double;
  li: TListItem;
begin
  if edit1.Text ='' then
   ShowMessage('Please Input A Value For Length')
  else
  begin
    l := strtofloat(edit1.Text);
    w := strtofloat(edit2.Text);
    d := strtofloat(edit3.Text);
    sum := (2*l*w) + (2*w*d) + (2*l*d);
    labelededit1.Text:='$' + (floattostr(sum* strtoint(frmsetup.Edit1.Text)));
 
     ListItem := form1.ListView1.Items.Add;
     with ListItem do begin
        datamodule1.AQGlobalID.SQL.Clear;
        Datamodule1.AQGlobalID.SQL.Add('Select * From GlobalEstID');
        datamodule1.AQGlobalID.Open;
//        DataModule1.AQGlobalID.Edit;  // Why are you doing this?
     Caption := Datamodule1.AQGlobalID.fieldbyname('GlobalID').AsString;// first column
     SubItems.Add(Form1.Label1.Caption);
     SubItems.Add('0.0');
     SubItems.Add(edit1.Text);
     SubItems.Add(Edit2.Text);
     SubItems.Add(Edit3.Text);
     SubItems.Add(floattostr(sum)) ;
     SubItems.Add(floatToStr(sum* StrToInt(Frmsetup.Edit1.Text)));
      begin
        with datamodule1.AQEstimates do
        try
        if active then close;
        SQL.CLEAR;
        SQL.Add('INSERT INTO Estimates ');
        SQL.Add('( ');
        SQL.Add(' EstimateID, ');
        SQL.Add(' Description, ');
        SQL.Add(' Labor, ');
        SQL.Add(' Length, ');
        SQL.Add(' Width, ');
        SQL.Add(' Depth, ');
        SQL.Add(' VolumOfDent, ');
        SQL.Add(' Price ');
        SQL.Add(') ');
        SQL.Add('VALUES ');
        SQL.Add('( ');
        SQL.Add(' :EstimateID)');
        SQL.Add(' :Description)');
        SQL.Add(' :Labor)');
        SQL.Add(' :Length)');
        SQL.Add(' :Width)');
        SQL.Add(' :Depth)');
        SQL.Add(' :VolumeOfDent)');
        SQL.Add(' :Price)');
        SQL.Add('); ');
        li:=form1.listview1.items[0];
        with li do
        begin
 
 
        Parameters.ParamValues['EstimateID']:=li.caption;
        Parameters.ParamValues['Description']:=li.SubItems[0];
        Parameters.ParamValues['Labor']:=li.SubItems[1];
        Parameters.ParamValues['Length']:=li.SubItems[2];
        Parameters.ParamValues['Width'] :=li.SubItems[3];
        Parameters.ParamValues['Depth'] :=li.SubItems[4];
        Parameters.ParamValues['VolumeOfDent']:=li.SubItems[5];
        parameters.ParamValues['Price']:=li.SubItems[6];
        end;
 
        ExecSQL
        finally
        Close
      end;
     frmDentRepair.Close;
 
     end;
     form1.calctotals;
  end;
  end;
end;

Open in new window

Grant Fullen

ASKER

I will give 250 more points to who ever helps me fix this. I need to resolve this and move on to other parts of this project.

Geert G

you forgot commas

SQL.CLEAR;
        SQL.Add('INSERT INTO Estimates ');
        SQL.Add('( ');
        SQL.Add(' EstimateID, ');
        SQL.Add(' Description, ');
        SQL.Add(' Labor, ');
        SQL.Add(' Length, ');
        SQL.Add(' Width, ');
        SQL.Add(' Depth, ');
        SQL.Add(' VolumOfDent, ');
        SQL.Add(' Price ');
        SQL.Add(') ');
        SQL.Add('VALUES ');
        SQL.Add('( ');
        SQL.Add(' :EstimateID');
        SQL.Add(', :Description');
        SQL.Add(', :Labor');
        SQL.Add(', :Length');
        SQL.Add(', :Width');
        SQL.Add(', :Depth');
        SQL.Add(', :VolumeOfDent');
        SQL.Add(', :Price');
        SQL.Add('); ');
        li:=form1.listview1.items[0];

Open in new window

SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

the ALT-mouse select only works within Delphi or other advanced editors

Grant Fullen

ASKER

Thanks