PenelopeSkye

Hi James, our company has paid to have our code converted to c#, so I won't be posting here anymore.  I just wanted to thank you for all your help and support.  If there is any feedback I can leave anywhere please let me know, but otherwise I am taking it all as a big personal favor!!! 

I just got back!!  Since I don't understand what you are talking about I can't weigh in, but I am very very happy to say that the code works as expected! 🙂

Thank you both for weighing in!!!

Duuuude!  You are a genius!!! Thank you!!!

I tried looping through the dataset and comparing it to the value but the UI behaved very oddly, Sorry, I am not making it easy for you to help, I should probably go home and come back when I feel better!

I am not using it for the memo box. I am clicking on a field with some text. I need to compare the value from that field to a list of values from the database.

Wouldn't that call for a dynamic array?

Thank you!

On 4/11/2023 at 3:22 PM, programmerdelphi2k said:

Hi James, I went back to your code as I needed exactly what you wrote and I still don't get it. I'm finally past embarrassment and so am asking how to populate that array using a query because I can't get it to work.

type
  TMyRecToStoreSomeValues2 = record
    ValInt: integer;
    ValString: string;
    ValDate: TDate;
    // etc...
  end;

  TMyValuesInArray = TArray<TMyRecToStoreSomeValues2>;

function HelloWorld: TMyValuesInArray;
var
  LMyValue : TMyRecToStoreSomeValues2;
  LMyValues: TMyValuesInArray;
begin
  LMyValues := [ dm.q_GetStockNumFromValuePackLinks.SQL.Text := 'SELECT art_status  FROM [AdiMS2].[dbo].[art_status]';
  //
  // ...
  //
  result := LMyValues;  
end;

I am able to loop through the result set from the query!  Thank you both so much!!!

Ah!  Got it!  Thanks!

Thank you both.

I get the master presentation from one table using FieldByName.

I use that master presentation to get anywhere from 1 to 8 presentations from a second table.

Then I need to loop through the dynamic list of presentations and update the row in the second table.

How would I get a dataset with this information without using a query?

Thanks James!!!  Unfortunately I haven't successfully gotten the query results into the dynamic array.

Did you explain how above and I'm so ignorant I didn't see it?  That is entirely possible!!!!!

I need to loop through a dynamic array which is populated by the result of a query.  I have gotten as far as what you see below, but I keep getting an error 

           Incompatible types: 'Char' and 'Variant'

at the end of the following line

           Result := q_InsValuePackLinks2.Fields[i].Value;

I have to admit that I changed a lot of this messing around to the point where I don't know what other parts are wrong, so I thought I'd ask for help!

How do I create a dynamic array from q result set?

Thanks!
 

procedure TfDesignMaster.btnVP_PostClick(Sender: TObject);
 
 var b: array of string;
Begin
         var i,j,numberOfFields: integer;
        var PresentationID,designid,master_pres,result,presentations:string;
        with dm do

            begin
              master_pres :=q_GetStockNumFromValuePackLinks.FieldByName('master_presentation').AsString;
              q_InsValuePackLinks2.SQL.Add('select presentation from presentation_master where master_presentation = '+master_pres);
              q_InsValuePackLinks2.Open;

              presentations := q_InsValuePackLinks2.Fields.ToString;
              SetLength(Result,i);
              for i := 0 to q_InsValuePackLinks2.Fields.Count - 1 do
                Result[i] := q_InsValuePackLinks2.Fields[i].Value;
            end;
end;

Doh!  It should have been obvious that that button is what brackets code!  Thank you!  

I am going to start studying the code you gave my above until I understand it, thanks again James!

Hi James, I hope you will see this, I will wait a bit then post is in a new topic.

I have a table with the following fields.  It was not my design choice but I was overruled.

I needed to search every pack_component field in every row for a specific value, so I chose union to bring that about.

You gave me most of the code I used to create this procedure which finds the value and its' position and display it in a memo box.

The code works perfectly but every now and then I get the attached error.

Can you tell me why since the code works?  Thank you!

[vp_id]
,[Presentation]
,[Design_id]
,[Stock_pack]
,[Order1],[Pack_component1],[Qty1]
,[Order2],[Pack_component2],[Qty2]
,[Order3],[Pack_component3],[Qty3]
,[Order4],[Pack_component4],[Qty4]
,[Order5],[Pack_component5],[Qty5]
,[Order6],[Pack_component6],[Qty6]
,[Order7],[Pack_component7],[Qty7]
,[Order8],[Pack_component8],[Qty8]
,[Order9],[Pack_component9],[Qty9]
,[Order10],[Pack_component10],[Qty10]
,[Order11],[Pack_component11],[Qty11]
,[Order12],[Pack_component12],[Qty12]

procedure TfDesignMaster.PopulatePdlNotesWithValuePack2(Sender: TObject);
begin
//var designid: string;
var
stocknummm,LText1,LText2,StockFromVP: string;
var
F: TField;
  stocknummm := dm.tb_design_master.fieldbyname('jmc_stock_num').asstring;
  StockFromVP := dm.tblValuePackLinks.fieldByName('Stock_pack').asString;
  Panel35.Visible :=false;
  Memo1.Clear;
  //ShowMessage(stocknummm);

    with  dm do
  begin

    q_GetStockNumFromValuePackLinks.SQL.Clear;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 1 from ValuePackLinks where Pack_component1 = ' +stocknummm ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union' ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 2 from ValuePackLinks where Pack_component2 = ' +stocknummm  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union  ' ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 3 from ValuePackLinks where Pack_component3 = ' +stocknummm ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union '  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 4 from ValuePackLinks where Pack_component4 = ' +stocknummm  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union '  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 5 from ValuePackLinks where Pack_component5 = ' +stocknummm  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union'   ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 6 from ValuePackLinks where Pack_component6 = ' +stocknummm  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union' ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 7 from ValuePackLinks where Pack_component7 = ' +stocknummm  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union' ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 8 from ValuePackLinks where Pack_component8 = ' +stocknummm ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union' ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 9 from ValuePackLinks where Pack_component9 = ' +stocknummm  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union' ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 10 from ValuePackLinks where Pack_component10 = ' +stocknummm  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union' ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 11 from ValuePackLinks where Pack_component11 = ' +stocknummm  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('union'  ) ;
    q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,stockOrder = 12 from ValuePackLinks where Pack_component12 = ' +stocknummm ) ;

      //ShowMessage(   q_GetStockNumFromValuePackLinks.SQL.Text)  ;
      q_GetStockNumFromValuePackLinks.Open;

    while not q_GetStockNumFromValuePackLinks.EOF do begin
          for F in q_GetStockNumFromValuePackLinks.Fields do begin

       LText1 := '';
      //
      for var i: integer := 0 to (q_GetStockNumFromValuePackLinks.Fields.Count - 1) do // field name + field value
        LText1            :=  q_GetStockNumFromValuePackLinks.Fields[0].Value;
        LText2            :=  q_GetStockNumFromValuePackLinks.Fields[1].Value;
        //ShowMessage(LText1);

      dm.q_GetStockNumFromValuePackLinks.Next; // to avoid "infinity looping" and catch next values...
    end;
                  //ShowMessage(LText1);
                  if    q_GetStockNumFromValuePackLinks.Fields[0].Value <> ''
                  then
                  Panel35.Visible :=true
                  else
                  Panel35.Visible :=false;

           q_GetStockNumFromValuePackLinks.Next;
        end;

        Memo1.Lines.Add('Bag '+stocknummm+' is in Value Pack ('+LText1+') in Position (' +LText2+')' );

                  q_GetStockNumFromValuePackLinks.Close;

         end;

end;

I created a test database and pointed the connection string to it, and I found 1 other place where the database name is mentioned after searching in all files.

Unfortunately although some of the code works to insert into the database, not everything does, and I am thinking that there are other places indicating the database that I am not finding by using the search function.

Does anyone know how to find other places where code is pointed to a database or do I need to search through every object?

Thanks!

LOL!!! Will do!

I was working with Master\Detail but I was seriously out of time when someone pointed out that there is already a function the refreshes the data used by the app. I used that function and it works perfectly 🤦‍♀️

I am still going to work with Master\Detail in the future cause now I know about it I want to figure it out!

Thanks James!

I am still getting errors but I will start up again Monday!  I will solve it!

I will go back to studying the master detail thingee!  Thanks Mr. Bond!

Ooh!  I will try closing and opening first!

All 5 fields are set to accept null values, only the 3 are not null and I always have that data.

They are indeed being inserted as new rows into another table, not the tables that the 3 fields are originally populated from

I am going to start investigating the master detail relationship!

Thanks James!

Long story short: How can I refresh a dbedit box with data from a table I have just inserted data into?

• Long story long: My sql table and UI has fields for PresentationID, DesignID, StockID plus 5 other initially blank fields. 
• With no input from the user I populate the PresentationID, DesignID, StockID fields in the database using values gotten from elsewhere in the application, the 5 fields are populated with null because they are not included in the insert (see code below).  If I did include them they would be null anyway since I don't enter data into them until after the row is inserted with the 3 values.
• After I insert values into the database using the code below I am able to enter text in the other 5 fields or update the 3 fields but when I click on another tab in the application, and leave the tab I am on, the code written elsewhere that posts the data kicks in and attempts to update the database with the values any of the 8 fields. At that point I get the following
•           Cannot insert the value NULL into column 'Presentation', table 'AdiMS2.dbo.ValuePackLinks'; column does not allow nulls. INSERT fails.
• because the application doesn't recognize the values just inserted for  PresentationID, DesignID, StockID.
• If I leave the app and come back in the 3 fields are populated with data and the post works just fine with values from the 8 fields.
• How can I refresh a dbedit box with data from a table I have just inserted data into so that I can immediately start editing and not have to leave the app?

Does that make sense?

Thank you!

 PresentationID := dm.tb_dm_presentation_design_links.fieldbyname('presentation').asstring;
 DesignID := dm.tb_design_master.fieldbyname('design_id').asstring;
 StockID := dm.tb_design_master.fieldbyname('jmc_stock_num').asstring;
 

  q_InsValuePackLinks.SQL.Add ('insert into ValuePackLinks   ');
  q_InsValuePackLinks.SQL.Add ('(Presentation,Design_id,Stock_pack)');
  q_InsValuePackLinks.SQL.Add ('VALUES('+Quotedstr(PresentationID)+','+Quotedstr(DesignID)+','+Quotedstr(StockID)+')');
   q_InsValuePackLinks.ExecSQL ;
  
       
  

LOL!!!!!!  Well thank you James!!!!!

I spoke too soon!  It works!  It feels too impersonal to thank your handle, so I shall call you Victor. 

Thank you so much Victor!!!

I have started working with your code, thank you so much for adding comments!!!  I am having some issues that I am working on, I will post back here if I can't solve them if you wouldn't mind.

Thank you!

Since you are kind enough to look into getting values from an array this is what I have so far. I can get the 2 values I want n each pass of the loop, but I want to be able to put both values together in a sentence and display them in a memo box.

You were kind enough to provide code but will it work if I use the code below to get the both values into a sentence since it is already set up and the code above makes my brain hurt?  I wish I were more experienced! Thanks!

procedure TfDesignMaster.PopulatePdlNotesWithValuePack(Sender: TObject);
begin
//var designid: string;
var
stocknummm: string;
var
F: TField;
  stocknummm := dm.tb_design_master.fieldbyname('jmc_stock_num').asstring;
  ShowMessage(stocknummm);
    with  dm do
    Begin

      q_GetStockNumFromValuePackLinks.SQL.Clear;
      q_GetStockNumFromValuePackLinks.SQL.Add('select Stock_pack,Qty1 from ValuePackLinks where Pack_component1 = '+stocknummm) ;
      q_GetStockNumFromValuePackLinks.Open;
               while not q_GetStockNumFromValuePackLinks.EOF do begin
    for F in q_GetStockNumFromValuePackLinks.Fields do begin
    ShowMessage(F.Value);
     q_GetStockNumFromValuePackLinks.Next;
  end;
    q_GetStockNumFromValuePackLinks.Close;
   end;
    End;
end;