Hello,
I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the SP
will rollback the insert.
If I were to write the same procedure as inline sql in a VB2005 app
cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
....
da.Update(ds, "tbl1")
how do I implement a rollback from the app if some condition is not met?
pseudocode:
....
da.Update(ds, "tbl1")
....
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")
Thanks,
Rich 7 1610
Why not just call the stored proc from VB.Net?
Thanks,
Seth Rowe
Rich wrote:
Hello,
I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the SP
will rollback the insert.
If I were to write the same procedure as inline sql in a VB2005 app
cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
...
da.Update(ds, "tbl1")
how do I implement a rollback from the app if some condition is not met?
pseudocode:
...
da.Update(ds, "tbl1")
...
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")
Thanks,
Rich
Rich,
You have to use the Connection. transaction methods for that http://windowssdk.msdn.microsoft.com.../1756xwa3.aspx
or the transaction class http://windowssdk.msdn.microsoft.com.../4zx2yex3.aspx
I hope this helps,
Cor
"Rich" <Ri**@discussions.microsoft.comschreef in bericht
news:69**********************************@microsof t.com...
Hello,
I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the
SP
will rollback the insert.
If I were to write the same procedure as inline sql in a VB2005 app
cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
...
da.Update(ds, "tbl1")
how do I implement a rollback from the app if some condition is not met?
pseudocode:
...
da.Update(ds, "tbl1")
...
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")
Thanks,
Rich
Thanks. Yes, this helps. I forgot about the connection object, and the
transaction object.
Rich
"Cor Ligthert [MVP]" wrote:
Rich,
You have to use the Connection. transaction methods for that http://windowssdk.msdn.microsoft.com.../1756xwa3.aspx
or the transaction class
http://windowssdk.msdn.microsoft.com.../4zx2yex3.aspx
I hope this helps,
Cor
"Rich" <Ri**@discussions.microsoft.comschreef in bericht
news:69**********************************@microsof t.com...
Hello,
I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the
SP
will rollback the insert.
If I were to write the same procedure as inline sql in a VB2005 app
cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
...
da.Update(ds, "tbl1")
how do I implement a rollback from the app if some condition is not met?
pseudocode:
...
da.Update(ds, "tbl1")
...
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")
Thanks,
Rich
Actually, I am already calling the SP from the app. But I did not write the
SP. The actual SP is quite lengthy and complex and calls several other SPs
within the SP. I am looking to simplify this SP, and it looks like writing
the Sql inline in the VB2005 app is much easier to control than having them
in a bunch of SPs on the server. The primary SP rolls back the transaction
if any of the inner SPs fail.
My issue is that this particular DB has hundreds of SPs, and this one
particular SP contains like 30 -50 sub Sps which are all over the place. My
plan is to write each of these sub SPs as inline sql in my app. If one of
them fails I go to Catch and rollback the entire transaction. Oh, and some
of the SPs are superfluous - so having everything inline, I can eliminate the
superfluous SPs (ones that write temp data to temp tables I can store those
inline in a dataTable object).
"rowe_newsgroups" wrote:
Why not just call the stored proc from VB.Net?
Thanks,
Seth Rowe
Rich wrote:
Hello,
I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the SP
will rollback the insert.
If I were to write the same procedure as inline sql in a VB2005 app
cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
...
da.Update(ds, "tbl1")
how do I implement a rollback from the app if some condition is not met?
pseudocode:
...
da.Update(ds, "tbl1")
...
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")
Thanks,
Rich
Won't passing all of the SPs as inline SQL hurt you performance wise
(as SQL Server can't optimize them)?
Just Curious - I'm in a similar circumstance with an app I'm working on
(it too uses a boat load of stored procedures)
Thanks,
Seth Rowe
Rich wrote:
Actually, I am already calling the SP from the app. But I did not write the
SP. The actual SP is quite lengthy and complex and calls several other SPs
within the SP. I am looking to simplify this SP, and it looks like writing
the Sql inline in the VB2005 app is much easier to control than having them
in a bunch of SPs on the server. The primary SP rolls back the transaction
if any of the inner SPs fail.
My issue is that this particular DB has hundreds of SPs, and this one
particular SP contains like 30 -50 sub Sps which are all over the place. My
plan is to write each of these sub SPs as inline sql in my app. If one of
them fails I go to Catch and rollback the entire transaction. Oh, and some
of the SPs are superfluous - so having everything inline, I can eliminate the
superfluous SPs (ones that write temp data to temp tables I can store those
inline in a dataTable object).
"rowe_newsgroups" wrote:
Why not just call the stored proc from VB.Net?
Thanks,
Seth Rowe
Rich wrote:
Hello,
>
I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the SP
will rollback the insert.
>
If I were to write the same procedure as inline sql in a VB2005 app
>
cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
...
da.Update(ds, "tbl1")
>
how do I implement a rollback from the app if some condition is not met?
>
pseudocode:
...
da.Update(ds, "tbl1")
...
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")
>
Thanks,
Rich
Maybe it is because I am simplifying some of the stored procedures (that I
inherited - a project that inherited) that they seem to run faster from the
app than the whole procedure in sqlserver. Well, I am not dealing with
millions of records at this time, so I am not seeing any performance hits by
running sql inline in the app. I was just tired of having to wade through
hundreds of SPs to locate the ones I needed to work on. Will be upgrading to
sqlserver2005 from 2000 in November. Wonder if they have a directory
structure I could use to store the SPs in a more orderly fashion?
"rowe_newsgroups" wrote:
Won't passing all of the SPs as inline SQL hurt you performance wise
(as SQL Server can't optimize them)?
Just Curious - I'm in a similar circumstance with an app I'm working on
(it too uses a boat load of stored procedures)
Thanks,
Seth Rowe
Rich wrote:
Actually, I am already calling the SP from the app. But I did not write the
SP. The actual SP is quite lengthy and complex and calls several other SPs
within the SP. I am looking to simplify this SP, and it looks like writing
the Sql inline in the VB2005 app is much easier to control than having them
in a bunch of SPs on the server. The primary SP rolls back the transaction
if any of the inner SPs fail.
My issue is that this particular DB has hundreds of SPs, and this one
particular SP contains like 30 -50 sub Sps which are all over the place. My
plan is to write each of these sub SPs as inline sql in my app. If one of
them fails I go to Catch and rollback the entire transaction. Oh, and some
of the SPs are superfluous - so having everything inline, I can eliminate the
superfluous SPs (ones that write temp data to temp tables I can store those
inline in a dataTable object).
"rowe_newsgroups" wrote:
Why not just call the stored proc from VB.Net?
>
Thanks,
>
Seth Rowe
>
Rich wrote:
Hello,
I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the SP
will rollback the insert.
If I were to write the same procedure as inline sql in a VB2005 app
cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
...
da.Update(ds, "tbl1")
how do I implement a rollback from the app if some condition is not met?
pseudocode:
...
da.Update(ds, "tbl1")
...
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")
Thanks,
Rich
>
>
On 9/14/06 12:50 PM, in article 69**********************************@microsoft.com, "Rich"
<Ri**@discussions.microsoft.comwrote:
Hello,
I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the SP
will rollback the insert.
If I were to write the same procedure as inline sql in a VB2005 app
cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
...
da.Update(ds, "tbl1")
how do I implement a rollback from the app if some condition is not met?
pseudocode:
...
da.Update(ds, "tbl1")
...
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")
Thanks,
Rich
First, I'd try the obvious approach. Test if the conditions are met *before*
you update the database. But that's the obvious approach, and you knew that.
:)
The obvious approach failing, you'd need to use a transaction (likely a
System.Data.SqlClient.SqlTransaction). Essentially, you either want it all
to succeed, or none of it. The code looks something like this:
Dim connection As SqlConnection
Dim transaction As SqlTransaction
Dim command As SqlCommand
Dim boolx As Boolean
Try
connection = GetConnection()
connection.Open()
transaction = connection.BeginTransaction()
command = Connection.CreateCommand()
command.CommandType = CommandType.Text
command.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
command.Transaction = transaction
command.ExecuteNonQuery() This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: anders_tung |
last post by:
Hi,
I have a procedure which will call 3 functions.
First function will update a record.
Second function will delete a record.
Third function will insert a record.
Each function will return...
|
by: level8 |
last post by:
Hi, Everybody,
I'm a Hungarian SQL user and I need a little help for SQL Server 7 !
I protect my table against bad data with a trigger. I use ROLLBACK and
RAISERROR statement in this trigger....
|
by: Susan Lam |
last post by:
Hi,
I am an Oracle developer and new to SqlServer. Is there rollback
segment in SqlServer? If there is, does it use only for the purpose
of rollback or to maintain read consistency as well?
...
|
by: Jim Heavey |
last post by:
I have tested a procedure in TOAD and it functions as expected, meaning for
this particular transaction it returns and error message and performs a
rollback within the procedure.
When I call...
|
by: saroxonline76 |
last post by:
Dear all,
I installed Visual Basic 2005 Express and SqlServer Express.
I tried some features of both of them but I'm not able to connect a
databse through a network.
To explain, I have two...
| |
by: pratikbarot |
last post by:
Below is my procedure....
There is no table called "pratik" in my database so that the procedure give error.
so that exception is fire ......but it cant rollback 2 insert statement that is written...
|
by: minapatel |
last post by:
I have the following procedure
CREATE OR REPLACE procedure FOL_PURGE_CASES
as
/* type "SET SERVEROUTPUT ON" in sqlplus to debug !! */
cursor all_cases is
cursor all_cases is
...
|
by: Ian Boyd |
last post by:
We're encountering a situation where we're encountering a deadlock, and
someone's been made the deadlock victim. But after that, DB2 refuses to run
any SQL, and instead we get the error message:
...
|
by: Amit |
last post by:
Hello ,
Is there any way i can import data from datagrid to sqlserver.
please advise .
thanks & Regards
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |