B4J Question One INSERT SQL Query for Dynamique table

david13

Member
Licensed User
Hi All,
I'm trying to find a solution to this problem from a long time and could not find anything,
I want to send in one insert SQL query all the table lines, I can do it when I know the table fixed lines number, but in case of a dynamic table, it impossible for me to do it,
Thanks for your help
 

david13

Member
Licensed User
Can you give a small example of the table before the change and after it?
Hi Erel, Thanks for the answer,

I send a table with two columns, one for the product name, and the other contains a number ( how much that product was sold)

The problem is the number of products changes depending on the company that is using my software,

When the user opens the software, all the product are loaded on a table, and since the number of product changes, the number of lines changes and so I can't use an INSERT query with a fixed number of columns,
 
Upvote 0

david13

Member
Licensed User
The number of columns shouldn't be changed. Only the number of rows which is equal to the number of products, right?

I'm not sure that I understand the problem...
Yes you are right, attached my table, but I'll explain only by two colonmns and two lines,


Product table ( when loaded by my customer number 1)
detergent 1 4 USD
detergent 2 3 USD
detergent 3 2 USD

Product table (same table) ( when loaded by my customer number 2)

Pillow 1 10 usd
Pillow 2 8 usd



Code to insert all the lines for the Product table ( when loaded by my customer number 1)

B4X:
INSERT INTO Amir_Test3_SQL_Stock (Produit,Stock, Date)  VALUES ('"&GetLabel(i,0).Text&"','"&liste_tableau_stock.Get(i)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+1,0).Text&"','"&liste_tableau_stock.Get(i+1)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+2,0).Text&"','"&liste_tableau_stock.Get(i+2)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+3,0).Text&"','"&liste_tableau_stock.Get(i+3)&"','"&DatePicker.GetDate&"')", Insert_Stock_Today_From_Produit)

Code to insert all the lines for the Product table ( when loaded by my customer number 1)

B4X:
INSERT INTO Amir_Test3_SQL_Stock (Produit,Stock, Date)  VALUES ('"&GetLabel(i,0).Text&"','"&liste_tableau_stock.Get(i)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+1,0).Text&"','"&liste_tableau_stock.Get(i+1)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+2,0).Text&"','"&liste_tableau_stock.Get(i+2)&"','"&DatePicker.GetDate&"')", Insert_Stock_Today_From_Produit)

As you can see the code changes depending on the number of the table lines, I want to have only one code for different lines!

I can send line by line using a for loop, but as I wrote in a different post, the problem is that if internet connection problem, some lines are sent and others no, while when I use insert for all the lines in the same time, I'll rather send all the lines or don't send anything!

Hope I could explain the problem :)
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
Upvote 0

david13

Member
Licensed User
Upvote 0
Top