Type conversion error - SQL query WHERE CLM IN LIST

Matt Norman

I have the following query which works fine when executed fine in SQL Mgmt Studio:

SELECT * FROM CMSDataViewer.dbo.RealTimeAgentData WHERE AgentID IN ('1937','1658','1608','1558','1640','1521') ORDER BY State, AuxReason, Time ASC

If I build out this query in C# it seems to be trying to be trying to convert the query value list to an int and fails when trying to read the results from the data reader:

using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
AgentQueryStart:
SqlCommand cmd = new SqlCommand("SELECT * FROM CMSDataViewer.dbo.RealTimeAgentData WHERE AgentID IN (@AgentIDList) ORDER BY State, AuxReason, Time ASC", connection);
cmd.Parameters.AddWithValue("@AgentIDList", AgentQueryParam);
dr = cmd.ExecuteReader();
while (dr.Read())
{
if (String.IsNullOrEmpty(dr[0].ToString()))
{
dr.Close();
goto AgentQueryStart;
}
//Construct list of data rows.
TimeSpan time = TimeSpan.FromSeconds(double.Parse(dr[5].ToString()));
ReturnData.Add(new AgentRealTimeDataRow(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), time.ToString(@"hh\:mm\:ss")));
}
}

This fails at 'dr.Read()' with: System.Data.SqlClient.SqlException: 'Conversion failed when converting the nvarchar value ''1937','1658','1608','1558','1640','1521'' to data type int.'

If I add a breakpoint I can see that all variable values are as expected:

AgentQueryParam: "'1937','1658','1608','1558','1640','1521'"

cmd.CommadText: "SELECT * FROM CMSDataViewer.dbo.RealTimeAgentData WHERE AgentID IN (@AgentIDList) ORDER BY State, AuxReason, Time ASC"

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
if AgentId is varchar in SQL Server then this error shouldn't come , if it is an int then it is obvious error
Also use parameterized query to prevent sql injection as shown below
var sql = "SELECT * FROM CMSDataViewer.dbo.RealTimeAgentData WHERE AgentID IN
({0}) ORDER BY State, AuxReason, Time ASC";
  int[] arr= {1937,1658,1608,1558,1640,1521}
  var valuearray = arr.Split(',');

  var parms = valuearray.Select((s, i) => "@p" + i.ToString()).ToArray();
  var inclause = string.Join(",", parms);

  using (var conn = new SqlConnection(connect))
  {
    var cmd = new SqlCommand(string.Format(sql, inclause), conn);
    for (var i = 0; i < valuearray.Length; i++)
    {
      cmd.Parameters.AddWithValue(parms[i], valuearray[i]);
    }
Matt Norman5y ago
@Sachin Singh

I am passing the query parameter as a comma sepearted string:
AgentQueryParam: "'1937','1658','1608','1558','1640','1521'"

It just seems to want to try converting that string to an int
Sachin Singh5y ago
Sql has no concept of list so you need to paas comma separated value. the same you are passing in SSMS.