How to update a record in a datagrid if primary key is uniqueident

[Hennie]

When trying to update a record in an editable datagrid I ran into a few
problems.
My update procedure is just not working.
Can someone please have a look at my code and see what am I doing wrong.

I had to set the datagrid's DataKeyField to MailSubscriberI D manually since
vs does not do it automatically.

Thanks

This is what my table looks like in SQL 2000:
CREATE TABLE [dbo].[tabMailSubscrib ers] (
[MailSubscriberI D] uniqueidentifie r ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_ CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_ CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE Latin1_General_ CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE Latin1_General_ CI_AS NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO

This is the update procedure:

CREATE PROCEDURE dbo.proc_tabMai lSubscibers_Upd ate
(
@MailSubscriber ID uniqueidentifie r,
@HotelID int,
@Name varchar(50),
@Surname varchar(50),
@Designation varchar(50),
@EmailAddress varchar(50),
@UpdatedBy int
)
AS
SET NOCOUNT OFF;
UPDATE tabMailSubscrib ers SET HotelID = @HotelID, Name = @Name, Surname =
@Surname, Designation = @Designation, EmailAddress = @EmailAddress, UpdatedBy
= @UpdatedBy, DateUpdated = (GetDate()) WHERE (MailSubscriber ID =
@MailSubscriber ID) ;
SELECT MailSubscriberI D, HotelID, Name, Surname, Designation, EmailAddress,
UpdatedBy, DateUpdated FROM tabMailSubscrib ers WHERE (MailSubscriber ID =
@MailSubscriber ID)
GO

This is my code to update the record:

Private Sub DGMailSubscribe rs_UpdateComman d(ByVal source As Object,
ByVal e As System.Web.UI.W ebControls.Data GridCommandEven tArgs) Handles
DGMailSubscribe rs.UpdateComman d
CType(e.Item.Ce lls(8).Controls (0), TextBox).Text = _
CType(e.Item.Fi ndControl("ddlH otelID"), DropDownList).S electedValue

' Step two: Fill DataSet and identify row to edit

SqlDAMailSubscr ibers.Fill(DsMa ilSubscribers1)
DAHotel.Fill(Ds Hotel1)
'DGMailSubscrib ers.DataBind()
Dim objEditRow As DataRow =
DsMailSubscribe rs1.Tables(0).R ows.Find(CType( e.Item.Cells(0) .Controls(0),
TextBox).Text)
' Step three: Cycle through valid "data" cells and put
' information back in underlying DataSet

SqlConn.Open()
Dim cnn As SqlClient.SqlCo nnection = SqlConn
Dim cmdUpdate As New SqlClient.SqlCo mmand
cmdUpdate = cnn.CreateComma nd
cmdUpdate.Comma ndText = "proc_tabMailSu bscribers_Updat e"
cmdUpdate.Comma ndType = CommandType.Sto redProcedure
cmdUpdate.Param eters.Add("@Mai lSubscriberID",
SqlDbType.Uniqu eIdentifier)
cmdUpdate.Param eters.Add("@Hot elID", SqlDbType.Int)
cmdUpdate.Param eters.Add("@Nam e", SqlDbType.VarCh ar)
cmdUpdate.Param eters.Add("@Sur name", SqlDbType.VarCh ar)
cmdUpdate.Param eters.Add("@Des ignation", SqlDbType.VarCh ar)
cmdUpdate.Param eters.Add("@Ema ilAddress", SqlDbType.VarCh ar)
cmdUpdate.Param eters.Add("@Upd atedBy", SqlDbType.Int)

'Update
cmdUpdate.Param eters("@MailSub scriberID").Val ue =
DGMailSubscribe rs.DataKeys(e.I tem.ItemIndex)
cmdUpdate.Param eters("@HotelID ").Value =
CType(e.Item.Fi ndControl("ddlH otelID"), DropDownList).S electedItem.Val ue()
cmdUpdate.Param eters("@Name"). Value =
CType(e.Item.Ce lls(0).Controls (0), TextBox).Text
cmdUpdate.Param eters("@Surname ").Value =
CType(e.Item.Ce lls(1).Controls (0), TextBox).Text
cmdUpdate.Param eters("@Designa tion").Value =
CType(e.Item.Ce lls(2).Controls (0), TextBox).Text
cmdUpdate.Param eters("@EmailAd dress").Value =
CType(e.Item.Ce lls(3).Controls (0), TextBox).Text
cmdUpdate.Param eters("@Updated By").Value = Session("LoginI D")

Try
cmdUpdate.Execu teNonQuery()
Message.InnerHt ml = "<b>Record Updated.</b><br>"

'DGMailSubscrib ers.EditItemInd ex = -5
Catch ex As SqlException
Message.InnerHt ml = "ERROR: Could not update record," _
& " please ensure the fields are correctly filled out."
Message.Style(" color") = "red"

End Try

[Elton Wang]

Hi Hennie,

Do you bind datasource of datagrid every time, no matter
it is in the first time or in postback. If that, change it
to only bind in IsPostback = false.

HTH

Elton Wang
el********@hotm ail.com

-----Original Message-----
When trying to update a record in an editable datagrid I ran into a fewproblems.
My update procedure is just not working.
Can someone please have a look at my code and see what am I doing wrong.
I had to set the datagrid's DataKeyField to MailSubscriberI D manually sincevs does not do it automatically.

Thanks

This is what my table looks like in SQL 2000:
CREATE TABLE [dbo].[tabMailSubscrib ers] (
[MailSubscriberI D] uniqueidentifie r ROWGUIDCOL NOT NULL , [HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_ CI_AS NOT NULL , [Surname] [varchar] (50) COLLATE Latin1_General_ CI_AS NOT NULL , [Designation] [varchar] (50) COLLATE Latin1_General_ CI_AS NOT NULL , [EmailAddress] [varchar] (50) COLLATE Latin1_General_ CI_AS NOT NULL , [UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO

This is the update procedure:

CREATE PROCEDURE dbo.proc_tabMai lSubscibers_Upd ate
(
@MailSubscriber ID uniqueidentifie r,
@HotelID int,
@Name varchar(50),
@Surname varchar(50),
@Designation varchar(50),
@EmailAddress varchar(50),
@UpdatedBy int
)
AS
SET NOCOUNT OFF;
UPDATE tabMailSubscrib ers SET HotelID = @HotelID, Name = @Name, Surname =@Surname, Designation = @Designation, EmailAddress = @EmailAddress, UpdatedBy= @UpdatedBy, DateUpdated = (GetDate()) WHERE (MailSubscriber ID =@MailSubscribe rID) ;
SELECT MailSubscriberI D, HotelID, Name, Surname, Designation, EmailAddress,UpdatedBy, DateUpdated FROM tabMailSubscrib ers WHERE (MailSubscriber ID =@MailSubscribe rID)
GO

This is my code to update the record:

Private Sub DGMailSubscribe rs_UpdateComman d(ByVal source As Object,ByVal e As System.Web.UI.W ebControls.Data GridCommandEven tArgs)
HandlesDGMailSubscrib ers.UpdateComma nd
CType(e.Item.Ce lls(8).Controls (0), TextBox).Text = _ CType(e.Item.Fi ndControl("ddlH otelID"), DropDownList).S electedValue
' Step two: Fill DataSet and identify row to edit

SqlDAMailSubscr ibers.Fill(DsMa ilSubscribers1)
DAHotel.Fill(Ds Hotel1)
'DGMailSubscrib ers.DataBind()
Dim objEditRow As DataRow =
DsMailSubscrib ers1.Tables(0). Rows.Find(CType (e.Item.Cells (0).Controls(0) ,TextBox).Tex t)
' Step three: Cycle through valid "data" cells and put ' information back in underlying DataSet
SqlConn.Open()
Dim cnn As SqlClient.SqlCo nnection = SqlConn
Dim cmdUpdate As New SqlClient.SqlCo mmand
cmdUpdate = cnn.CreateComma nd
cmdUpdate.Comma ndText = "proc_tabMailSu bscribers_Updat e" cmdUpdate.Comma ndType = CommandType.Sto redProcedure cmdUpdate.Param eters.Add("@Mai lSubscriberID",
SqlDbType.Uniq ueIdentifier)
cmdUpdate.Param eters.Add("@Hot elID", SqlDbType.Int) cmdUpdate.Param eters.Add("@Nam e", SqlDbType.VarCh ar) cmdUpdate.Param eters.Add("@Sur name", SqlDbType.VarCh ar) cmdUpdate.Param eters.Add("@Des ignation", SqlDbType.VarCh ar) cmdUpdate.Param eters.Add("@Ema ilAddress", SqlDbType.VarCh ar) cmdUpdate.Param eters.Add("@Upd atedBy", SqlDbType.Int)
'Update
cmdUpdate.Param eters("@MailSub scriberID").Val ue =
DGMailSubscrib ers.DataKeys(e. Item.ItemIndex)
cmdUpdate.Param eters("@HotelID ").Value =
CType(e.Item.F indControl("ddl HotelID"), DropDownList).S electedItem.Val ue() cmdUpdate.Param eters("@Name"). Value =
CType(e.Item.C ells(0).Control s(0), TextBox).Text
cmdUpdate.Param eters("@Surname ").Value =
CType(e.Item.C ells(1).Control s(0), TextBox).Text
cmdUpdate.Param eters("@Designa tion").Value =
CType(e.Item.C ells(2).Control s(0), TextBox).Text
cmdUpdate.Param eters("@EmailAd dress").Value =
CType(e.Item.C ells(3).Control s(0), TextBox).Text
cmdUpdate.Param eters("@Updated By").Value = Session ("LoginID")
Try
cmdUpdate.Execu teNonQuery()
Message.InnerHt ml = "<b>Record Updated.</b><br>"
'DGMailSubscrib ers.EditItemInd ex = -5
Catch ex As SqlException
Message.InnerHt ml = "ERROR: Could not update record," _ & " please ensure the fields are correctly filled out."
Message.Style(" color") = "red"

End Try
.

[Hennie]

Hi Elton
Thanks for your interest.

I only bind in the Page_Load event.

Hennie

"Elton Wang" wrote:

Hi Hennie,

Do you bind datasource of datagrid every time, no matter
it is in the first time or in postback. If that, change it
to only bind in IsPostback = false.

HTH

Elton Wang
el********@hotm ail.com

-----Original Message-----
When trying to update a record in an editable datagrid I

ran into a few

problems.
My update procedure is just not working.
Can someone please have a look at my code and see what am

I doing wrong.

I had to set the datagrid's DataKeyField to

MailSubscriberI D manually since

vs does not do it automatically.

Thanks

This is what my table looks like in SQL 2000:
CREATE TABLE [dbo].[tabMailSubscrib ers] (
[MailSubscriberI D] uniqueidentifie r ROWGUIDCOL

NOT NULL ,

[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_ CI_AS

NOT NULL ,

[Surname] [varchar] (50) COLLATE

Latin1_General_ CI_AS NOT NULL ,

[Designation] [varchar] (50) COLLATE

Latin1_General_ CI_AS NOT NULL ,

[EmailAddress] [varchar] (50) COLLATE

Latin1_General_ CI_AS NOT NULL ,

[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO

This is the update procedure:

CREATE PROCEDURE dbo.proc_tabMai lSubscibers_Upd ate
(
@MailSubscriber ID uniqueidentifie r,
@HotelID int,
@Name varchar(50),
@Surname varchar(50),
@Designation varchar(50),
@EmailAddress varchar(50),
@UpdatedBy int
)
AS
SET NOCOUNT OFF;
UPDATE tabMailSubscrib ers SET HotelID = @HotelID, Name =

@Name, Surname =

@Surname, Designation = @Designation, EmailAddress =

@EmailAddress, UpdatedBy

= @UpdatedBy, DateUpdated = (GetDate()) WHERE

(MailSubscriber ID =

@MailSubscribe rID) ;
SELECT MailSubscriberI D, HotelID, Name, Surname,

Designation, EmailAddress,

UpdatedBy, DateUpdated FROM tabMailSubscrib ers WHERE

(MailSubscriber ID =

@MailSubscribe rID)
GO

This is my code to update the record:

Private Sub DGMailSubscribe rs_UpdateComman d(ByVal

source As Object,

ByVal e As

System.Web.UI.W ebControls.Data GridCommandEven tArgs)
Handles

DGMailSubscrib ers.UpdateComma nd
CType(e.Item.Ce lls(8).Controls (0), TextBox).Text

= _

CType(e.Item.Fi ndControl("ddlH otelID"),

DropDownList).S electedValue

' Step two: Fill DataSet and identify row to edit

SqlDAMailSubscr ibers.Fill(DsMa ilSubscribers1)
DAHotel.Fill(Ds Hotel1)
'DGMailSubscrib ers.DataBind()
Dim objEditRow As DataRow =
DsMailSubscrib ers1.Tables(0). Rows.Find(CType (e.Item.Cells

(0).Controls(0) ,

TextBox).Tex t)
' Step three: Cycle through valid "data" cells

and put

' information back in underlying

DataSet

SqlConn.Open()
Dim cnn As SqlClient.SqlCo nnection = SqlConn
Dim cmdUpdate As New SqlClient.SqlCo mmand
cmdUpdate = cnn.CreateComma nd
cmdUpdate.Comma ndText

= "proc_tabMailSu bscribers_Updat e"

cmdUpdate.Comma ndType =

CommandType.Sto redProcedure

cmdUpdate.Param eters.Add("@Mai lSubscriberID",
SqlDbType.Uniq ueIdentifier)
cmdUpdate.Param eters.Add("@Hot elID",

SqlDbType.Int)

cmdUpdate.Param eters.Add("@Nam e",

SqlDbType.VarCh ar)

cmdUpdate.Param eters.Add("@Sur name",

SqlDbType.VarCh ar)

cmdUpdate.Param eters.Add("@Des ignation",

SqlDbType.VarCh ar)

cmdUpdate.Param eters.Add("@Ema ilAddress",

SqlDbType.VarCh ar)

cmdUpdate.Param eters.Add("@Upd atedBy",

SqlDbType.Int)

'Update
cmdUpdate.Param eters("@MailSub scriberID").Val ue =
DGMailSubscrib ers.DataKeys(e. Item.ItemIndex)
cmdUpdate.Param eters("@HotelID ").Value =
CType(e.Item.F indControl("ddl HotelID"),

DropDownList).S electedItem.Val ue()

cmdUpdate.Param eters("@Name"). Value =
CType(e.Item.C ells(0).Control s(0), TextBox).Text
cmdUpdate.Param eters("@Surname ").Value =
CType(e.Item.C ells(1).Control s(0), TextBox).Text
cmdUpdate.Param eters("@Designa tion").Value =
CType(e.Item.C ells(2).Control s(0), TextBox).Text
cmdUpdate.Param eters("@EmailAd dress").Value =
CType(e.Item.C ells(3).Control s(0), TextBox).Text
cmdUpdate.Param eters("@Updated By").Value = Session

("LoginID")

Try
cmdUpdate.Execu teNonQuery()
Message.InnerHt ml = "<b>Record

Updated.</b><br>"

'DGMailSubscrib ers.EditItemInd ex = -5
Catch ex As SqlException
Message.InnerHt ml = "ERROR: Could not update

record," _

& " please ensure the fields are correctly filled out."
Message.Style(" color") = "red"

End Try
.

[Elton Wang]

That's right. And you also need

If Not IsPostBack Then
' Bind datagrid
End If

-----Original Message-----
Hi Elton
Thanks for your interest.

I only bind in the Page_Load event.

Hennie

"Elton Wang" wrote:

Hi Hennie,

Do you bind datasource of datagrid every time, no matter it is in the first time or in postback. If that, change it to only bind in IsPostback = false.

HTH

Elton Wang
el********@hotm ail.com

>-----Original Message-----
>When trying to update a record in an editable datagrid I

ran into a few

>problems.
>My update procedure is just not working.
>Can someone please have a look at my code and see what

am I doing wrong.

>
>I had to set the datagrid's DataKeyField to

MailSubscriberI D manually since

>vs does not do it automatically.
>
>Thanks
>
>This is what my table looks like in SQL 2000:
>CREATE TABLE [dbo].[tabMailSubscrib ers] (
> [MailSubscriberI D] uniqueidentifie r ROWGUIDCOL

NOT NULL ,

> [HotelID] [int] NOT NULL ,
> [Name] [varchar] (50) COLLATE Latin1_General_ CI_AS

NOT NULL ,

> [Surname] [varchar] (50) COLLATE

Latin1_General_ CI_AS NOT NULL ,

> [Designation] [varchar] (50) COLLATE

Latin1_General_ CI_AS NOT NULL ,

> [EmailAddress] [varchar] (50) COLLATE

Latin1_General_ CI_AS NOT NULL ,

> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
>) ON [PRIMARY]
>GO
>
>This is the update procedure:
>
>CREATE PROCEDURE dbo.proc_tabMai lSubscibers_Upd ate
>(
> @MailSubscriber ID uniqueidentifie r,
> @HotelID int,
> @Name varchar(50),
> @Surname varchar(50),
> @Designation varchar(50),
> @EmailAddress varchar(50),
> @UpdatedBy int
>)
>AS
> SET NOCOUNT OFF;
>UPDATE tabMailSubscrib ers SET HotelID = @HotelID, Name

= @Name, Surname =

>@Surname, Designation = @Designation, EmailAddress =

@EmailAddress, UpdatedBy

>= @UpdatedBy, DateUpdated = (GetDate()) WHERE

(MailSubscriber ID =

>@MailSubscribe rID) ;
> SELECT MailSubscriberI D, HotelID, Name, Surname,

Designation, EmailAddress,

>UpdatedBy, DateUpdated FROM tabMailSubscrib ers WHERE

(MailSubscriber ID =

>@MailSubscribe rID)
>GO
>
>This is my code to update the record:
>
> Private Sub DGMailSubscribe rs_UpdateComman d(ByVal

source As Object,

>ByVal e As

System.Web.UI.W ebControls.Data GridCommandEven tArgs)
Handles

>DGMailSubscrib ers.UpdateComma nd
> CType(e.Item.Ce lls(8).Controls (0),

TextBox).Text = _

> CType(e.Item.Fi ndControl("ddlH otelID"),

DropDownList).S electedValue

>
> ' Step two: Fill DataSet and identify row to

edit >
> SqlDAMailSubscr ibers.Fill(DsMa ilSubscribers1)
> DAHotel.Fill(Ds Hotel1)
> 'DGMailSubscrib ers.DataBind()
> Dim objEditRow As DataRow =
>DsMailSubscrib ers1.Tables(0). Rows.Find(CType (e.Item.Cells (0).Controls(0) ,

>TextBox).Tex t)
> ' Step three: Cycle through valid "data" cells

and put

> ' information back in underlying

DataSet

>
> SqlConn.Open()
> Dim cnn As SqlClient.SqlCo nnection = SqlConn
> Dim cmdUpdate As New SqlClient.SqlCo mmand
> cmdUpdate = cnn.CreateComma nd
> cmdUpdate.Comma ndText

= "proc_tabMailSu bscribers_Updat e"

> cmdUpdate.Comma ndType =

CommandType.Sto redProcedure

> cmdUpdate.Param eters.Add("@Mai lSubscriberID",
>SqlDbType.Uniq ueIdentifier)
> cmdUpdate.Param eters.Add("@Hot elID",

SqlDbType.Int)

> cmdUpdate.Param eters.Add("@Nam e",

SqlDbType.VarCh ar)

> cmdUpdate.Param eters.Add("@Sur name",

SqlDbType.VarCh ar)

> cmdUpdate.Param eters.Add("@Des ignation",

SqlDbType.VarCh ar)

> cmdUpdate.Param eters.Add("@Ema ilAddress",

SqlDbType.VarCh ar)

> cmdUpdate.Param eters.Add("@Upd atedBy",

SqlDbType.Int)

>
> 'Update
> cmdUpdate.Param eters

("@MailSubscrib erID").Value = >DGMailSubscrib ers.DataKeys(e. Item.ItemIndex)
> cmdUpdate.Param eters("@HotelID ").Value =
>CType(e.Item.F indControl("ddl HotelID"),

DropDownList).S electedItem.Val ue()

> cmdUpdate.Param eters("@Name"). Value =
>CType(e.Item.C ells(0).Control s(0), TextBox).Text
> cmdUpdate.Param eters("@Surname ").Value =
>CType(e.Item.C ells(1).Control s(0), TextBox).Text
> cmdUpdate.Param eters("@Designa tion").Value =
>CType(e.Item.C ells(2).Control s(0), TextBox).Text
> cmdUpdate.Param eters("@EmailAd dress").Value =
>CType(e.Item.C ells(3).Control s(0), TextBox).Text
> cmdUpdate.Param eters("@Updated By").Value = Session ("LoginID")

>
> Try
> cmdUpdate.Execu teNonQuery()
> Message.InnerHt ml = "<b>Record

Updated.</b><br>"

>
> 'DGMailSubscrib ers.EditItemInd ex = -5
> Catch ex As SqlException
> Message.InnerHt ml = "ERROR: Could not

update record," _

> & " please ensure the fields are correctly filled

out." > Message.Style(" color") = "red"
>
> End Try
>.
>

.

[Hennie]

OK, So I am doing everything correct, so why is it not working?

"Elton Wang" wrote:

That's right. And you also need

If Not IsPostBack Then
' Bind datagrid
End If

-----Original Message-----
Hi Elton
Thanks for your interest.

I only bind in the Page_Load event.

Hennie

"Elton Wang" wrote:

Hi Hennie,

Do you bind datasource of datagrid every time, no matter it is in the first time or in postback. If that, change it to only bind in IsPostback = false.

HTH

Elton Wang
el********@hotm ail.com
>-----Original Message-----
>When trying to update a record in an editable datagrid I ran into a few
>problems.
>My update procedure is just not working.
>Can someone please have a look at my code and see what am I doing wrong.
>
>I had to set the datagrid's DataKeyField to
MailSubscriberI D manually since
>vs does not do it automatically.
>
>Thanks
>
>This is what my table looks like in SQL 2000:
>CREATE TABLE [dbo].[tabMailSubscrib ers] (
> [MailSubscriberI D] uniqueidentifie r ROWGUIDCOL
NOT NULL ,
> [HotelID] [int] NOT NULL ,
> [Name] [varchar] (50) COLLATE Latin1_General_ CI_AS
NOT NULL ,
> [Surname] [varchar] (50) COLLATE
Latin1_General_ CI_AS NOT NULL ,
> [Designation] [varchar] (50) COLLATE
Latin1_General_ CI_AS NOT NULL ,
> [EmailAddress] [varchar] (50) COLLATE
Latin1_General_ CI_AS NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
>) ON [PRIMARY]
>GO
>
>This is the update procedure:
>
>CREATE PROCEDURE dbo.proc_tabMai lSubscibers_Upd ate
>(
> @MailSubscriber ID uniqueidentifie r,
> @HotelID int,
> @Name varchar(50),
> @Surname varchar(50),
> @Designation varchar(50),
> @EmailAddress varchar(50),
> @UpdatedBy int
>)
>AS
> SET NOCOUNT OFF;
>UPDATE tabMailSubscrib ers SET HotelID = @HotelID, Name = @Name, Surname =
>@Surname, Designation = @Designation, EmailAddress =
@EmailAddress, UpdatedBy
>= @UpdatedBy, DateUpdated = (GetDate()) WHERE
(MailSubscriber ID =
>@MailSubscribe rID) ;
> SELECT MailSubscriberI D, HotelID, Name, Surname,
Designation, EmailAddress,
>UpdatedBy, DateUpdated FROM tabMailSubscrib ers WHERE
(MailSubscriber ID =
>@MailSubscribe rID)
>GO
>
>This is my code to update the record:
>
> Private Sub DGMailSubscribe rs_UpdateComman d(ByVal
source As Object,
>ByVal e As
System.Web.UI.W ebControls.Data GridCommandEven tArgs)
Handles
>DGMailSubscrib ers.UpdateComma nd
> CType(e.Item.Ce lls(8).Controls (0), TextBox).Text = _
> CType(e.Item.Fi ndControl("ddlH otelID"),
DropDownList).S electedValue
>
> ' Step two: Fill DataSet and identify row to edit >
> SqlDAMailSubscr ibers.Fill(DsMa ilSubscribers1)
> DAHotel.Fill(Ds Hotel1)
> 'DGMailSubscrib ers.DataBind()
> Dim objEditRow As DataRow =
>DsMailSubscrib ers1.Tables(0). Rows.Find(CType (e.Item.Cells (0).Controls(0) ,
>TextBox).Tex t)
> ' Step three: Cycle through valid "data" cells
and put
> ' information back in underlying
DataSet
>
> SqlConn.Open()
> Dim cnn As SqlClient.SqlCo nnection = SqlConn
> Dim cmdUpdate As New SqlClient.SqlCo mmand
> cmdUpdate = cnn.CreateComma nd
> cmdUpdate.Comma ndText
= "proc_tabMailSu bscribers_Updat e"
> cmdUpdate.Comma ndType =
CommandType.Sto redProcedure
> cmdUpdate.Param eters.Add("@Mai lSubscriberID",
>SqlDbType.Uniq ueIdentifier)
> cmdUpdate.Param eters.Add("@Hot elID",
SqlDbType.Int)
> cmdUpdate.Param eters.Add("@Nam e",
SqlDbType.VarCh ar)
> cmdUpdate.Param eters.Add("@Sur name",
SqlDbType.VarCh ar)
> cmdUpdate.Param eters.Add("@Des ignation",
SqlDbType.VarCh ar)
> cmdUpdate.Param eters.Add("@Ema ilAddress",
SqlDbType.VarCh ar)
> cmdUpdate.Param eters.Add("@Upd atedBy",
SqlDbType.Int)
>
> 'Update
> cmdUpdate.Param eters ("@MailSubscrib erID").Value = >DGMailSubscrib ers.DataKeys(e. Item.ItemIndex)
> cmdUpdate.Param eters("@HotelID ").Value =
>CType(e.Item.F indControl("ddl HotelID"),
DropDownList).S electedItem.Val ue()
> cmdUpdate.Param eters("@Name"). Value =
>CType(e.Item.C ells(0).Control s(0), TextBox).Text
> cmdUpdate.Param eters("@Surname ").Value =
>CType(e.Item.C ells(1).Control s(0), TextBox).Text
> cmdUpdate.Param eters("@Designa tion").Value =
>CType(e.Item.C ells(2).Control s(0), TextBox).Text
> cmdUpdate.Param eters("@EmailAd dress").Value =
>CType(e.Item.C ells(3).Control s(0), TextBox).Text
> cmdUpdate.Param eters("@Updated By").Value = Session ("LoginID")
>
> Try
> cmdUpdate.Execu teNonQuery()
> Message.InnerHt ml = "<b>Record
Updated.</b><br>"
>
> 'DGMailSubscrib ers.EditItemInd ex = -5
> Catch ex As SqlException
> Message.InnerHt ml = "ERROR: Could not update record," _
> & " please ensure the fields are correctly filled out." > Message.Style(" color") = "red"
>
> End Try
>.
>

.

[Elton Wang]

You can do three steps to find out error:

Step 1. Does SP works? Directly run SP from QA. If it works

Step 2. Does update code works? Directly run update code,
e.g. use a test program and hard code assign values to
parameters. If it works

Step 3. In UpdateCommand event the parameters are assigned
correct values? Set breakpoint in the event and trace the
running process to see if all things are correct.

I think you should be able to find out error.

HTH

Elton Wang

-----Original Message-----
OK, So I am doing everything correct, so why is it not working?
"Elton Wang" wrote:

That's right. And you also need

If Not IsPostBack Then
' Bind datagrid
End If

>-----Original Message-----
>Hi Elton
>Thanks for your interest.
>
>I only bind in the Page_Load event.
>
>Hennie
>
>"Elton Wang" wrote:
>
>> Hi Hennie,
>>
>> Do you bind datasource of datagrid every time, no

matter

>> it is in the first time or in postback. If that, change

it

>> to only bind in IsPostback = false.
>>
>> HTH
>>
>> Elton Wang
>> el********@hotm ail.com
>>
>>
>> >-----Original Message-----
>> >When trying to update a record in an editable

datagrid I

>> ran into a few
>> >problems.
>> >My update procedure is just not working.
>> >Can someone please have a look at my code and see

what am

>> I doing wrong.
>> >
>> >I had to set the datagrid's DataKeyField to
>> MailSubscriberI D manually since
>> >vs does not do it automatically.
>> >
>> >Thanks
>> >
>> >This is what my table looks like in SQL 2000:
>> >CREATE TABLE [dbo].[tabMailSubscrib ers] (
>> > [MailSubscriberI D] uniqueidentifie r ROWGUIDCOL
>> NOT NULL ,
>> > [HotelID] [int] NOT NULL ,
>> > [Name] [varchar] (50) COLLATE Latin1_General_ CI_AS
>> NOT NULL ,
>> > [Surname] [varchar] (50) COLLATE
>> Latin1_General_ CI_AS NOT NULL ,
>> > [Designation] [varchar] (50) COLLATE
>> Latin1_General_ CI_AS NOT NULL ,
>> > [EmailAddress] [varchar] (50) COLLATE
>> Latin1_General_ CI_AS NOT NULL ,
>> > [UpdatedBy] [int] NOT NULL ,
>> > [DateUpdated] [datetime] NOT NULL
>> >) ON [PRIMARY]
>> >GO
>> >
>> >This is the update procedure:
>> >
>> >CREATE PROCEDURE dbo.proc_tabMai lSubscibers_Upd ate
>> >(
>> > @MailSubscriber ID uniqueidentifie r,
>> > @HotelID int,
>> > @Name varchar(50),
>> > @Surname varchar(50),
>> > @Designation varchar(50),
>> > @EmailAddress varchar(50),
>> > @UpdatedBy int
>> >)
>> >AS
>> > SET NOCOUNT OFF;
>> >UPDATE tabMailSubscrib ers SET HotelID = @HotelID,

Name =

>> @Name, Surname =
>> >@Surname, Designation = @Designation, EmailAddress

= >> @EmailAddress, UpdatedBy
>> >= @UpdatedBy, DateUpdated = (GetDate()) WHERE
>> (MailSubscriber ID =
>> >@MailSubscribe rID) ;
>> > SELECT MailSubscriberI D, HotelID, Name, Surname,
>> Designation, EmailAddress,
>> >UpdatedBy, DateUpdated FROM tabMailSubscrib ers WHERE >> (MailSubscriber ID =
>> >@MailSubscribe rID)
>> >GO
>> >
>> >This is my code to update the record:
>> >
>> > Private Sub DGMailSubscribe rs_UpdateComman d (ByVal >> source As Object,
>> >ByVal e As
>> System.Web.UI.W ebControls.Data GridCommandEven tArgs)
>> Handles
>> >DGMailSubscrib ers.UpdateComma nd
>> > CType(e.Item.Ce lls(8).Controls (0),

TextBox).Text

>> = _
>> > CType(e.Item.Fi ndControl("ddlH otelID"),
>> DropDownList).S electedValue
>> >
>> > ' Step two: Fill DataSet and identify row to edit

>> >
>> > SqlDAMailSubscr ibers.Fill

(DsMailSubscrib ers1) >> > DAHotel.Fill(Ds Hotel1)
>> > 'DGMailSubscrib ers.DataBind()
>> > Dim objEditRow As DataRow =
>> >DsMailSubscrib ers1.Tables(0). Rows.Find(CType

(e.Item.Cells

>> (0).Controls(0) ,
>> >TextBox).Tex t)
>> > ' Step three: Cycle through valid "data" cells >> and put
>> > ' information back in underlying >> DataSet
>> >
>> > SqlConn.Open()
>> > Dim cnn As SqlClient.SqlCo nnection = SqlConn
>> > Dim cmdUpdate As New SqlClient.SqlCo mmand
>> > cmdUpdate = cnn.CreateComma nd
>> > cmdUpdate.Comma ndText
>> = "proc_tabMailSu bscribers_Updat e"
>> > cmdUpdate.Comma ndType =
>> CommandType.Sto redProcedure
>> > cmdUpdate.Param eters.Add ("@MailSubscrib erID", >> >SqlDbType.Uniq ueIdentifier)
>> > cmdUpdate.Param eters.Add("@Hot elID",
>> SqlDbType.Int)
>> > cmdUpdate.Param eters.Add("@Nam e",
>> SqlDbType.VarCh ar)
>> > cmdUpdate.Param eters.Add("@Sur name",
>> SqlDbType.VarCh ar)
>> > cmdUpdate.Param eters.Add("@Des ignation",
>> SqlDbType.VarCh ar)
>> > cmdUpdate.Param eters.Add("@Ema ilAddress",
>> SqlDbType.VarCh ar)
>> > cmdUpdate.Param eters.Add("@Upd atedBy",
>> SqlDbType.Int)
>> >
>> > 'Update
>> > cmdUpdate.Param eters

("@MailSubscrib erID").Value =

>> >DGMailSubscrib ers.DataKeys(e. Item.ItemIndex)
>> > cmdUpdate.Param eters("@HotelID ").Value =
>> >CType(e.Item.F indControl("ddl HotelID"),
>> DropDownList).S electedItem.Val ue()
>> > cmdUpdate.Param eters("@Name"). Value =
>> >CType(e.Item.C ells(0).Control s(0), TextBox).Text
>> > cmdUpdate.Param eters("@Surname ").Value =
>> >CType(e.Item.C ells(1).Control s(0), TextBox).Text
>> > cmdUpdate.Param eters("@Designa tion").Value = >> >CType(e.Item.C ells(2).Control s(0), TextBox).Text
>> > cmdUpdate.Param eters("@EmailAd dress").Value = >> >CType(e.Item.C ells(3).Control s(0), TextBox).Text
>> > cmdUpdate.Param eters("@Updated By").Value =

Session

>> ("LoginID")
>> >
>> > Try
>> > cmdUpdate.Execu teNonQuery()
>> > Message.InnerHt ml = "<b>Record
>> Updated.</b><br>"
>> >
>> > 'DGMailSubscrib ers.EditItemInd ex = -5
>> > Catch ex As SqlException
>> > Message.InnerHt ml = "ERROR: Could not

update

>> record," _
>> > & " please ensure the fields are correctly

filled out."

>> > Message.Style(" color") = "red"
>> >
>> > End Try
>> >.
>> >
>>
>.
>

.