How To Update Stock automatically

[arsharma159]

Hello guys,
I'm working on a VB.Net Project. I'm using MS SQL Server as my backend.
I can create Purchase/Sales Voucher forms and many more through the VB.Net Database EXpress.
Say for example I have created a Purchase Voucher form where the user can enter the vendor info (in TextBox view) as well as the the Product, Quantity, Rate , Totals (in Datagridview). thus the user can save the purchase voucher to the database as for record.
BUT THE PROBLEM IS THAT I also want that when the user enters the Product, Quantity, Rate, etc. the stock also get increased (updated) automatically.
IN THIS CASE I NEED YOUR 'VALUABLE SOLUTION'
Thanks!!

[john.knapp]

You'll probably not want to increase the "stock on hand" until you take delivery. What I recommend is to track that amount in a separate column, "On Order" . If you have MSAccess, there's a pretty good inventory layout in the Northwind sample database. See screenshot inline. (yes, I know you have MSSQL, but the principle and strategies are the same, and the code similiar)

That said, to automatically change the stock numbers, you have a couple of choices that come to mind immediately - either a new SQL query (client executed), or a trigger (executed server-side) on whatever table you update that can then update that table or multiple tables.

Show us what you have for code so far and we can tune these suggestions.

[arsharma159]

Thanks for such a quick reply,

But as I said I did that all through the VB.net Express available in Visual Studio 2005.
I just need to click on 'Data' menu > 'Add data source' | add the tables | drag the vendor (in case of Purchase voucher) info like Name, Contact Person, Address, etc. in the 'Textbox view' from the Vendor table AND ______
drag the Order table in 'Datagrid view'. AND everything works just fine, that I can save the purchase voucher, edit it, delete it, add new record through the buttons automatically added by just dragging the tables on the FORM.

What just I want is that when I enter the purchase voucher the stock items get automatically increased (updated) and decreased when I enter the Sales voucher.
That's just very easy for Code Masters but not for me. I'm unable to provide you any code. If possible after your preceous time please give me the code.

So, we a very kind request I would like to tell you that it's challanging me on my Respect wall.

[john.knapp]

You want an update query.

Assuming you have a TableAdapter (visible in the "tray" in Form Designer View), AND the table you want to update is already in your dataset:

1. Right click that TableAdapter and select "Edit Queries in DataSet Designer"
2. In DataSet Designer right-click the main table to be updated, select "Add a query".

3. Click the "Query Builder" button in the next window to go to Query Builder and build your update query from there.

   You'll have to specify a "WHERE" clause (it will not add it for you) and set the parameters and values that you then also have to code into your application by hand (no drag-n-drop here!)

The final query should look something like this:

UPDATE tblProducts 
SET [column1] = @value1, [column2] = @value2, [column3] = @value3
FROM tblProducts JOIN tblOrders ON
    tblProducts.ProductID = tblOrders.ProductID
WHERE tblProducts.ProductID = @param

[arsharma159]

Thank you John, and very sorry for the late reply !!

After about 1 and half month I got a right solution.
But the problem is that still I can't get the problem solved AND the reason is that perhaps because I can't code as extra work needed as you said.

I'm sure that you can provide me the solution because you are well equiped with coding as well as the GUI !!

I'm just near the solution if you could provide me the step-by-step guidance. I'm seeking your HELP in this regard!!

[arsharma159]

I have three tables viz,
POrder: POID(PK), PID(FK_Stock), Product, Quantity, Rate, Total.
SOrder: SOID(PK), PID(FK_Stock), Product, Quantity, Rate, Total.
Stock: PID(PK), Product, Quantity(On hand_Quantity).

I want that upon entering the purchase voucher the Stock gets automatically increased with the respective items AND on entering the Sales voucher, the stock items get deducted.
IF NOT POSSIBLE there could also be a button to do that.
THANKS JHON !!

[john.knapp]

Clean your project from the Build menu, zip it up into a compressed folder, and attach it to a post.

We need to see what you have for code, if you've done everything in the designer so far, you won't see anything in your main form's code behind file - but it will be visible in the Designer generated code file.

[arsharma159]

Thank you very much, John.
I'm attaching the project file as you said withing few hours.
Thank you!!

[arsharma159]

Very SORRY for the delay John,
I'm attaching the ZIP file herewhithin as you told me to do. PLEASE Check it out.
It is just a very simple project for my freinds shop for which my self respect is on Bet.
Please me OUT!!

[john.knapp]

You really should've used the Northwind example and modded it to suit your needs...

Some quick notes:
1. Database design is "off" - Customer table and Vendor table have no relation to any other tables
2. Add POrder.POID as a foreign key to vendor table, add SOrder.SOID as foreign key to Customer table
3. You need error handling in your application.
For every sub and function, add a Try...Catch block

For example:

Sub mySub()
  Dim myBool as Boolean = False
  Try
    Me.VendorTableAdapter.Fill(Me.GPOSDataSet.Vendor)
    ' more code
    If myBool = false Then
      ' do something
    End If
  Catch ex as Exception
    Debug.Print (ex.Message)
  Finally
    ' optional codeblock, runs regardless of successful try
    ' see http://msdn.microsoft.com/en-us/library/fk6t46tz(v=vs.71).aspx
  End Try
End Sub

[john.knapp]

Please script your database and attach that script.
In server explorer, right click database and select "publish to provider". Go through wizard and save the output file.
Zip and upload that output file

[ranvijay005]

Use Stored Procejures that I can say.

[arsharma159]

Thank GOD and You John,
Sorry to say but when I right-click the DB I find options like Refresh, Delete, Modify Connection, Close Connection, Change View, New Query, Application Debugging, Allow SQL/CLR Debugging, Rename, and Properties. Nowhwre I find this 'Publish to Provider' option in my Server Explorer in my VStudio 2005 John.
Thanks!!

[arsharma159]

John,
would you please provide me the client executed query that you suggested me earlier.
Keeping in mind, I added 'RECEIVE STOCK' button under Purchase Order Voucher so that once clicked that will update (increase) the respective stock items entered above in the datagrid view.
AND after all see what's good for this. that's all upto you. I just want the solution trough any of your several ways.
THANKS!!

[john.knapp]

Scripting may not be available in your version of Visual Studio.
I cannot build the query without the tables.
You can download the SQL Server 2005 management studio at this link, assuming you are running SQL Server 2005...

[arsharma159]

You are helping me a lot since the start BUT my fault or perhaps bad luck that I can't use properly your suggetionss.
This time you please let me conclude from my Problem.

In one of the previous POSTS you suggested me to create a Query. THE problem is that I'm unable to write the exact 'WHERE' condition as well as the extra code needed you said.

Now could you please try at rough or exactly to provide me that Query With the 'WHERE' condition based on TABLE deatils provided in one of previous POSTS and ALSO that 'Extra Code'. 100% chances are You can help me solve my problem this time.