Update sql command in vb.net flaws?

Question

i have this code made by myself researching, it returns no error, it update(s) some of the data entered in textboxes but not all the fields

i check the codes near the field that is updating to compare it to the textboxes that do not update.

but i dont see the difference, it just not update all fields, only some fields

   Dim sqlconn As New SqlClient.SqlConnection
    sqlconn.ConnectionString = "server = SKPI-APPS1;" & _
    "Database = EOEMS;integrated security=true"

    Dim myCommand As SqlCommand
    Try

        'update command
        sqlconn.Open()

        myCommand = New SqlCommand(
          "UPDATE tblOfficeEquipmentProfile SET OE_Category = '" & cmbCategory.Text
& "',OE_SubCategory = '" & cmbSubCategory.Text
& "', OE_Name = '" & txtName.Text
& "', OE_User = '" & txtUser.Text
& "', OE_Brand = '" & cmbBrand.Text
& "', OE_Model = '" & cmbModel.Text
& "', OE_Specs = '" & txtSpecs.Text
& "', OE_SerialNo = '" & txtSerialNo.Text
& "', OE_PropertyNo = '" & txtPropertyNo.Text
& "', OE_MacAddress = '" & txtMacAddress.Text
& "', OE_Static_IP = '" & txtStaticIp.Text
& "', OE_Vendor = '" & cmbVendor.Text
& "', OE_PurchaseDate = '" & txtPurchaseDate.Text
& "', OE_WarrantyInclusiveYear = '" & cmbWarrantyInclusiveYear.Text
& "', OE_WarrantyStatus = '" & txtWarrantyStatus.Text
& "', OE_Status = '" & txtStatus.Text
& "', OE_Dept_Code = '" & cmbDeptCode.Text
& "', OE_Location_Code = '" & cmbLocationCode.Text
& "', OE_Remarks ='" & cmbRemarks.Text
& "' WHERE OE_ID = '" & txtOEID.Text & "'", sqlconn)
' ^^  (edited to separate lines for ease of viewing )
        myCommand.ExecuteNonQuery()
        MessageBox.Show("Office Equipment Profile Successfully Updated Records")
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

We don't have your database or data, so there's no way for *us* to run your code - so maybe you could give us some hints - e.g. you say "some fields" - maybe tell us which ones work and which ones don't? — Damien_The_Unbeliever, Apr 11 '13 at 08:27
the fields that only updated are, OE_Name, OE_Specs, OE_SerialNo, and OE_PropertyNo the rest didnt update my primary key is OE_ID — ivandinglasan, Apr 11 '13 at 08:34
actually i got parametrized code for updating but im not used to it, and i cant even get it to work.... but with this style of code add,delete is already working i only need this update — ivandinglasan, Apr 11 '13 at 08:36
Are you passing newly updated values for them to be updated? What if you run the same query generated from your VB.NET, on the database itself - does it update the same records? Make sure that the WHERE clause is set correctly so you update your wanted records only. — Mez, Apr 11 '13 at 08:52
I will check if your database fields are all of text/varchar/nvarchar datatype. You are passing every value enclosed in quotes and this means that the receiving field is of text type. Is it so? — Steve, Apr 11 '13 at 08:55
@ivandiglasan You *do* realize that writing such code is an invitation for SQL injection attacks, right? — Frank Schmitt, Apr 11 '13 at 10:52
@FrankSchmitt yes sir i do realize it for sql injection attacks. — ivandinglasan, Apr 12 '13 at 02:32
If some of that fields are not strings then do not pass strings (quotes around your values) but the plain numeric value. Remember that one of the many parameters benefits are the correct handling of these situations — Steve, Apr 12 '13 at 07:03

score 0 · Answer 1 · answered Apr 11 '13 at 21:09

Some troubleshooting suggestions:

Try a pattern like this:

        Dim SQL As String = "UPDATE STaff Set Initials='RCH' WHERE Initials = 'RCH'"
        myCommand = New SqlCommand(SQL, sqlconn)
        Dim iCnt As Integer = myCommand.ExecuteNonQuery()
        MessageBox.Show("Office Equipment Profile Successfully Updated " & iCnt & " Records")

Place a breakpoint on the second line and use the Text Visualizer to look at the SQL. You can also copy it and use in some other query tool to work on it and find the errors.

Also, capture the number of records changed (iCnt above) and do some QA and/or debug.

Injection: While your project may not be exposed to an injection attack you can step on your self by not making sure that the .Text values don't break the SQL. For example if any of the .Text contains an apostrophe the SQL will fail. You can write a Function to replace ' with '' and you'll be safe.

Or do each: OE_Location_Code = '" & cmbLocationCode.Text.replace("'","''")

This will convert "Fred's Room" to "Fred''s Room"

Update sql command in vb.net flaws?

1 Answers1