New to VB.NET, need to know how to perform error checking and add updates to MS Access database, big project!
Posted on 2004-08-09
First of all, I'm new to VB.NET so I apologize in advance if I'm starting out in an incorrect manner, I've been learning as I go.
I'm creating a program that can check Item accuracy in orders, and it gets the data to check from an Access database: checking.mdb
Fields in table checking are id , order_no , item, qty, box_qty, processed. For a typical order, the database might look like this:
Fig 1. (I have omitted the id column)
order_no item qty boxqty processed
--------- --------- --- ------ ----------
1001 DSN001 1 0 no
1001 DSN002 2 0 no
1001 DSN433 5 0 no
Now with these field names in mind, I would like to explain the steps I'm envisioning for a successful Order Check:
Step 1: Order # is typed in, and program makes sure Order # exists in the database, if it does it is displayed on the screen
Step 2: User begins entering Item numbers they have pulled and verifying they belong on that Order. Every time they enter an item
it should go through these steps:
a. If an Item is typed in that is not found in the 'item' field for that Order#, tell the user
b. When an item is matched in the item field for that order #, box_qty should be increased by 1
(Hopefully by the end, this will equal qty, so we know they got the right ammount!)
c. When qty = box_qty , processed field should be updated to "yes"
d. If an item is entered, and the processed field is "yes" for that Item, tell the user they have too many
(Could another way, be: if box_qty > qty , msg the user and don't increment? )
Step 3: They should click a finish button, which would perform one last check:
a. If box_qty < qty for any item in that order, tell the user they didn't pull enough, and which Item it is they are lacking
Step 4: Well, that should be it I guess! Now the screen would be reset and ready for the next Order # input.
Okay, now I'll show you my meager progress so far!
I've established a connection to the database and have a DataAdapter: odaChecking , and DataSet: dsChecking (also the Jet connect odcChecking)
When a user types in an order number, it displays that order on the screen with a datagrid. I've done this by doing the following:
Dim sql As String
Dim dt As DataTable
Dim dblOrderNo as Double
dblOrderNo = Val(txtOrder.Text)
sql = ("SELECT order_no,item,qty,box_qty,processed FROM checking WHERE order_no=" & dblOrderNo)
dt = New DataTable
odaChecking.SelectCommand.CommandText = sql
OrderGrid.DataSource = dt
This works just fine, except now I would like to add in there somewhere, the error checking I described above.
I've searched around but have not seen an example of how to do this (for my newbie skills anyway)
How would I go about doing this?
Once I know how to do that , it would take care of a great deal! For the Item checking, it seems like I'd just be performing
another query, just with adding: WHERE oder = # AND item = 'blahblah' to the SQL statement, then processing the rest if it passes
the "check" if it exists.
Also, suggestions on how to complete the rest of those checks would be GREATLY appreciated. ;) It seems "simple" just thinking about the steps
the program goes through, but I'm just lacking the skills to do it at this point. =(
Thanks in advance!!