Validating Data with VB.NET

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.
LVL 3
AccessYourBiz_ComAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

natlozCommented:
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.
0
rdrunnerCommented:
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
0
The_BiochemistCommented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

AccessYourBiz_ComAuthor Commented:
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.
0
natlozCommented:
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
            begin
                  set @ReturnID = -3 --Invalid Login
            end
      else
            begin
                  set @ReturnID = (select pkUserID from tblUser
                                    where varUsername = @varUsername)
            end

      select @ReturnID
      return @ReturnID
GO

**********************************************
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
        Me.Close()
    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.")
            Me.txtUsername.Focus()
            bFail = True
        Else
            If Me.txtPassword.Text = "" Then
                MsgBox("Please enter a Password.")
                Me.txtPassword.Focus()
                bFail = True
            End If
        End If

        If bFail = False Then 'All is well
            'Connect to SQL server and validate login
            Try
                '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
                oComm.Parameters.Add(sParam)

                '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.")
                    Me.txtUsername.Focus()
                Else
                    Dim frmMain As frmMain = New frmMain
                    'Set the global variable for user logged in
                    _intUserID = intReturnID

                    'Open Main form and hide Login screen
                    frmMain.Show()
                    Me.Hide()
                End If

            Catch
                MsgBox("Error connecting to SQL Server")
            Finally
                oConn.Close() 'Close connection
            End Try
        End If
    End Sub
End Class
0
emglantzCommented:
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
        adpSQL.Fill(dt)

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

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Timbo87Commented:
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)
AS
IF EXISTS(SELECT * FROM tblBarcodes WHERE Barcode = @Barcode)
     return 1
ELSE
     return 0
GO

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

conn.Open()
cmd.ExecuteNonQuery()
conn.Close()

If Int32.Parse(cmd.Parameters("@Return").Value) = 0 Then
     MessageBox.Show("That barcode does not exist.")
0
ptakjaCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.