'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. It is hard to read and maintain
2. It is wide open to SQL injection attacks
3. It assumes the code knows the order the DB layer will serve up the columns
4. Doesn't appear disposables are being disposed
5. Unwanted data type conversion seem to be going on

为地址为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中丢失的parens都将更加明显并且根本不会发生.接下来，某些列似乎是数字，例如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

另一个问题是该代码似乎未执行任何数据验证.如果他们输入我喜欢馅饼"作为薪水该怎么办?或在BirthDate中输入"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屋！