how to retrieve multiple columns from two tables in C#, using SQL?

Question

I have two tables and need to retrieve some columns from both tables based on the common key which is ProductID. I tried the following but got an error "Incorrect syntax near the keyword 'JOIN'"

string selectProductStatement = 
"SELECT Products.Code, Products.Description,"+
"Products.Category, Products.Price, BookProducts.Author"+
"FROM Products"+
"INNER JOIN BookProducts ON (Products.@ProductID = BookProducts.ProductID)";

SqlCommand comm = new SqlCommand(selectProductStatement, connection);
comm.Parameters.AddWithValue("@productID", productID);

the tables are:

Products

  ProductID
  Code
  Description
  Category
  Price

BookProducts

  BookID PK
  ProductID FK
  Author

you're missing some spaces.. after BookProducts.Author and after FROM Products — Terkel
– Terkel, Commented Feb 23, 2012 at 16:55

M.Babcock · Accepted Answer · 2012-02-23 16:55:08Z

4

You're missing spaces in your SQL:

string selectProductStatement = 
    "SELECT Products.Code, Products.Description,"+
    "Products.Category, Products.Price, BookProducts.Author"+
    " FROM Products"+
    " INNER JOIN BookProducts ON (Products.@ProductID = BookProducts.ProductID)";

answered Feb 23, 2012 at 16:55

M.Babcock

19k6 gold badges57 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

WraithNath · Accepted Answer · 2012-02-23 17:01:18Z

Personally I like to use a literal string for SQL commands to get rid of all the concatination all together. Saves time and headaches!

note the @ symbol before defining the string. Also wrapping commands in a 'using' brace means you cant forget to dispose of it.

 string selectProductStatement = @"           SELECT 
                                              Products.Code, 
                                              Products.Description,
                                              Products.Category, 
                                              Products.Price, 
                                              BookProducts.Author
                                              FROM Products
                                              INNER JOIN BookProducts 
                                              ON (Products.@ProductID = BookProducts.ProductID) ";
            string connectionString = string.Empty;

            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(selectProductStatement, sqlConnection))
                {
                    sqlCommand.Parameters.AddWithValue("@productID", productID);

                    //Etc
                }
            }

Roy Dictus · Accepted Answer · 2012-02-23 16:55:18Z

3

There is no space between Products and INNER. Add a space before and after FROM Products on the 3rd line of your query and you'll be fine :-)

answered Feb 23, 2012 at 16:55

Roy Dictus

33.3k9 gold badges64 silver badges77 bronze badges

Comments

Carl Sharman · Accepted Answer · 2012-02-23 16:55:25Z

2

Reckon you just need a space between "PRODUCTS" and "INNER" there mate, i.e.

"FROM Products "+
"INNER JOIN BookProducts ON (Products.@ProductID = BookProducts.ProductID)";

answered Feb 23, 2012 at 16:55

Carl Sharman

4,8931 gold badge35 silver badges31 bronze badges

Comments

Morphed · Accepted Answer · 2012-02-23 16:57:53Z

2

Defining the string as a string literal can help with erroneous spacing:

string selectProductStatement = 
 @"SELECT Products.Code, Products.Description, 
Products.Category, Products.Price,
 BookProducts.Author FROM Products 
INNER JOIN BookProducts ON (Products.@ProductID =
BookProducts.ProductID)";

It omits the need - and associated problems/readability of multiple string fragments

answered Feb 23, 2012 at 16:57

Morphed

3,6292 gold badges32 silver badges59 bronze badges

Comments

atbebtg · Accepted Answer · 2012-02-23 16:59:02Z

2

Try the following

string selectProductStatement = 
"SELECT Products.Code, Products.Description,"+
"Products.Category, Products.Price, BookProducts.Author"+
" FROM Products "+
"INNER JOIN BookProducts ON (Products.ProductID = BookProducts.ProductID) WHERE Products.ProductId = @productID";

edited Feb 23, 2012 at 16:59

answered Feb 23, 2012 at 16:55

atbebtg

4,1139 gold badges40 silver badges49 bronze badges

Comments

Taryn · Accepted Answer · 2012-02-23 17:02:16Z

0

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

Above is the general syntax for inner join query. Just follow accordingly. :)

edited Feb 23, 2012 at 17:02

Taryn

249k57 gold badges374 silver badges409 bronze badges

answered Feb 23, 2012 at 16:59

jyotiprakash

2,0861 gold badge20 silver badges27 bronze badges

1 Comment

Taryn Over a year ago

The question had the correct syntax there were spaces missing.

Collectives™ on Stack Overflow

how to retrieve multiple columns from two tables in C#, using SQL?

Products

BookProducts

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Products

BookProducts

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related