VB.NET - Find entry from embedded database

This should be simple but I can't find the answer.
I have a small Access database with only 3 columns, for example:
ID     ComputerNo     TestCell
1       DSF00234          TestCell14
2       DSF00976          TestCell09

My applications purpose is for the end user to select a TestCell from a ComboBox (the contents of the ComboBox are generated by the contents of the TestCell column). When they select the testcell I want to be able to populate a TextBox with the associated ComputerNo.
The problem is I am having difficulty finding out how to achieve the second part.
The DataGridView on another form shows the entire table and I can select a TestCell from the ComboBox but I do not know how to populate the TextBox (txtCompNo) with its associated ComputerNo on the same row.
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.GedTestCellsTableAdapter1.Fill(Me.TestCellListDataSet1.GEDTestCells)
        ComboBox1.DataSource = TestCellListDataSet1.GEDTestCells
        ComboBox1.ValueMember = "TestCell"
    End Sub
    Private Sub GEDTestCellsBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
        Me.Validate()
        Me.GEDTestCellsBindingSource.EndEdit()
        Me.TableAdapterManager1.UpdateAll(Me.TestCellListDataSet1)
    End Sub
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim sel = ComboBox1.ValueMember = "TestCell"
        Dim result As String
        Dim valueToSearch As String = sel
        For Each dTable As DataTable In TestCellListDataSet1.Tables
            For Each dRow As DataRow In dTable.Rows
                For index As Integer = 0 To dTable.Columns.Count - 1
                    Convert.ToString(dRow(index)).Contains(valueToSearch)
                    result = dRow.Item("ComputerNo")
                Next
            Next
        Next
        txtCompName.Text = result
    End Sub

Open in new window

The code above just inserts the last ComputerNo entry in the column in the txtCompName.Text field and it doesn't matter what TestCell you select in the ComboBox it remains the same.
LVL 2
fruitloopyAsked:
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.

AndyAinscowFreelance programmer / ConsultantCommented:
>>...it doesn't matter what TestCell you select in the ComboBox it remains the same.

Because you tell it to always populate with the final value.  You need something like this:

if Convert.ToString(dRow(index)).Contains(valueToSearch) then  result = dRow.Item("ComputerNo")
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
fruitloopyAuthor Commented:
Thanks Andy. You've rescued me a few times now!
Here's the working code:
Dim sel As String = ComboBox1.Text.ToString()
        Dim result As String
        Dim valueToSearch As String = sel
        For Each dTable As DataTable In TestCellListDataSet1.Tables
            For Each dRow As DataRow In dTable.Rows
                For index As Integer = 0 To dTable.Columns.Count - 1
                    If Convert.ToString(dRow(index)).Contains(valueToSearch) Then
                        result = dRow.Item("ComputerNo")
                    Else
                        MsgBox("Computer name not found")
                    End If
                Next
            Next
        Next
        txtCompName.Text = result

Open in new window

I also had to change the "Dim sel = ComboBox1.ValueMember = "TestCell" line as it just returned True or False
0
AndyAinscowFreelance programmer / ConsultantCommented:
                    Else
                        MsgBox("Computer name not found")
                    End If

Open in new window


erm, won't that give you loads of messagebox's even if the computer name is found?

probably better to change
        txtCompName.Text = result

Open in new window

to something like:
if len(result) = 0 then
  MsgBox("Computer name not found")
else
 txtCompName.Text = result
end if

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

fruitloopyAuthor Commented:
Yeah I took that Else statement out straight away after that happened!
The only problem with your code amendment above is that the first time it runs it show the "Computer name not found" message. After that it's fine.
I tried making a Private Sub ComboBox1.click but as soon as I click it it gives the same message
0
AndyAinscowFreelance programmer / ConsultantCommented:
What is the value of 'valueToSearch' in the first time?
(You might need to check that and have a return at the start of the complete procedure if it is not set to a value that indicates a selection has been made in the combo).
0
fruitloopyAuthor Commented:
I threw it all into an Try - Catch thingy and it seems to have corrected it. I have tested it with blank entries and it seems to work.
Try
            Dim sel As String = ComboBox1.Text.ToString()
            Dim result As String
            Dim valueToSearch As String = sel
            For Each dTable As DataTable In TestCellListDataSet1.Tables
                For Each dRow As DataRow In dTable.Rows
                    For index As Integer = 0 To dTable.Columns.Count - 1
                        If Convert.ToString(dRow(index)).Contains(valueToSearch) Then
                            result = dRow.Item("ComputerNo")
                        End If
                    Next
                Next
            Next
            Try
                If Len(result) > 0 Then
                    txtCompName.Text = result
                End If
            Catch ex As Exception
                MsgBox("Computer name not found")
            End Try
        Catch ex As Exception
            If ex.Message.Contains("Conversion from") Then
                MsgBox("An entry in the database does not have a Computer Number entry")
            Else
                MsgBox("There are some missing entries or errors in the database" & vbCrLf + ex.Message)
            End If

        End Try

Open in new window

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.