none
Finding part of a string in a datarow and use it to... RRS feed

  • Question

  •  

    Hello,

     

    VB2005 - MS ACCESS 2003 - LEVEL: New to Programming

    Untyped DataSet and Unbound Controls

     

    I would dearly appreciate some help with the following: (I'll first explain what the current situation is, then I'll state my problem and finally I list the code that I am using)

     

    Variable:

    I have declared a variable magnetic as string

    Swiping a Magnetic Card through a USB Magnetic Card reader sets variable magnetic to 34906743583538789

     

    Form Elements:

    I have a combo box called cboMagnetic and its selectedindex value is hooked up to the bindingmanagerbase.position of the MS Access 2003 database. (The Combo Box serves as navigator)

     

    Database:

    I have come as far as being able to get a list of Personal ID's from an MS-Access 2003 Database Table personalData, column PersNumber (type: text). A Personal ID Number in that list would look like: 67435835

     

    Objective:

    What I want to do is to compare all row items to variable magnetic. Once a match is found as in the example above (Database row item 67435835 is part of variable magnetic 34906743583538789) I should:

     

    Use the row item value 67435835 and set that as cboMagnetic.SelectedIndex so that the bindingmanagerbase.position changes and displays the record details (Name, Address etc.)  for the person that swiped his Magnetic Club Card

     

    Help Needed with:

    I can't seem to figure out how to:

    1. Compare the row items from the database to the variable magnetic value (matching them) 
    2. Cast the found row item value to a string and set a variable with that string so that I can set the cboMagnetic.SelectedIndex with the string variable
    3. Or directly set the cboMagnetic.SelectedIndex with the row item value found

    Below are some extracts of code that I use to accomplish my goals:

    Thank you in advance for any offered help!

    - Tsadok

     

     

     

    Dim bmbdraft As BindingManagerBase

    Dim dsDraft As New DataSet()

    Dim daDraft As New OleDb.OleDbDataAdapter()

    Dim conDraft As New OleDb.OleDbConnection()

    Dim magnetic As String

    Dim MdbFileName As String

    --------

    Public Sub CreateObjects()

    'This line comes from the Registry else from the OpenFileDialog

    MdbFileName = "C:\Documents and Settings\Tsadok\My Documents\Visual Studio 2008\Projects\Draft\Draft\draft.mdb"

    'Create objects for database Communication

    Dim sDraftConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MdbFileName

    conDraft.ConnectionString = sDraftConnection

    Dim cmdPersonalData As New OleDb.OleDbCommand

    cmdPersonalData.Connection = conDraft

    Dim sPersonalDataSelect As String = "Select PersonalData.* FROM PersonalData"

    cmdPersonalData.CommandText = sPersonalDataSelect

    daDraft.SelectCommand = cmdPersonalData

    Dim cbPersonalData As New OleDb.OleDbCommandBuilder()

    cbPersonalData.DataAdapter = daDraft

    End Sub

    -------------------------------

    Private Sub BindControls()

    With cboMagnetic

    .DataSource = dsDraft.Tables("PersonalData")

    .DisplayMember = "PersNumber"

    End With

    txtPersNumber.DataBindings.Add("Text", dsDraft, "PersonalData.PersNumber")

    txtFname.DataBindings.Add("Text", dsDraft, "PersonalData.Fname")

    txtLname.DataBindings.Add("Text", dsDraft, "PersonalData.Lname")

    txtIdentity.DataBindings.Add("Text", dsDraft, "PersonalData.Identity")

    txtMobile.DataBindings.Add("Text", dsDraft, "PersonalData.Mobile")

    txtFatherName.DataBindings.Add("Text", dsDraft, "PersonalData.Fathername")

    dtpDob.DataBindings.Add("Text", dsDraft, "PersonalData.Dob")

    End Sub

    ------------------------------

    Private Sub FindMagnetic()

    magnetic = txtMagnetic.Text

    Dim drLineItem As DataRow

    Dim strDrLineItem As String = ""

    Dim i, c As Integer

    c = dsDraft.Tables("Personaldata").Rows.Count() - 1 'Give me total rows in Column PersNumber

    For i = 0 To c 'From 0 to 373

    drLineItem = dsDraft.Tables("personalData").Rows.Item(i)

    Console.WriteLine(drLineItem("PersNumber")) 'gives me a list with all persNumbers

    Next

    Here I got stuck - what to do to, please help???

    End Sub

    -----------------------------

    Private Sub cboMagnetic_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboMagnetic.SelectedIndexChanged

    If Not BtnLoading Then

    bmbdraft.Position = cboMagnetic.SelectedIndex

    End If

    End Sub

    Monday, January 28, 2008 10:23 AM

Answers

  • This is easy. The reason it didn't found the record in the second example because the regular expression is designed to look for 4 digits then your personal number and again 5 digits. In the second case 860800534220700000000000000000000000 there are many 0s after the personal number in question.

    "^[0-9]{4}" & currentPersonalNo & "[0-9]{5}$"


    So what to do? Just tune up the above regular expression to match your requirement. If you say the personal number can be anywhere in the 860800534220700000000000000000000000 magnetic number then use the following regex.

    "^[0-9]*" & currentPersonalNo & "[0-9]*$"

    I think by now you must have figured out how to customize that regular expression string.

    ^ means begining of string
    [0-9] means any number from 0-9
    * means that the number cab occour 0 or more number of times
    $ means end of string
    {4} means the number can occur exactly 4 times.

    Hope this helps.
    Monday, January 28, 2008 4:10 PM

All replies

  • I hope following shall fulfill your requirement.

    Code Snippet

    Dim matchedPersonalNo As String = String.Empty
    Dim currentPersonalNo As String = String.Empty


    For i = 0 To c 'From 0 to 373

        drLineItem = dsDraft.Tables("personalData").Rows.Item(i)
        Console.WriteLine(drLineItem("PersNumber")) 'gives me a list with all persNumbers
        currentPersonalNo = drLineItem("PersNumber").ToString()

        ' check if the item matches with database record
        If System.Text.RegularExpressions.Regex.IsMatch(magnetic, "^[0-9]{4}" & currentPersonalNo & "[0-9]{5}$") Then
            matchedPersonalNo = currentPersonalNo
                    Exit For
        End If
    Next

    ' set the current selection in the combo box to matched item
    If Not String.IsNullOrEmpty(matchedPersonalNo) Then
        cboMagnetic.Text = matchedPersonalNo
    End If

    Monday, January 28, 2008 12:10 PM
  • Thank you for your quick reply.

    Unfortunately the code doesn't do what you intended it to do.

     

    I debugged it in depth and found that at:

     

    currentPersonalNo = drLineItem("PersNumber").ToString()

    only the first entry of the column is selected and not the whole array/collection of 373 Id numbers - this is right because the loop has not yet gone to the next record, then it compares that value to magnetic, as it (probably not) does with code:

    If System.Text.RegularExpressions.Regex.IsMatch(magnetic, "^[0-9]{4}" & currentPersonalNo & "[0-9]{5}$") Then

    matchedPersonalNo = currentPersonalNo

    When I set a breakpoint at NEXT and read the variables currentPersonalNo and matchedPersonalNo and Magnetic then I get:

    Magnetic = 8608005417050110000000000000000000000

    MatchedPersonalNo = ""

    currentPersonalNo = 5342207

     

    This is correct for the above scenario

     

    If I however change magnetic to be: 860800534220700000000000000000000000

    Then MacthedPersonalNo should be = 5342207 but it stays always "" (Nothing)

    In Other Words the matching doesn't work.

     

    Then If I let the loop finish until it iterated through all the 373 record all values BUT Magnetic are set to "" (nothing)

    PS: I Tired replacing the String Expressions with * . ? {0,} and others but MacthedPersonalNo always stays "" Nothing

    this is because the input string doesn't necesarily have to begin with 5 characters and end with 10 or 15...

    Any Idea?

     

    Monday, January 28, 2008 3:35 PM
  • This is easy. The reason it didn't found the record in the second example because the regular expression is designed to look for 4 digits then your personal number and again 5 digits. In the second case 860800534220700000000000000000000000 there are many 0s after the personal number in question.

    "^[0-9]{4}" & currentPersonalNo & "[0-9]{5}$"


    So what to do? Just tune up the above regular expression to match your requirement. If you say the personal number can be anywhere in the 860800534220700000000000000000000000 magnetic number then use the following regex.

    "^[0-9]*" & currentPersonalNo & "[0-9]*$"

    I think by now you must have figured out how to customize that regular expression string.

    ^ means begining of string
    [0-9] means any number from 0-9
    * means that the number cab occour 0 or more number of times
    $ means end of string
    {4} means the number can occur exactly 4 times.

    Hope this helps.
    Monday, January 28, 2008 4:10 PM