"Unclosed quotation mark after the character string" error

Question

Server Error in '/' Application.

Incorrect syntax near 'B'. Unclosed quotation mark after the character string ',e)'.

Description: An unhandled exception occurred.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'B'. Unclosed quotation mark after the character string ',e)'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'B'. Unclosed quotation mark after the character string ',e)'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action'1 wrapCloseInAction) +3278868
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +791
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4927
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1275
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource'1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +367
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +386
HalcytronicsInc.Controllers.ExcellUploadController.Upload(HttpPostedFileBase upload) in C:\Users\M1037515\Documents\Visual Studio 2015\Projects\HalcytronicsInc\HalcytronicsInc\Controllers\ExcellUploadController.cs:94 lambda_method(Closure , ControllerBase , Object[] ) +139
System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary'2 parameters) +229
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary'2 parameters) +35
System.Web.Mvc.<>c__DisplayClass15.b__12() +80 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func'1 continuation) +453
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func'1 continuation) +453
System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +533

using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using HalcytronicsInc.Models;
using System.Data.SqlClient;

namespace HalcytronicsInc.Controllers
{
    public class ExcellUploadController : Controller
    {
        public string country;
        public string state;
        public string city;
        public string name;
        public string pno;
        // GET: ExcellUpload
        public ActionResult Index()
        {
            return View();
        }
        public ActionResult Upload()
        {
            return View();
        }

        [HttpPost]
        //[ValidateAntiForgeryToken]
        public ActionResult Upload(HttpPostedFileBase upload)
        {

            if (ModelState.IsValid)
            {

                if (upload != null && upload.ContentLength > 0)
                {
                    // ExcelDataReader works with the binary Excel file, so it needs a FileStream
                    // to get started. This is how we avoid dependencies on ACE or Interop:
                    Stream stream = upload.InputStream;

                    // We return the interface, so that
                    IExcelDataReader reader = null;


                    if (upload.FileName.EndsWith(".xls"))
                    {
                        reader = ExcelReaderFactory.CreateBinaryReader(stream);
                    }
                    else if (upload.FileName.EndsWith(".xlsx"))
                    {
                        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    }
                    else
                    {
                        ModelState.AddModelError("File", "This file format is not supported");
                        return View();
                    }

                    reader.IsFirstRowAsColumnNames = true;

                    DataSet result = reader.AsDataSet();
                    // string connectionString = null;
                    SqlConnection connection;
                    SqlCommand command;
                    SqlDataAdapter adpter = new SqlDataAdapter();

                    connection= new SqlConnection(/*"Data Source=A2ML10582;User ID =sa;Password =****************;Integrated Security = true"*/
                        "Data Source=A2ML10582;Initial Catalog=HalcytronicsINCSitecore_Master;User ID=sa;Password=****************"
                        );
                    //connectionString = "Data Source = 172.17.2.13; Initial Catalog ="User ID = sa Password = ***********"  Integrated Security = true";

                    //connection = new SqlConnection(connectionString);

                    int i = 0;
                    connection.Open();
                    for (i = 0; i <= result.Tables[0].Rows.Count - 1; i++)
                    {
                        country = result.Tables[0].Rows[i].ItemArray[0].ToString();
                        state = result.Tables[0].Rows[i].ItemArray[1].ToString();
                        city =result.Tables[0].Rows[i].ItemArray[2].ToString();
                        name = result.Tables[0].Rows[i].ItemArray[3].ToString();
                        pno = result.Tables[0].Rows[i].ItemArray[4].ToString();
                        string sql = "insert into SalesRepresentative(" + country + ",'" + state + "'," + city + "','+" + name + "'," + pno + ")";
                        command = new SqlCommand(sql, connection);
                        adpter.InsertCommand = command;
                        adpter.InsertCommand.ExecuteNonQuery();
                    }
                    connection.Close();

                    reader.Close();

                    return View(result.Tables[0]);
                }
                else
                {
                    ModelState.AddModelError("File", "Please Upload Your file");
                }
            }
            return View();
        }
    }
}

Answer 1

You should use a parameterized query to avoid Sql Injection hacks and simple syntax errors caused by typos (as you have here in forgetting to add proper quotes around many strings values).
Also a parameters avoid problems if any of your values contains a single quote.

....
DataSet result = reader.AsDataSet();

string cmdText = @"insert into SalesRepresentative
    (@country,@state,@city,@name,@pno)";

// using statement around disposable objects.....
using(SqlConnection connection= new SqlConnection(....))
using(SqlCommand cmd = new SqlCommand(cmdText, connection))
{
    connection.Open();

    // Add all parameters before entering the insert loop        
    cmd.Parameters.Add("@country", SqlDbType.NVarChar);
    cmd.Parameters.Add("@state", SqlDbType.NVarChar);
    cmd.Parameters.Add("@city", SqlDbType.NVarChar);
    cmd.Parameters.Add("@name", SqlDbType.NVarChar);
    cmd.Parameters.Add("@pno", SqlDbType.NVarChar);


    for (i = 0; i < result.Tables[0].Rows.Count; i++)
    {
        country = result.Tables[0].Rows[i].ItemArray[0].ToString();
        state = result.Tables[0].Rows[i].ItemArray[1].ToString();
        city =result.Tables[0].Rows[i].ItemArray[2].ToString();
        name = result.Tables[0].Rows[i].ItemArray[3].ToString();
        pno = result.Tables[0].Rows[i].ItemArray[4].ToString();

        // Set the parameter values 
        cmd.Parameters["@country"].Value = country;
        cmd.Parameters["@state"].Value = state;
        cmd.Parameters["@city"].Value = city ;
        cmd.Parameters["@name"].Value = name;
        cmd.Parameters["@pno"].Value = pno;

        // No need of an SqlDataAdapter here, just execute the command...
        cmd.ExecuteNonQuery();
   }
}
return View(result.Tables[0]);

Answer 2

As others have stated, concatenating SQL like this is poor form. That being said, the cause of your error is a missing single quote near "city". Even if every aspect of this code is under your control, you should be using paramaterized queries.

string sql = "insert into SalesRepresentative(" + country + ",'" + state + "'," + city + "','+" + name + "'," + pno + ")";

Should be:

string sql = "insert into SalesRepresentative(" + country + ",'" + state + "','" + city + "','+" + name + "'," + pno + ")";