CODEWITHSUNDEEP
sqlvb.netms-access
Roy
Roy

Reputation: 35

Beginner working with VB.NET for Inventory Control

I am a somewhat newcomer to VB, having come from Visual Foxpro a few years ago, and need some help with some structuring and syntax.

I am attempting to create a system that can accept a Employee's ID number, from a barcode scanner with a keyboard wedge, and pull them up from an Access Database with their name and employee number.

The purpose of this section is to pull the ID data beforehand, and to make sure they have access to that area, so it can be used to a inventory check in/out system.

Currently the code makes it to the actual Query "OleEmp" section and then just stops without pulling data, or populating anything.

What exactly am I doing wrong?

Thanks!

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Public EmpID As Double
    Public dbEmpID As Double
    Public Barcode As Double
    Public FirstName As String '= "Test"
    Public LastName As String '= "Account"
    Public OleDbConn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = C:\Users\rcassel\Documents\Visual Studio 2012\Projects\Inventory Control\Inventory Control\Inventory Control2.accdb;")


Private Sub TextBox1_LostFocus(sender As Object, e As EventArgs) Handles TextBox1.LostFocus
    EmpID = (Val(TextBox1.Text))

    'Checks to see if someone entered a Badge
    If EmpID = Nothing Then
        MsgBox("You must scan your Badge!", MsgBoxStyle.OkOnly)
        TextBox1.Focus()
    Else
        'Fire Query into Database
        Try
            Dim OleEmp As New OleDbCommand("SELECT [First Name],[Last Name],[Employee ID] FROM Contacts WHERE [Employee ID]=" + EmpID + "", OleDbConn)

            Dim r1 As OleDbDataReader = OleEmp.ExecuteReader()

            While r1.Read()
                FirstName = CStr(r1("First Name"))
                LastName = CStr(r1("Last Name"))
                dbEmpID = CInt(r1("Employee ID"))
            End While

            r1.Close()
        Catch ex As Exception
            MsgBox("Cannot Pull Data." & vbCrLf & ex.Message)
        End Try

        If dbEmpID = Nothing Then
            MsgBox("You are not Authorised to use this device. This activity has been logged.", MsgBoxStyle.OkOnly)

        Else
            Me.ListBox1.Items.Add(FirstName)
            Me.ListBox1.Items.Add(LastName)
            Me.ListBox1.Items.Add(EmpID)
            TextBox2.Focus()
        End If

        OleDbConn.Close()
    End If

End Sub

Upvotes: 2

Views: 934

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123609

I see two issues:

The first issue is that your connection string is using comma where a semi-colon is needed. The correct syntax would be

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\rcassel\Documents\Visual Studio 2012\Projects\Inventory Control\Inventory Control\Inventory_Control2DataSet.xsd;

The second issue is that once you've fixed the connection string you'll find that Microsoft.Jet.OLEDB.4.0 does not support using an .xsd (XML schema document) file as a Data source. It also doesn't support reading XML data directly.

You'll either need to import your XML data into your Access database or possibly use some VB.NET magic (ADO.NET, perhaps) to perform lookups directly on your XML data.

Upvotes: 1

Related Questions