Validating Data with VB.NET

Posted on 2004-10-27
Last Modified: 2010-04-24
I am creating a Pocket PC program for a symbol scanner. The user will be able to scan a bar code with the scanner. What I would like to do is validate the bar code against an inventory table I have in a SQL database. If the bar code does not exist then a message will be displayed otherwise it will go on from there. How can I go about doing this and what event would I do this on? Any examples would be great. Thanks.
Question by:AccessYourBiz_Com
    LVL 7

    Expert Comment

    If you can set the scanner so that it TABS after it scans the barcode...then you can place the event in ONLOSTFOCUS for the field being scanned in.
    LVL 11

    Expert Comment

    If you cant configure the scanner to tab then you need to use the textchanged event and check if the lenght is correct. If so check on the db
    LVL 6

    Expert Comment

    or you can set the barcode scanner to do a carriage return after it has read teh barcode and then use the textchanged event to look for chr(15) or VbCr
    LVL 3

    Author Comment

    Thanks for all the answers about which event to use but does anyone know how to lookup data in a sql table and return it if it exists or to display a message if it does not exist? Any examples would be great.
    LVL 7

    Expert Comment

    SQL Stored Procedure used by VB.NET Code below

    --Used to validate a user login...empty result is invalid
    create proc spValidateLogin
          @varUsername varchar(25),
          @varPassword varchar(25),
          @ReturnID int output as
          SET NOCOUNT ON

          select * from tblUser where varUsername = @varUsername and varPassword = @varPassword

          if @@rowcount = 0 --if nothing came back from the query
                      set @ReturnID = -3 --Invalid Login
                      set @ReturnID = (select pkUserID from tblUser
                                        where varUsername = @varUsername)

          select @ReturnID
          return @ReturnID

    VB.NET CODE <---- Validate a user login...
    Option Explicit On

    'Import required Namespaces
    Imports System.Data
    Imports System.Data.SqlClient

    Public Class frmLogin
        Inherits System.Windows.Forms.Form

        'The user does not want to Login, close Application
        Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
            'Close the main form
        End Sub

        'The user wants to attempt to login to the system
        Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
            'Variables and objects
            Dim cDB As clsDBConn = New clsDBConn 'Connection Class
            Dim strConn As String = cDB.getStrConn 'Get connection string for database
            Dim oConn As New SqlConnection 'SQL connection object
            Dim intReturnID As Integer 'Return ID from Stored Procedures
            Dim oComm As SqlCommand 'SQL Command object
            Dim bFail As Boolean = False 'Test for failure

            'Validate form has data
            If Me.txtUsername.Text = "" Then
                MsgBox("Please enter a User Name.")
                bFail = True
                If Me.txtPassword.Text = "" Then
                    MsgBox("Please enter a Password.")
                    bFail = True
                End If
            End If

            If bFail = False Then 'All is well
                'Connect to SQL server and validate login
                    'Pass connection string to SqlConnection
                    oConn.ConnectionString = strConn

                    'SQL Database statements
                    oComm = New SqlCommand
                    oComm.CommandType = CommandType.StoredProcedure
                    oComm.CommandText = "spValidateLogin"
                    oComm.Connection = oConn

                    'Send in parameters
                    oComm.Parameters.Add(New SqlParameter("@varUserName", SqlDbType.VarChar)).Value = Me.txtUsername.Text
                    oComm.Parameters.Add(New SqlParameter("@varPassword", SqlDbType.VarChar)).Value = Me.txtPassword.Text

                    'Return value from stored procedure
                    Dim sParam As SqlParameter
                    sParam = New SqlParameter
                    sParam.ParameterName = "@ReturnID"
                    sParam.SqlDbType = SqlDbType.Int
                    sParam.Direction = ParameterDirection.Output

                    'Open connection and execute oComm
                    oConn.Open() 'Open connection
                    oComm.ExecuteNonQuery() 'Execute stored procedure
                    intReturnID = oComm.Parameters("@ReturnID").Value 'Get Return value
                    oConn.Close() 'Close connection

                    'Analyze Return value for errors
                    If intReturnID < 0 Then 'All errors are less than 0
                        MsgBox("Invalid Login, please try again.")
                        Dim frmMain As frmMain = New frmMain
                        'Set the global variable for user logged in
                        _intUserID = intReturnID

                        'Open Main form and hide Login screen
                    End If

                    MsgBox("Error connecting to SQL Server")
                    oConn.Close() 'Close connection
                End Try
            End If
        End Sub
    End Class
    LVL 1

    Accepted Solution

    Here's a function I've used in the past for a similar problem:

     Dim dt As New DataTable

            ' Create and open database connection
            Dim conn As New SqlConnection(strDB_Connection)
            If conn.State <> ConnectionState.Open Then conn.Open()

            ' Create and define command object
            Dim myCommand As SqlCommand = New SqlCommand("SELECT TOP 1 * FROM Inventory WHERE barcode = '" & txtBarCode & "'", conn)
            myCommand.CommandType = CommandType.Text

            ' Create and define data adapter
            Dim adpSQL As SqlDataAdapter = New SqlDataAdapter
            adpSQL.SelectCommand = myCommand
            adpSQL.TableMappings.Add("Table", "Results_Table")

            ' Fill the datatable with the data

            ' Close the database connection
            If conn.State <> ConnectionState.Closed Then conn.Close()

            If dt.Rows.Count > 0 Then
                'Record exists - return TRUE
                'Record doesn't exists - return error message to user
            End If

    LVL 15

    Expert Comment

    You could either use the Blur event of the textbox or if you have a button, you could use that.

    Stored Procedure:

    CREATE PROCEDURE sp_CheckBarcode
    @Barcode varchar(24)
    IF EXISTS(SELECT * FROM tblBarcodes WHERE Barcode = @Barcode)
         return 1
         return 0

    In the code:
    ' assume conn is a SqlConnection object
    Dim barcode As String = textbox1.Text ' replace this with the actual textbox value

    Dim cmd As SqlCommand = conn.CreateCommand()
    cmd.CommandText = "sp_CheckBarcode"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@Barcode", SqlDbType.VarChar, 24).Value = barcode
    cmd.Parameters.Add("@Return", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue


    If Int32.Parse(cmd.Parameters("@Return").Value) = 0 Then
         MessageBox.Show("That barcode does not exist.")
    LVL 14

    Expert Comment

    Hi. Looks like the other guys have the db thing nailed.  But I thought I would throw my 2 cents worth in since I have just wrapped up a Pocket PC app on a Symbol scanner talking to a web service via a Wireless network . One thing I did was configure the scanner (using the ScanWEDGE app that comes with the scanner) to append a "$" to the scanned characters.

    Then in the textbox's TextChanged event I check to see if the contents end with a "$". If it does, then I know the scan is complete. This also tells me that the user actually used the scanner and didn't just type in the barcode data. In my case that makes a difference since I have additional data embedded in the barcode that tells my app what the target field should be for a specific barcode. So I can do some basic validation without hitting the web service by checking this additional data. If a barcode that doesn't apply for the current operation is scanned, the app pops up a message box indicating that.

    So the 1st line of code in my TextChanged event looks like this:

    If TextBox1.Text.EndsWith("$") Then     ' User performed a scan...validate it
           Dim txt as String = Mid(TextBox1.Text, 1, TextBox1.Text.Length -1)   ' Strip off $ char
           Validate txt...

    Your database validation scheme goes in here.

    End If

    Something else that is cool is the Symbol SDK you can get from Symbol's DevCenter website. This SDK gives you access to a bunch of stuff specific to the scanner. In my case I am displaying the wireless signal strength on a meter I made that looks just like the signal strength meter on your typical cell phone.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 ( But the ability to create custom scanning profiles al…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    846 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

    6 Experts available now in Live!

    Get 1:1 Help Now