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..