Solved

Insert items from listview into database.

Posted on 2009-05-06
16
292 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now