SQL query with BETWEEN operator causes "Syntax error in number in query expression"

Question

I'm using a MS Access database. I want to get first and last records that are in between 2 dates. But I get error when using a BETWEEN operator:

Syntax error in number in query expression 'Datum_k BETWEEN 3.4.2017. AND 3.4.2017.'.

My code:

private void GetPrviZadnjiBrojRacuna()
{
    OleDbCommand commandOD = new OleDbCommand("SELECT Dokument FROM DnevniPromet WHERE (Datum_k BETWEEN " + datumOd + " AND " + datumDo + ") ORDER BY [Datum_k] ASC", dataModel.CS);
    OleDbCommand commandDO = new OleDbCommand("SELECT Dokument FROM DnevniPromet WHERE [Datum_k] >= " + datumOd + " AND [Datum_k] <= " + datumDo + " ORDER BY [Datum_k] DESC", dataModel.CS);

    try
    {
        dataModel.DT.Clear();
        OleDbDataAdapter ODbDA = new OleDbDataAdapter(commandOD);

        if (!dataModel.CS.State.Equals(ConnectionState.Open))
        {
            dataModel.CS.Open();
        }

        // GET OD 
        ODbDA.Fill(dataModel.DT);
        odRacuna = dataModel.DT.Rows[0].ToString();

        // GET DO
        ODbDA.SelectCommand = commandDO; 
        dataModel.DT.Clear();
        ODbDA.Fill(dataModel.DT);

        doRacuna = dataModel.DT.Rows[0].ToString();

        dataModel.CS.Close();
        dataModel.DataLoaded = true;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

Firstly, you should parameterise your queries, not use string interpolation. But you've missed the ' single quotes around the date variable values. — Chris Pickford
– Chris Pickford, Commented Apr 3, 2017 at 11:50
nononononono! never; never; never - never concatenate input into SQL. Never. But: yeah, that isn't valid SQL. I would say "that needs quotes", but I have a horrible suspicion you'd add them; what it actually needs is: parameters. We can't tell you the exact syntax you need unless you tell us what the provider is here; since you seem to be using OleDb, we can't infer anything. Can you tell us what the actual server is here? is it SQL Server? Oracle? ... or? — Marc Gravell
– Marc Gravell, Commented Apr 3, 2017 at 11:50
no, "OleDb" is not self-explanatory - you can talk to just about anything via OleDb - it is a very generic transport — Marc Gravell
– Marc Gravell, Commented Apr 3, 2017 at 11:55

solujic · Accepted Answer · 2017-04-03 12:09:34Z

5

Addressing two issues with your code snippet:

You should never use string interpolation to build a query. This lends itself to SQL injection. Many, many topics surrounding this. I suggest you read up.
You have missed the ' single quotes around your date strings.

Using parameterised queries, you can kill two birds with one stone:

OleDbCommand commandOD = new OleDbCommand(@"
    SELECT Dokument
    FROM DnevniPromet
    WHERE (Datum_k BETWEEN @datumOd AND @datumDo)
    ORDER BY [Datum_k] ASC", dataModel.CS);

commandOD.Parameters.AddRange(new OleDbParameter[]
{
    new OleDbParameter("@datumOd", datumOd),
    new OleDbParameter("@datumDo", datumDo)
});

edited Apr 3, 2017 at 12:09

solujic

1,0551 gold badge20 silver badges48 bronze badges

answered Apr 3, 2017 at 11:57

Chris Pickford

9,0606 gold badges51 silver badges76 bronze badges

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

2 Comments

solujic Over a year ago

welp that solved my issue even thought I never saw what was wrong in my original query to begin with - yes I'm lazy but I guess I need to start writing parameters.

Marco Guignard Over a year ago

With Access you should use # instead of ' when you are using date constant. msdn.microsoft.com/en-us/library/bb221200(v=office.12).aspx Of course using parameters is the best (the only right ?) way to go !

Collectives™ on Stack Overflow

SQL query with BETWEEN operator causes "Syntax error in number in query expression"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related