Rewriting a VBA app In .NET - Connecting to DB

Ian W

Active member
Joined
Feb 11, 2010
Messages
31
Programming Experience
1-3
I've started to rewrite a AC97 app in .NET.

I've never done any .net stuff and i'm confused on how I can connect and update to my back end access db's.

What is the best method to do this?

I need to be able to open a recordset based on a select statement and then update the record.

Any help much appreciated.
 
Here's something I threw together :) Should be fairly self-explanatory, but feel free to ask questions :D

VB.NET:
Option Explicit On
Option Strict On

Imports System.ComponentModel

Public Class Form1

    Private ReceivedBarcodes As New Queue(Of String)
    Private MessagesToSend As New Queue(Of String)

    Private WithEvents CheckTimer As New Timer

    Private WithEvents bwBarcodeProcessor As New BackgroundWorker
    Private WithEvents bwMessageSender As New BackgroundWorker

    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.

        'setup the timer
        With CheckTimer
            .Interval = 50 'run every 50ms
            .Start()
        End With

        'setup the backgroundworker bwBarcodeProcessor
        bwBarcodeProcessor.WorkerReportsProgress = False

        'setup the backgroundworker bwMessageSender
        bwMessageSender.WorkerReportsProgress = False

        'for testing
        ReceivedBarcodes.Enqueue("00000001")
        ReceivedBarcodes.Enqueue("00000002")
        ReceivedBarcodes.Enqueue("00000003")

    End Sub

    Private OldMessageToRemove As String = String.Empty
    Private Sub CheckTimer_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckTimer.Tick
        If ReceivedBarcodes.Count > 0 And bwBarcodeProcessor.IsBusy = False Then
            'there is a barcode to process, run the backgroundworker
            bwBarcodeProcessor.RunWorkerAsync()
        End If

        If MessagesToSend.Count > 0 AndAlso bwMessageSender.IsBusy = False Then
            'there is a message to send, run the backgroundworker
            bwMessageSender.RunWorkerAsync()
        End If

    End Sub

    Private ReceivedBarcode As String = String.Empty
    Private Sub bwBarcodeProcessor_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bwBarcodeProcessor.DoWork
        While ReceivedBarcodes.Count > 0
            'keep repeating until the queue is empty

            ReceivedBarcode = ReceivedBarcodes.Dequeue
            ShowMessage(String.Format("Received Barcode: {0}", ReceivedBarcode))

            'TODO: process your Received Barcode here
            'your select and update SQL queries would all go here

            'when finished processing, add the response to send into the queue
            MessagesToSend.Enqueue("ResponseMessage related to " & ReceivedBarcode)
        End While
    End Sub
    Private Sub bwBarcodeProcessor_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwBarcodeProcessor.RunWorkerCompleted
        'currently no more messages to process
    End Sub


    Private MessageToSend As String = String.Empty
    Private Sub bwMessageSender_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bwMessageSender.DoWork
        While MessagesToSend.Count > 0
            'keep repeating until the queue is empty

            MessageToSend = MessagesToSend.Dequeue
            'TODO: send your message in reply here

            ShowMessage(String.Format("Sent Message: {0}", MessageToSend))
        End While
    End Sub
    Private Sub bwMessageSender_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwMessageSender.RunWorkerCompleted
        'currently no more outgoing messages to send
    End Sub


    Delegate Sub ShowMessageInvoker(ByVal Message As String)
    Public Sub ShowMessage(ByVal Message As String)
        If txtMessages.InvokeRequired Then
            'invoke required
            txtMessages.Invoke(New ShowMessageInvoker(AddressOf ShowMessage), New Object() {Message})
        Else
            'add the processed message to the text box
            txtMessages.Text &= Message & Environment.NewLine
        End If
    End Sub


    Private RandomBarcodeGenerator As New Random()
    Private RandomBarcode As Integer
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'for testing purposes ONLY !!
        RandomBarcode = RandomBarcodeGenerator.Next(1, 10000000)
        ReceivedBarcodes.Enqueue(String.Format("{0:000000000}", RandomBarcode))
    End Sub
End Class
 
I don't know why people would take a perfectly reasonable suggestion like cjard's and suggest rolling your own OleDbConnection.

Following this article from cjard's DW3 link: Walkthrough: Creating a Form to Search Data in a Windows Application I was able to get a working example up in about 2 minutes counting the time it took me to create an MS Access Database to work with.

MS Access Database structure.

ID Autonumber
Description Text
HasBeenScanned Yes/No

10 records in the database with descriptions of Item 1-10 and all HasBeenScanned are No.

I added a query called FillByID.

Here's my full query:

VB.NET:
SELECT        ID, Description, HasBeenScanned
FROM            Barcodes
WHERE        (ID = ?)

Since I don't have a scanner I'll create a stub and pretend the ID I'm looking for is 2 in my table.

VB.NET:
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadData(2)
    End Sub

    Private Sub LoadData(ByVal rowID As Integer)
        Me.BarcodesTableAdapter.FillByID(Me.TestDatabaseDataSet.Barcodes, rowID)
    End Sub

I only really need 1 line of code to pull the data but I've re-factored it into it's own method.

Updating data is just as simple. The same link cjard provided has examples for doing exactly that.
 
Just managed to get back to this, its a side project so not my first priority sadly.

InertiaM - Many thanks for the sample code, looks great, hopefully i'll get chance to go through it this week.
 
Just a few things here guys..

Having read the OP's description of what is going on, it seems more like the program itself is a CLIENT of the barcode readers, which are servers. I know it seems an odd way round, but it has been presented that the program connects to the scanner, reads a barcode and pushes some data back to the scanner..

I'd like to get some clarification on this, because it's rather an odd way round of working and definitely has a bearing on how the app is designed. If it were true, then I'd make an app that async makes a conenction to the scanner and attempts a read.. The read will unblock and .net will create a thread to complete the work, so the program can re-enter the async read immediately. Then the complete thread will perform the db lookup and async write the data back to the socket before expiring..

There are no backgroundworkers or persistent completer threads with this..
 
Hi cjard,

Your description of how the program works is pretty much correct. Its bascially as this...

1.Program connects to scanner.
2.Barcodes scanned in are processed by the app.
3.Data is sent back to the scanner to show information about barcode.
 
That's the way I originally read the question.

So for your scenario this thread pertains to step 2 where you've got a barcode and you want to do a database search based on that information.

In the code I provided I stubbed it into the page_load event because I don't have a scanner. In your situation you'd just hook that up to your event where you successfully capture a barcode.
 
Back
Top