It has an error in mysql query near between. can some one help me how to fix it ?

Question

0.00/5 (No votes)

See more:

C#

Try
     strsql = "SELECT SUM(totalhrs) as SumTotalHrs from tbl_attendance between dates='" & dtpstart.text & "' and dates='" & dtpend.text & "' where employeeNumber ='" & textempno.text & "'"
     cmd = New MysqlCommand(strsql, conn)
datardr = cmd.ExecuteReader

Catch ex As MysqlException
      Messagebox.Show(ex.Message)
Finally
      conn.dispose()
End Try

What I have tried:

It has an error in mysql query near between. Can some one help me how to fix it ?

Posted 8-Mar-16 2:26am

Real Corks

Updated 8-Mar-16 3:07am

CHill60

v2

Add a Solution

Comments

CHill60 8-Mar-16 8:38am

NEVER use concatenated text from textboxes to create SQL queries - see SQL Injection Prevention Cheat Sheet - OWASP[^]
If you use a parameterized query the problem will likely go away.

3 solutions

Solution 1

You have a syntax error in your sql - see the reference for BETWEEN MySQL: BETWEEN Condition[^]
The sql should be

C#

strSql = "SELECT SUM(totalhrs) as SumTotalHrs from tbl_attendance where dates between @dtpstart and @dtpend and employeeNumber =@textempno";

then insert values for @dtpstart, @dtpend and @texttempno using parameters - see the link in my comment

[edit] An example of what I mean - NB untested

C#

strsql = "SELECT SUM(totalhrs) as SumTotalHrs from tbl_attendance where dates between @start and @end and employeeNumber =@tempno"
cmd = New MySqlCommand(strsql, conn)
cmd.Parameters.AddWithValue("@start", dtpstart.Value.ToString("yyyy-MM-dd"))
cmd.Parameters.AddWithValue("@end", dtpend.Value.ToString("yyyy-MM-dd"))
cmd.Parameters.AddWithValue("@tempno", textempno.text)

datardr = cmd.ExecuteReader

Posted 8-Mar-16 2:41am

CHill60

Updated 8-Mar-16 4:06am

v3

Comments

F. Xaver 8-Mar-16 8:43am

5ed!

Solution 3

For starters, never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
And fix your query! You need a WHERE clause, and it shoudl look like:

SQL

SELECT value FROM MyTable WHERE columnName BETWEEN startDate AND endDate AND otherColumn = otherValue

If you do that, you can also send your dates through as DateTime values directly using the DateTimePicker.Value property, and your problem will almost certainly disappear at the same time.

Posted 8-Mar-16 2:41am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Accepted Answer · 2016-03-08T02:41:00

Solution 2

You use an invalid query. The basic general syntax is

SELECT something FROM table-name WHERE conditions

So you must move the date range condition behind the WHERE and combine it with the following conditions (usually with AND).

Untested:

SQL

SELECT SUM(totalhrs) AS SumTotalHrs FROM tbl_attendance WHERE dates BETWEEN '" & dtpstart.text & "' AND '" & dtpend.text & "' AND employeeNumber ='" & textempno.text & "'

Posted 8-Mar-16 2:41am

Jochen Arndt

Comments

Real Corks 8-Mar-16 9:20am

No problem at the query but the result is not accurate. I got 0 in my result but the answer must be 6.

Jochen Arndt 8-Mar-16 9:30am

Check your query string to ensure that the start date is lower than the end date. Ensure also that the date strings are using a format that is understood by MySQL (e.g. ISO-8601 YYYY-MM-DD hh:mm:ss).

Or even better - as already suggested - use a parametrized query where you can pass datetime values rather than strings.

Finally (I'm actually not quite sure if this is necessary), enclose the BETWEEN condition in parentheses to separate it from the next one.