insert into 语句中的语法错误 - 错误是什么? [英] Syntax error in insert into statement - what's the error?

问题描述

这是一个按钮的完整代码，用于将数据添加到 2 个不同的表，奇怪的是我有另一个表单，带有一个具有类似功能的按钮，并且它在相同的语句下工作正常选项比较数据库

this is the full code here for a button to add data to 2 different tables the weird thing is that i have another form wih a button wih similar function and it works fine wih the same statment Option Compare Database

     Private Sub addbutton_Click()

     CurrentDb.Execute "INSERT INTO Clients(F_Name,L_Name,Phone_Number,E-mail,Gender,Date_of_Birth) " & "VALUES(" & Me.textfname & ",'" & Me.textlname & "','" & Me.textpnumber & "','" & Me.textemail & "','" & Me.textgender & "','-" & Me.textdob & "')"
     CurrentDb.Execute "INSERT INTO Accounts(F_Name,L_Name,Username,Password,accounttype) " & "VALUES(" & Me.textfname & ",'" & Me.textlname & "','" & Me.textusername & "','" & Me.textpassword & "','" & Me.textaccountype & "')"

clientssubform.Form.Requery


     End Sub

推荐答案

首先，Password是保留字，所以使用[Password].

First, Password is a reserved word, so use [Password].

其次，日期表达式必须包裹在octothorpes中:#2017/04/24#

Second, a date expression must be wrapped in octothorpes: #2017/04/24#

另外，如果你不使用参数，即使推荐，使用这样的函数来连接你的值:

Also, if you won't use parameters, even though recommended, use a function like this to concatenate your values:

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " #yyyy/mm/dd hh:nn:ss#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function

研究内嵌注释的用法.

这篇关于insert into 语句中的语法错误 - 错误是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！