to JOIN or INNER JOIN, that is the question...

[§tudz]

Hi,

I have two tables (well that are required for this function anyway)

- tbl_Items
- tbl_Bids

and this is my code:

Sub imgcmd_Search_Click(sender As Object, e As ImageClickEventArgs)
   Dim  MyConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & _
        server.mappath("cgi-bin/db/orctions.mdb"))
   Dim CommandText As String = "SELECT * FROM tbl_Items WHERE SearchData like '%" &  txt_Search.text & "%'"
 
   Dim myCommand As New OleDBCommand(CommandText, myConnection)
        myConnection.Open()
        rep_Search.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        rep_Search.DataBind()
End Sub

what I need to do is get the Fields 'Bidder' and 'BidValue' from 'tbl_Bids' as long as it matches the critera in the Where Clause which is looking at the 'tbl_Itmes'

I hope that is clear enough ... ?

Any help would be great thanks

[dj4uk]

Can you list the fields in both tables and state the relationship between them if it isn't that obvious.

[hellswraith]

Originally posted by §tudz
Hi,

I have two tables (well that are required for this function anyway)

- tbl_Items
- tbl_Bids

and this is my code:

Sub imgcmd_Search_Click(sender As Object, e As ImageClickEventArgs)
   Dim  MyConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & _
        server.mappath("cgi-bin/db/orctions.mdb"))
   Dim CommandText As String = "SELECT * FROM tbl_Items WHERE SearchData like '%" &  txt_Search.text & "%'"
 
   Dim myCommand As New OleDBCommand(CommandText, myConnection)
        myConnection.Open()
        rep_Search.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        rep_Search.DataBind()
End Sub

what I need to do is get the Fields 'Bidder' and 'BidValue' from 'tbl_Bids' as long as it matches the critera in the Where Clause which is looking at the 'tbl_Itmes'

I hope that is clear enough ... ?

Any help would be great thanks

You should try something like this. You need to fill in the common table column between them (identified with the SOMECOLUMN word)
"SELECT b.Bidder, b.BidValue FROM tbl_Items a
JOIN tbl_Bids b,
ON a.SOMECOLUMN = b.SOMECOLUMN
WHERE a.SearchData like '%" & txt_Search.text & "%'"

[hellswraith]

Also, that would work in SQL Server, but might not in Access.

[§tudz]

what should the real values be from b and a ? are they the tble names?

[§tudz]

this is the sql I used:


"SELECT b.*, a.* FROM tbl_Items a JOIN tbl_Bids b, ON a.ItemCode = b.ItemCode WHERE a.SearchData like '%" & txt_Search.text & "%'"


But this just errors?

[dj4uk]

Try:

"SELECT b.*, a.* FROM tbl_Items AS a INNER JOIN tbl_Bids AS b, ON a.ItemCode = b.ItemCode WHERE a.SearchData like '%" & txt_Search.text & "%'"

[§tudz]

I'm afraid that didn't work either,

This is the Error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Syntax error in FROM clause.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in FROM clause.

[§tudz]

I've got it to work now with this:


"SELECT tbl_Items.ItemCode, tbl_Items.Owner, tbl_Items.ItemName, tbl_Items.SearchData, tbl_Items.IsSold, tbl_Items.ShortDescription, tbl_Items.BidClosingDate, tbl_Items.SearchData, tbl_Bids.Bidder, tbl_Bids.BidValue, tbl_Bids.Time FROM tbl_Items INNER JOIN tbl_Bids ON tbl_Items.ItemCode = tbl_Bids.ItemCode WHERE tbl_Items.SearchData like '%" & txt_Search.text & "%'"

[hellswraith]

Sorry, in SQL you can alias tables, that is where the a and b came from. Access won't let you do that, you have to specify the tablename.field explicitly.

[§tudz]

thanks, anyway, wouldn't have had a clue to even start with the statement, so you helped loads.

Thanks again