Trim characters in a SQL update statement?

Question

Is it possible to do a trim for a column in a SQL update statement? Basically once the button is clicked on my gridview I want it to trim 10 characters from the left for column "Status" Is that possible within what I am using below for the column that is selected on the gridview?

   Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        If (e.CommandName = "Unlock") Then

            Dim index As Integer = Convert.ToInt32(e.CommandArgument)
            Dim row As GridViewRow = GridView1.Rows(index)


            Try

                Dim Con As SqlConnection
                Dim cmd As SqlCommand
                Con = New SqlConnection
                Con.ConnectionString = "Data Source="
                Con.Open()

                cmd = New SqlCommand
                cmd.Connection = Con

                '''''Can I trim the status column 10 characters where it is the selected row?'''''''''''
                cmd.CommandText = "UPDATE tbltest SET Status = '" & ?????? & "' where row = '" & ???????? & "';"

                cmd.ExecuteNonQuery()

            Catch ex As System.Exception


            End Try

        End If
    End Sub

That's part of my question, how to do I make sure it trims the correct row that the button was clicked on on the gridview? There can be 10 rows with 10 unlock buttons for each row — user1342164, Apr 19 '16 at 15:25
You have the GridViewRow, you could either store the PK-information in a (hidden) control which is in a `TemplateField`, then you get it via `row.FindControl("ControlID")`. Or you can [get it via `DataKeys`](http://stackoverflow.com/questions/2818203/get-datakey-values-in-gridview-rowcommand). Then change your sql query to use sql-parameters(mainly to fix your sql injection vulnerability). You can then use a sql like this: `UPDATE tbltest SET Status=RIGHT(Status, LEN(Status) - 10) WHERE ID=@ID` — Tim Schmelter, Apr 19 '16 at 15:35

score 0 · Accepted Answer · answered Apr 19 '16 at 15:29

For how to trim your string see below. This does NOT answer how to select the correct record in Sql. You need to figure out what the identifier is in your Table and make sure your datagrid uses that same identifier. Ideally the identifier is the Primary key of the table. Then pass that in as a parameter in your WHERE clause using a parameterized query.

See sql's SubString method. Your sql query would be

UPDATE tbltest 
SET Status = SubString(Status, 0, CASE WHEN LEN(Status) >= 10 THEN 10 ELSE LEN(Status) END) 
where TablePkColumn = @myPkParameter

I added a length check to ensure that you do not get an exception if there are less than 10 characters available in which case an empty string would be returned.

Other important points

Please wrap your connection strings in using blocks so the connection is always closed when you are done with it. Alternatively use a finally block to close the connection.
Use a parameterized query instead of string concatination. This is for your where clause but you should do it anywhere you have external input that is being used in your query.

Trim characters in a SQL update statement?

1 Answers1