Data from two tables
-
Hello everybody. I am trying to retrive data from a Access Database containing two tables. 1) tblKeyword 2) tblArea In tblKeyword there are two attributes: a) Keyword and b) AreaName in tblArea there are couple including AreaName which is the connection between the two tables. I am trying to use the following code: ======================================================================== Dim strConnection As String = OleDbConnection1.ConnectionString Dim connect As New OleDbConnection(strConnection) Dim test As String Dim te As String Dim SQLString As String connect.Open() te = cmbKeywords.Items(cmbKeywords.SelectedIndex) On Error Resume Next SQLString = "SELECT tblKeywords.Keyword, tblArea.AreaName, tblArea.StartDate, tblArea.JobNumber FROM tblKeywords INNER JOINT tblArea ON tblKeywords.AreaName = tblArea.AreaName WHERE tblKeyWords.Keyword =" & "'" & te & "'" Dim cmd As New OleDbCommand(SQLString, connect) Dim reader As OleDbDataReader = cmd.ExecuteReader() While reader.Read() txtAreaName.Text = reader.GetValue(1) End While ======================================================================== The SQL command goes through OK but the reader.Read is causing an error. How can i retrive the data from that SQL. I am lost. :confused: I know that the GetValue gets a value from attributes in one table. But with the inner joint I need values from both tables. Any help would be appreaciated. Thank you.. Still trying to find the way
-
Hello everybody. I am trying to retrive data from a Access Database containing two tables. 1) tblKeyword 2) tblArea In tblKeyword there are two attributes: a) Keyword and b) AreaName in tblArea there are couple including AreaName which is the connection between the two tables. I am trying to use the following code: ======================================================================== Dim strConnection As String = OleDbConnection1.ConnectionString Dim connect As New OleDbConnection(strConnection) Dim test As String Dim te As String Dim SQLString As String connect.Open() te = cmbKeywords.Items(cmbKeywords.SelectedIndex) On Error Resume Next SQLString = "SELECT tblKeywords.Keyword, tblArea.AreaName, tblArea.StartDate, tblArea.JobNumber FROM tblKeywords INNER JOINT tblArea ON tblKeywords.AreaName = tblArea.AreaName WHERE tblKeyWords.Keyword =" & "'" & te & "'" Dim cmd As New OleDbCommand(SQLString, connect) Dim reader As OleDbDataReader = cmd.ExecuteReader() While reader.Read() txtAreaName.Text = reader.GetValue(1) End While ======================================================================== The SQL command goes through OK but the reader.Read is causing an error. How can i retrive the data from that SQL. I am lost. :confused: I know that the GetValue gets a value from attributes in one table. But with the inner joint I need values from both tables. Any help would be appreaciated. Thank you.. Still trying to find the way
Chaos Machine wrote: The SQL command goes through OK What you mean is that you are able to create the SqlCommand, however no validation is done on the provided SQL string provided at this point. Chaos Machine wrote: but the reader.Read is causing an error At the point of opening the datareader, the SQL provided is executed, so any syntactical errors will be thrown here. Chaos Machine wrote: INNER JOINT Now, if that were my mistake it would've been freidian ;) Oh, and another pointer: Chaos Machine wrote: I know that the GetValue gets a value from attributes in one table. But with the inner joint I need values from both tables. By doing an join, you are effectively creating one table (albeit a virtual table that doesnt exist in the DB). Your single table will have the columns you specify in the select list: Chaos Machine wrote: SELECT tblKeywords.Keyword, tblArea.AreaName, tblArea.StartDate, tblArea.JobNumber Hope it helps.
-
Chaos Machine wrote: The SQL command goes through OK What you mean is that you are able to create the SqlCommand, however no validation is done on the provided SQL string provided at this point. Chaos Machine wrote: but the reader.Read is causing an error At the point of opening the datareader, the SQL provided is executed, so any syntactical errors will be thrown here. Chaos Machine wrote: INNER JOINT Now, if that were my mistake it would've been freidian ;) Oh, and another pointer: Chaos Machine wrote: I know that the GetValue gets a value from attributes in one table. But with the inner joint I need values from both tables. By doing an join, you are effectively creating one table (albeit a virtual table that doesnt exist in the DB). Your single table will have the columns you specify in the select list: Chaos Machine wrote: SELECT tblKeywords.Keyword, tblArea.AreaName, tblArea.StartDate, tblArea.JobNumber Hope it helps.
Hello J4amieC, thanks for replying. I am still at a loss of what to do. Can you please show me with a bit of code how I can retrive the informations that i have collect from the SQL command? I am trying from books and online info, but still nothing. Thank you for your help. Still trying to find the way
-
Hello J4amieC, thanks for replying. I am still at a loss of what to do. Can you please show me with a bit of code how I can retrive the informations that i have collect from the SQL command? I am trying from books and online info, but still nothing. Thank you for your help. Still trying to find the way
OK, my comment was that you had a error in your SQLString (you used INNER JOINT instead of INNER JOIN) it should read:
SQLString = "SELECT tblKeywords.Keyword, tblArea.AreaName, tblArea.StartDate, tblArea.JobNumber FROM tblKeywords INNER JOIN tblArea ON tblKeywords.AreaName = tblArea.AreaName WHERE tblKeyWords.Keyword ='" & te & "'"Now when you execute this string you will have the following columns: 0: Keyword (from tblKeywords) 1: AreaName (from tblArea) 2: StartDate (from tblArea) 3: JobNumber (from tblArea) so if you use: reader.GetValue(0) you will get the Keyword column. -
OK, my comment was that you had a error in your SQLString (you used INNER JOINT instead of INNER JOIN) it should read:
SQLString = "SELECT tblKeywords.Keyword, tblArea.AreaName, tblArea.StartDate, tblArea.JobNumber FROM tblKeywords INNER JOIN tblArea ON tblKeywords.AreaName = tblArea.AreaName WHERE tblKeyWords.Keyword ='" & te & "'"Now when you execute this string you will have the following columns: 0: Keyword (from tblKeywords) 1: AreaName (from tblArea) 2: StartDate (from tblArea) 3: JobNumber (from tblArea) so if you use: reader.GetValue(0) you will get the Keyword column.Thank you J4amieC it worked!!! Thank you so much. Now i have to expand this so when i have two areas with the same keyword a list is populated. Hmmm lets see if i can do it!! LOL Thank you again for your help...:-D Still trying to find the way