Solved

Insert items from listview into database.

Posted on 2009-05-06
16
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 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