C# SqlCommand is not sending the right query with parameters

Question

It's a pretty simple problem, but I can't find a solution (not even sure what question to google here).

Here is my code to create the command:

var targetStartDate = "October 2014";//ConfigurationManager.AppSettings["StartDateString"].ToString(CultureInfo.InvariantCulture);
var acceptedStatusCodes = 4;//ConfigurationManager.AppSettings["ImportStatusIds"].ToString(CultureInfo.InvariantCulture);
using (var connection = new SqlConnection(connString))
{
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = @"
                            SELECT
                            ai.FirstName, 
                            ai.LastName, 
                            ai.Email, 
                            ai.SSN, 
                            ai.Phone,
                            ai.Program, 
                            r.FirstName + ' ' + r.LastName AS RepName, 
                            d.Name AS StartDateName, 
                            s.CvId AS CvSySchoolStatusId,
                            ai.CampusId as SyStudentId
                            FROM     
                            dbo.aspnet_Users AS u LEFT OUTER JOIN
                            dbo.ApplicantInfo AS ai ON u.UserId = ai.UserId LEFT OUTER JOIN
                            dbo.AdmissionReps AS r ON ai.AdmissionsRepId = r.RepId LEFT OUTER JOIN
                            dbo.ProgramStartDate AS d ON ai.StartDateId = d.CvStartDateId LEFT OUTER JOIN
                            dbo.CvSySchoolStatus AS s ON ai.CvSySchoolStatusId = s.CvId
                            where d.Name = '@StartDate'
                            and CvSySchoolStatusId in (@Statuses)";
        cmd.Parameters.AddWithValue("@StartDate", SqlDbType.VarChar).Value = (object) targetStartDate;
        cmd.Parameters.AddWithValue("@Statuses", SqlDbType.Int).Value = (object) acceptedStatusCodes;
    ...
    }
....
}

The reader sends the command fine, I can see it go through via SQL profiler:

exec sp_executesql N'
SELECT
ai.FirstName, 
ai.LastName, 
ai.Email, 
ai.SSN, 
ai.Phone,
ai.Program, 
r.FirstName + '' '' + r.LastName AS RepName, 
d.Name AS StartDateName, 
s.CvId AS CvSySchoolStatusId,
ai.CampusId as SyStudentId
FROM     
dbo.aspnet_Users AS u LEFT OUTER JOIN
dbo.ApplicantInfo AS ai ON u.UserId = ai.UserId LEFT OUTER JOIN
dbo.AdmissionReps AS r ON ai.AdmissionsRepId = r.RepId LEFT OUTER JOIN
dbo.ProgramStartDate AS d ON ai.StartDateId = d.CvStartDateId LEFT OUTER JOIN
dbo.CvSySchoolStatus AS s ON ai.CvSySchoolStatusId = s.CvId
where d.Name = ''@StartDate''
and CvSySchoolStatusId in (@Statuses)'
,N'@StartDate nvarchar(12),@Statuses int'
,@StartDate=N'October 2014'
,@Statuses=4

This query gives me 0 results, but if I take this exact query and just paste in the parameters and run just the query text, I get the expected results. Any idea where I am going wrong here?

Habib · Accepted Answer · 2013-11-11 17:36:28Z

5

You need to remove single quotes around your parameter in string query this:

where d.Name = '@StartDate'

should be:

where d.Name = @StartDate

No single parameters.

answered Nov 11, 2013 at 17:36

Habib

224k30 gold badges420 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ledgeJumper Over a year ago

Awesome, thank you! So for string types I do not need '' around them? That seems a little odd.

Habib Over a year ago

@davidisawesome, cmd.Parameters.AddWithValue would do that for you,

ddang · Accepted Answer · 2013-11-11 17:39:52Z

1

Perhaps it's the extra single quotes around startdate. I think you might want to remove those single quotes and try again. Should look like:

... where d.Name = @StartDate

answered Nov 11, 2013 at 17:39

ddang

111 bronze badge

1 Comment

Amir Keshavarz Over a year ago

Perhaps it should be a comment.

Collectives™ on Stack Overflow

C# SqlCommand is not sending the right query with parameters

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related