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")
Catch err As Exception
MessageBox.Show("Error: " & err.Message)
If Not conn Is Nothing Then conn.Close()
If partNumber <> "" Then
partNumber = partNumber.Trim()
lblPartNum.Text = partNumber
MessageBox.Show("Job Number Not Found")
txtJobNum.Text = ""