使用 vb.net 中的文本框更新 sql 2005 数据库 [英] updating a sql 2005 database using text boxes in vb.net

问题描述

我有一个 VB.Net 表单，它允许用户更新客户详细信息，例如姓名、联系方式:等.因此，当客户输入客户名称等的新名称时，应用程序应更新相应的字段在与客户 ID 相关的现有条目中.

I have a VB.Net form which allows the user to update the customer details such as name, contact no:, etc. So when the customer enters the new name for the customer name etc. the application should update the corresponding field in the existing entry that relates to the customer ID.

Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim adapter As New SqlDataAdapter
Dim dt As New DataTable

cn.ConnectionString = ("Data Source=NIMO-HP\SQLEXPRESS;Initial Catalog=FYP_db;Integrated Security=True")
cmd.Connection = cn
cn.Open()

cmd.CommandText = " UPDATE TblCustomerDetails (compID, compName, compContact, compAddress, compFax, compEmail, compPayterm, compTaxscheme, compPaymode, compRemarks ) SET Values ('" & lblCID.Text & "', '" & txtCname.Text & "', '" & txtCpno.Text & "', '" & txtCaddrs.Text & "','" & txtCfax.Text & "', '" & txtCemail.Text & "', '" & cmbPterm.Text & "','" & cmbTaxschm.Text & "',' " & cmbPmode.Text & "', '" & txtRemarks.Text & "')  WHERE compID = '" & lblCID.Text & "';"

cmd.ExecuteNonQuery()
MsgBox("Account updated!!", MsgBoxStyle.Information, "Updation complete")

推荐答案

您在 UPDATE 语句中使用了 INSERT 语法.您的 UPDATE 语句应具有以下形式:

Your using a INSERT syntax for your UPDATE statement. Your UPDATE statement should have the form:

UPDATE tableName
SET    col1 = val1,
       col2 = val2,
       col3 = val3
WHERE  someColumn = someValue

此外，您对使用非参数化查询的 SQL 注入攻击持开放态度.最后，我将使用 Using 块来确保您的连接和命令被正确关闭和处理.

Additionally, you are wide open to SQL Injection attacks by using non-parameterized queries. Finally, I would use a Using blocks to ensure your connection and command are properly closed and disposed of.

把它们放在一起看起来像这样:

Putting it all together it would look something like this:

Using Dim cn As SqlConnection = New SqlConnection("Data Source=NIMO-HP\SQLEXPRESS;Initial Catalog=FYP_db;Integrated Security=True")

    cn.Open()

    Dim sqlQuery As String = "UPDATE TblCustomerDetails " + _
                             "SET compName = @compName, " + _
                             "compContact = @compContact, " + _
                             "compAddress = @compAddress, " + _
                             "compFax = @compFax, " + _
                             "compEmail = @compEmail, " + _
                             "compPayterm = @compPayterm, " + _
                             "compTaxscheme = @compTaxscheme, " + _
                             "compPaymode = @compPaymode, " + _
                             "compRemarks = @compRemarks " + _
                             "WHERE compID = @compID"

    Using Dim cmd As SqlCommand = New SqlCommand(sqlQuery, cn)

        cmd.Parameters.AddWithValue("@compFax", txtCname.Text)
        cmd.Parameters.AddWithValue("@compContact", txtCpno.Text)
        cmd.Parameters.AddWithValue("@compAddress", txtCaddrs.Text)
        cmd.Parameters.AddWithValue("@compFax", txtCfax.Text)
        cmd.Parameters.AddWithValue("@compEmail", txtCemail.Text)
        cmd.Parameters.AddWithValue("@compPayterm", cmbPTerm.Text)
        cmd.Parameters.AddWithValue("@compTaxscheme", cmbTaxschm.Text)
        cmd.Parameters.AddWithValue("@compPaymode", cmbPmode.Text)
        cmd.Parameters.AddWithValue("@compRemarks", txtRemarks.Text)
        cmd.Parameters.AddWithValue("@compID", lblCID.Text)

        Dim result As Integer

        result = cmd.ExecuteNonQuery()

        If result = 1 Then
            MsgBox("Account updated!!", MsgBoxStyle.Information, _
                   "Updation complete")
        Else
            MsgBox("Account not updated!!", MsgBoxStyle.Information, _
                   "Updation not complete")
        End If
    End Using
End Using

上面的代码示例还有几点需要注意:

There are a few more things to note in the above code sample:

首先，我从要更新的值列表中删除了 compID.您在 WHERE 查询中使用它，所以我认为如果您尝试更新作为 WHERE 子句一部分使用的同一列，您的查询中会得到有趣的结果.此外，该值的来源是一个标签，它告诉我它不应该被更改.

First, I removed compID from the list of values to update. You're using that in your WHERE query, so I think you would have interesting results in your query if you're trying to update the same column you are using as part of your WHERE clause. Additionally, the source for that value is a Label, which tells me it's not supposed to be changed.

其次，ExecuteNonQuery() 返回一个包含受影响行数的 int.在这种情况下，它应该是 1 - 如果它不是 1，我会让你显示一个不同的消息框.

Secondly, ExecuteNonQuery() returns an int with the number of rows affected. In this case, it should be 1 - if it's not 1, I have you show a different message box.

第三，cmbPTerm、cmbTaxxshm 和 cmbPmode 对我来说听起来像 ComboBox，你不会去使用他们的 Text 属性来获得我认为您期望的内容.我想你会想要 SelectedText - 很难说不知道你的 ComboBoxes 是如何绑定的.我会把它留给你作为练习:)

Thirdly, cmbPTerm, cmbTaxxshm and cmbPmode sound like ComboBox to me, and you're not going to get what I think you're expecting using their Text property. I think you'll want SelectedText - hard to say without knowning how your ComboBoxes are bound. I'll leave that as an exercise for you :)

第四，为了便于阅读，我将 UPDATE 查询分成几行 - 只要查询正确，您就不必这样做.

Fourth, I broke the UPDATE query up across several lines simply for readability - you don't have to do it that way, as long as the query is correct.

最后，我建议使用 MessagBox.Show() 与 MsgBox.

Finally, I'd suggest using MessagBox.Show() vs MsgBox.

这篇关于使用 vb.net 中的文本框更新 sql 2005 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！