'4' 附近的语法不正确 [英] Incorrect syntax near '4'

问题描述

我有一个 INSERT 代码，其中变量 s = 4.非常感谢帮助.

I have an INSERT code where the variable s = 4. Help would be pretty appreciated.

 con.Open()
            Dim cmd As New SqlCommand("INSERT INTO Employee VALUES('" & txtfname.Text & "','" & txtmi.Text & "','" & txtlname.Text & "','" & txtssn.Text & "','" & txtbdate.Text & "','" & txtaddress.Text & "','" & cmbsex.Text.ToUpper & "','" & txtsalary.Text & "','" & DBNull.Value & "','" & s & "'", con)
            Dim cmd1 As New SqlCommand("INSERT INTO Depndent VALUES('" & txtessn.Text & "','" & txtdepname.Text & "','" & cmbdsex.Text.ToUpper & "','" & txtdbdate.Text & "','" & txtrelationship.Text & "'", con)

            cmd.ExecuteNonQuery()
            cmd1.ExecuteNonQuery()


            cleartxt()
            con.Close()

推荐答案

缺少右括号是您所拥有的较小问题之一:

The missing closing parens is one of the lesser problems in what you have:

1. 难以阅读和维护
2. 对 SQL 注入开放攻击
3. 假设代码知道 DB 层为列提供服务的顺序
4. 似乎没有丢弃一次性用品
5. 似乎正在进行不需要的数据类型转换

为地址 1、2 和 3 的长查询创建 SQL 的更好方法如下:

A better way to create the SQL for a long query which addresses 1, 2 and 3 is something like this:

' an XML literal
Dim SQL = <sql>
        INSERT INTO Employee 
           (FirstName, LastName, SocSecNum, BirthDate,
             Address, Gender
             ...) 
        VALUES 
            (@firstN, @lastN, @SSN ... )
        </sql>

Using cmd As New SqlCommand(sql.Value, con)
       ...
    cmd.Parameters.AddWithValue("@firstN", txtfname.Text)
    cmd.Parameters.AddWithValue("@lastN", txtlname.Text)
    ...
    cmd.ExecuteNonQuery()
    ...
End Using     ' disposes of the object when done

尽管 INSERT INTO tablename VALUES ... 是合法的语法，但它假定您的数据库将以某种顺序包含列，并且在您更改数据库时它不会改变.上述方法根据列的顺序和参数占位符将参数显式映射到列.名称 ("@firstN") 让您可以轻松查看正在使用的值.

Even though INSERT INTO tablename VALUES ... is legal syntax, it assumes that your db will have the columns in the some order and that it will not change as you alter the db. The above method explicitly maps parameters to columns based on the order of the columns and parameter placeholders. The names ("@firstN") make it easy for you to see which value you are working with.

使用 XML 文字将允许您对文本进行布局，但它对您来说最易读，但即使是字符串文字也比连接更容易阅读和维护:

Using an XML literal will allow you to layout the text however it is most readable to you, but even a string literal can be easier to read and maintain than concatenating:

Dim strSQL As String = "INSERT INTO Employee (FirstName, LastName, SocSecNum, " _
             & "BirthDate, Address, Gender ..." _
             & " VALUES (@p1, @p2...)"

使用任何一种方法，OP 中缺少的括号会更加明显，并且根本不可能发生.接下来，有些列似乎是数字，例如 Salary 和 BirthDate，所以这样的 SQL 可能是错误的:

Using either method, the missing parens in the OP would have been much more obvious and less likely to have happened at all. Next, some columns appear to be numeric such as Salary and BirthDate, so SQL such as this may be wrong:

... & "','" & txtbdate.Text & "','" & txtsalary.Text & "','" ...

在值周围放置刻度可确保您将字符串传递给 dbLayer.如果 Salary 是一个数字列，而 DateOfBirth 是一个日期列——它们应该是这样的——那么您很可能会遇到数据类型不匹配错误.参数可以更轻松地传递正确的数据类型，因为您可以看到正在处理的内容:

Placing ticks around the value assures that you are passing a String to the dbLayer. If Salary is a numeric column and DateOfBirth is a date column -- as they should be -- then you may well get a data type mismatch error. Parameters make it easier to pass the correct data type because you can see what you are working with:

  cmd.Parameters.AddWithValue("@DOB", CDate(txtbdate.Text))
  cmd.Parameters.AddWithValue("@Salary", CDec(txtsalary.Text))

(注意，在使用 OleDB 时，参数只是序数，因此请务必按照 SQL 中指定的顺序进行 AddWithValue).传递错误的数据类型可能会导致 dblayer 做出一些假设，为避免这种情况，您可以长期这样做:

(Note, when using OleDB, parameters are simply ordinal so be sure to AddWithValue in the same order as specified in the SQL). Passing the wrong datatype can cause the dblayer to make some assumptions, to avoid this you can do it the long way:

cmd.Parameters.Add("@firstN", SqlDbType.VarChar, 32)        ' 32 = column def
cmd.Parameters("@firstN").Value = txtfname.Text

另一个问题是代码似乎没有执行任何数据验证.如果他们输入我喜欢馅饼"作为薪水怎么办?或者输入02/31/1986" (mm/dd) 作为出生日期?您的代码将深入此过程.因此，在用户输入上使用 DateTime.TryParse 和 Integer.TryParse 之类的数据验证应该(并且可能已经)在上游更远的地方进行.

One more issue is that the code does not seem to perform any data validation. What if they type "I like pie" as the Salary? Or enter "02/31/1986" (mm/dd) for BirthDate? Your code will crash deep into this procedure. So data validation using things like DateTime.TryParse and Integer.TryParse on the user input should have (and may have) taken place much further upstream.

这篇关于'4' 附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！