Displaying query from three tables

slims07 · March 31st, 2008, 10:34 AM

I am using sql server 2000 and vb 2005. I am trying to join three tables programmatically using the inner join SQL statement and display the result on a datagridview, however i am getting an "end of statement expected" error. Please help

samjudson · March 31st, 2008, 10:41 AM

Well there will likely be an error in you SQL. Try getting the SQL to run in Query Analyzer first to get it right.

/- Sam Judson : Wrox Technical Editor -/

slims07 · March 31st, 2008, 11:00 AM

the sql statement works ok in query analyzer, but not in vb 2005.
What I need to know, is it possible to display the query from three tables on a datagridview in vb 2005, if it is, what do i need to do?

samjudson · March 31st, 2008, 11:06 AM

If the SQL can be run in query analyzer then there must be an error in the way you are running it.

Show us the code you are running and we can try to work out what you are doing wrong, but without more to go on its very hard to guess.

/- Sam Judson : Wrox Technical Editor -/

slims07 · March 31st, 2008, 01:09 PM

Here is the SQL statement that work ok in query analyzer but not in vb 2005.

SELECT table1.table1Field_name, table2.table2Field_name, table3.table3Field_name
FROM table1 INNER JOIN table2 ON table1.table1Field_name = table2.table2Field_name INNER JOIN
table3 ON table2.table2Field_name = table3.table3Field_name

gbianchi · March 31st, 2008, 01:48 PM

Hi there, the SQL looks fine, can you trace the error to the exact line in your code?

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

samjudson · March 31st, 2008, 02:45 PM

Yes, if it works fine in SQL analyzer then it must be an error in the VB code you are using to implement the SQL.

Be careful if concatonating strings that you are leaving spaces at the end of each line

Code:

Dim sql As String = "SELECT table1.table1Field_name, table2.table2Field_name, table3.table3Field_name " _ ' Note space at thend of string
 & "FROM table1 INNER JOIN table2 ON table1.table1Field_name = table2.table2Field_name INNER JOIN " _ 
 & "table3 ON table2.table2Field_name = table3.table3Field_name"

Dim conn As New SqlConnection
conn.ConnectionString = "yadayada"

Dim adapter As New SqlDataAdapter
adapter.SelectCommand = new SqlCommand(sql, conn);

Dim ds As New DateSet()

adapter.Fill(ds);

/- Sam Judson : Wrox Technical Editor -/

slims07 · April 2nd, 2008, 10:19 AM

Thx. It was the way I was concatenating the strings. But now I have another error message when filling the dataset

dataAdapter.fill(ds, "dsName")

The error message is "Incorrect syntax near the keyword 'INNER'."

samjudson · April 2nd, 2008, 10:47 AM

Well it looks like there is still an error in the way you are concatenating your strings, as the SQL statement is not valid.

/- Sam Judson : Wrox Technical Editor -/