user1342164 user1342164 - 1 year ago 59 Question

Trim characters in a SQL update statement?

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)


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

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 = '" & ???????? & "';"


Catch ex As System.Exception

End Try

End If
End Sub


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.