1

I am trying to implement a GetProduct method that lets me retrieve the product code of the product. I am using a database file that has my products table. But when I run I get a message saying

Incorrect syntax near Product

I can't for the life of me understand why it isn't working. Any thoughts?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace ProductMaintenance
{
    class ProductDB
    {
        static Product product = new Product();

        public static Product GetProduct(string code)
        {
            SqlConnection connection = MMABooksDB.GetConnection();

            string select = "SELECT ProductCode, Description, UnitPrice"
                          + "FROM Products"
                          + "WHERE ProductCode = @ProductCode";

            SqlCommand selectCommand = new SqlCommand(select, connection);
            selectCommand.Parameters.AddWithValue("@ProductCode", code);

            try
            {
                connection.Open();

                SqlDataReader prodReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);

                if (prodReader.Read())
                {
                    product.Code = prodReader["ProductCode"].ToString(); ;
                    product.Description = prodReader["Description"].ToString();
                    product.Price = ((decimal)prodReader["Price"]);

                    return product;
                }
                else
                {
                    return null;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jerry Smith
  • 77
  • 6
  • 2
    Missing a space after UnitPrice or before FROM. Why not use the verbatim string character @ and remove all those + to concatenate strings? – Steve Oct 05 '16 at 23:01
  • Thats it! It Worked! – Jerry Smith Oct 05 '16 at 23:04
  • 2
    Side note - catching an exception just to re-throw it is pointless. All it does is reset the stack trace, giving you _less_ information to debug with. Don't catch an exception unless you plan to do something with it other then rethrowing it. – D Stanley Oct 05 '16 at 23:17
  • you could use [String.Format](http://stackoverflow.com/questions/4671610/why-use-string-format) It is much more readable and easy to make changes in string. – Badiparmagi Oct 06 '16 at 07:14

1 Answers1

1

The sql generated from this is missing spaces between the segments

string select = "SELECT ProductCode, Description, UnitPrice"
           + "FROM Products"
           + "WHERE ProductCode = @ProductCode";

Change to:

                                                //        add space
                                                //         ↓
string select = "SELECT ProductCode, Description, UnitPrice "
           + "FROM Products "
           + "WHERE ProductCode = @ProductCode";

better still just:

string select = @"SELECT ProductCode, Description, UnitPrice 
                  FROM Products 
                  WHERE ProductCode = @ProductCode";
Gilad Green
  • 36,708
  • 7
  • 61
  • 95