[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Validating Data with VB.NET

Posted on 2004-10-27
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 12422655
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

ID: 12423214
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

Expert Comment

ID: 12423559
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 12424007
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.

Expert Comment

ID: 12424107
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

      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

Accepted Solution

emglantz earned 2000 total points
ID: 12424409
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

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

Stored Procedure:

@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

ID: 12429925
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

656 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