TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Matt Norman
NA
20
425
Type conversion error - SQL query WHERE CLM IN LIST
Feb 8 2021 4:06 PM
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"
Reply
Answers (
3
)
How lazy loading works in linq? And when do we use it?
how set code for school fees type in monthly , quarterly, Annually