I am getting this error while trying to execute the attached code. Please help. I am getting values as list array in controller method successfully but when I try to read data from sql database I am getting error.
Controller method:
[HttpPost] public JsonResult searchdata(List<string> firstField, List<string> secondField, List<string> thirdField) { List<string> lt = new List<string>(); Console.WriteLine(firstField); Console.WriteLine(secondField); string dogCsv = string.Join(",", firstField.ToArray()); Console.WriteLine(dogCsv); try { using (SqlConnection connection = new SqlConnection("data source=.; database=Srivatsava; integrated security=SSPI")) { connection.Open(); using (SqlCommand command = connection.CreateCommand()) { command.CommandTimeout = 0; command.CommandText = @"select accntname,BU,salesop,isdormant from fourth_page as fg INNER JOIN linked as ld on ld.productid=fg.productid INNER JOIN isdormant as it on it.productid=ld.productid where fg.accountname in (" + dogCsv + ")"; //command.Parameters.AddWithValue("firstField", dogCsv); using (SqlDataReader sdr = command.ExecuteReader()) { while (sdr.Read()) { lt.Add(sdr["accntname"].ToString()); } } } connection.Close(); } } catch (Exception e) { Console.WriteLine(e); } Console.WriteLine(lt); return Json(firstField); }
Execption:-
{System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'account2'. Invalid column name 'account1'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at MvcApplication32.Controllers.TodoListController.searchdata(List`1 firstField, List`1 secondField, List`1 thirdField) in c:UsersADMINDocumentsVisual Studio 2012ProjectsMvcApplication32MvcApplication32ControllersTodoListController.cs:line 597 ClientConnectionId:a0674fe4-e0d5-499f-be61-66e1b27768fa}
Advertisement
Answer
Since you are using inline SQL, every value within dogCSV string that’s comma separated needs a single quote around it. For instance, if dogCSV has "account2,someotheraccount"
, this won’t work it needs to be "'account2','someotheraccount'"
.
If you don’t that definitely would be the issue.
EDIT: Build dogCSV like this:
string dogCsv = string.Join(",", firstField.Select(i => "'" + i + "'"));
The way it’s built now will not put quotes around it.