0

I have code like this:

using (DbCommand command = factory.CreateCommand())
{
    command.Connection = connection;
    command.CommandText = string.Format("SELECT {0} FROM {1} WHERE {2} >= ? AND {2} <= ?",
            selectColumns,
            TableName,
            TimeStampColumn);

    var startTimeParam = command.CreateParameter();

    startTimeParam.ParameterName = "@StartTime";
    startTimeParam.DbType = DbType.DateTime;
    startTimeParam.Value = StartDate;

    var endTimeParam = command.CreateParameter();
    endTimeParam.ParameterName = "@EndTime";
    endTimeParam.DbType = DbType.DateTime;
    endTimeParam.Value = EndDate;

    DbDataAdapter adapter = factory.CreateDataAdapter();
    adapter.SelectCommand = command;
    adapter.SelectCommand.Parameters.Add(startTimeParam);
    adapter.SelectCommand.Parameters.Add(endTimeParam);

    adapter.Fill(table);
}

But I'm unable to set the parameters for the select command. I tried use @StartTime and @EndTime in the instead of ? but i got message Must declare scalar variable @StartTime.

When I'm using ? instead of @StartTime or @EndTime i got no results.

It is working only when I put all variable in the String.Format() function, but i don't want to use is like this.

Steve
  • 213,761
  • 22
  • 232
  • 286
PoorDeveloper
  • 186
  • 1
  • 19
  • You should use `?` instead of `@StartTime` / `@EndTime`. What error do you get when you use `?`? – Wagner DosAnjos Jan 18 '17 at 16:14
  • 1
    What datatype are StartDate and EndDate? Where do you declare them and how do you initialize them? – Steve Jan 18 '17 at 16:15
  • 2
    @wdosanjos: should not matter anyway, even if you give them names only the order matters(because [OleDb doesn't support named parameters](http://stackoverflow.com/questions/2407685/oledbparameters-and-parameter-names)). But that seems to be correct, because the StartTime-parameter is added before the EndTime-parameter. – Tim Schmelter Jan 18 '17 at 16:15
  • Probably is something related to the Time part of those two parameters. – Steve Jan 18 '17 at 16:15
  • By the way `@StartDate` or `@StartTime` ??? – Steve Jan 18 '17 at 16:18
  • @wdosanjos i tried using `?` but i got no results. Empty DataTable object. @Steve both DateTime objects – PoorDeveloper Jan 18 '17 at 16:18
  • I think it should be `OleDbType` instead of `DbType`. – Wagner DosAnjos Jan 18 '17 at 16:18
  • One idea: maybe the start- and end-date are same and you expect that all records of this day are included. That's not the case if the record contains also the time. You have to use `AND {2} < ?` and `endTimeParam.Value = EndDate.Date.AddDays(1)` if you want to include the whole day. – Tim Schmelter Jan 18 '17 at 16:19

0 Answers0