CODEWITHSUNDEEP
c#sqlsql-server
ledgeJumper
ledgeJumper

Reputation: 3630

C# SqlCommand is not sending the right query with parameters

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?

Upvotes: 0

Views: 118

Answers (2)

ddang
ddang

Reputation: 11

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

Upvotes: 1

Habib
Habib

Reputation: 223187

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.

Upvotes: 5

Related Questions