SqlDataAdapter1.update for datagrid1 does not work

  • Thread starter Thread starter rodriguez.a.christian
  • Start date Start date
R

rodriguez.a.christian

I cannot make my update command work for a datagrid:

Public Class subcatinsert
Inherits System.Web.UI.Page
Protected WithEvents SqlCon As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlSelectCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlUpdateCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents AdapSubCat As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents AdapCatMaster As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents SqlSelectCommand2 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand2 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlUpdateCommand2 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlDeleteCommand2 As
System.Data.SqlClient.SqlCommand
Protected WithEvents GL_DS_SubCatCatMaster1 As
_packpay.GL_DS_SubCatCatMaster
Protected WithEvents DataGrid1 As
System.Web.UI.WebControls.DataGrid
Protected WithEvents SqlDeleteCommand1 As
System.Data.SqlClient.SqlCommand

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlCon = New System.Data.SqlClient.SqlConnection
Me.AdapSubCat = New System.Data.SqlClient.SqlDataAdapter
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
Me.AdapCatMaster = New System.Data.SqlClient.SqlDataAdapter
Me.SqlDeleteCommand2 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand2 = New System.Data.SqlClient.SqlCommand
Me.SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand2 = New System.Data.SqlClient.SqlCommand
Me.GL_DS_SubCatCatMaster1 = New _packpay.GL_DS_SubCatCatMaster
CType(Me.GL_DS_SubCatCatMaster1,
System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlCon
'
Me.SqlCon.ConnectionString = "workstation id=HERE GES THE
LOGIN"
'
'AdapSubCat
'
Me.AdapSubCat.DeleteCommand = Me.SqlDeleteCommand1
Me.AdapSubCat.InsertCommand = Me.SqlInsertCommand1
Me.AdapSubCat.SelectCommand = Me.SqlSelectCommand1
Me.AdapSubCat.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "SubCat", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("SubCatId", "SubCatId"), New
System.Data.Common.DataColumnMapping("CatMasterId", "CatMasterId"),
New System.Data.Common.DataColumnMapping("SubCatStatus",
"SubCatStatus"), New
System.Data.Common.DataColumnMapping("SubCatDesc", "SubCatDesc")})})
Me.AdapSubCat.UpdateCommand = Me.SqlUpdateCommand1
'
'SqlDeleteCommand1
'
Me.SqlDeleteCommand1.CommandText = "DELETE FROM SubCat WHERE
(SubCatId = @Original_SubCatId) AND (CatMasterId = @" & _
"Original_CatMasterId OR @Original_CatMasterId IS NULL AND
CatMasterId IS NULL) A" & _
"ND (SubCatDesc = @Original_SubCatDesc OR @Original_SubCatDesc
IS NULL AND SubCat" & _
"Desc IS NULL) AND (SubCatStatus = @Original_SubCatStatus OR
@Original_SubCatStat" & _
"us IS NULL AND SubCatStatus IS NULL)"
Me.SqlDeleteCommand1.Connection = Me.SqlCon
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_SubCatId",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "SubCatId",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CatMasterId",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "CatMasterId",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_SubCatDesc",
System.Data.SqlDbType.VarChar, 150,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "SubCatDesc", System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_SubCatStatus",
System.Data.SqlDbType.TinyInt, 1,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "SubCatStatus", System.Data.DataRowVersion.Original, Nothing))
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO
SubCat(CatMasterId, SubCatStatus, SubCatDesc) VALUES (@CatMasterI" & _
"d, @SubCatStatus, @SubCatDesc); SELECT SubCatId, CatMasterId,
SubCatStatus, SubC" & _
"atDesc FROM SubCat WHERE (SubCatId = @@IDENTITY)"
Me.SqlInsertCommand1.Connection = Me.SqlCon
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CatMasterId",
System.Data.SqlDbType.Int, 4, "CatMasterId"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SubCatStatus",
System.Data.SqlDbType.TinyInt, 1, "SubCatStatus"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SubCatDesc",
System.Data.SqlDbType.VarChar, 150, "SubCatDesc"))
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT SubCatId,
CatMasterId, SubCatStatus, SubCatDesc FROM SubCat"
Me.SqlSelectCommand1.Connection = Me.SqlCon
'
'SqlUpdateCommand1
'
Me.SqlUpdateCommand1.CommandText = "UPDATE SubCat SET
CatMasterId = @CatMasterId, SubCatStatus = @SubCatStatus, S" & _
"ubCatDesc = @SubCatDesc WHERE (SubCatId = @Original_SubCatId)
AND (CatMasterId =" & _
" @Original_CatMasterId OR @Original_CatMasterId IS NULL AND
CatMasterId IS NULL)" & _
" AND (SubCatDesc = @Original_SubCatDesc OR
@Original_SubCatDesc IS NULL AND SubC" & _
"atDesc IS NULL) AND (SubCatStatus = @Original_SubCatStatus OR
@Original_SubCatSt" & _
"atus IS NULL AND SubCatStatus IS NULL); SELECT SubCatId,
CatMasterId, SubCatStat" & _
"us, SubCatDesc FROM SubCat WHERE (SubCatId = @SubCatId)"
Me.SqlUpdateCommand1.Connection = Me.SqlCon
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CatMasterId",
System.Data.SqlDbType.Int, 4, "CatMasterId"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SubCatStatus",
System.Data.SqlDbType.TinyInt, 1, "SubCatStatus"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SubCatDesc",
System.Data.SqlDbType.VarChar, 150, "SubCatDesc"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_SubCatId",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "SubCatId",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CatMasterId",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "CatMasterId",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_SubCatDesc",
System.Data.SqlDbType.VarChar, 150,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "SubCatDesc", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_SubCatStatus",
System.Data.SqlDbType.TinyInt, 1,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "SubCatStatus", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SubCatId",
System.Data.SqlDbType.Int, 4, "SubCatId"))
'
'AdapCatMaster
'
Me.AdapCatMaster.DeleteCommand = Me.SqlDeleteCommand2
Me.AdapCatMaster.InsertCommand = Me.SqlInsertCommand2
Me.AdapCatMaster.SelectCommand = Me.SqlSelectCommand2
Me.AdapCatMaster.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "CatMaster", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("CatMasterId", "CatMasterId"),
New System.Data.Common.DataColumnMapping("CatMasterStatus",
"CatMasterStatus"), New
System.Data.Common.DataColumnMapping("CatMasterDesc",
"CatMasterDesc")})})
Me.AdapCatMaster.UpdateCommand = Me.SqlUpdateCommand2
'
'SqlDeleteCommand2
'
Me.SqlDeleteCommand2.CommandText = "DELETE FROM CatMaster
WHERE (CatMasterId = @Original_CatMasterId) AND (CatMas" & _
"terDesc = @Original_CatMasterDesc OR @Original_CatMasterDesc
IS NULL AND CatMast" & _
"erDesc IS NULL) AND (CatMasterStatus =
@Original_CatMasterStatus OR @Original_Ca" & _
"tMasterStatus IS NULL AND CatMasterStatus IS NULL)"
Me.SqlDeleteCommand2.Connection = Me.SqlCon
Me.SqlDeleteCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CatMasterId",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "CatMasterId",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CatMasterDesc",
System.Data.SqlDbType.VarChar, 150,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "CatMasterDesc", System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CatMasterStatus",
System.Data.SqlDbType.TinyInt, 1,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "CatMasterStatus", System.Data.DataRowVersion.Original,
Nothing))
'
'SqlInsertCommand2
'
Me.SqlInsertCommand2.CommandText = "INSERT INTO
CatMaster(CatMasterStatus, CatMasterDesc) VALUES (@CatMasterStatu" & _
"s, @CatMasterDesc); SELECT CatMasterId, CatMasterStatus,
CatMasterDesc FROM " & _
"CatMaster WHERE (CatMasterId = @@IDENTITY)"
Me.SqlInsertCommand2.Connection = Me.SqlCon
Me.SqlInsertCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CatMasterStatus",
System.Data.SqlDbType.TinyInt, 1, "CatMasterStatus"))
Me.SqlInsertCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CatMasterDesc",
System.Data.SqlDbType.VarChar, 150, "CatMasterDesc"))
'
'SqlSelectCommand2
'
Me.SqlSelectCommand2.CommandText = "SELECT CatMasterId,
CatMasterStatus, CatMasterDesc FROM CatMaster"
Me.SqlSelectCommand2.Connection = Me.SqlCon
'
'SqlUpdateCommand2
'
Me.SqlUpdateCommand2.CommandText = "UPDATE CatMaster SET
CatMasterStatus = @CatMasterStatus, CatMasterDesc = @cat" & _
"MasterDesc WHERE (CatMasterId = @Original_CatMasterId) AND
(CatMasterDesc = @Ori" & _
"ginal_CatMasterDesc OR @Original_CatMasterDesc IS NULL AND
CatMasterDesc IS NULL" & _
") AND (CatMasterStatus = @Original_CatMasterStatus OR
@Original_CatMasterStatus " & _
"IS NULL AND CatMasterStatus IS NULL); SELECT CatMasterId,
CatMasterStatus, CatMa" & _
"sterDesc FROM CatMaster WHERE (CatMasterId = @CatMasterId)"
Me.SqlUpdateCommand2.Connection = Me.SqlCon
Me.SqlUpdateCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CatMasterStatus",
System.Data.SqlDbType.TinyInt, 1, "CatMasterStatus"))
Me.SqlUpdateCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CatMasterDesc",
System.Data.SqlDbType.VarChar, 150, "CatMasterDesc"))
Me.SqlUpdateCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CatMasterId",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "CatMasterId",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CatMasterDesc",
System.Data.SqlDbType.VarChar, 150,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "CatMasterDesc", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CatMasterStatus",
System.Data.SqlDbType.TinyInt, 1,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "CatMasterStatus", System.Data.DataRowVersion.Original,
Nothing))
Me.SqlUpdateCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CatMasterId",
System.Data.SqlDbType.Int, 4, "CatMasterId"))
'
'GL_DS_SubCatCatMaster1
'
Me.GL_DS_SubCatCatMaster1.DataSetName =
"GL_DS_SubCatCatMaster"
Me.GL_DS_SubCatCatMaster1.Locale = New
System.Globalization.CultureInfo("en-US")
Me.GL_DS_SubCatCatMaster1.Namespace = "http://192.168.1.214/
admin/dataset/GL_DS_SubCatCatMaster.xsd"
CType(Me.GL_DS_SubCatCatMaster1,
System.ComponentModel.ISupportInitialize).EndInit()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form
Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'let get it on
Dim DBString As New dls
Dim GetDatabaseString As String
SqlCon.ConnectionString =
DBString.GetLongString("GetDatabaseString")
SqlCon.Open()

Rebind()

End Sub
Private Sub Rebind()

AdapCatMaster.Fill(GL_DS_SubCatCatMaster1, "CatMaster")
AdapSubCat.Fill(GL_DS_SubCatCatMaster1, "SubCat")
DataGrid1.DataBind()

End Sub

Public Function BindJob(ByVal o As Object) As String

Dim drv As DataRowView = o

Dim dr As GL_DS_SubCatCatMaster.SubCatRow = drv.Row

'Dim dp As Employees.jobsRow = dr.GetParentRow("jobsemployee")
'Return dp.job_desc
' Alternatively, since this is strongly typed:
Return dr.CatMasterRow.CatMasterDesc

End Function

Public Sub EditCommand(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)

DataGrid1.EditItemIndex = e.Item.ItemIndex

Rebind()

End Sub

Public Sub CancelCommand(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)

DataGrid1.EditItemIndex = -1

Rebind()

End Sub

Public Sub ItemDataBound(ByVal sender As Object, ByVal e As
DataGridItemEventArgs)

Dim lit As ListItemType = e.Item.ItemType

If lit = ListItemType.EditItem Then

Dim ddlCatMaster As DropDownList
ddlCatMaster = e.Item.FindControl("ddlCatMaster")

Dim drv As DataRowView = e.Item.DataItem

If Not drv Is Nothing Then

ddlCatMaster.Items.FindByValue(drv("CatMasterId")).Selected = True
End If

End If

End Sub

Public Sub UpdateCommand(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)

'Rebind()

'Get reference to current row
Dim dr As GL_DS_SubCatCatMaster.SubCatRow =
GL_DS_SubCatCatMaster1.SubCat.Item(e.Item.DataSetIndex)

Dim ddlCatMaster As DropDownList
ddlCatMaster = e.Item.FindControl("ddlCatMaster")

dr.CatMasterId = ddlCatMaster.SelectedItem.Value

' Update
AdapSubCat.Update(GL_DS_SubCatCatMaster1, "SubCat")
GL_DS_SubCatCatMaster1.AcceptChanges()

DataGrid1.EditItemIndex = -1

Rebind()

End Sub

End Class
 
I found that the update command works but the value on the drop down
is the same as the original value.
 
Back
Top