Editing entire Datagrid

[toddw607]

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:[code=vb]
Private Sub doSave(ByVal sender As Object, ByVal e As System.EventArg s)

Dim myCommand As SqlCommand
Dim dstCopy As New DataSet
Dim strQuery As String

objConnection = New SqlConnection(" server=SQLSERVE R;database=upsi zedCandidate;In tegrated Security=True;" )
objConnection.O pen()

Dim dgItem As DataGridItem
For Each dgItem In myInfo.Items
Dim txtmyNewCol1 As TextBox = _
CType(dgItem.Ce lls(0).FindCont rol("txtmyNewCo l1"), TextBox)

Dim txtmyNewCol2 As TextBox = _
CType(dgItem.Ce lls(1).FindCont rol("txtmynewCo l2"), TextBox)

strQuery = String.Format(" update ToddsTable1 set myNewCol1= '" + txtmyNewCol1.Te xt + "',myNewCol 2='" + txtmyNewCol2.Te xt + "'")

myCommand = New SqlCommand(strQ uery, objConnection)
myCommand.Execu teNonQuery()
Next

Page.DataBind()
myInfo.DataBind ()
objConnection.C lose()

myInfo.EditItem Index = -1

ShowDataGrid()


End Sub
----------------------------------------------------------------

Here is the button that fires it:
<asp:TemplateCo lumn HeaderText="Sav e">
<FooterTemplate >
<asp:Button CommandName="Sa ve" Text="Save" ID="btnSave" OnClick="doSave " runat="server" />
</FooterTemplate>
</asp:TemplateCol umn>
[/code]
Please help!!

[toddw607]

My problem is that the last row is being used as the source to update all tables. How can I fix this? Please help!!

[bhar]

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.Sql Client.SqlTrans action 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 BeginTransactio n() 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.

[code=vb]
Sub SaveToTable(ByV al s As Object, ByVal e As EventArgs)
Dim Aname As String
Dim Aamt As Integer
Dim intcounter As Integer

‘Open the connection
myConnection.Op en()
mycommand.Conne ction = myConnection

‘Retrieve transaction from connection
mytrans = myConnection.Be ginTransaction

‘Assign Transaction to commands
mycommand.Trans action = mytrans

‘Set the CommandType property of the SqlCommand class to the value ‘CommandType.St oredProcedure.
‘Set the CommandText property of the SqlCommand class to the name of the stored procedure.


Try

mycommand.Comma ndText = “NewTransaction ”
mycommand.Comma ndType = CommandType.Sto redProcedure

‘Create and add parameters to the SqlCommand object.
‘We create a new parameter object for Transaction number and set its name, type, value and add it to the command objects paramter collection.
....
...

---
....


objDT = Session(“Accoun ts”)

For intcounter = 0 To objDT.Rows.Coun t - 1
objDR = objDT.Rows(intc ounter)
If objDR(“damt”) = 0 Then
Aamt = objDR(“camt”) * (-1)
Else
Aamt = objDR(“damt”)
End If
p = mycommand.Param eters(“@Tcode”)
p.Value = TranNo.Text
p = mycommand.Param eters(“@TDate”)
p.Value = Tdate.Text
p = mycommand.Param eters(“@TAccnam e”)
p.Value = objDR(“Name”)
p = mycommand.Param eters(“@TAmt”)
p.Value = Aamt
p = mycommand.Param eters(“@TCat”)
p.Value = “a” ‘TRAN_CAT
mycommand.Execu teNonQuery()
Next
mytrans.Commit( )
Response.Write( “Transaction is written to database”)
Catch exc As Exception
mytrans.Rollbac k()
Response.Write( “Transaction failed”)

Finally
myConnection.Cl ose()
End Try
End Sub
[/code]
The statement mycommnad.Execu teNonQuery 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]