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
– Tetsuya Yamamoto, Commented Nov 24, 2017 at 4: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
– Imsopov, Commented Nov 24, 2017 at 4: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
– Tetsuya Yamamoto, Commented Nov 24, 2017 at 4: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
– Imsopov, Commented Nov 24, 2017 at 4: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
– Tetsuya Yamamoto, Commented Nov 24, 2017 at 4:25

Tetsuya Yamamoto · Accepted Answer · 2017-11-24 07:16:38Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related