3
Answers

Type conversion error - SQL query WHERE CLM IN LIST

Matt Norman

Matt Norman
4y
434
1
I have the following query which works fine when executed fine in SQL Mgmt Studio:
 
  1. 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:
 
  1. using (SqlConnection connection = new SqlConnection(builder.ConnectionString))  
  2.                 {  
  3.                     connection.Open();  
  4.   
  5.                 AgentQueryStart:  
  6.                     SqlCommand cmd = new SqlCommand("SELECT * FROM CMSDataViewer.dbo.RealTimeAgentData WHERE AgentID IN (@AgentIDList) ORDER BY State, AuxReason, Time ASC", connection);  
  7.                     cmd.Parameters.AddWithValue("@AgentIDList", AgentQueryParam);  
  8.                     dr = cmd.ExecuteReader();  
  9.   
  10.                     while (dr.Read())  
  11.                     {  
  12.                         if (String.IsNullOrEmpty(dr[0].ToString()))  
  13.                         {  
  14.                             dr.Close();  
  15.                             goto AgentQueryStart;  
  16.                         }  
  17.   
  18.                         //Construct list of data rows.  
  19.                         TimeSpan time = TimeSpan.FromSeconds(double.Parse(dr[5].ToString()));  
  20.                         ReturnData.Add(new AgentRealTimeDataRow(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), time.ToString(@"hh\:mm\:ss")));  
  21.                     }  
  22.                 }  
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"
Answers (3)
Next Recommended Forum