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
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
using
blocks so the connection is always closed when you are done with it. Alternatively use a finally
block to close the connection.