Question

我试图使用VB按钮将数据插入数据库，但它不断显示我为异常设置的错误消息。

有人能帮我解释一下为什么这不会更新数据库吗？

   Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click

    Dim connetionString As String
    Dim sqlCnn As SqlConnection
    Dim sql As String
    Dim adapter As New SqlDataAdapter
    Dim Customer As String = TextBox1.Text
    Dim Product As String = TextBox2.Text
    Dim Location As String = TextBox3.Text
    Dim Details As String = TextBox4.Text
    Dim Owners As String = DropDownList1.Text
    Dim Urgency As String = DropDownList2.Text


    connetionString = "Data Source=ZUK55APP02;Initial Catalog=BugFixPortal;User ID=SLC***;Password=rep***"
    sql = "INSERT INTO Requests (Owner, Customer, Product, Location, Urgency, Details) VALUES ( " & Owners & ", " & Customer & ", " & Product & ", " & Location & ", " & Urgency & ", " & Details & " )"
    sqlCnn = New SqlConnection(connetionString)

    Try
        sqlCnn.Open()
        adapter.UpdateCommand = sqlCnn.CreateCommand
        adapter.UpdateCommand.CommandText = sql
        adapter.UpdateCommand.ExecuteNonQuery()
        sqlCnn.Close()

    Catch ex As Exception
        MsgBox("Unable to update Database with Request - Please speak to Supervisor!")

    End Try

End Sub

Answer 1

我不会走这条路，因为你的代码对SQL注入的抵抗力很弱

您应该使用参数。大致如下

c.Open();
string insertString = @"insert into YourTable(name, street, city,....) values(@par1,  @par2, @parN,....)"
SqlCommand cmd = new SqlCeCommand(insertString, c);
cmd.Parameters.Add("@par1", SqlDbType.VarChar).Value = "MyName";
//etc
cmd.ExecuteNonQuery();
c.Close();

Answer 2

你错误地引用了你的价值观。

此字符串在所有值周围都有一个单引号，这是不正确的。

VALUES ( " & Owners & ", " & Customer & ", " & Product & ", " & Location & ", " & Urgency & ", " & Details & " )"

相反，在字符数据周围加上单引号，例如，如果Product是varchar，它看起来像这样：

VALUES (" & Owners & ", " & Customer & ",  " & Product & " , " & Location & ", " & Urgency & ", " & Details & ")"

真正的问题是，你应该使用参数化查询。此代码容易受到SQL注入攻击

Answer 3

改变这一点；

MsgBox("Unable to update Database with Request - Please speak to Supervisor!")

像这样的东西；

MsgBox("Unable to update Database with Request - Please speak to Supervisor!" & ex.Message)

它将为您提供有关异常的更多详细信息，但快速浏览一下，我可以看到一个问题，您试图插入的值是字符串，您将所有值都包含在一组字符中，而不是将每个字符串参数包含在一对值中，即。

sql = "INSERT INTO Requests (Owner, Customer, Product, Location, Urgency, Details) VALUES ( " & Owners & " ,  " & Customer & " ,  " & Product & " ,  " & Location & " ,  " & Urgency & " ,  " & Details & " )"

Answer 4

你真的应该考虑参数化你的查询，因为你很容易受到SQL注入攻击。请参见此处

就代码本身而言，SQL语法是错误的，因为需要在每个值周围加撇号。试试这个：

sql = "INSERT INTO Requests (Owner, Customer, Product, Location, Urgency, Details)
VALUES ( " & Owners & " ,  " & Customer & " ,  " & Product &
     " ,  " & Location & " ,  " & Urgency & " ,  " & Details & " )"

下面是一个使用Parameters的示例

sql = "INSERT INTO Requests (Owner, Customer, Product, Location, Urgency, Details)
VALUES ( @Owners ,  @Customer ,  @Product ,  @Location ,  @Urgency ,  @Details )"

然后添加如下参数：

command.Parameters.AddWithValue("@Owners", Owners)
command.Parameters.AddWithValue("@Customer", Customer)
command.Parameters.AddWithValue("@Product", Product)
command.Parameters.AddWithValue("@Location", Location)
command.Parameters.AddWithValue("@Urgency", Urgency)
command.Parameters.AddWithValue("@Details", Details)

Answer 5

我认为你想使用适配器。InsertCommand而不是适配器。UpdateCommand

在里面

Try
    sqlCnn.Open()
    adapter.UpdateCommand = sqlCnn.CreateCommand //(adapter.InsertCommand)
    adapter.UpdateCommand.CommandText = sql //(adapter.InsertCommand)
    adapter.UpdateCommand.ExecuteNonQuery() //(adapter.InsertCommand)
    sqlCnn.Close()

Catch ex As Exception
    MsgBox("Unable to update Database with Request - Please speak to Supervisor!")

End Try

并同意参数化sql查询

请参见http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx了解更多信息

友情链接