"Unclosed quotation mark after the character string"错误

Question

'/'应用程序中的服务器错误。

"B"附近的语法不正确。字符后未闭合的引号字符串'，e)'。

描述：发生未处理的异常。

异常详细信息：System.Data.SqlClient.SqlException：不正确"B"附近的语法。字符串后未闭合的引号'，e)'。

来源错误：

在执行期间生成了未处理的异常当前web请求。关于可以使用下面的异常堆栈跟踪来识别异常。

堆栈跟踪：

[SqlException(0x80131904)："B"附近的语法不正确。未关闭字符串'，e)'后的引号。]
System.Data.SqlClient.SqlConnection.OnError(SqlException异常，Boolean breakConnection，Action‘1 wrapCloseInAction)+327868
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj，布尔调用程序HasConnectionLock，布尔asyncClose)+791
System.Data.SqlClient.TdsParser.TryRun(RunBehavior RunBehavior，SqlCommand cmdHandler，SqlDataReader数据流，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObjectstateObj，布尔&dataReady)+4927
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(字符串methodName，布尔异步，Int32超时，布尔异步写入)+1275
System.Data.SqlClient.SqlCommand.InteralExecuteNonQuery(TaskCompletionSource'1completion，String methodName，布尔sendToPipe，Int32超时，布尔异步写入)+367
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()+386
HalcytronicsInc.Controllers.ExcelUploadController.Upload(HttpPostedFileBase上载)在C:\Users\M1037515\Documents\Visual Studio中2015\Projects\HalcytronicsInc\Halcytronics\Controllers\ExcellUploadController.cs:94lambda_method(闭包、ControllerBase、Object[])+139
System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContextcontrollerContext，IDictionary'2个参数)+229
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(controllerContextcontrollerContext，ActionDescriptor ActionDescriptor，IDictionary'2参数)+35
System.Web.Mvc.<>c_DisplayClass15.b_12()+80 System.Web.Mvc.ControllerActionInvoker.IInvokeActionMethodFilter(IActionFilterfilter，ActionExecutingContext preContext，Func'1 continuation)+453
System.Web.Mvc.ControllerActionInvoker.IInvokeActionMethodFilter(IActionFilterfilter，ActionExecutingContext preContext，Func'1 continuation)+453
System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContextcontrollerContext，字符串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

您应该使用参数化查询来避免Sql注入漏洞和由拼写错误引起的简单语法错误(就像您在这里忘记在许多字符串值周围添加正确的引号一样)
如果您的任何值包含一个引号，参数也可以避免问题。

....
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

正如其他人所说，像这样串联SQL的形式很糟糕。也就是说，错误的原因是"city"附近缺少一个单引号。即使此代码的各个方面都在您的控制之下，也应该使用参数化查询。

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

应为：

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