Search only first 8 chars of a column in database

[livedifferent]

Hey Guys,

Like usual, I tried searching but couldn't find anything specific to this problem I'm trying to fix.

I have a program that is reading a field from an Access database and if the field matches then it returns the second column in the database. However, the field that it is trying to match/lookup in the database is not always in the same format as the one that is input in the program.

Example:

Program: Field to lookup/match in database is 20092109
Database: The lookup column may have records in the same format, 8 characters long, or may have records with 11 characters long.

I want to be able to do a lookup in the database so that it is only searching the first 8 characters of the lookup column. So that if the record in the column is 11 characters long, only take the first 8 characters and see if it matches the field in the program.

What I have now:

        Dim InvoiceNumber As String
        Dim strSearchInvoice As String
        strSearchInvoice = "Select * from TrackingInfo"
        acscmd = New OleDbCommand(strSearchInvoice, acsconn)
        acsdr = acscmd.ExecuteReader

        While acsdr.Read
            If acsdr(1).ToString = InvoiceNumber Then
                lblTrackingNum.Text = acsdr(7)
            End If
        End While
        acsdr.Close()

InvoiceNumber is always 8 characters long in the program. I need to lookup InvoiceNumber in the second column of my "TrackingInfo" database but only search the first 8 characters of that column. If a match is found, return value in another column and insert in lblTrackingNum.text.

I am still fairly new to the VB environment but I am willing to learn and take pointers. Any help would be greatly appreciated as I have used this forum for a lot of coding help but just recently decided to register.

Thank you.

[kevininstructor]

Most databases have some form of sub string function, MS-Access uses Mid$ which will work just fine with OleDb methods to return data. In the example below we get all fields where the first eight chars of Column1 meets the condition in SearchItem string.

VS2008 VB.NET fully tested with MS-Access 2007

Public Class Form1
   Private Sub Form1_Load( _
      ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim SearchItem As String = "15368234"
      Dim dt As New DataTable
      Using cn As New OleDb.OleDbConnection With _
         {.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb"}
         Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
            cmd.CommandText = _
               <SQL>SELECT * FROM Table1 Where Mid$(Column1,1,8) = '<%= SearchItem %>'</SQL>.Value
            cn.Open()
            Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
            If reader.HasRows Then
               While reader.Read
                  Console.WriteLine(reader.GetString(reader.GetOrdinal("Column2")))
               End While
            End If
         End Using
      End Using
   End Sub
End Class

You could even have variables for the start and total length to check in the where condition as shown below using the first two variables in the where condition as xml literals

Public Class Form1
   Private Sub Form1_Load( _
      ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim StartPosition As Integer = 1
      Dim TotalLength As Integer = 8

      Dim SearchItem As String = "15368234"
      Dim dt As New DataTable
      Using cn As New OleDb.OleDbConnection With _
         {.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb"}
         Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
            cmd.CommandText = _
               <SQL>
                  SELECT * 
                  FROM Table1 
                  Where Mid$(Column1,<%= StartPosition %>,<%= TotalLength %>) = '<%= SearchItem %>'
               </SQL>.Value
            cn.Open()
            Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
            If reader.HasRows Then
               While reader.Read
                  Console.WriteLine(reader.GetString(reader.GetOrdinal("Column2")))
               End While
            End If
         End Using
      End Using
   End Sub
End Class

[kevininstructor]

Looking at the problem from another angle, we have loaded the data into a DataTable. We can create a virtual column that uses the first eight chrs of the column to search for a match.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
   Dim dt As New DataTable
   dt.Columns.Add(New DataColumn("Col1", GetType(System.String)))
   dt.Columns.Add(New DataColumn("Col2", GetType(System.String)))
   dt.Columns.Add(New DataColumn("Col3", GetType(System.String)))
   dt.Columns("Col3").Expression = "SUBSTRING(Col1,1,8)"

   dt.Rows.Add(New Object() {"12345678901", "A"})
   dt.Rows.Add(New Object() {"12345678", "B"})
   dt.Rows.Add(New Object() {"12345677", "C"})
   dt.Rows.Add(New Object() {"12345678", "D"})
   dt.Rows.Add(New Object() {"12345677", "E"})
   DataGridView1.DataSource = dt
   DataGridView1.Columns("Col3").Visible = False
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
   Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)
   Dim Items = dt.Select("Col3 ='12345678'")
   For Each row As DataRow In Items
      Console.WriteLine(String.Join(",", row.ItemArray.Select(Function(s) s.ToString).ToArray))
   Next
End Sub

[stanav]

How about this?

Dim invoiceNUmber As String = "12345678"
strSearchInvoice = "Select * from TrackingInfo Where InvoiceNumber LIKE @invoiceNumber + '%'"
'....
acscmd.Parameters.AddWithValue("@invoiceNumber", invoiceNumber)

[Maverickz]

Since you are pulling all records instead of using a Where clause to only pull the relevant records, the easiest thing to do would be modify your If statement to be:

If Instr(acsdr(1).ToString, InvoiceNumber) = 1 Then

This will only return true if the first 8 characters of acsdr(1) exactly matches InvoiceNumber.

EDIT: I should point out that while this is the easiest thing to change, if your tables gets very large, not using a Where clause could cause a significant performance hit.
This could be mitigated by limiting the number of returned fields using a Where clause as such:

strSearchInvoice = "Select * from TrackingInfo Where InvoiceNumber LIKE " & InvoiceNumber

This would only return the records that contain the 8 digit InvoiceNumber that are a perfect match in the 8 digit format AND the records that contain the InvoiceNumber at any location in the 11 character format. This combined with the modified If statement would get you what you want.

[livedifferent]

Since you are pulling all records instead of using a Where clause to only pull the relevant records, the easiest thing to do would be modify your If statement to be:

If Instr(acsdr(1).ToString, InvoiceNumber) = 1 Then

This will only return true if the first 8 characters of acsdr(1) exactly matches InvoiceNumber.

EDIT: I should point out that while this is the easiest thing to change, if your tables gets very large, not using a Where clause could cause a significant performance hit.
This could be mitigated by limiting the number of returned fields using a Where clause as such:

strSearchInvoice = "Select * from TrackingInfo Where InvoiceNumber LIKE " & InvoiceNumber

This would only return the records that contain the 8 digit InvoiceNumber that are a perfect match in the 8 digit format AND the records that contain the InvoiceNumber at any location in the 11 character format. This combined with the modified If statement would get you what you want.

This worked! Thank you! I did not think about using LIKE.