在 vb.net 中使用 sql 参数更新

updating using sql parameters in vb.net

提问by ivandinglasan

my code returns error saying

我的代码返回错误说

violation of PRIMARY KEY constraint 'PK_tblOfficeEquipmentProfile'. Cannot insert duplicate key in object 'tblOfficeEquipmentProfile'.

违反 PRIMARY KEY 约束“PK_tblOfficeEquipmentProfile”。无法在对象“tblOfficeEquipmentProfile”中插入重复键。

This is my code:

这是我的代码：

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    Dim sqlconn As New SqlClient.SqlConnection
    sqlconn.ConnectionString = "server = SKPI-APPS1;" & _
    "Database = EOEMS;integrated security=true"

    Dim myCommand As SqlCommand


    'parametrized update sql command
    Try
        sqlconn.Open()

        myCommand = New SqlCommand("UPDATE tblOfficeEquipmentProfile SET OE_Category = @OE_Category, OE_SubCategory = @OE_SubCategory, OE_ID = @OE_ID, OE_Name = @OE_Name, OE_User = @OE_User, OE_Brand = @OE_Brand, OE_Model =@OE_Model, OE_Specs =@OE_Specs, OE_SerialNo =@OE_SerialNo, OE_PropertyNo = @OE_PropertyNo, OE_MacAddress = @OE_MacAddress, OE_Static_IP = @OE_Static_IP, OE_Vendor = @OE_Vendor, OE_PurchaseDate =@OE_PurchaseDate, OE_WarrantyInclusiveYear=@OE_WarrantyInclusiveYear, OE_WarrantyStatus=@OE_WarrantyStatus,OE_Status=@OE_Status,OE_Dept_Code=@OE_Dept_Code,OE_Location_Code=@OE_Location_Code,OE_Remarks=@OE_Remarks", sqlconn)
        myCommand.Parameters.Add("@OE_Category", cmbCategory.Text)
        myCommand.Parameters.Add("@OE_SubCategory", cmbSubCategory.Text)
        myCommand.Parameters.Add("@OE_ID", txtOEID.Text)
        myCommand.Parameters.Add("@OE_Name", txtName.Text)
        myCommand.Parameters.Add("@OE_User", txtUser.Text)
        myCommand.Parameters.Add("@OE_Brand", cmbBrand.Text)
        myCommand.Parameters.Add("@OE_Model", cmbModel.Text)
        myCommand.Parameters.Add("@OE_Specs", txtSpecs.Text)
        myCommand.Parameters.Add("@OE_SerialNo", txtSerialNo.Text)
        myCommand.Parameters.Add("@OE_PropertyNo", txtPropertyNo.Text)
        myCommand.Parameters.Add("@OE_MacAddress", txtMacAddress.Text)
        myCommand.Parameters.Add("@OE_Static_IP", txtStaticIp.Text)
        myCommand.Parameters.Add("@OE_Vendor", cmbVendor.Text)
        myCommand.Parameters.Add("@OE_PurchaseDate", txtPurchaseDate.Text)
        myCommand.Parameters.Add("@OE_WarrantyInclusiveYear", cmbWarrantyInclusiveYear.Text)
        myCommand.Parameters.Add("@OE_WarrantyStatus", txtWarrantyStatus.Text)
        myCommand.Parameters.Add("@OE_Status", txtStatus.Text)
        myCommand.Parameters.Add("@OE_Dept_Code", cmbDeptCode.Text)
        myCommand.Parameters.Add("@OE_Location_Code", cmbLocationCode.Text)
        myCommand.Parameters.Add("@OE_Remarks", cmbRemarks.Text)

        myCommand.ExecuteNonQuery()
    Catch ex As Exception
        MsgBox(ex.Message)
        MsgBox("Successfully Updated Records")
    End Try
End Sub

回答by BhaveshParmar

Check primary key of table tblOfficeEquipmentProfile

检查表 tblOfficeEquipmentProfile 的主键

Based on primary filed you can update rest of the fields, means remove updation of primary key fileds

根据主文件，您可以更新其余字段，这意味着删除主键文件的更新

回答by BhaveshParmar

Check where is utilized based on your primary field OE_ID

根据您的主要字段 OE_ID 检查使用位置

myCommand = New SqlCommand("UPDATE tblOfficeEquipmentProfile SET OE_Category = @OE_Category, OE_SubCategory = @OE_SubCategory, OE_Name = @OE_Name, OE_User = @OE_User, OE_Brand = @OE_Brand, OE_Model =@OE_Model, OE_Specs =@OE_Specs, OE_SerialNo =@OE_SerialNo, OE_PropertyNo = @OE_PropertyNo, OE_MacAddress = @OE_MacAddress, OE_Static_IP = @OE_Static_IP, OE_Vendor = @OE_Vendor, OE_PurchaseDate =@OE_PurchaseDate, OE_WarrantyInclusiveYear=@OE_WarrantyInclusiveYear, OE_WarrantyStatus=@OE_WarrantyStatus,OE_Status=@OE_Status,OE_Dept_Code=@OE_Dept_Code,OE_Location_Code=@OE_Location_Code,OE_Remarks=@OE_Remarks

myCommand = New SqlCommand("UPDATE tblOfficeEquipmentProfile SET OE_Category = @OE_Category, OE_SubCategory = @OE_SubCategory, OE_Name = @OE_Name, OE_User = @OE_User, OE_Brand = @OE_Brand, OE_Model =@Special_NoProp,@OE_Model =@Spec_NoProp_Model, = @OE_PropertyNo，OE_MacAddress = @OE_MacAddress，OE_Static_IP = @OE_Static_IP，OE_Vendor = @OE_Vendor，OE_PurchaseDate = @ OE_PurchaseDate，OE_WarrantyInclusiveYear = @ OE_WarrantyInclusiveYear，OE_WarrantyStatus = @ OE_WarrantyStatus，OE_Status = @ OE_Status，OE_Dept_Code = @ OE_Dept_Code，OE_Location_Code = @ OE_Location_Code，OE_Remarks =@OE_备注

where OE_ID = @OE_ID", sqlconn)

其中 OE_ID = @OE_ID", sqlconn)

   myCommand.Parameters.Add("@OE_Category", cmbCategory.Text)
    myCommand.Parameters.Add("@OE_SubCategory", cmbSubCategory.Text)
    myCommand.Parameters.Add("@OE_ID", txtOEID.Text)
    myCommand.Parameters.Add("@OE_Name", txtName.Text)
    myCommand.Parameters.Add("@OE_User", txtUser.Text)
    myCommand.Parameters.Add("@OE_Brand", cmbBrand.Text)
    myCommand.Parameters.Add("@OE_Model", cmbModel.Text)
    myCommand.Parameters.Add("@OE_Specs", txtSpecs.Text)
    myCommand.Parameters.Add("@OE_SerialNo", txtSerialNo.Text)
    myCommand.Parameters.Add("@OE_PropertyNo", txtPropertyNo.Text)
    myCommand.Parameters.Add("@OE_MacAddress", txtMacAddress.Text)
    myCommand.Parameters.Add("@OE_Static_IP", txtStaticIp.Text)
    myCommand.Parameters.Add("@OE_Vendor", cmbVendor.Text)
    myCommand.Parameters.Add("@OE_PurchaseDate", txtPurchaseDate.Text)
    myCommand.Parameters.Add("@OE_WarrantyInclusiveYear", cmbWarrantyInclusiveYear.Text)
    myCommand.Parameters.Add("@OE_WarrantyStatus", txtWarrantyStatus.Text)
    myCommand.Parameters.Add("@OE_Status", txtStatus.Text)
    myCommand.Parameters.Add("@OE_Dept_Code", cmbDeptCode.Text)
    myCommand.Parameters.Add("@OE_Location_Code", cmbLocationCode.Text)
    myCommand.Parameters.Add("@OE_Remarks", cmbRemarks.Text)

回答by Gino4nx

Hi This Query Updates All Datas in table so if You Have Some ID Unique you can't Update With identical ID, I think you need add Where Statement to this query.

嗨，此查询更新表中的所有数据，因此如果您有一些唯一的 ID，则无法使用相同的 ID 进行更新，我认为您需要向此查询添加 Where 语句。