SqlCommand.Parameters.Add NOT substituting into Command Text

Question

I've tried everything I can think of and this simple C# code is not working. The result is that the parameters are NOT getting substituted so the query results in no data.

string query = @"select TOP 20 [ID], [NAME], LATITUDE, LONGITUDE, ADDRESS, FEATURES, [DATE UPDATED],
                           ABS(ABS(LATITUDE)- @Lat1 ) + ABS(ABS(LONGITUDE)- @Lng1 ) as diff 
                           from Facility f
                           where (ABS(ABS(LATITUDE)- @Lat2 ) + ABS(ABS(LONGITUDE)- @Lng2 ) < 2)
                           order by 8";

using (SqlConnection conn = new SqlConnection())
{
    // Create the connectionString
    // Trusted_Connection is used to denote the connection uses Windows Authentication
    conn.ConnectionString = "Server=ERICS_TOSHIBA\\WEAVSQL;Database=WellBe;Trusted_Connection=true";
    conn.Open();
    // Create the command
    SqlCommand command = new SqlCommand(query, conn);

    // Add the parameters.
    command.Parameters.Add(new SqlParameter("@Lat1", SqlDbType.Float));
    command.Parameters["@Lat1"].Value = dHomeLat;
    command.Parameters.Add(new SqlParameter("@Lat2", SqlDbType.Float));
    command.Parameters["@Lat2"].Value = dHomeLat;
    command.Parameters.Add(new SqlParameter("@Lng1", SqlDbType.Float));
    command.Parameters["@Lng1"].Value = dHomeLng;
    command.Parameters.Add(new SqlParameter("@Lng2", SqlDbType.Float));
    command.Parameters["@Lng2"].Value = dHomeLng;

    Console.WriteLine("QUERY IS: \n\n {0}", command.CommandText);
}

The output of this WriteLine is this:

select TOP 20 [ID], [NAME], LATITUDE, LONGITUDE, ADDRESS, FEATURES, [DATE UPDATED],
                           ABS(ABS(LATITUDE)- @Lat1 ) + ABS(ABS(LONGITUDE)- @Lng1 ) as diff
                           from Facility f
                           where (ABS(ABS(LATITUDE)- @Lat2 ) + ABS(ABS(LONGITUDE)- @Lng2 ) < 2)
                           order by 8

Why won't the parameter values substitute? I've tried about 6 different versions of the syntax. Not all the code is listed above. There are valid values for all variables including the dHomeLat and dHomeLng.

Parameters never replace the query string. It sends the actual values separate from it. You'll see them when running a the profiler on the server. But what you're seing is the expected result. — Alejandro
– Alejandro, Commented May 22, 2017 at 20:46

SLaks · Accepted Answer · 2017-05-22 20:46:55Z

4

Parameters aren't substituted into CommandText; they're sent to the server alongside the command (this enables things like query plan caching).

You don't actually have a problem.

answered May 22, 2017 at 20:46

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

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

1 Comment

E W Over a year ago

Hmm. Thanks. So I guess the query results in 0 rows. but when I run the same query in SQL Management Studio I get results. So there must be some other issue with the query.

Collectives™ on Stack Overflow

SqlCommand.Parameters.Add NOT substituting into Command Text

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related