469,927 Members | 1,946 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

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
  1. <%@ Import Namespace="System.Data" %>
  2. <%@ Import Namespace="System.Data.SqlClient" %>
  3. <%@ Import Namespace="System.Data.OleDb" %>
  4. <html>
  5. <script language="VB" runat="server">
  6. Dim MyConnection As SqlConnection
  7.  
  8. Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
  9.  
  10. MyConnection = New SqlConnection("Data Source=SQLSERVER;Initial Catalog=upsizedCandidate;Integrated Security=True")
  11.  
  12. If Not (IsPostBack) Then
  13. BindGrid()
  14. End If
  15.  
  16. End Sub
  17.  
  18. Sub MyDataGrid_Edit(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
  19.  
  20. MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
  21. BindGrid()
  22. End Sub
  23.  
  24. Sub MyDataGrid_Cancel(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
  25.  
  26. MyDataGrid.EditItemIndex = -1
  27. BindGrid()
  28. End Sub
  29.  
  30. Sub MyDataGrid_Update(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
  31.  
  32. Dim DS As DataSet
  33. Dim MyCommand As SqlCommand
  34.  
  35. Dim UpdateCmd As String = "UPDATE ToddsTable1 SET myNewCol1 = @myNewCol1, myNewCol2 = @myNewCol2"
  36.  
  37. MyCommand = New SqlCommand(UpdateCmd, MyConnection)
  38.  
  39. MyCommand.Parameters.Add(New SqlParameter("@myNewCol1", SqlDbType.NVarChar, 40))
  40. MyCommand.Parameters.Add(New SqlParameter("@myNewCol2", SqlDbType.NVarChar, 20))
  41.  
  42. MyCommand.Parameters("@Id").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
  43.  
  44. Dim Cols As String() = {"@myNewCol1", "@myNewCol2"}
  45.  
  46. Dim NumCols As Integer = E.Item.Cells.Count
  47.  
  48. Dim I As Integer
  49. For I = 2 To NumCols - 2 'skip first, second and last column
  50.  
  51. Dim CurrentTextBox As TextBox
  52. CurrentTextBox = E.Item.Cells(I).Controls(0)
  53. Dim ColValue As String = CurrentTextBox.Text
  54.  
  55. MyCommand.Parameters(Cols(I - 1)).Value = Server.HtmlEncode(ColValue)
  56. Next
  57.  
  58. MyCommand.Connection.Open()
  59.  
  60. Try
  61. MyCommand.ExecuteNonQuery()
  62. Message.InnerHtml = "<b>Record Updated</b><br>" & UpdateCmd.ToString()
  63. MyDataGrid.EditItemIndex = -1
  64. Catch Exp As SQLException
  65. If Exp.Number = 2627 Then
  66. Message.InnerHtml = "ERROR: A record already exists with the same primary key"
  67. Else
  68. Message.InnerHtml = "ERROR: Could not update record, please ensure the fields are correctly filled out"
  69. End If
  70. Message.Style("color") = "red"
  71. End Try
  72.  
  73. MyCommand.Connection.Close()
  74.  
  75. BindGrid()
  76. End Sub
  77.  
  78.  
  79. Sub MyDataGrid_ItemDataBound(ByVal Sender As Object, ByVal E As DataGridItemEventArgs)
  80. If (E.Item.ItemType = ListItemType.EditItem) Then
  81. Dim i As Integer
  82. For i = 0 To E.Item.Controls.Count - 1
  83. If (E.Item.Controls(i).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.TextBox") Then
  84. Dim tb As TextBox
  85. tb = E.Item.Controls(i).Controls(0)
  86. tb.Text = Server.HtmlDecode(tb.Text)
  87. End If
  88. Next
  89. End If
  90. End Sub
  91.  
  92. Sub BindGrid()
  93.  
  94. Dim DS As DataSet
  95. Dim MyCommand As SqlDataAdapter
  96. MyCommand = New SqlDataAdapter("select * from ToddsTable1", MyConnection)
  97.  
  98. DS = New DataSet()
  99. MyCommand.Fill(DS, "ToddsTable1")
  100.  
  101.  
  102. MyDataGrid.DataSource = DS.Tables("ToddsTable1").DefaultView
  103. MyDataGrid.DataBind()
  104.  
  105. End Sub
  106.  
  107. </script>
  108.  
  109.  
  110. <body style="font: 10pt verdana">
  111.  
  112. <form id="Form1" runat="server">
  113.  
  114. <h3><font face="Verdana">Updating ToddsTable1</font></h3>
  115.  
  116. <span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/><p>
  117.  
  118.  
  119. <ASP:DataGrid id="MyDataGrid" runat="server"
  120. Width="700"
  121. BackColor="#ccccff"
  122. BorderColor="black"
  123. ShowFooter="false"
  124. CellPadding=3
  125. CellSpacing="0"
  126. Font-Name="Verdana"
  127. Font-Size="8pt"
  128. HeaderStyle-BackColor="#aaaadd"
  129. OnEditCommand="MyDataGrid_Edit"
  130. OnCancelCommand="MyDataGrid_Cancel"
  131. OnUpdateCommand="MyDataGrid_Update"
  132. DataKeyField="myNewCol1"
  133. OnItemDataBound="MyDataGrid_ItemDataBound"
  134. EnableViewState="false"
  135. >
  136.  
  137. <Columns>
  138. <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" ItemStyle-Wrap="false"/>
  139. </Columns>
  140.  
  141. </ASP:DataGrid>
  142.  
  143. </form>
  144. </body>
  145. </html>
  146.  
  147.  
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
2 4159
Ok, I switched my code as follows:
Expand|Select|Wrap|Line Numbers
  1. <%@ Page Language="VB" %>
  2. <%@ Import Namespace="System.Data" %>
  3. <%@ Import Namespace="System.Data.SqlClient" %>
  4. <html>
  5. <head>
  6. <script runat="server">
  7. Public Sub Page_Load(Source As Object, E As EventArgs)
  8. If Not Page.IsPostBack Then
  9. BindData()
  10. End If
  11. End Sub
  12.  
  13. Public Sub DataGrid_Edit(ByVal Source As Object, _
  14. ByVal E As DataGridCommandEventArgs)
  15. myInfo.EditItemIndex = E.Item.ItemIndex
  16. BindData()
  17. End Sub
  18.  
  19. Public Sub DataGrid_Cancel(ByVal Source As Object, _
  20. ByVal E As DataGridCommandEventArgs)
  21. myInfo.EditItemIndex = -1
  22. BindData()
  23. End Sub
  24.  
  25. Public Sub DataGrid_Update(ByVal Source As Object, _
  26. ByVal E As DataGridCommandEventArgs)
  27. Dim myConnection As SqlConnection
  28. Dim myCommand As SqlCommand
  29. Dim txtmyNewCol1 As TextBox = E.Item.Cells(1).Controls(0)
  30. Dim txtmyNewCol2 As TextBox = E.Item.Cells(2).Controls(0)
  31. Dim strUpdateStmt As String
  32.  
  33. strUpdateStmt = "UPDATE ToddsTable1 SET " & _
  34. "myNewCol1 = '" & txtmyNewCol1.Text & "', " & _
  35. "myNewcol2 = '" & txtmyNewCol2.Text & "' "
  36.  
  37. myConnection = New SqlConnection( _
  38. "server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")
  39. myCommand = New SqlCommand(strUpdateStmt, myConnection)
  40. myConnection.Open()
  41. myCommand.ExecuteNonQuery()
  42.  
  43. myInfo.EditItemIndex = -1
  44. BindData()
  45. End Sub
  46.  
  47. Public Sub BindData()
  48. Dim myDataSet As New DataSet
  49. Dim mySqlDataAdapter As SqlDataAdapter
  50. mySqlDataAdapter = New SqlDataAdapter( _
  51. "SELECT * FROM ToddsTable1", _
  52. "server=SQLSERVER;database=upsizedCandidate;Integrated Security=True")
  53. mySqlDataAdapter.Fill(myDataSet, "ToddsTable1")
  54. myInfo.DataSource = myDataSet.Tables("ToddsTable1")
  55. myInfo.DataBind()
  56. End Sub
  57. </script>
  58. </head>
  59. <body>
  60. <form id="Form1" runat="server" method="post">
  61. <H3>Editing ToddsTable1 from SQL Server</H3>
  62.  
  63. <asp:DataGrid id="myInfo" runat="server"
  64. AutoGenerateColumns="False"
  65. OnEditCommand="DataGrid_Edit" Width="702px"
  66. OnCancelCommand="DataGrid_Cancel"
  67. OnUpdateCommand="DataGrid_Update">
  68.  
  69. <Columns>
  70. <asp:EditCommandColumn
  71. CancelText="Cancel"
  72. EditText="Edit"
  73. UpdateText="Update" />
  74. <asp:BoundColumn
  75. DataField="myNewCol1"
  76. HeaderText="myNewCol1" />
  77. <asp:BoundColumn
  78. DataField="myNewCol2"
  79. HeaderText="myNewCol2" />
  80. </Columns>
  81. </asp:DataGrid>
  82.  
  83.  
  84. </form>
  85. </body>
  86. </html>
  87.  
  88.  
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
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
  1. <%@ Page Language="VB" %>
  2. <%@ Import Namespace="System.Data" %>
  3. <%@ Import Namespace="System.Data.SqlClient" %>
  4. <html>
  5. <head>
  6. <script runat="server">
  7. Public Sub Page_Load(Source As Object, E As EventArgs)
  8. If Not Page.IsPostBack Then
  9. BindData()
  10. End If
  11. End Sub
  12.  
  13. Public Sub DataGrid_Edit(ByVal Source As Object, _
  14. ByVal E As DataGridCommandEventArgs)
  15. myInfo.EditItemIndex = E.Item.ItemIndex
  16. BindData()
  17. End Sub
  18.  
  19. Public Sub DataGrid_Cancel(ByVal Source As Object, _
  20. ByVal E As DataGridCommandEventArgs)
  21. myInfo.EditItemIndex = -1
  22. BindData()
  23. End Sub
  24.  
  25. Public Sub DataGrid_Update(ByVal Source As Object, _
  26. ByVal E As DataGridCommandEventArgs)
  27. Dim myConnection As SqlConnection
  28. Dim myCommand As SqlCommand
  29. Dim txtmyNewCol1 As TextBox = E.Item.Cells(1).Controls(0)
  30. Dim txtmyNewCol2 As TextBox = E.Item.Cells(2).Controls(0)
  31. Dim strUpdateStmt As String
  32.  
  33. strUpdateStmt = "UPDATE ToddsTable1 SET " & _
  34. "myNewCol1 = '" & txtmyNewCol1.Text & "', " & _
  35. "myNewcol2 = '" & txtmyNewCol2.Text & "' "
  36.  
  37. myConnection = New SqlConnection( _
  38. "server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")
  39. myCommand = New SqlCommand(strUpdateStmt, myConnection)
  40. myConnection.Open()
  41. myCommand.ExecuteNonQuery()
  42.  
  43. myInfo.EditItemIndex = -1
  44. BindData()
  45. End Sub
  46.  
  47. Public Sub BindData()
  48. Dim myDataSet As New DataSet
  49. Dim mySqlDataAdapter As SqlDataAdapter
  50. mySqlDataAdapter = New SqlDataAdapter( _
  51. "SELECT * FROM ToddsTable1", _
  52. "server=SQLSERVER;database=upsizedCandidate;Integrated Security=True")
  53. mySqlDataAdapter.Fill(myDataSet, "ToddsTable1")
  54. myInfo.DataSource = myDataSet.Tables("ToddsTable1")
  55. myInfo.DataBind()
  56. End Sub
  57. </script>
  58. </head>
  59. <body>
  60. <form id="Form1" runat="server" method="post">
  61. <H3>Editing ToddsTable1 from SQL Server</H3>
  62.  
  63. <asp:DataGrid id="myInfo" runat="server"
  64. AutoGenerateColumns="False"
  65. OnEditCommand="DataGrid_Edit" Width="702px"
  66. OnCancelCommand="DataGrid_Cancel"
  67. OnUpdateCommand="DataGrid_Update">
  68.  
  69. <Columns>
  70. <asp:EditCommandColumn
  71. CancelText="Cancel"
  72. EditText="Edit"
  73. UpdateText="Update" />
  74. <asp:BoundColumn
  75. DataField="myNewCol1"
  76. HeaderText="myNewCol1" />
  77. <asp:BoundColumn
  78. DataField="myNewCol2"
  79. HeaderText="myNewCol2" />
  80. </Columns>
  81. </asp:DataGrid>
  82.  
  83.  
  84. </form>
  85. </body>
  86. </html>
  87.  
  88.  
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Jeremy Ames | last post: by
reply views Thread by sameer mowade via .NET 247 | last post: by
1 post views Thread by Jim Heavey | last post: by
8 posts views Thread by Inigo Jimenez | last post: by
3 posts views Thread by Rob Rogers | last post: by
2 posts views Thread by Its_Me_SunnY | last post: by
6 posts views Thread by p.mc | last post: by
5 posts views Thread by tshad | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.