Jerry Smith Jerry Smith - 2 months ago 19
C# Question

Incorrect Syntax With DataBase Method

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();
}
}
}


}

Answer

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";