3 Replies - 2311 Views - Last Post: 23 March 2009 - 12:01 AM Rate Topic: -----

#1 optimisererp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 22-March 09

ROLLBACK IN A STORED PROCEDURE

Post icon  Posted 22 March 2009 - 10:52 AM

Dim trans As OleDbTransaction
Dim Cmd As New OleDbCommand
Cmd.CommandType = CommandType.StoredProcedure

If con.State = ConnectionState.Closed Then con.Open()
Cmd.Connection = con
trans = con.BeginTransaction()
Cmd.Transaction = trans
try
'---------------------------------
calling first store procedure(values,con,cmd)

calling second store procedure(values,con,cmd)
'----------------------

trans.Commit()
MsgBoxAC(500, "Records Saved")
Catch ex As Exception
trans.Rollback()
MsgBox(ex.Message)
Finally
con.Close()
FrmSalesDetails.Close()
End Try



Problem --
If I use one Store Procedure It saves
If Both are used Error - "Data Type Mismatched"

Kindly suggest

Is This A Good Question/Topic? 0
  • +

Replies To: ROLLBACK IN A STORED PROCEDURE

#2 RajputS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-September 08

Re: ROLLBACK IN A STORED PROCEDURE

Posted 22 March 2009 - 07:08 PM

You cant rollback a procedure in .Net. What you are trying to do, works fine if commandtype is text but it wont if its procedure.
You can rollback in SQL, Write a procedure in SQL, inside that procedure declare first procedure and then second procedure. In case any of the procedure fails just rollback, otherwise commit in the end.



View Postoptimisererp, on 22 Mar, 2009 - 09:52 AM, said:

Dim trans As OleDbTransaction
Dim Cmd As New OleDbCommand
Cmd.CommandType = CommandType.StoredProcedure

If con.State = ConnectionState.Closed Then con.Open()
Cmd.Connection = con
trans = con.BeginTransaction()
Cmd.Transaction = trans
try
'---------------------------------
calling first store procedure(values,con,cmd)

calling second store procedure(values,con,cmd)
'----------------------

trans.Commit()
MsgBoxAC(500, "Records Saved")
Catch ex As Exception
trans.Rollback()
MsgBox(ex.Message)
Finally
con.Close()
FrmSalesDetails.Close()
End Try



Problem --
If I use one Store Procedure It saves
If Both are used Error - "Data Type Mismatched"

Kindly suggest

Was This Post Helpful? 0
  • +
  • -

#3 optimisererp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 22-March 09

Re: ROLLBACK IN A STORED PROCEDURE

Posted 22 March 2009 - 11:52 PM

RajputS ,
Thanks
Can pls explain . How to work in sql .

I am not using Sql Server. I am using Access Database.

thanks
regards
Mohandeep Singh


View PostRajputS, on 22 Mar, 2009 - 06:08 PM, said:

You cant rollback a procedure in .Net. What you are trying to do, works fine if commandtype is text but it wont if its procedure.
You can rollback in SQL, Write a procedure in SQL, inside that procedure declare first procedure and then second procedure. In case any of the procedure fails just rollback, otherwise commit in the end.



View Postoptimisererp, on 22 Mar, 2009 - 09:52 AM, said:

Dim trans As OleDbTransaction
Dim Cmd As New OleDbCommand
Cmd.CommandType = CommandType.StoredProcedure

If con.State = ConnectionState.Closed Then con.Open()
Cmd.Connection = con
trans = con.BeginTransaction()
Cmd.Transaction = trans
try
'---------------------------------
calling first store procedure(values,con,cmd)

calling second store procedure(values,con,cmd)
'----------------------

trans.Commit()
MsgBoxAC(500, "Records Saved")
Catch ex As Exception
trans.Rollback()
MsgBox(ex.Message)
Finally
con.Close()
FrmSalesDetails.Close()
End Try



Problem --
If I use one Store Procedure It saves
If Both are used Error - "Data Type Mismatched"

Kindly suggest

Was This Post Helpful? 0
  • +
  • -

#4 searockruz  Icon User is offline

  • D.I.C Regular

Reputation: 24
  • View blog
  • Posts: 460
  • Joined: 07-March 09

Re: ROLLBACK IN A STORED PROCEDURE

Posted 23 March 2009 - 12:01 AM

check whether this tries to solve your problem

http://www.vb-helper...ransaction.html
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1