• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1009
  • Last Modified:

using scope_identity() with SqlDataSource

I have a SqlDataSource set up on my page with an insert command that inserts the values from a form on the same page.  To execute it I use SqlDataSource.Insert() in the code behind of the submit button.  That part works just fine.

I also need it to e-mail the information from the form on submit - I also have that working just fine.

What I haven't been able to figure out though is how to return the identity of the inserted information so that I can use it as a line in the body of the e-mail.

I've done lots of searching and reading and understand how I can use Scope_Identity() with a SQLCommand but I'm not defining one since I'm using a SqlDataSource control instead.

I'm still kind of new to VS 2005 and C# so maybe I'm just going about this all wrong... Any help would be appreciated.
Deleted by modus_operandi, 500 points refunded. - 8/25/2007 9:18:39 PM
0
megatherian
Asked:
megatherian
  • 5
  • 2
  • 2
  • +1
1 Solution
 
megatherianAuthor Commented:
That question and the answers contained within make their connections via Sql connection commands.  My page is set up using a SqlDataSource which is how I'd like to keep it if possible.

here's my data source code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=RSLCO4-0601;Integrated Security=SSPI;Initial Catalog=CSC;Connect Timeout=120"
            InsertCommand="INSERT INTO [warranty] (...) VALUES (...); SELECT SCOPE_IDENTITY() AS trackBack">
<Insert Parameters>
...
</Insert Parameters>

all I want to know now is how to reference the value of "trackBack" so in my e-mail it's something like:
message.Body = "Track Back Number:" + trackBack;
0
 
David H.H.LeeCommented:
Dear megatherian,
You can retrieved new inserted id from asp:ControlParameter object.
eg:
<InsertParameters>
<asp:ControlParameter ControlID="lblEmailTrackNumber"  Name="trackBack" Direction="Output" Type="string" />
</InsertParameters>

Note: "lblEmailTrackNumber" is a label control that used to display retrieved new id.

message.Body = "Track Back Number:" + lblEmailTrackNumber.text;
0
Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

 
megatherianAuthor Commented:
thanks x_com
I think you're definately on the right track but I'm not getting any output from that.

I made an aspLabel with the name lblEmailTrackNumber and put in your code but the resulting e-mail just says:
Track Back Number:

I have tried fiddling around with it and a bunch of google searches on what I could do to make that work but have come up short.  Any idea why that might be coming back as blank?

I there a problem with my statement "SELECT SCOPE_IDENTITY() AS trackBack"?

I tried substituting @trackBack but that errored out

Should I be doing something with executeScalar()?

I don't want to complicate the issue I'm just not sure where to go from here.  I'm new :)
0
 
David H.H.LeeCommented:
Dear megatherian,
Instead of SELECT SCOPE_IDENTITY() AS trackBack,
Change it to
SELECT  @trackBack=scope_identity()
0
 
megatherianAuthor Commented:
unfortunately It's still not returning anything.

I did try putting ProprtyName="text" in the control parameter but that didn't fix it either.

you had me put type="string" but since my ID field is int I tried changing that to type="int32" - again with no luck though.

I'm grasping at straws at this point.
0
 
ShazbotOKCommented:
if your using MSSQL as the backend replace scope_identity() with @@identity and it should replace the identity for that row.
0
 
megatherianAuthor Commented:
Still nothing.  This is getting ridiculous.  It shouldn't be this hard.
0
 
ShazbotOKCommented:
You are correct that this should not be so hard... I tested that on my MS-SQL server (SQL 2000) and it worked fine... Q: Is your SQL Table setup with a Identity column that is set to auto-seed and increment?

If not that will be the most likely cause to the problem.
0
 
megatherianAuthor Commented:
I have an identity column and it is set to seed.

I finally just gave up this weekend, deleted the SqlDataSource and just hand wrote out my connection and insert command.  Scope_identity() worked just fine in that context.

I'm not sure what I was doing wrong but I just don't have anymore time to spend on this issue.

I appreciate the help though.

I'm not sure what I should do as far as the points on this question - or should I just ask that it be deleted (it sounds like ShazbotOK tested the solution and it worked for him).  What's the proper way to handle this?
0

Featured Post

Meet the Family that is Made for Collaboration

The TeamConnect Family product group as part of the Sennheiser for Business Portfolio comprising high-quality, technically well-conceived meeting solutions for business communication – designed for any meeting room and any meeting situation.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now