Update SQL Table Data using DataGrid

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

Hello All,

Is it possible to update Sql Table through DataGrid. I have a DataGrid
which is being populated through a stored procedure, all i wanted to do
is to update one field (FieldName-Authorised) which has a datatype bit
through DataGrid but not sure how to go about it. Any Ideas Guys on
this one, your help is greatly appreciated.

This is the procedure used to populate the datagrid, The primary key
for the table is EntryID which is displayed on the datagrid. I need to
update only one column based on the entryid, is that possible.

Private Sub FillData()
Dim username As String
username = lblUser.Text.Trim
Try
Dim cmd2 As New SqlClient.SqlCommand
cmd2.CommandText = "c_sp_manager_access"
cmd2.CommandType = CommandType.StoredProcedure
cmd2.Connection = SqlConnection1
cmd2.Parameters.Clear()
Dim myparam As New SqlClient.SqlParameter
myparam.ParameterName = "@UserName"
myparam.Direction = ParameterDirection.Input
myparam.SqlDbType = SqlDbType.VarChar
myparam.Value = username
cmd2.Parameters.Add(myparam)
SqlDataAdapter1.SelectCommand = cmd2
SqlDataAdapter1.Fill(DataSet31, "c_sp_manager_access")
DataGrid1.DataBind()

Catch ex As Exception
Dim debug As String = ex.Message
lblfeedback.Text = ex.Message
End Try
End Sub.

I have written a stored procedure to update the column, it takes the
following parameters

EntryID, Authorised(Value either True or False), EditedBy(String).

Now how do i use this stored procedure in the datagrid to update my sql
table. Please advice.

Thanks in Advance, Shilpa.
 
This is the Code i am using to update the data, the checkbox column in
the datagrid is template column. I am not sure do i add a button to the
form to get the below procedure executed how do i go about this one.
Can anyone help me here please.

Private Sub UpdateCommand_Click(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DataGrid1.UpdateCommand
Dim DBCONN As New System.Data.SqlClient.SqlConnection(CNString)
Dim CEID As TextBox = e.Item.Cells(0).Controls(0)
Dim CAuth As TextBox = e.Item.Cells(9).Controls(0)
Dim username As String
username = lblUser.Text.Trim
Try
Dim cmd2 As New SqlClient.SqlCommand
cmd2.CommandText = "Update Table t_AbsenceEntry Set
Authorised='" & CAuth.Text & "' and EditedBy='" & username & "' Where
EntryID = " & CEID.Text
cmd2.CommandType = CommandType.StoredProcedure
cmd2.Connection = DBCONN
DBCONN.Open()
cmd2.ExecuteNonQuery()
DataGrid1.EditItemIndex = -1
'BindData()
Dim rowsAffected As Integer = 1
If rowsAffected > 0 Then
lblfeedback.Text = "Successfully Update database!"
Else
lblfeedback.Text = "Fail to update Database!"
End If

Catch ex As Exception
lblfeedback.Text = ex.Message
End Try

End Sub
 
Back
Top