Hi,
I have 2 stored procedure(PR and PR_Det)
How can i add Transaction(Commit or Rollback) to btnSave_Click
Below is my code behind:
Public Function SaveCustomer() As Integer
Dim id As Integer
Dim constr As String = ConfigurationManager.ConnectionStrings("ConnString").ToString()
Using con As New SqlConnection(constr)
con.Open()
Using cmd As New SqlCommand("PR", con)
cmd.Parameters.AddWithValue("@GRNumber", txtGRno.Text.Trim())
cmd.Parameters.Add("@GRID", SqlDbType.BigInt).Direction = ParameterDirection.Output
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
id = cmd.Parameters("@GRID").Value.ToString()
End Using
Return Convert.ToInt32(id)
End Using
End Function
Public Sub SaveCustomerOrders(id As Integer)
Dim constr As String = ConfigurationManager.ConnectionStrings("ConnString").ToString()
For Each row As GridViewRow In GView.Rows
Dim ponum As String = DirectCast(row.Cells(1).FindControl("lblPONumber"), Label).Text
Dim partyname As String = DirectCast(row.Cells(2).FindControl("lblPartyName"), Label).Text
Dim itemname As String = DirectCast(row.Cells(3).FindControl("lblItemName"), Label).Text
Dim qty As String = DirectCast(row.Cells(4).FindControl("lblQtyOrdered"), Label).Text
Dim qtydelivered As String = DirectCast(row.Cells(5).FindControl("lblQtyDelivered"), Label).Text
Using con As New SqlConnection(constr)
con.Open()
Using cmd As New SqlCommand("PR_Det", con)
cmd.Parameters.AddWithValue("@GRID", id)
cmd.Parameters.Add("@PONumber", SqlDbType.VarChar).Value = ponum
cmd.Parameters.Add("@PartyName", SqlDbType.VarChar).Value = partyname
cmd.Parameters.Add("@ItemName", SqlDbType.VarChar).Value = itemname
cmd.Parameters.AddWithValue("@QTYOrdered", qty).ToString()
cmd.Parameters.AddWithValue("@QTYReceived", qtydelivered).ToString()
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
End Using
End Using
Next
End Sub
Protected Sub btnSave_Click(sender As Object, e As ImageClickEventArgs) Handles ImgBtnSave.Click
Dim id As Integer = SaveCustomer()
SaveCustomerOrders(id)
GView.DataSource = Nothing
End Sub
I prefer