By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,595 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Editing entire Datagrid

P: 41
Hi,
I've brought a table from SQL Server into a Datagrid using VB.NET and have changed all fields to textboxes so I can edit any field and only save once. Everything works fine except the save button. I tried to use stored procedures but this is almost working. I've been working on this for a long time and I still need some help. If anyone could give me some idea it will be greatly appreciated. Here is my function:
Expand|Select|Wrap|Line Numbers
  1.               Private Sub doSave(ByVal sender As Object, ByVal e As System.EventArgs)
  2.  
  3.         Dim myCommand As SqlCommand
  4.         Dim dstCopy As New DataSet
  5.         Dim strQuery As String
  6.  
  7.         objConnection = New SqlConnection("server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")
  8.         objConnection.Open()
  9.  
  10.         Dim dgItem As DataGridItem
  11.         For Each dgItem In myInfo.Items
  12.             Dim txtmyNewCol1 As TextBox = _
  13.             CType(dgItem.Cells(0).FindControl("txtmyNewCol1"), TextBox)
  14.  
  15.             Dim txtmyNewCol2 As TextBox = _
  16.             CType(dgItem.Cells(1).FindControl("txtmynewCol2"), TextBox)
  17.  
  18.             strQuery = String.Format("update ToddsTable1 set myNewCol1= '" + txtmyNewCol1.Text + "',myNewCol2='" + txtmyNewCol2.Text + "'")
  19.  
  20.             myCommand = New SqlCommand(strQuery, objConnection)
  21.             myCommand.ExecuteNonQuery()
  22.         Next
  23.  
  24.         Page.DataBind()
  25.         myInfo.DataBind()
  26.         objConnection.Close()
  27.  
  28.         myInfo.EditItemIndex = -1
  29.  
  30.         ShowDataGrid()
  31.  
  32.  
  33.     End Sub
  34.    ----------------------------------------------------------------
  35.  
  36.   Here is the button that fires it:
  37.            <asp:TemplateColumn HeaderText="Save">
  38.       <FooterTemplate>
  39.        <asp:Button CommandName="Save" Text="Save" ID="btnSave" OnClick="doSave" runat="server" /> 
  40.       </FooterTemplate>
  41.  </asp:TemplateColumn> 
  42.  
Please help!!
Apr 25 '07 #1
Share this Question
Share on Google+
2 Replies


P: 41
My problem is that the last row is being used as the source to update all tables. How can I fix this? Please help!!
Apr 26 '07 #2

P: 37
hi,

Programming the Save button involves saving multiple records at the same time. The data saved
over a period of time accumulates and results in a large database. While databases can efficiently hold large amounts of information that can be queried, all that data is useless if the data is incorrect. Databases provide many techniques for ensuring the integrity and consistency of the database. Primary key and unique constraints are employed to ensure entity integrity; foreign key constraints and ensuring relational integrity.

Working With Transactions - the SqlTransaction Class

In Microsoft SQL Server, we can use the System.Data.SqlClient.SqlTransaction class to start a transaction. We begin by creating a connection to the database via the SqlConnection class. Next, create a SqlTransaction instance by calling the BeginTransaction() method of the SqlConnection class.

Next, create the SqlCommand object that we will use, to issue the SQL statements. When creating this object we need to specify that it should use the myTrans SqlTransaction object. We can assign this through the constructor or the SqlCommand’s Transaction property.

Expand|Select|Wrap|Line Numbers
  1. Sub SaveToTable(ByVal s As Object, ByVal e As EventArgs)
  2. Dim Aname As String
  3. Dim Aamt As Integer
  4. Dim intcounter As Integer
  5.  
  6. ‘Open the connection
  7. myConnection.Open()
  8. mycommand.Connection = myConnection
  9.  
  10. ‘Retrieve transaction from connection
  11. mytrans = myConnection.BeginTransaction
  12.  
  13. ‘Assign Transaction to commands
  14. mycommand.Transaction = mytrans
  15.  
  16. ‘Set the CommandType property of the SqlCommand class to the value ‘CommandType.StoredProcedure.
  17. ‘Set the CommandText property of the SqlCommand class to the name of the stored procedure.
  18.  
  19.  
  20. Try
  21.  
  22. mycommand.CommandText = “NewTransaction”
  23. mycommand.CommandType = CommandType.StoredProcedure
  24.  
  25. ‘Create and add parameters to the SqlCommand object.     
  26. ‘We create a new parameter object for Transaction number and set its name, type, value and add it to the command objects paramter collection.
  27. ....
  28. ...
  29.  
  30. ---
  31. ....
  32.  
  33.  
  34. objDT = Session(“Accounts”)
  35.  
  36. For intcounter = 0 To objDT.Rows.Count - 1
  37.     objDR = objDT.Rows(intcounter)
  38.     If objDR(“damt”) = 0 Then
  39.         Aamt = objDR(“camt”) * (-1)
  40.     Else
  41.         Aamt = objDR(“damt”)
  42.     End If
  43. p = mycommand.Parameters(“@Tcode”)
  44. p.Value = TranNo.Text
  45. p = mycommand.Parameters(“@TDate”)
  46. p.Value = Tdate.Text
  47. p = mycommand.Parameters(“@TAccname”)
  48. p.Value = objDR(“Name”)
  49. p = mycommand.Parameters(“@TAmt”)
  50. p.Value = Aamt
  51. p = mycommand.Parameters(“@TCat”)
  52. p.Value = “a” ‘TRAN_CAT
  53. mycommand.ExecuteNonQuery()
  54. Next
  55. mytrans.Commit()
  56. Response.Write(“Transaction is written to database”)
  57. Catch exc As Exception
  58. mytrans.Rollback()
  59. Response.Write(“Transaction failed”)
  60.  
  61. Finally
  62. myConnection.Close()
  63. End Try
  64. End Sub
  65.  
The statement mycommnad.ExecuteNonQuery is in the loop. For each account there will be a separate command. All the commands forms a single transaction. If any command fails, all the commands will also fail.

[Link Removed]
Apr 27 '07 #3

Post your reply

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