ADO.NET and SQL Queries

Dev Shed Forums Sponsor:

March 18th, 2003, 09:48 PM

iamtgo3

Contributing User

Join Date: Nov 2001

Location: USA

Posts: 313

Time spent in forums: 3 h 31 m 33 sec
Reputation Power: 17

ADO.NET and SQL Queries

I am trying to do a join with ADO.NET. I have tested this Query is both Access and SQL Server and it works fine so I am thinking I missed something on my form. Here is the query it ain't pretty and may not be the best way to do it but it works. I think my error has to be something with the connection on my VB.NET form. It is one of those errors that drive you nuts cause the solution will probably have nothing to do with the query.

PHP Code:


		
			
 SELECT Sales.SalesID, Sales.WinningBid, Items.Location, Items.Description, Bidder.BidderNumber, Bidder.Name, Bidder.Address, Bidder.City, Bidder.State, Bidder.Zip, Bidder.Phone

FROM (Bidder INNER JOIN Sales ON Bidder.BidderNumber = Sales.BidderNumber) INNER JOIN Items ON Sales.ItemLocation = Items.Location

WHERE (Sales.BidderNumber = '1')

Here is my error if it helps at all.

PHP Code:


		
			
 Syntax error (missing operator) in query expression 'Bidder.BidderNumber = Sales.BidderNumber INNER JOIN Items ON Sales.ItemLocation = Items.Location'.

Now what I have found out is if you use the DataAdapter control it does not do multiple joins really well. What I had to do is program this through code with out controls. When I added the multiple join to the DataAdaper it would rewrite it like this.

PHP Code:


		
			
 SELECT Sales.SalesID, Sales.WinningBid, Items.Location, Items.Description, Bidder.BidderNumber, Bidder.Name, Bidder.Address, Bidder.City, Bidder.State, Bidder.Zip, Bidder.Phone

FROM Bidder INNER JOIN Sales ON Bidder.BidderNumber = Sales.BidderNumber INNER JOIN Items ON Sales.ItemLocation = Items.Location

WHERE (Sales.BidderNumber = '1')

This would give me the error stated above. So if you are having trouble getting more complex SQL Queries to work with the DataAdapter you may nee to skip the easy way out and do this through code and it should work just fine.

Last edited by iamtgo3 : March 20th, 2003 at 08:22 AM.

May 30th, 2003, 09:03 AM

cmavin

Junior Member

Join Date: May 2003

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

Inner joins?

Did you ever get this to work?
i'm ado programmer and i'm undertaking an ado.net project at the moment, does the ado.net data adapter even support joins? i've heard that you need a different data adapter for each table you want to access.

May 30th, 2003, 09:17 AM

iamtgo3

Contributing User

Join Date: Nov 2001

Location: USA

Posts: 313

Time spent in forums: 3 h 31 m 33 sec
Reputation Power: 17

Well I did a work around if I remembr correctly. I think I just did it through code. What I think you can do is a single JOIN but not a multiple JOINS with the DataAdapter control. ADO.NET works great it is just a matter of understanding the differences and how it works. I have found it is easier to do complex things through code rather then controls.

ADO.NET and SQL Queries

Developer Shed Advertisers and Affiliates