469,570 Members | 1,709 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,570 developers. It's quick & easy.

how to rollback a procedure (sqlserver) from VB2005?

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
Sep 14 '06 #1
7 1345
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
Sep 14 '06 #2
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

Sep 14 '06 #3
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


Sep 14 '06 #4
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

Sep 14 '06 #5
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
Sep 14 '06 #6
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
>
>

Sep 14 '06 #7
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()

Sep 16 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by anders_tung | last post: by
3 posts views Thread by level8 | last post: by
1 post views Thread by Susan Lam | last post: by
reply views Thread by Jim Heavey | last post: by
2 posts views Thread by Ian Boyd | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.