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!!
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.
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.
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 !!
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.
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!!
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
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
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
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!!
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!!
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...
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.