它之间的mysql查询有错误。有人可以帮我解决这个问题吗？ [英] It has an error in mysql query near between. can some one help me how to fix it ?

问题描述

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

我的尝试：



它有mysql查询之间的错误。有人可以帮我解决这个问题吗？

What I have tried:

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

推荐答案

你的sql中有语法错误 - 请参阅BETWEEN的参考 MySQL：BETWEEN条件 [ ^ ]

sql应为

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

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

然后使用参数插入@ dtpstart，@ dtpend和@texttempno的值 - 请参阅我的评论中的链接



我的意思的一个例子 - NB未经测试

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

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

对于初学者，永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。

并修复您的查询！你需要一个WHERE子句，它看起来像：

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:

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

如果你这样做，你也可以使用DateTimePicker.Value属性直接发送你的日期作为DateTime值，你的问题几乎肯定会同时消失。

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.

你使用无效的查询。基本的一般语法是

You use an invalid query. The basic general syntax is

SELECT something FROM table-name WHERE conditions

所以你必须移动日期 WHERE 后面的范围条件，并将其与以下条件相结合（通常使用 AND ）。



未经测试：

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

Untested:

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

这篇关于它之间的mysql查询有错误。有人可以帮我解决这个问题吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！