• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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

0
Grant Fullen
Asked:
Grant Fullen
  • 9
  • 4
  • 3
2 Solutions
 
8080_DiverCommented:
You need to learn a lot more about SQL, by the way.
The code I have posted will correct your SQL in the INSERT query.  
I also noted a line in the initial SELECT process that is unneeded.  (You do not need to have the query in Edit mode in order to access the data);.
Do you really anticipate only getting one row back from that initial SELECT query?  You are only processing one row.  
I am not at all sure about the rest of you code but I can tell you that the way you were trying to construct the INSERT query was not even close.  The code I supplied will get that part of the task taken care of but you need to carefully review how to work with the other things you are trying to use.  "Mastering Delphi x" books are a good place to start, as is the Help documentation for Delphi.

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;  // 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 := Datamodule1.AQGlobalID.fieldbyname('GlobalID').AsString;
        Parameters.ParamByName('Description').Value := 0 subitem
        Parameters.ParamByName('Labor').Value := 1st subitem
        Parameters.ParamByName('Length').Value := 2nd subitem
        Parameters.ParamByName('Width').Value := 3rd subitem
        Parameters.ParamByName('Depth').Value := 4th subitem
        Parameters.ParamByName('VolumeOfDent').Value := 5th subitem
        Parameters.ParamByName('Price').Value := 6th subitem
        ExecSQL
        finally
        Close
      end;
     frmDentRepair.Close;
 
     end;
     form1.calctotals;
  end;
 
end;

Open in new window

0
 
Grant FullenAuthor Commented:
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.
0
 
Grant FullenAuthor Commented:
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.......
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Geert GOracle dbaCommented:
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];
0
 
Grant FullenAuthor Commented:
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

0
 
8080_DiverCommented:
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).
0
 
Grant FullenAuthor Commented:
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.
0
 
Grant FullenAuthor Commented:
I am able to put Prepared; there but still same error.
0
 
Grant FullenAuthor Commented:
Anyone have an ideal why i am getting this error ?
0
 
8080_DiverCommented:
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.
 
0
 
Grant FullenAuthor Commented:
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

0
 
Grant FullenAuthor Commented:
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.
0
 
Geert GOracle dbaCommented:
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

0
 
Geert GOracle dbaCommented:
wouldn't this be easier as a syntax ?

hold down the ALT and use the mouse select to only copy the statement within the quotes
SQL.Text := 
  'INSERT INTO Estimates                                         '+
  ' (EstimateID, Description, Labor, Length, Width, Depth,       '+
  '  VolumOfDent, Price)                                         '+
  ' VALUES                                                       '+
  ' (:EstimateID, :Description, :Labor, :Length, :Width, :Depth, '+ 
  '  :VolumeOfDent, :Price)                                      ');

Open in new window

0
 
Geert GOracle dbaCommented:
the ALT-mouse select only works within Delphi or other advanced editors
0
 
Grant FullenAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now