Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2013

    Question Unanswered: Variable + Wildcard in SQL


    I believe what I'm having is a SQL syntax problem, but I'm posting the entire sub in case I'm wrong and it's somewhere else.

    I have a textbox that receives an alphanumeric barcode scan (jobNumber). The number will look like this: "CX0XX0-0000" The number I'm searching in the SQL DB will match this without the "C" at the front, so I'm trimming the "C" to start.

    The first scan the user enters has worked because the numbers matched, however each time the user scans their next barcode, the trailing "-0000" will increment by one on the sheet they are scanning from. The new incremented numbers (X0XX0-0001, etc) do not exist in the database. My program only needs to validate the first 5 characters because they are unique in the DB. So I'm attempting to trim the trailing "0"s immediately, then my jobNumber string ends up looking like "X0XX0-" This is the point where I'm stuck. I'd like to use a LIKE with wildcards for the 4 trailing chars. I've tried the '%' that is in the code now, as well as '____', but either way the program returns "Job Number Not Found"

    Any expert advice is greatly appreciated!

    Private Sub QueryJobReturnPart()
    Dim jobNumber As String    ' var to send to SQL
    Dim partNumber As String    ' part number back from SQL
            jobNumber = txtJobNum.Text        ' Pass data from text to var
            jobNumber = jobNumber.TrimStart("C")    'trim leading "C" from barcode
            jobNumber = jobNumber.TrimEnd("0")    'trim trailing 0's
            partNumber = ""           
    Dim conn As New SqlClient.SqlConnection       'Create new connection object
    Dim reader As SqlClient.SqlDataReader         'Create reader for PartNum 
    Dim connectionString As String = "Data Source=MyServer;Database=MyDatabase;Persist Security Info=True;User ID=MyUser;Password=MyPass;"
            conn.ConnectionString = connectionString
            Dim sql As String
            sql = "Select fpartno "        ' sql query retrieving part # by job #
            sql += "From jomast "
            'sql += "Where fjobno = @fjobno"    '  <---- WORKS
            sql += "Where fjobno LIKE @fjobno + '%'"   ' <---- DOESN'T WORK
    Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, conn)
    cmd.Parameters.Add("@fjobno", jobNumber)        ' pass the jobNumber to sql
                reader = cmd.ExecuteReader          'Assign cmd to reader
                If reader.Read() Then       ' If no matching Job Number record partNumber empty
                    partNumber = reader("fpartno")    
                End If
            Catch err As Exception
                MessageBox.Show("Error: " & err.Message)
                If Not conn Is Nothing Then conn.Close()
            End Try
            If partNumber <> "" Then
                partNumber = partNumber.Trim()
                lblPartNum.Text = partNumber
                MessageBox.Show("Job Number Not Found")
                txtJobNum.Text = ""
            End If
    End Sub

  2. #2
    Join Date
    Oct 2013

    Sorry, this was too easy..

    I've solved it, my TrimEnd needed to cover more than just 0's. Sorry for any wasted time/effort.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts