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

G

Guest

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 MailSubscriberID manually since
vs does not do it automatically.

Thanks

This is what my table looks like in SQL 2000:
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier 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_tabMailSubscibers_Update
(
@MailSubscriberID uniqueidentifier,
@HotelID int,
@Name varchar(50),
@Surname varchar(50),
@Designation varchar(50),
@EmailAddress varchar(50),
@UpdatedBy int
)
AS
SET NOCOUNT OFF;
UPDATE tabMailSubscribers SET HotelID = @HotelID, Name = @Name, Surname =
@Surname, Designation = @Designation, EmailAddress = @EmailAddress, UpdatedBy
= @UpdatedBy, DateUpdated = (GetDate()) WHERE (MailSubscriberID =
@MailSubscriberID) ;
SELECT MailSubscriberID, HotelID, Name, Surname, Designation, EmailAddress,
UpdatedBy, DateUpdated FROM tabMailSubscribers WHERE (MailSubscriberID =
@MailSubscriberID)
GO

This is my code to update the record:

Private Sub DGMailSubscribers_UpdateCommand(ByVal source As Object,
ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DGMailSubscribers.UpdateCommand
CType(e.Item.Cells(8).Controls(0), TextBox).Text = _
CType(e.Item.FindControl("ddlHotelID"), DropDownList).SelectedValue

' Step two: Fill DataSet and identify row to edit

SqlDAMailSubscribers.Fill(DsMailSubscribers1)
DAHotel.Fill(DsHotel1)
'DGMailSubscribers.DataBind()
Dim objEditRow As DataRow =
DsMailSubscribers1.Tables(0).Rows.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.SqlConnection = SqlConn
Dim cmdUpdate As New SqlClient.SqlCommand
cmdUpdate = cnn.CreateCommand
cmdUpdate.CommandText = "proc_tabMailSubscribers_Update"
cmdUpdate.CommandType = CommandType.StoredProcedure
cmdUpdate.Parameters.Add("@MailSubscriberID",
SqlDbType.UniqueIdentifier)
cmdUpdate.Parameters.Add("@HotelID", SqlDbType.Int)
cmdUpdate.Parameters.Add("@Name", SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@Surname", SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@Designation", SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@EmailAddress", SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@UpdatedBy", SqlDbType.Int)

'Update
cmdUpdate.Parameters("@MailSubscriberID").Value =
DGMailSubscribers.DataKeys(e.Item.ItemIndex)
cmdUpdate.Parameters("@HotelID").Value =
CType(e.Item.FindControl("ddlHotelID"), DropDownList).SelectedItem.Value()
cmdUpdate.Parameters("@Name").Value =
CType(e.Item.Cells(0).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Surname").Value =
CType(e.Item.Cells(1).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Designation").Value =
CType(e.Item.Cells(2).Controls(0), TextBox).Text
cmdUpdate.Parameters("@EmailAddress").Value =
CType(e.Item.Cells(3).Controls(0), TextBox).Text
cmdUpdate.Parameters("@UpdatedBy").Value = Session("LoginID")

Try
cmdUpdate.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Updated.</b><br>"

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

End Try
 
E

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
(e-mail address removed)

-----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
Click to expand...
MailSubscriberID manually since
vs does not do it automatically.

Thanks

This is what my table looks like in SQL 2000:
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE
Click to expand...
Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE
Click to expand...
Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE
Click to expand...
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_tabMailSubscibers_Update
(
@MailSubscriberID uniqueidentifier,
@HotelID int,
@Name varchar(50),
@Surname varchar(50),
@Designation varchar(50),
@EmailAddress varchar(50),
@UpdatedBy int
)
AS
SET NOCOUNT OFF;
UPDATE tabMailSubscribers SET HotelID = @HotelID, Name = @Name, Surname =
@Surname, Designation = @Designation, EmailAddress = @EmailAddress, UpdatedBy
= @UpdatedBy, DateUpdated = (GetDate()) WHERE (MailSubscriberID =
@MailSubscriberID) ;
SELECT MailSubscriberID, HotelID, Name, Surname, Designation, EmailAddress,
UpdatedBy, DateUpdated FROM tabMailSubscribers WHERE (MailSubscriberID =
@MailSubscriberID)
GO

This is my code to update the record:

Private Sub DGMailSubscribers_UpdateCommand(ByVal source As Object,
ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
Handles
DGMailSubscribers.UpdateCommand
CType(e.Item.Cells(8).Controls(0), TextBox).Text = _
CType(e.Item.FindControl("ddlHotelID"), DropDownList).SelectedValue

' Step two: Fill DataSet and identify row to edit

SqlDAMailSubscribers.Fill(DsMailSubscribers1)
DAHotel.Fill(DsHotel1)
'DGMailSubscribers.DataBind()
Dim objEditRow As DataRow =
DsMailSubscribers1.Tables(0).Rows.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.SqlConnection = SqlConn
Dim cmdUpdate As New SqlClient.SqlCommand
cmdUpdate = cnn.CreateCommand
cmdUpdate.CommandText
Click to expand...
= "proc_tabMailSubscribers_Update"
 
G

Guest

Hi Elton
Thanks for your interest.

I only bind in the Page_Load event.

Hennie

Elton Wang said:
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
(e-mail address removed)

-----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
Click to expand...
MailSubscriberID manually since
vs does not do it automatically.

Thanks

This is what my table looks like in SQL 2000:
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE
Click to expand...
Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE
Click to expand...
Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE
Click to expand...
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_tabMailSubscibers_Update
(
@MailSubscriberID uniqueidentifier,
@HotelID int,
@Name varchar(50),
@Surname varchar(50),
@Designation varchar(50),
@EmailAddress varchar(50),
@UpdatedBy int
)
AS
SET NOCOUNT OFF;
UPDATE tabMailSubscribers SET HotelID = @HotelID, Name = @Name, Surname =
@Surname, Designation = @Designation, EmailAddress = @EmailAddress, UpdatedBy
= @UpdatedBy, DateUpdated = (GetDate()) WHERE (MailSubscriberID =
@MailSubscriberID) ;
SELECT MailSubscriberID, HotelID, Name, Surname, Designation, EmailAddress,
UpdatedBy, DateUpdated FROM tabMailSubscribers WHERE (MailSubscriberID =
@MailSubscriberID)
GO

This is my code to update the record:

Private Sub DGMailSubscribers_UpdateCommand(ByVal source As Object,
ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
Handles
DGMailSubscribers.UpdateCommand
CType(e.Item.Cells(8).Controls(0), TextBox).Text = _
CType(e.Item.FindControl("ddlHotelID"), DropDownList).SelectedValue

' Step two: Fill DataSet and identify row to edit

SqlDAMailSubscribers.Fill(DsMailSubscribers1)
DAHotel.Fill(DsHotel1)
'DGMailSubscribers.DataBind()
Dim objEditRow As DataRow =
DsMailSubscribers1.Tables(0).Rows.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.SqlConnection = SqlConn
Dim cmdUpdate As New SqlClient.SqlCommand
cmdUpdate = cnn.CreateCommand
cmdUpdate.CommandText
Click to expand...
= "proc_tabMailSubscribers_Update"
cmdUpdate.CommandType = CommandType.StoredProcedure
SqlDbType.Int)

'Update
cmdUpdate.Parameters("@MailSubscriberID").Value =
DGMailSubscribers.DataKeys(e.Item.ItemIndex)
cmdUpdate.Parameters("@HotelID").Value =
CType(e.Item.FindControl("ddlHotelID"), DropDownList).SelectedItem.Value()
cmdUpdate.Parameters("@Name").Value =
CType(e.Item.Cells(0).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Surname").Value =
CType(e.Item.Cells(1).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Designation").Value =
CType(e.Item.Cells(2).Controls(0), TextBox).Text
cmdUpdate.Parameters("@EmailAddress").Value =
CType(e.Item.Cells(3).Controls(0), TextBox).Text
cmdUpdate.Parameters("@UpdatedBy").Value = Session ("LoginID")

Try
cmdUpdate.ExecuteNonQuery()
Message.InnerHtml = "<b>Record
Click to expand...
Updated. said:
'DGMailSubscribers.EditItemIndex = -5
Catch ex As SqlException
Message.InnerHtml = "ERROR: Could not update record," _
& " please ensure the fields are correctly filled out."
Message.Style("color") = "red"

End Try
.
Click to expand...
Click to expand...
 
E

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 said:
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
(e-mail address removed)

-----Original Message-----
When trying to update a record in an editable datagrid
Click to expand...
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
Click to expand...
am
I doing wrong.
I had to set the datagrid's DataKeyField to
Click to expand...
MailSubscriberID manually since
vs does not do it automatically.

Thanks

This is what my table looks like in SQL 2000:
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE
Click to expand...
Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE
Click to expand...
Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE
Click to expand...
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_tabMailSubscibers_Update
(
@MailSubscriberID uniqueidentifier,
@HotelID int,
@Name varchar(50),
@Surname varchar(50),
@Designation varchar(50),
@EmailAddress varchar(50),
@UpdatedBy int
)
AS
SET NOCOUNT OFF;
UPDATE tabMailSubscribers SET HotelID = @HotelID, Name
Click to expand...
=
@Name, Surname =
@Surname, Designation = @Designation, EmailAddress = @EmailAddress, UpdatedBy
= @UpdatedBy, DateUpdated = (GetDate()) WHERE (MailSubscriberID =
@MailSubscriberID) ;
SELECT MailSubscriberID, HotelID, Name, Surname, Designation, EmailAddress,
UpdatedBy, DateUpdated FROM tabMailSubscribers WHERE (MailSubscriberID =
@MailSubscriberID)
GO

This is my code to update the record:

Private Sub DGMailSubscribers_UpdateCommand(ByVal source As Object,
ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
Handles
DGMailSubscribers.UpdateCommand
CType(e.Item.Cells(8).Controls(0),
Click to expand...
TextBox).Text
= _
CType(e.Item.FindControl("ddlHotelID"), DropDownList).SelectedValue

' Step two: Fill DataSet and identify row to edit

SqlDAMailSubscribers.Fill(DsMailSubscribers1)
DAHotel.Fill(DsHotel1)
'DGMailSubscribers.DataBind()
Dim objEditRow As DataRow =
DsMailSubscribers1.Tables(0).Rows.Find(CType
Click to expand...
(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.SqlConnection = SqlConn
Dim cmdUpdate As New SqlClient.SqlCommand
cmdUpdate = cnn.CreateCommand
cmdUpdate.CommandText
Click to expand...
= "proc_tabMailSubscribers_Update"
cmdUpdate.CommandType = CommandType.StoredProcedure
SqlDbType.Int)

'Update
cmdUpdate.Parameters ("@MailSubscriberID").Value =
DGMailSubscribers.DataKeys(e.Item.ItemIndex)
cmdUpdate.Parameters("@HotelID").Value =
CType(e.Item.FindControl("ddlHotelID"), DropDownList).SelectedItem.Value()
cmdUpdate.Parameters("@Name").Value =
CType(e.Item.Cells(0).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Surname").Value =
CType(e.Item.Cells(1).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Designation").Value =
CType(e.Item.Cells(2).Controls(0), TextBox).Text
cmdUpdate.Parameters("@EmailAddress").Value =
CType(e.Item.Cells(3).Controls(0), TextBox).Text
cmdUpdate.Parameters("@UpdatedBy").Value =
Click to expand...
Session
("LoginID")
Try
cmdUpdate.ExecuteNonQuery()
Message.InnerHtml = "<b>Record
Click to expand...
Updated. said:
'DGMailSubscribers.EditItemIndex = -5
Catch ex As SqlException
Message.InnerHtml = "ERROR: Could not
Click to expand...
update
record," _
& " please ensure the fields are correctly filled out."
Message.Style("color") = "red"

End Try
.
Click to expand...
Click to expand...
.
Click to expand...
 
G

Guest

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

Elton Wang said:
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 said:
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
(e-mail address removed)


-----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
MailSubscriberID manually since
vs does not do it automatically.

Thanks

This is what my table looks like in SQL 2000:
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier 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_tabMailSubscibers_Update
(
@MailSubscriberID uniqueidentifier,
@HotelID int,
@Name varchar(50),
@Surname varchar(50),
@Designation varchar(50),
@EmailAddress varchar(50),
@UpdatedBy int
)
AS
SET NOCOUNT OFF;
UPDATE tabMailSubscribers SET HotelID = @HotelID, Name =
@Name, Surname =
@Surname, Designation = @Designation, EmailAddress =
@EmailAddress, UpdatedBy
= @UpdatedBy, DateUpdated = (GetDate()) WHERE
(MailSubscriberID =
@MailSubscriberID) ;
SELECT MailSubscriberID, HotelID, Name, Surname,
Designation, EmailAddress,
UpdatedBy, DateUpdated FROM tabMailSubscribers WHERE
(MailSubscriberID =
@MailSubscriberID)
GO

This is my code to update the record:

Private Sub DGMailSubscribers_UpdateCommand(ByVal
source As Object,
ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs)
Handles
DGMailSubscribers.UpdateCommand
CType(e.Item.Cells(8).Controls(0), TextBox).Text
= _
CType(e.Item.FindControl("ddlHotelID"),
DropDownList).SelectedValue

' Step two: Fill DataSet and identify row to edit

SqlDAMailSubscribers.Fill(DsMailSubscribers1)
DAHotel.Fill(DsHotel1)
'DGMailSubscribers.DataBind()
Dim objEditRow As DataRow =
DsMailSubscribers1.Tables(0).Rows.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.SqlConnection = SqlConn
Dim cmdUpdate As New SqlClient.SqlCommand
cmdUpdate = cnn.CreateCommand
cmdUpdate.CommandText
= "proc_tabMailSubscribers_Update"
cmdUpdate.CommandType =
CommandType.StoredProcedure
cmdUpdate.Parameters.Add("@MailSubscriberID",
SqlDbType.UniqueIdentifier)
cmdUpdate.Parameters.Add("@HotelID",
SqlDbType.Int)
cmdUpdate.Parameters.Add("@Name",
SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@Surname",
SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@Designation",
SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@EmailAddress",
SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@UpdatedBy",
SqlDbType.Int)

'Update
cmdUpdate.Parameters ("@MailSubscriberID").Value =
DGMailSubscribers.DataKeys(e.Item.ItemIndex)
cmdUpdate.Parameters("@HotelID").Value =
CType(e.Item.FindControl("ddlHotelID"),
DropDownList).SelectedItem.Value()
cmdUpdate.Parameters("@Name").Value =
CType(e.Item.Cells(0).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Surname").Value =
CType(e.Item.Cells(1).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Designation").Value =
CType(e.Item.Cells(2).Controls(0), TextBox).Text
cmdUpdate.Parameters("@EmailAddress").Value =
CType(e.Item.Cells(3).Controls(0), TextBox).Text
cmdUpdate.Parameters("@UpdatedBy").Value = Session
("LoginID")

Try
cmdUpdate.ExecuteNonQuery()
Message.InnerHtml = "<b>Record
Updated.</b><br>"

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

End Try
.
Click to expand...
.
Click to expand...
Click to expand...
 
E

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 said:
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

:

Hi Hennie,

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

HTH

Elton Wang
(e-mail address removed)


-----Original Message-----
When trying to update a record in an editable
Click to expand...
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
Click to expand...
what
am
I doing wrong.

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

Thanks

This is what my table looks like in SQL 2000:
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier 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_tabMailSubscibers_Update
(
@MailSubscriberID uniqueidentifier,
@HotelID int,
@Name varchar(50),
@Surname varchar(50),
@Designation varchar(50),
@EmailAddress varchar(50),
@UpdatedBy int
)
AS
SET NOCOUNT OFF;
UPDATE tabMailSubscribers SET HotelID = @HotelID,
Click to expand...
Name
=
@Name, Surname =
@Surname, Designation = @Designation, EmailAddress =
@EmailAddress, UpdatedBy
= @UpdatedBy, DateUpdated = (GetDate()) WHERE
(MailSubscriberID =
@MailSubscriberID) ;
SELECT MailSubscriberID, HotelID, Name, Surname,
Designation, EmailAddress,
UpdatedBy, DateUpdated FROM tabMailSubscribers WHERE
(MailSubscriberID =
@MailSubscriberID)
GO

This is my code to update the record:

Private Sub DGMailSubscribers_UpdateCommand (ByVal
source As Object,
ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs)
Handles
DGMailSubscribers.UpdateCommand
CType(e.Item.Cells(8).Controls(0), TextBox).Text
= _
CType(e.Item.FindControl("ddlHotelID"),
DropDownList).SelectedValue

' Step two: Fill DataSet and identify row
Click to expand...
to
edit
SqlDAMailSubscribers.Fill (DsMailSubscribers1)
DAHotel.Fill(DsHotel1)
'DGMailSubscribers.DataBind()
Dim objEditRow As DataRow =
DsMailSubscribers1.Tables(0).Rows.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.SqlConnection = SqlConn
Dim cmdUpdate As New SqlClient.SqlCommand
cmdUpdate = cnn.CreateCommand
cmdUpdate.CommandText
= "proc_tabMailSubscribers_Update"
cmdUpdate.CommandType =
CommandType.StoredProcedure
cmdUpdate.Parameters.Add ("@MailSubscriberID",
SqlDbType.UniqueIdentifier)
cmdUpdate.Parameters.Add("@HotelID",
SqlDbType.Int)
cmdUpdate.Parameters.Add("@Name",
SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@Surname",
SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@Designation",
SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@EmailAddress",
SqlDbType.VarChar)
cmdUpdate.Parameters.Add("@UpdatedBy",
SqlDbType.Int)

'Update
cmdUpdate.Parameters ("@MailSubscriberID").Value =
DGMailSubscribers.DataKeys(e.Item.ItemIndex)
cmdUpdate.Parameters("@HotelID").Value =
CType(e.Item.FindControl("ddlHotelID"),
DropDownList).SelectedItem.Value()
cmdUpdate.Parameters("@Name").Value =
CType(e.Item.Cells(0).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Surname").Value =
CType(e.Item.Cells(1).Controls(0), TextBox).Text
cmdUpdate.Parameters("@Designation").Value =
CType(e.Item.Cells(2).Controls(0), TextBox).Text
cmdUpdate.Parameters("@EmailAddress").Value =
CType(e.Item.Cells(3).Controls(0), TextBox).Text
cmdUpdate.Parameters("@UpdatedBy").Value = Session
("LoginID")

Try
cmdUpdate.ExecuteNonQuery()
Message.InnerHtml = "<b>Record
Updated.</b><br>"

'DGMailSubscribers.EditItemIndex = -5
Catch ex As SqlException
Message.InnerHtml = "ERROR: Could not update
record," _
& " please ensure the fields are correctly
Click to expand...
filled
out."
Message.Style("color") = "red"

End Try
.


.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Primary Key in DataGrid 2
ASP1 DataGrid Cannot Update /Read entered cell value. 0
DataGrid Edit/Update problem 3
Can't get datagrid to Update record using UpdateCommand 3
is it so tedious to update a record 4
Update and Insert problems in ListView 1
Completely unable to update an access db from a datagrid 4
UPDATE Command problem 0

Members online

No members online now.
Total: 25 (members: 2, guests: 23)
Robots: 183

Forum statistics

Threads
473,756
Messages
2,569,535
Members
45,008
Latest member
obedient dusk

Latest Threads

Top