Solved

Insert items from listview into database.

Posted on 2009-05-06
16
309 Views
Last Modified: 2012-05-06
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
Comment
Question by:Grant Fullen
  • 9
  • 4
  • 3
16 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 250 total points
ID: 24321722
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
 

Author Comment

by:Grant Fullen
ID: 24321906
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
 

Author Comment

by:Grant Fullen
ID: 24321913
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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 24322675
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
 

Author Comment

by:Grant Fullen
ID: 24331264
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24331414
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
 

Author Comment

by:Grant Fullen
ID: 24331974
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
 

Author Comment

by:Grant Fullen
ID: 24331985
I am able to put Prepared; there but still same error.
0
 

Author Comment

by:Grant Fullen
ID: 24337670
Anyone have an ideal why i am getting this error ?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24340643
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
 

Author Comment

by:Grant Fullen
ID: 24341720
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
 

Author Comment

by:Grant Fullen
ID: 24341745
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 24352653
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
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 250 total points
ID: 24352695
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 24352697
the ALT-mouse select only works within Delphi or other advanced editors
0
 

Author Closing Comment

by:Grant Fullen
ID: 31578826
Thanks
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question