oleDBCommand and SQL syntax

[minn]

Hello,

I am very new to VB.Net and ADO and am trying to write an SQL select statement with joins. However, the syntax that i am using is not correct.

This is what i require:

"SELECT GKLS_Software.Application, Software_Type.SoftwareType, Vendors.Vendor, GKLS_Software.ProductKey
FROM GKLS_Software, Vendors, Software_type
INNER JOIN Software_Type ON Software_Type.SoftwareTypeID= GKLS_Software.SoftwareTypeID
INNER JOIN Vendors ON Vendors.VendorID=GKLS_Software.VendorsID
ORDER BY GKLS_Software.Application"

Can anyone please help me to transform this into the correct Oledbcommand syntax for my vb code.

Also can someone give me a few pointers on the syntax for writing future sql queries in vb.net using oledbcommand?

Help much appreciated!!!

 

[TechGnome]

this is the problem:
FROM GKLS_Software, Vendors, Software_type

You listed all the tables, then name them again in the joins.....
You should only have the main table in the FRom... GKLS_Software....
The others get names in the joins. The only time you need to name the tables in the FROM is when you link them in the WHERE (instead of the join)..

-tg

 

[minn]

I am still having problems with the SQL syntax. I am using the following statement

sql = "SELECT GKLS_Software.Application, Software_Type.SoftwareType, Vendors.Vendor, GKLS_Software.ProductKey " & _
"FROM(GKLS_Software)" & _
"INNER JOIN Software_Type ON Software_Type.SoftwareTypeID = GKLS_Software.SoftwareTypeID " & _
"INNER JOIN Vendors ON Vendors.VendorID=GKLS_Software.VendorsID " & _
"ORDER BY GKLS_Software.Application"

I think i may not be using joins properly. Can anyone help???

 

[TechGnome]

Hmmmmm....
See if this works (sorry, but I'm don't use Access, so sometimes I'm just stabbing in the dark....).

sql = "SELECT GKLS_Software.Application, Software_Type.SoftwareType, Vendors.Vendor, GKLS_Software.ProductKey " & _
"FROM GKLS_Software, Software_Type, Vendors" & _
"WHERE Software_Type.SoftwareTypeID = GKLS_Software.SoftwareTypeID " & _
"AND Vendors.VendorID=GKLS_Software.VendorsID " & _
"ORDER BY GKLS_Software.Application"

I think this is the way that Access preferrs joins to be done.

-tg

 

[minn]

No Luck mate,

any other idea's anyone?

 

[TechGnome]

Here's a silly idea.... open the Access DB, and build a View with the tables and fields you want. Then look at the SQL view of the query and copy the results there.....

-tg

 

[minn]

Thanks again.. but that was the first thing i tried and no luck