Using datagrid in ASP.NET to get table from SQL SERVER-Not updating

Hi Everyone!

I have a ASP.NET webform that brings data in from SQL Server 2000 and displays it on a page. I want to update the webpage while in IE using the following code:

Expand|Select|Wrap|Line Numbers

 
<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.Data.OleDb" %>

<html>

<script language="VB" runat="server">

Dim MyConnection As SqlConnection
 
Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
 
MyConnection = New SqlConnection("Data Source=SQLSERVER;Initial Catalog=upsizedCandidate;Integrated Security=True")
 
If Not (IsPostBack) Then

BindGrid()

End If
 
End Sub
 
Sub MyDataGrid_Edit(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
 
MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)

BindGrid()

End Sub
 
Sub MyDataGrid_Cancel(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
 
MyDataGrid.EditItemIndex = -1

BindGrid()

End Sub
 
Sub MyDataGrid_Update(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
 
Dim DS As DataSet

Dim MyCommand As SqlCommand
 
Dim UpdateCmd As String = "UPDATE ToddsTable1 SET myNewCol1 = @myNewCol1, myNewCol2 = @myNewCol2"
 
MyCommand = New SqlCommand(UpdateCmd, MyConnection)
 
MyCommand.Parameters.Add(New SqlParameter("@myNewCol1", SqlDbType.NVarChar, 40))

MyCommand.Parameters.Add(New SqlParameter("@myNewCol2", SqlDbType.NVarChar, 20))
 
MyCommand.Parameters("@Id").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
 
Dim Cols As String() = {"@myNewCol1", "@myNewCol2"}
 
Dim NumCols As Integer = E.Item.Cells.Count
 
Dim I As Integer

For I = 2 To NumCols - 2 'skip first, second and last column
 
Dim CurrentTextBox As TextBox

CurrentTextBox = E.Item.Cells(I).Controls(0)

Dim ColValue As String = CurrentTextBox.Text
 
MyCommand.Parameters(Cols(I - 1)).Value = Server.HtmlEncode(ColValue)

Next
 
MyCommand.Connection.Open()
 
Try

MyCommand.ExecuteNonQuery()

Message.InnerHtml = "<b>Record Updated</b><br>" & UpdateCmd.ToString()

MyDataGrid.EditItemIndex = -1

Catch Exp As SQLException

If Exp.Number = 2627 Then

Message.InnerHtml = "ERROR: A record already exists with the same primary key"

Else

Message.InnerHtml = "ERROR: Could not update record, please ensure the fields are correctly filled out"

End If

Message.Style("color") = "red"

End Try
 
MyCommand.Connection.Close()
 
BindGrid()

End Sub
 
Sub MyDataGrid_ItemDataBound(ByVal Sender As Object, ByVal E As DataGridItemEventArgs)

If (E.Item.ItemType = ListItemType.EditItem) Then

Dim i As Integer

For i = 0 To E.Item.Controls.Count - 1

If (E.Item.Controls(i).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.TextBox") Then

Dim tb As TextBox

tb = E.Item.Controls(i).Controls(0)

tb.Text = Server.HtmlDecode(tb.Text)

End If

Next

End If

End Sub
 
Sub BindGrid()
 
Dim DS As DataSet

Dim MyCommand As SqlDataAdapter

MyCommand = New SqlDataAdapter("select * from ToddsTable1", MyConnection)
 
DS = New DataSet()

MyCommand.Fill(DS, "ToddsTable1")
 
MyDataGrid.DataSource = DS.Tables("ToddsTable1").DefaultView

MyDataGrid.DataBind()
 
End Sub
 
</script>
 
<body style="font: 10pt verdana">
 
<form id="Form1" runat="server">
 
<h3><font face="Verdana">Updating ToddsTable1</font></h3>
 
<span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/><p>
 
<ASP:DataGrid id="MyDataGrid" runat="server"

Width="700"

BackColor="#ccccff"

BorderColor="black"

ShowFooter="false"

CellPadding=3

CellSpacing="0"

Font-Name="Verdana"

Font-Size="8pt"

HeaderStyle-BackColor="#aaaadd"

OnEditCommand="MyDataGrid_Edit"

OnCancelCommand="MyDataGrid_Cancel"

OnUpdateCommand="MyDataGrid_Update"

DataKeyField="myNewCol1"

OnItemDataBound="MyDataGrid_ItemDataBound"

EnableViewState="false"

>
 
<Columns>

<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" ItemStyle-Wrap="false"/>

</Columns>
 
</ASP:DataGrid>
 
</form>

</body>

</html>

The table is only 2 columns just to get something working but when I click the edit link my title still shows up but the table disappears. I've also tried to take out the IsPostBack statement from the Page_Load method and the text boxes show up for editting but when "update" is clicked the data returns back to it's original data. Any thoughts on what I could do differently? Anything will be appreciated just to help me think differently. Thanks!

Apr 12 '07 #1

Follow Post Reply

4249

toddw607

Ok, I switched my code as follows:

Expand|Select|Wrap|Line Numbers

 
<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<html>

<head>

<script runat="server">

Public Sub Page_Load(Source As Object, E As EventArgs)

If Not Page.IsPostBack Then

BindData()

End If

End Sub
 
Public Sub DataGrid_Edit(ByVal Source As Object, _

ByVal E As DataGridCommandEventArgs)

myInfo.EditItemIndex = E.Item.ItemIndex

BindData()

End Sub
 
Public Sub DataGrid_Cancel(ByVal Source As Object, _

ByVal E As DataGridCommandEventArgs)

myInfo.EditItemIndex = -1

BindData()

End Sub
 
Public Sub DataGrid_Update(ByVal Source As Object, _

ByVal E As DataGridCommandEventArgs)

Dim myConnection As SqlConnection

Dim myCommand As SqlCommand

Dim txtmyNewCol1 As TextBox = E.Item.Cells(1).Controls(0)

Dim txtmyNewCol2 As TextBox = E.Item.Cells(2).Controls(0)

Dim strUpdateStmt As String
 
strUpdateStmt = "UPDATE ToddsTable1 SET " & _

"myNewCol1 = '" & txtmyNewCol1.Text & "', " & _

"myNewcol2 = '" & txtmyNewCol2.Text & "' "
 
myConnection = New SqlConnection( _

"server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")

myCommand = New SqlCommand(strUpdateStmt, myConnection)

myConnection.Open()

myCommand.ExecuteNonQuery()
 
myInfo.EditItemIndex = -1

BindData()

End Sub
 
Public Sub BindData()

Dim myDataSet As New DataSet

Dim mySqlDataAdapter As SqlDataAdapter

mySqlDataAdapter = New SqlDataAdapter( _

"SELECT * FROM ToddsTable1", _

"server=SQLSERVER;database=upsizedCandidate;Integrated Security=True")

mySqlDataAdapter.Fill(myDataSet, "ToddsTable1")

myInfo.DataSource = myDataSet.Tables("ToddsTable1")

myInfo.DataBind()

End Sub

</script>

</head>

<body>

<form id="Form1" runat="server" method="post">

<H3>Editing ToddsTable1 from SQL Server</H3>
 
<asp:DataGrid id="myInfo" runat="server"

AutoGenerateColumns="False"

OnEditCommand="DataGrid_Edit" Width="702px"

OnCancelCommand="DataGrid_Cancel"

OnUpdateCommand="DataGrid_Update">
 
<Columns>

<asp:EditCommandColumn

CancelText="Cancel"

EditText="Edit"

UpdateText="Update" />

<asp:BoundColumn

DataField="myNewCol1"

HeaderText="myNewCol1" />

<asp:BoundColumn

DataField="myNewCol2"

HeaderText="myNewCol2" />

</Columns>

</asp:DataGrid>
 
</form>

</body>

</html>

Everything works fine except when I click"Edit" then edit the text and click "update" it changes every cell in the column to the value I entered in the edited column. Any thoughts on how I could fix this?

Apr 16 '07 #2

vakashchaudhary04

Hi,
I am Akash,
i am facing a problem while using datagrid control in vb.net, c#.net and ofcourse in vb and asp also while updating a data viewed in datagrid control at runtime by using buttons like edit, update or delete buttons. Can you plz send me proper used step by step code to edit or update a viewed data by using datagrid in all above language specially in vb.net and c#.net.

Ok, I switched my code as follows:

Expand|Select|Wrap|Line Numbers

<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<html>

<head>

<script runat="server">

Public Sub Page_Load(Source As Object, E As EventArgs)

If Not Page.IsPostBack Then

BindData()

End If

End Sub

Public Sub DataGrid_Edit(ByVal Source As Object, _

ByVal E As DataGridCommandEventArgs)

myInfo.EditItemIndex = E.Item.ItemIndex

BindData()

End Sub

Public Sub DataGrid_Cancel(ByVal Source As Object, _

ByVal E As DataGridCommandEventArgs)

myInfo.EditItemIndex = -1

BindData()

End Sub

Public Sub DataGrid_Update(ByVal Source As Object, _

ByVal E As DataGridCommandEventArgs)

Dim myConnection As SqlConnection

Dim myCommand As SqlCommand

Dim txtmyNewCol1 As TextBox = E.Item.Cells(1).Controls(0)

Dim txtmyNewCol2 As TextBox = E.Item.Cells(2).Controls(0)

Dim strUpdateStmt As String

strUpdateStmt = "UPDATE ToddsTable1 SET " & _

"myNewCol1 = '" & txtmyNewCol1.Text & "', " & _

"myNewcol2 = '" & txtmyNewCol2.Text & "' "

myConnection = New SqlConnection( _

"server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")

myCommand = New SqlCommand(strUpdateStmt, myConnection)

myConnection.Open()

myCommand.ExecuteNonQuery()

myInfo.EditItemIndex = -1

BindData()

End Sub

Public Sub BindData()

Dim myDataSet As New DataSet

Dim mySqlDataAdapter As SqlDataAdapter

mySqlDataAdapter = New SqlDataAdapter( _

"SELECT * FROM ToddsTable1", _

"server=SQLSERVER;database=upsizedCandidate;Integrated Security=True")

mySqlDataAdapter.Fill(myDataSet, "ToddsTable1")

myInfo.DataSource = myDataSet.Tables("ToddsTable1")

myInfo.DataBind()

End Sub

</script>

</head>

<body>

<form id="Form1" runat="server" method="post">

<H3>Editing ToddsTable1 from SQL Server</H3>

<asp:DataGrid id="myInfo" runat="server"

AutoGenerateColumns="False"

OnEditCommand="DataGrid_Edit" Width="702px"

OnCancelCommand="DataGrid_Cancel"

OnUpdateCommand="DataGrid_Update">

<Columns>

<asp:EditCommandColumn

CancelText="Cancel"

EditText="Edit"

UpdateText="Update" />

<asp:BoundColumn

DataField="myNewCol1"

HeaderText="myNewCol1" />

<asp:BoundColumn

DataField="myNewCol2"

HeaderText="myNewCol2" />

</Columns>

</asp:DataGrid>

</form>

</body>

</html>

Everything works fine except when I click"Edit" then edit the text and click "update" it changes every cell in the column to the value I entered in the edited column. Any thoughts on how I could fix this?

Jun 27 '07 #3

Using datagrid in ASP.NET to get table from SQL SERVER-Not updating

Post your reply

Similar topics