Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using BeginTran/Commit Tran for MS SQL in c#

Posted on 2004-08-12
9
Medium Priority
?
970 Views
Last Modified: 2010-08-05
Hi All,

I need to know how to use the BeginTran and Commit Tran in C#.

Here's the basic background on the problem:
I have a webpage where the user can add new record.  The record is inserted into a MS SQL DB.  the table has a primary key.

What I need is to get the ID (primary key) of the record that was inserted.  

I can get the last record that was added using the select Ident_current.  So if I tie the Insert Into and the select ident_current into one transaction, I can get an accurate ID of the record.

So, how do I do this in .Net using C#?????

Please be kind and elobrate your comments
Thanks.
0
Comment
Question by:xile001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 20

Expert Comment

by:ihenry
ID: 11785798
Except select statement how many insert/update statements do you have?
0
 
LVL 8

Assisted Solution

by:boulder_bum
boulder_bum earned 750 total points
ID: 11785814
You'd get the value returned from the final SELECT of your SQL/Sproc by using SqlCommand.ExecuteScalar() which returns a single value. The INSERT will still execute, you'll just get a value back in addition.
0
 
LVL 20

Expert Comment

by:ihenry
ID: 11785849
Just want to mention, if you have just a single insert/update statement you don't need transaction to handle that.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:AerosSaga
ID: 11785964
If you want it in VB let me know.

Regards,

Aeros
0
 

Author Comment

by:xile001
ID: 11788182
boulder_bum:
That's interesting.  I didn't know that method return the value.  That would works perfectly without me having to use the transaction.

though, I did find the information for using transaction in c#.  So incase anyone needs it, here's the link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconperformingtransactionusingadonet.asp
0
 
LVL 8

Expert Comment

by:boulder_bum
ID: 11788252
"I didn't know that method return the value."

Yeah. The method simply returns the first value returned, so if you have a statement like:

INSERT INTO --etc etc
SELECT @@IDENTITY AS MyNewIdentity

then ExecuteScalar will snag the value of "MyNewIdentity".
0
 

Author Comment

by:xile001
ID: 11788330
buolder_bum:

If I use the Insert into (val.....) values (x1,....), will it return the primary key of the inserted row using the execute scalar?

0
 
LVL 17

Accepted Solution

by:
AerosSaga earned 750 total points
ID: 11789120
Yes it can be done like so:

cmd.CommandText = "INSERT INTO ShippingAddresses (FirstName, LastName, Address1, Address2, City, State, Zip, Country)" & _
                    " Values ('" & CStr(Session("BillFirstName")) & "','" & _
            CStr(Session("BillLastName")) & "','" & CStr(Session("BillAddress1")) & "','" & CStr(Session("BillAddress2")) & "','" & _
            CStr(Session("BillCity")) & "','" & CStr(Session("BillState")) & "','" & CStr(Session("BillZip")) & "','" & _
            CStr(Session("BillCountry")) & "')"
            End If
            cmd.ExecuteNonQuery()
            cmd.CommandText = "SELECT LAST_INSERT_ID() AS ShippingAddressID"
            cmd.ExecuteScalar()
            strShippingAddressID = CStr(cmd.ExecuteScalar())
            Me.lblShippingAddressID.Text = strShippingAddressID
            cmd.CommandText = "INSERT INTO Orders (CustomerID, OrderID, Total, Shipping, Tax, OrderDate, AuthNetAuthCode, AuthNetTransID, CCType, ShipAddressID)" & _
            " Values ('" & Me.lblCustomerID.Text.ToString & "','" & Me.lblTrackingID.Text.ToString & "','" & Me.lblOrderSubTotal.Text.ToString & "','" & _
            Me.lblTotalTax.Text & "','" & Me.lblShippingTotal.Text & "','" & ThisDay & "','" & "" & "','" & "" & "','" & Me.ddlCreditType.SelectedValue & "','" & Me.lblShippingAddressID.Text & "')"
            'Insert Null AuthNet Codes to create OrderTrackingID, will update after transaction
            cmd.ExecuteNonQuery()
            cmd.CommandText = "SELECT LAST_INSERT_ID() As OrderTrackingID"
            cmd.ExecuteScalar()
            strOrderTrackingID = CStr(cmd.ExecuteScalar())
            Me.lblOrderTrackingID.Text = strOrderTrackingID

Regards,

Aeros
0
 
LVL 8

Expert Comment

by:boulder_bum
ID: 11789948
"will it return the primary key of the inserted row using the execute scalar"

It all depends on the SQL. The trick is not so much in ExecuteScalar (which simply selects a single value) as it is in the statement you use. After the INSERT, you want to have a little bit of SQL that returns the primary key like AerosSaga's "SELECT LAST_INSERT_ID()" or the identity selection I mentioned, "SELECT @@IDENTITY".
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question