Joining two table error

[msqueen082]

I am working with database and I am trying to join two tables . In one of my methods I wrote this code

'this will construct a client object.  The client data
    'for the target client id(parameter) will be retrieved
    'from the client data table.  All of the transactions for
    'the client will be retrieved from the client table and
    'stored in m_tranlist.  If the client id is not on file
    'an exception will be thrown
    Public Sub New(ByVal targetID As Integer)
        'Dim clientFile As System.IO.StreamReader 'file of client data
        'Dim clientFileName As String = "faclient.txt"

        Dim conString As String = _
           "Provider = Microsoft.JET.OLEDB.4.0;" & _
           "Data Source = barestern2003.mdb"

        'Declare adapter
        Dim tranAdapter As OleDb.OleDbDataAdapter

        'Declare a dataTable
        Dim tranTable As DataTable

        Dim selectTranStmt As String

        Dim holdtranClient As Integer   'id found on client file record
        Dim holdtranStock As String 'last name from record
        Dim holdtranDate As Date 'first name from record
        Dim holdtranType As String 'phone from record
        Dim holdtranShares As Integer
        Dim holdtranprice As Double
        Dim idFound As Boolean

        Dim holdId As Integer
        Dim holdLName As String
        Dim holdFName As String
        Dim holdPhone As String

        'Build a select command
        selectTranStmt = "select ID, last_name, first_name, phone , tran_client, tran_stock, tran_date, tran_type, tran_shares, tran_price " & _
                          "  from transact, client " & _
                          "  where ID =   " & targetID & " and tran_client = ID "

        'used for testing select statement
        MessageBox.Show(selectTranStmt)

        'instantiate adapter
        tranAdapter = New OleDb.OleDbDataAdapter(selectTranStmt, conString)

        'instantiate the data table
        tranTable = New DataTable

        tranAdapter.Fill(tranTable)

        If tranTable.Rows.Count = 0 Then
            MessageBox.Show("no trans found")
            Exit Sub
        End If

        For index = 0 To tranTable.Rows.Count - 1
            holdtranClient = CInt(tranTable.Rows(index).Item(1))
            holdtranStock = CStr(tranTable.Rows(index).Item(2))
            holdtranDate = CDate(tranTable.Rows(index).Item(3))
            holdtranType = CStr(tranTable.Rows(index).Item(4))
            holdtranShares = CInt(tranTable.Rows(index).Item(5))
            holdtranprice = CDbl(tranTable.Rows(index).Item(6))
        Next

        holdId = CInt(tranTable.Rows(0).Item(0))
        holdLName = CStr(tranTable.Rows(0).Item(1))
        holdFName = CStr(tranTable.Rows(0).Item(2))
        holdPhone = CStr(tranTable.Rows(0).Item(3))


        'is this the target client?
        If CInt(holdId) = targetID Then
            idFound = True
            m_ID = CInt(holdId)
            m_lname = holdLName
            m_fname = holdFName
            m_phone = holdPhone
            LoadTranList()
        End If
    End Sub

It keeps giving me this error
"conversion from string"smith" to type integer is not valid"

I am trying two join two tables - transaction table and client table - they are joined
by client id...transaction table its the tran_client which is an integer and in client is id which is also an integer..It seems to be joining it to the last name..cant really see the error in my code..Could sure use some help solving this..
Thank you

[kvprajapati]

What is field type of ID and tran_client columns?

[msqueen082]

both field types are integer (id number or keys)

look at your for loop. You have cint(item(1)). Item 1 is the last name. Now look at the code block after the for loop. You have holdid = cint(item(0)), lastname = cstr(item(1)). Those are correct.