This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.
checking Stocks
I want to make a simple program that check the stocks.
I have 2 tables called STOCKS.dbf and SOLD.dbf. I am using dBase for Windows for now!
In my STOCK and SOLD table I have these fields:
--------------------------
ID, Name, Qty, Total
--------------------------
now what I really need is for example you have 5x IPhone on Stock table and when you sell 1x IPhone I want it automatically to be 4x on STOCK Table.
I am using delphi 2009.
regards,
I have 2 tables called STOCKS.dbf and SOLD.dbf. I am using dBase for Windows for now!
In my STOCK and SOLD table I have these fields:
--------------------------
ID, Name, Qty, Total
--------------------------
now what I really need is for example you have 5x IPhone on Stock table and when you sell 1x IPhone I want it automatically to be 4x on STOCK Table.
I am using delphi 2009.
regards,
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It would be easier to do if you were using a different data storage (e.g. MS Access or SQL SERver 2005 Express).
Okay, you have a few of choices here:
Okay, you have a few of choices here:
- You can decrease the Qty in the Stock table when you write to the Sold table (either before or after you write the Sold record).
- When you access the Stock table to find out how many you have on hand, you can take the Stock.Qty minus the sum of the Sold.Qty's for the item.
actually I am using MySQL but when i want to test something new I use dbase...
anyway, is it possible to show me with code?
I will try to do what you said, but if you can show with code that would be great...
anyway, is it possible to show me with code?
I will try to do what you said, but if you can show with code that would be great...
Well, I don't really do mySQL, so I am not conversant in creating stored procs in mySQL . . . in fact, I don't know if you even can. ;-)
However, the following is some basic SQL that would do an INSERT into the Sold table and then decrease the amount in the Stock table, assuming that you pass the ItemNo and Qty as @ItemNo and @Qty.
Now, there is another issue you need to face. In the first place, you have indicated that both tables have the same columns . . . including a Name column in each of them. This is a bad database design; you should only have the Name in the Stock table. You have also indicated that each has a Total column . . . what is it a "Total" of?
I am currently developing an inventory database and application and I am dealing with something like the same problem, i.e. I need to know how many of whatever I have wherever. (I have multiple locations involved. ;-) I have a table (DistinctItems) that has the basic descriptive information on the Items that will be in the inventory. I have another table (ReceivedGoods) that I use for entering the goods that are received and it is linked via the Item ID to the BasicItems table. I have another table, ItemTransactions, that carries information about how many items are being taken from one location and added to another location. Then, in order to know how much I have on hand at any given location, I execute a query that subtracts the total qty in the "taken from" column for an item in a given location from the total quantity in the "added to" for the same item in the same location. The DistinctItems table does not have any Quantity On Hand information in it at all.
However, the following is some basic SQL that would do an INSERT into the Sold table and then decrease the amount in the Stock table, assuming that you pass the ItemNo and Qty as @ItemNo and @Qty.
Now, there is another issue you need to face. In the first place, you have indicated that both tables have the same columns . . . including a Name column in each of them. This is a bad database design; you should only have the Name in the Stock table. You have also indicated that each has a Total column . . . what is it a "Total" of?
I am currently developing an inventory database and application and I am dealing with something like the same problem, i.e. I need to know how many of whatever I have wherever. (I have multiple locations involved. ;-) I have a table (DistinctItems) that has the basic descriptive information on the Items that will be in the inventory. I have another table (ReceivedGoods) that I use for entering the goods that are received and it is linked via the Item ID to the BasicItems table. I have another table, ItemTransactions, that carries information about how many items are being taken from one location and added to another location. Then, in order to know how much I have on hand at any given location, I execute a query that subtracts the total qty in the "taken from" column for an item in a given location from the total quantity in the "added to" for the same item in the same location. The DistinctItems table does not have any Quantity On Hand information in it at all.
INSERT INTO Sold
(
ID,
QTY
)
VALUES
(
@ItemNo,
@Qty
);
UPDATE Stock
SET QTY = QTY - @Qty
WHERE ID = @ItemNo;
>>i want to test something new I use dbase
how can you test something new on something this ancient like dbase ?
how do you test stored procedures, foreign keys, triggers, // do i need to go on ?
test on the database you will work with
otherwise the test is worthless
i would suggest trying to find a application on sourceforge
http://sourceforge.net/search/?type_of_search=soft&words=stock+delphi
how can you test something new on something this ancient like dbase ?
how do you test stored procedures, foreign keys, triggers, // do i need to go on ?
test on the database you will work with
otherwise the test is worthless
i would suggest trying to find a application on sourceforge
http://sourceforge.net/search/?type_of_search=soft&words=stock+delphi
true, you can't do stored procedures, triggers on dbase...
I will make on mySQL something.. and will be back here with my code and it will be more clear to discuss how can i solve!
I will make on mySQL something.. and will be back here with my code and it will be more clear to discuss how can i solve!
If you can create a trigger in mySQL (again, I am not familiar with/a frequent user of that DBMS), then you can put the trigger on your Sold table so that it updates the Stock table.
you can use a calculated field that displays the stock remaining on the fly and then just do an update...
what is wrong on the code?
and is it possible to minus from the STOCKS like this?
if yes, please write the code!
and is it possible to minus from the STOCKS like this?
if yes, please write the code!
procedure TForm1.Button4Click(Sender: TObject);
var
stockQty, soldQty, remain: integer;
begin
MyQuery2.First;
while not MyQuery2.eof do begin
MyQuery1.Close;
MyQuery1.SQL.Clear;
MyQuery1.SQL.Add('SELECT * from STOCKS.stocks');
MyQuery1.SQL.Add('WHERE id= ''' + dbText3.Caption + '''' );
MyQuery1.Open;
stockQty:=StrToInt(dbText2.Caption);
soldQty:=StrToInt(dbText4.Caption);
remain:= stockQty - soldQty;
Label1.Caption:=IntToStr(remain);
myUpdateSQL1.ModifySQL.Add('Update STOCKS Set qty=4 Where qty=5');
myUpdateSQL1.ExecSQL(ukModify);
MyQuery2.Next;
end;
end;
what query component type are you using ?
TQuery, TUniDac component ? TMyDac component ?
you would be better off using indentation, makes the code a lot more readable
and use parameters, your dba will hit on you if you keep doing it like this
especially in bigger companies when millions of queries hit a db
is your database STOCKS ?
and your table STOCKS too ?
i hope you use id integer (and not id varchar)
the following piece of code will set stock for record id to remain
i left the loop with MyQuery2, but i have no clue as to why you would do this ?
TQuery, TUniDac component ? TMyDac component ?
you would be better off using indentation, makes the code a lot more readable
and use parameters, your dba will hit on you if you keep doing it like this
especially in bigger companies when millions of queries hit a db
is your database STOCKS ?
and your table STOCKS too ?
i hope you use id integer (and not id varchar)
the following piece of code will set stock for record id to remain
i left the loop with MyQuery2, but i have no clue as to why you would do this ?
procedure TForm1.Button4Click(Sender: TObject);
var
stockQty, soldQty, remain: integer;
begin
MyQuery1.SQL.Text :=
'SELECT * FROM STOCKS.stocks WHERE id = :id';
MyQuery1.Prepare;
MyQuery3.SQL.Text :=
'UPDATE STOCKS.stocks SET QTY = :qty WHERE id = :id';
MyQuery3.Prepare;
MyQuery2.First;
while not MyQuery2.eof do
begin
MyQuery1.Close;
MyQuery1.ParamByName('id').AsString := dbText3.Caption;
MyQuery1.Open;
stockQty:=StrToInt(dbText2.Caption);
soldQty:=StrToInt(dbText4.Caption);
remain:= stockQty - soldQty;
Label1.Caption:=IntToStr(remain);
MyQuery3.ParamByName('ID').AsInteger := MyQuery1.ParamByName('ID').AsInteger;
MyQuery3.ParamByName('QTY').AsInteger := remain;
MyQuery3.ExecSQL;
// the following lines would set *ALL* stock from 5 to 4
//myUpdateSQL1.ModifySQL.Add('Update STOCKS Set qty=4 Where qty=5');
//myUpdateSQL1.ExecSQL(ukModify);
MyQuery2.Next;
end;
end;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial