Solved

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
21
226 Views
Last Modified: 2010-04-23
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

        odaChecking.Fill(dt)

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






0
Comment
Question by:tj44
  • 10
  • 7
  • 4
21 Comments
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
Use something like:

        Dim order_no_found As Boolean = False
        For i = 0 To dt.Rows.Count
            If dt.Rows(i).Item(0).ToString = dblOrderNo Then
            order_no_found = True
            Exit For
            End If
        Next

-Baan
0
 

Author Comment

by:tj44
Comment Utility
I have Option Explicit and Option Strict enabled, so I had to convert it - I now have:

Dim i as Integer
Dim order_no_found As Boolean = False
        For i = 0 To dt.Rows.Count
            If Convert.ToDouble(dt.Rows(i).Item(0).ToString) = dblOrderNo Then
                order_no_found = True
                Exit For
            End If
        Next

When I type a correct order, it populates, but if I type an incorrect number, it gives me this error:

An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll

Additional information: There is no row at position 0.

0
 
LVL 1

Expert Comment

by:Shatai
Comment Utility
Rather than going 0 to .Count, try using the LBound and UBound functions.

So

For i = 0 to dt.Rows.Count

becomes

For i = LBound(dt.Rows) to UBound(dt.Rows)

That should fix your out of range error.

0
 

Author Comment

by:tj44
Comment Utility
Dim order_no_found As Boolean = True
        For i = LBound(dt.Rows) To UBound(dt.Rows)  <---------- Problem -----------------
            If Convert.ToDouble(dt.Rows(i).Item(0).ToString) = dblOrderNo Then
                order_no_found = False
                Exit For
            End If
        Next

On that row, blue squigglies appear and says:

Value of type 'System.Data.DataRowCollection' cannot be converted to 'System.Array'

Any thoughts? I'm rackin my brain and still haven't been able to find a good way to perform the error checking listed in my original post.
0
 
LVL 1

Expert Comment

by:Shatai
Comment Utility
Ah, darn.  Well, at least you have something for an array.

If someone can provide an equivalent to LBound/UBound for the DataRowCollection, that would be preferable.  Otherwise, I can suggest two things.

1.  Set it to be 1 to Count (or 1 to Count-1, since it seems to be a 0-based array).  If you know there's never going to be a row at position 0, you could get away with this, but it's not good practice.

2.  You could try catching the error.  Find out what the Err.Number is and then, in your error-handling code, test for that number.  If it's that number, and that's the only place you refer to an index in that procedure, you can work around it.  And do something else for any other error number, of course.

Start your procedure with....

On Error Goto ErrorHandler

Before the End Sub line, write....

    Exit Sub ' If the sub finishes OK, don't let it hit the error handler
ErrorHandler:
    If Err.Number = 9 Then    ' 9 is the IndexOutOfRange error
        order_no_found = False
        Resume Next
    Else
        MsgBox Err.Number & " - " & Err.Description
    End If


Hope that helps.
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
Hi again...
... sorry about thins... a little mistake from my side... your For loop should be one less than total count, because we are starting fron 0.

        Dim order_no_found As Boolean = False
        For i = 0 To dt.Rows.Count - 1     'Here.......
            If dt.Rows(i).Item(0).ToString = dblOrderNo Then
            order_no_found = True
            Exit For
            End If
        Next
-Baan
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
good luck TJ...
0
 

Author Comment

by:tj44
Comment Utility
Thanks for all your help so far!

I was able to get order error checking working using the above method, so I'm confused why I'm not getting it to work for checking item
The code for button "check":

        Dim dblOrderNo As Double
        Dim sql, strItem As String
        Dim itemTable As DataTable
        Dim i As Integer

   strItem = txtItem.Text
   dblOrderNo = Val(txtOrder.Text)

sql = ("SELECT order_no,item,quantf,boxqty,processed FROM checking WHERE order_no=" & dblOrderNo & "AND item='" & strItem & "'")

'Populate Item Data with SQL statement

        itemTable = New DataTable
        odaChecking.SelectCommand.CommandText = sql
        odaChecking.Fill(itemTable)

'Check to see if Item exists for order                  
        Dim item_found As Boolean = False
        For i = 0 To itemTable.Rows.Count - 1
            If itemTable.Rows(i).Item(0).ToString = strItem Then  <-- Problem Area??
                item_found = True
                Exit For
            End If
        Next
        If item_found = True Then
            OrderGrid.DataSource = itemTable
        Else
            MsgBox("Item does not exist for this order")
            txtItem.Text = ""
            txtItem.Focus()
            Exit Sub
        End If

--- So, this is pretty much identical to the order check except for the order check I used the line :

If Convert.ToDouble(dt.Rows(i).Item(0).ToString) = dblOrderNo Then

because its double, not a string. But for the above code, I can type in an order that DOES exist, and it won't  change to True, it just keeps saying
it doesn't exist

Thanks again!

0
 
LVL 1

Expert Comment

by:Shatai
Comment Utility
Could it be the .Item(0) reference?  Are you sure they should be the same for both tables?
0
 

Author Comment

by:tj44
Comment Utility
Yeah I thought about that, like .Item(0) being the order_no column index, but I changed it to (1) and it still did the same thing (and other values) but if going by column rows 0 should be order_no, 1 item, 2 quantf, etc..

So I'm not sure what the problem is
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Expert Comment

by:Shatai
Comment Utility
Ok, must be something with the values themselves, then.

You could try setting a breakpoint and manually checking the values of dblOrderNo and dt.Rows(i).Item(0).
Alternatively, you could try converting txtOrder.Text by using something other than the Val function.  You could try either the Convert.ToDouble function or the CDbl function.
0
 

Author Comment

by:tj44
Comment Utility
I think you meant trying to convert the txtItem.Text ? Heh maybe I'm getting confused.. the Order part is working right, but a few posts ago
was my example of the Item part that I'm hung up on now.

I tried doing dblItem = Convert.ToDouble(txtItem.Text) and checking the output of that variable in a msgbox and it just errors..

I've never used to CDbl function to get data from a text box, syntax? ;)

If I was able to correctly convert the item field data from string to double, and put it into the error checking, it should work fine since
the error checking that currently works uses a double - So I'm thinking there may be a problem with the checking using a string..which seems odd
however because that was the original way suggested by DotNetLover.

Frustrating =)

Sorry if this post didn't make perfect sense, it's late and I should get some sleep!

Once again I appreciate the help
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
Hi there...
in this line...  "If itemTable.Rows(i).Item(0).ToString = strItem Then" ...  you are comparing "Item" to the "OrderNumber". Take a llok at your SELECT statement. The correct "If" condition will be comparing to  Item(1) not Item(0)
        If itemTable.Rows(i).Item(1).ToString = strItem Then  

-Baan
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
oops... sorry , I didn't read all the posts... you have already figured that out.
0
 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 500 total points
Comment Utility
One suggestion....
with the SELECT statement you have , you don't have to go for that IF LOOP, because the checking is already done in the STATEMENT.


        Dim dblOrderNo As Double
        Dim sql, strItem As String
        Dim itemTable As DataTable
        Dim i As Integer
        sql = ("SELECT order_no,item,quantf,boxqty,processed FROM checking WHERE order_no=" & dblOrderNo & "AND item='" & strItem & "'")
        itemTable = New DataTable
        odaChecking.SelectCommand.CommandText = sql
        odaChecking.Fill(itemTable)
       
       'Now here just check if the table has rows....
        Dim item_found As Boolean = False
        If itemTable.Rows.Count > 0 Then
             item_found = True
        End If

-Baan
0
 

Author Comment

by:tj44
Comment Utility
Hey again,

Whew I think I only have a few things left. I like that method better for checking, Baan. You're a genius! =)

I discovered that trying to make qty and box_qty match to determine when the correct quantity has been met is a bit ridiculous.

What I did was change box_qty field to need_scan , and when the checking.mdb is created qty and need_scan use the same numbers

Now when an item is entered, found to exist on the order, it decreases the need_scan number by 1 on the item. Then I added another check so if it sees need_scan < 1 , it tells the user
they've already reached the proper qty - counting down to 0 and checking that sounds MUCH easier than counting up, and then checking it based on another variable, etc etc

So that brings me my next problem in this forsaken project =)
After the item is found and need_scan decreased, I want to update the database to reflect this change, here is some code:


' /// Query to get the item
        sql = ("SELECT order_no,item,qty,need_scan,processed FROM checking WHERE order_no=" & dblOrderNo & "AND item='" & strItem & "'")
        itemTable = New DataTable
        odaChecking.SelectCommand.CommandText = sql
        odaChecking.Fill(itemTable)


' /// Check to see if Item exists for that Order
        Dim item_found As Boolean = False
        If itemTable.Rows.Count > 0 Then
            ' /// Item Found, Perform Updates
            item_found = True
            Dim intScan, updateScan As Integer
           ' /// Get intScan value from itemTable
            intScan = Convert.ToInt32(itemTable.Rows(0).Item("need_scan"))
           ' /// Make sure need_scan hasn't already been reduced to 0
            If intScan < 1 Then
                MsgBox("Quantity for " & strItem & " has already been met")
            Else
           ' /// decrease intScan by 1 and update
                Dim updateSQL As String
                updateScan = intScan - 1
                ' /// Replace the decreased number in the itemTable
                itemTable.Rows(0).Item("need_scan") = updateScan
               ' /// Perform database update
                odaChecking.UpdateCommand.CommandText = updateSQL
                updateSQL = ("UPDATE checking SET need_scan=" & updateScan & " WHERE order=" & dblOrderNo & "AND item='" & strItem & "'")
               ' /// More code needed here ???
            End If

Ok, so this actually runs without errors, but its not storing the updated values. I have a feeling I am missing a step of some sort.
The code has updated the need_scan field in the DataTable "itemTable "

But now should the itemTable update the DataSet dsChecking somehow? If you recall, my DataAdaper is odaChecking, DataSet is dsChecking

The program seems to be able to grab the data and manipulate it, now it's just trying to figure out how to stuff it back in the database.
Other solutions on EE seem to be using DataRows and such, and it's very confusing !

-TJ


         
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
Hi.. good work...
well, to update.. just use Update method of the Adapter and pass the DataSet in it.

updateSQL = ("UPDATE checking SET need_scan=" & updateScan & " WHERE order=" & dblOrderNo & "AND item='" & strItem & "'")
odaChecking.UpdateCommand.CommandText = updateSQL
odaChecking.Update(dsChecking)

-Baan
0
 

Author Comment

by:tj44
Comment Utility
Hmm..

Well I gave that a try and it still isn't updating the database. I tried putting in acceptchanges to the itemTable DataTable to see if perhaps it just wasn't saving the replaced value. But I close and restart the program, and type in the order number, and the need_scan field is unchanged.

I opened the database in access to make sure, and they remain unchanged.

Code:

                Dim updateSQL As String
                updateScan = intScan - 1
                itemTable.Rows(0).Item("need_scan") = updateScan
                itemTable.AcceptChanges() <----------------------------- Unsure if needed----------------------
                updateSQL = ("UPDATE checking SET need_scan=" & updateScan & " WHERE order=" & dblOrderNo & "AND item='" & strItem & "'")
                odaChecking.UpdateCommand.CommandText = updateSQL
                odaChecking.Update(dsChecking)

I do know the updateScan value is getting reduced correcly, after this code I have a snippet that outputs the variable to a label so I could make sure nothing funky was going on.

0
 

Author Comment

by:tj44
Comment Utility
Another quick question about the above,

Do I even need a dataset? I have my DataAdapter odaChecking, and I've just been creating DataTables using

odaChecking.Fill(mydatatable) to show different information on the screen - I'm never messing with a DataSet

Seems like odaChecking.Update(mydatatable) would update the database based on the changes .. but its not.. HMM! =)
0
 

Author Comment

by:tj44
Comment Utility
More frustration.. maybe its not the update that's the problem
I think itemTable isn't updating with the new itemTable.Rows(0).Item("need_scan") = updateScan  

Code:

' /// Check to see if Item exists for that Order
        Dim item_found As Boolean = False
        If itemTable.Rows.Count > 0 Then
            ' /// Item Found, Perform Updates
            item_found = True
            Dim intScan, updateScan As Integer
            intScan = Convert.ToInt32(itemTable.Rows(0).Item("need_scan"))
            ' /// Make sure Qty hasn't already been met
            If intScan < 1 Then
                MsgBox("Quantity for " & strItem & " has already been met")
                txtItem.Focus()
                Exit Sub
            Else
                ' /// Update itemTable DataTable , get ready to update DataBase with new DataSet dsItems
                updateScan = intScan - 1
                itemTable.Rows(0).Item("need_scan") = updateScan
                itemTable.AcceptChanges()  <--------------------- It's not updating? -------------------
                odaChecking.Fill(dsItems, "itemTable") <-----------Filled new dataset with (hopefully) updated information from the DataTable-------
                'View Updated Table with dsItems DataSet
                OrderGrid.DataSource = dsItems <-------- Try to show updated info, but its unchanged! ------

So perhaps the problem isn't with updating to the database, although it may still pose problems, the DataTable isn't getting updated..

However! .. could I just skip some of this with an SQL statement? Because as this code runs, the updateScan variable is successfully reduced

With the SQL statement:

updateSQL = ("UPDATE checking SET need_scan=" & updateScan & " WHERE order=" & dblOrderNo & "AND item='" & strItem & "'")

It should be able to fill this line in the database with the new updateScan number, correct?

It appears just running the line

odaChecking.UpdateCommand.CommandText = updateSQL

does not update the database =\

Thanks again for any help, I seem to be asking a lot of questions to get this problem solved!

0
 

Author Comment

by:tj44
Comment Utility
Alright, well I guess I'll close this question out, and ask new specific questions with my last remaining problems

Thanks for all your help!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now