C# code not returning parameterized query values (Datetime) - Same query works in SQL

Question

I've been having trouble with a dynamic query where I receive values via querystring to be search parameters for the select query.

When it reads it has no values at all, when I look at SQL Activity monitor to see what SQL is actually passed to it. I select the query and run it on SQL server by itself and it brings back values.

I'm very confused right now, all other fields work searching except dates. I tried parameterizing the date values a lot of other ways also, but I was getting the error. "Conversion failed when converting date and/or time from character string.'"

I'm very confused right now.

I have tried to cut out as much of the code as possible for easy reading.

var queryString = this.Request.GetQueryNameValuePairs();
List<KeyValuePair<string, object>> QueryStringKeys = new List<KeyValuePair<string, object>>();

foreach (var pair in queryString)
{
     QueryStringKeys.Add(new KeyValuePair<string, object>(pair.Key.ToString(), pair.Value));
}

 DateTime thedates;
 thedates = DateTime.ParseExact(keys.Value.ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
 string tempdatestring = thedates.Year.ToString() + "-" + thedates.Month.ToString() + "-" + thedates.Day.ToString();
 SqlCommand cmd = new SqlCommand("", connection);
 StringBuilder QString = new StringBuilder();
 StringBuilder sqlBuilder = new StringBuilder();

 QString.Append(" Where @").Append(keys.Key).Append("=@" + keys.Key + "value");
 cmd.Parameters.AddWithValue("@" + keys.Key, keys.Key);
 cmd.Parameters.AddWithValue("@" + keys.Key + "value", "cast('" + tempdatestring + "' as datetime)");

 sqlBuilder.Append("SELECT [EMP_ID],[EMP_SURNAME],[EMP_GIVENNAMES],[EMP_TITLE],[EMP_STARTDATE] from EMPLOYEES " + QString.ToString() + ")");
 cmd.CommandText = sqlBuilder.ToString(); 

 SqlDataReader test;
 test = cmd.ExecuteReader();

 while (test.Read())
 {
     //No values returned from dates searches
 }

other ways I have tried to add the date.

Please also note the date field is a date field and not datetime.

cmd.Parameters.Add("@" + keys.Key + "value", SqlDbType.DateTime).Value = tempdatestring;
// -----
SqlParameter parameter = cmd.Parameters.Add("@" + keys.Key + "value", System.Data.SqlDbType.DateTime);
parameter.Value = DateTime.Parse(tempdatestring);
//------
cmd.Parameters.AddWithValue("@" + keys.Key + "value", new SqlDateTime(thedates));
//----
cmd.Parameters.AddWithValue("@" + keys.Key + "value", thedates);

value in sql table and in variable

`cast('" + tempdatestring + "' as datetime)` => I think this is not a good way to perform string-to-date conversion. The conversion should be entirely done in C# and pass the result to DB as `datetime` or `datetime2`. — Tetsuya Yamamoto, Nov 24 '17 at 04:03
I know, this is the only way I could actually run it without getting an exception so far. As you can see I have tried to use other methods below — Imsopov, Nov 24 '17 at 04:07
You have `DateTime thedates = DateTime.ParseExact(keys.Value.ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);`, try directly pass that `DateTime` to parameter without `tempdatestring`: `cmd.Parameters.AddWithValue("@" + keys.Key + "value", thedates);`. — Tetsuya Yamamoto, Nov 24 '17 at 04:10
I tried cmd.Parameters.AddWithValue("@" + keys.Key + "value", DateTime.ParseExact(keys.Value.ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture)); and got 'Conversion failed when converting date and/or time from character string.' — Imsopov, Nov 24 '17 at 04:21
Then you should inspect `keys.Value`, what kind of string it has & is it contains valid datetime string? `DateTime.ParseExact` will throw error if invalid datetime string passed (please note `DateTime` has no specific format, it stores each date component as properties). — Tetsuya Yamamoto, Nov 24 '17 at 04:25
Sorry I had left that out my code when trying to cut the code down. keys are list of keyvaluepairs of the key/value sent via query string to our API. I have a foreach loop which I have left out to not complicate things. — Imsopov, Nov 24 '17 at 04:35
I think its because the dateformat you are sending doesnt match with the date format at the server — Sujit.Warrier, Nov 24 '17 at 04:56
`cmd.Parameters.AddWithValue("@" + keys.Key + "value", "cast('" + tempdatestring + "' as datetime)");` This is not doing what you think. It is passing the string `cast('2017-11-24' as datetime)` into a date parameter & asking SQL to convert that entire string into a date — Ashley Pillay, Nov 24 '17 at 05:14
Please copy the actual sql statement you are sending to the database into your question. Currently, it's kinda hard to read and understand it. In any case, `DateTime` values should be passed to SQL Server as parameters of type `SqlDbType.DateTime` (or `Date` or `Time` or `DateTime2`), and the value of the parameter should be an instance of the `DateTime` struct — Zohar Peled, Nov 24 '17 at 06:08
@ZoharPeled SELECT [EMP_ID],[EMP_CREATIONDATE],[EMP_LASTCHANGED],[EMP_CREATEDBY],[EMP_UPDATEDBY],[EMP_SURNAME],[EMP_GIVENNAMES],[EMP_TITLE],[EMP_STARTDATE] FROM [SQLDEMO].[dbo].[EMPLOYEES] Where @EMP_STARTDATE=@EMP_STARTDATEvalue) as RowConstrainedResult where Rownum > 0 and RowNum <= 10000 order by rownum This was pulled from cmd.CommandText — Imsopov, Nov 24 '17 at 06:22
I've added a screenshot of the parameter in visual studio and the format in the sql table @Sujit.Warrier — Imsopov, Nov 24 '17 at 06:23
`Where @EMP_STARTDATE=@EMP_STARTDATEvalue` => it should be contain column name: `Where EMP_STARTDATE=@EMP_STARTDATEvalue`. Possibly you construct the query in wrong way by using `@` prefix after `WHERE` clause in `StringBuilder`. — Tetsuya Yamamoto, Nov 24 '17 at 06:31
It does have a column name, @EMP_STARTDATE has a value of "EMP_STARTDATE" This query is created dynamically and in this example there is only key value pair. I could have multiple coming from the query string — Imsopov, Nov 24 '17 at 06:32
This is what possibly occur when both parameters passed to query: `Where 'EMP_STARTDATE' = [any datetime value]` (note string quotes around field name). It compares datetime against string and throwing conversion error. You can read how to build dynamic `WHERE` clause example: https://stackoverflow.com/questions/19118245/dynamic-where-clause-in-parameter. — Tetsuya Yamamoto, Nov 24 '17 at 06:41
*"It does have a column name, @EMP_STARTDATE has a value of "EMP_STARTDATE""* - You can't parameterize identifiers... Why do you construct the query dynamically in the first place? — Zohar Peled, Nov 24 '17 at 07:00

score 1 · Accepted Answer · answered Nov 24 '17 at 07:16

Looking from the result query:

SELECT [EMP_ID],[EMP_CREATIONDATE],[EMP_LASTCHANGED],[EMP_CREATEDBY‌],[EMP_UPDATEDBY],[E‌MP_SURNAME],[EMP_GIV‌ENNAMES],[EMP_TITLE]‌,[EMP_STARTDATE]
FROM [SQLDEMO].[dbo].[EMPLOYEES]
Where @EMP_STARTDATE=@EMP_STARTDATEvalue

You're actually passing 2 parameters as part of comparison:

@EMP_STARTDATE as string parameter (contains column name);
@EMP_STARTDATEvalue as date parameter (contains DateTime value).

When both parameter values are passed, it will form this example query (as I analyzed in SSMS):

SELECT [EMP_ID],[EMP_CREATIONDATE],[EMP_LASTCHANGED],[EMP_CREATEDBY‌],[EMP_UPDATEDBY],[E‌MP_SURNAME],[EMP_GIV‌ENNAMES],[EMP_TITLE]‌,[EMP_STARTDATE]
FROM [SQLDEMO].[dbo].[EMPLOYEES] 
Where 'EMP_STARTDATE' = [any datetime value]

The query above tries to convert 'EMP_STARTDATE' string value to datetime (as date comparison) and throwing conversion failure as its result. You can pass column name literally as given below:

QString.Append(" Where ").Append(keys.Key).Append(" = @" + keys.Key + "value");
DateTime thedates = DateTime.ParseExact(keys.Value.ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);

using (SqlCommand cmd = new SqlCommand("", connection))
{
    StringBuilder QString = new StringBuilder();
    StringBuilder sqlBuilder = new StringBuilder();

    // note there is no '@' before Append(keys.Key) method
    // so that it becomes column name instead of parameter name
    QString.Append(" Where ").Append(keys.Key).Append(" = @" + keys.Key + "value");
    // just pass single parameter value
    cmd.Parameters.AddWithValue("@" + keys.Key + "value", thedates);

    sqlBuilder.Append("SELECT [EMP_ID],[EMP_SURNAME],[EMP_GIVENNAMES],[EMP_TITLE],[EMP_STARTDATE] from EMPLOYEES " + QString.ToString() + ")");
    cmd.CommandText = sqlBuilder.ToString(); 

    SqlDataReader test = cmd.ExecuteReader();

    // other stuff
}

The statement above generates this example, which is a valid query (EMP_STARTDATE becomes column name identifier instead parameter value):

SELECT [EMP_ID],[EMP_CREATIONDATE],[EMP_LASTCHANGED],[EMP_CREATEDBY‌],[EMP_UPDATEDBY],[E‌MP_SURNAME],[EMP_GIV‌ENNAMES],[EMP_TITLE]‌,[EMP_STARTDATE]
FROM [SQLDEMO].[dbo].[EMPLOYEES]
Where EMP_STARTDATE = @EMP_STARTDATEvalue

NB: Better to create stored procedure(s) with parameter switch(es) to determine which query should be executed instead of building dynamic queries.

Thank you so much!, this was it. I had the @ after the where. — Imsopov, Nov 26 '17 at 23:09

C# code not returning parameterized query values (Datetime) - Same query works in SQL

1 Answers1