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).BeginIni t()
'
'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_SubC atId",
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_CatM asterId",
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_SubC atDesc",
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_SubC atStatus",
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_SubC atId",
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_CatM asterId",
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_SubC atDesc",
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_SubC atStatus",
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("CatMasterSta tus",
"CatMasterStatus"), New
System.Data.Common.DataColumnMapping("CatMasterDes c",
"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_CatM asterId",
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_CatM asterDesc",
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_CatM asterStatus",
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("@CatMasterStat us",
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("@CatMasterStat us",
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_CatM asterId",
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_CatM asterDesc",
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_CatM asterStatus",
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.DataSetI ndex)
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
