查询表达式中日期的语法错误 [英] Syntax error in date in query expression

问题描述

大家好，
我正在做一个电子图书馆管理系统，其中包括每个学生每次去或使用电子图书馆时登录的时间计数.但是我在frmReserve中遇到了麻烦.

这是我得到的错误:
查询表达式``tblReserve.studID = tblStudents.StudNumber和tblReserve.inDate =#00/30/2012#''中日期的语法错误.

以下是m

Hi Guys,
I am doing an Electronic Library Management System that includes counting of time for each student who logged in every time he/she goes or used e-library. But I''m having trouble in frmReserve.

Here is the error that I got:
Syntax error in date in query expression ''tblReserve.studID=tblStudents.StudNumber and tblReserve.inDate =#00/30/2012#''.

Below are m

<pre lang="vb">

y代码:
单击命令cmdLoad按钮时将执行此代码

y codes:
this code is executed when the command cmdLoad button is clicked

Private Sub cmdLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLoad.Click
       MsgBox(Today.ToString)
       paintStudList("SELECT tblReserve.pcNum, tblStudents.FullName, tblReserve.numOfTime FROM tblReserve,tblStudents where tblReserve.studID=tblStudents.StudNumber and tblReserve.inDate =#" & Today.ToString("mm/dd/yyyy") & "#", dgrid2, "PC NUMBER", "STUDENT NAME", "NUMBER OF TIME")
       dgrid2.Columns.Item(0).ReadOnly = True
       dgrid2.Columns.Item(1).ReadOnly = True
   End Sub

如果上述SQL为true，则执行以下代码

the following codes are executed if the SQL above is true

Sub paintStudList(ByVal newSQL As String, ByVal myGrid As System.Object, ByVal str1 As String, ByVal str2 As String, ByVal str3 As String)
        Dim adap1 As OleDbDataAdapter
        Dim dt1 As New DataTable
        Dim con As New OleDbConnection

        'con.ConnectionString = "PROVIDER=Microsoft.Jet.OleDb.4.0; Data Source=Library2.mdb; JET OleDb:Database Password=3Musketeers"
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\Library2.mdb; JET OleDb:Database Password=3Musketeers"
        'Data Source=" & Application.StartupPath & "\location.mdb"

        adap1 = New OleDbDataAdapter(newSQL, con)
        con.Open()
        'fill data to datable
        adap1.Fill(dt1)
        myGrid.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing

        dt1.Columns.Item(0).ColumnName = str1
        dt1.Columns.Item(1).ColumnName = str2
        dt1.Columns.Item(2).ColumnName = str3
        myGrid.DataSource = dt1
        'bsource.DataSource = dt1
        con.Close()
    End Sub

希望有人能帮助我...
在此先感谢

Hope someone could help me...
thanks in advance

推荐答案

将#"字符替换为引号，并将日期转换为ISO格式:

Replace the "#" characters with quotes, and put the date into ISO format:

paintStudList("SELECT tblReserve.pcNum, tblStudents.FullName, tblReserve.numOfTime FROM tblReserve,tblStudents where tblReserve.studID=tblStudents.StudNumber and tblReserve.inDate ='" & Today.ToString("yyyy-MM-dd") & "'", dgrid2, "PC NUMBER", "STUDENT NAME", "NUMBER OF TIME")

如果您使用参数化查询而不是这种非常不稳定的字符串连接，那么您将根本不会遇到这个问题，并且您的代码将更具可读性和可维护性.

If you used a parameterized query instead of this very shaky string concatentation, you wouldn''t have this problem at all and your code would be much more readable and maintainable.

VB.NET Parameterized Query[^]

Hi OriginalGriff，

我已经自己解决了.这里的问题是，我没有注意到数据库上的tblReserve.studID字段使用不同的数据类型(文本)，而tblStudents.StudNumber则使用整数格式.但是，仍然感谢您的快速回复. :)

Hi OriginalGriff,

I already resolved this by myself. The thing here is that, I did not noticed that the tblReserve.studID field on my database is in different data type (Text) whereas tblStudents.StudNumber is in Integer format. But still thanks for your quick response. :)

这篇关于查询表达式中日期的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！