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
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
ASKER
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;Integra
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;
here's my data source code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=RSLCO4-0601;Integra
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;
Dear megatherian,
You can retrieved new inserted id from asp:ControlParameter object.
eg:
<InsertParameters>
<asp:ControlParameter ControlID="lblEmailTrackNu
</InsertParameters>
Note: "lblEmailTrackNumber" is a label control that used to display retrieved new id.
message.Body = "Track Back Number:" + lblEmailTrackNumber.text;
You can retrieved new inserted id from asp:ControlParameter object.
eg:
<InsertParameters>
<asp:ControlParameter ControlID="lblEmailTrackNu
</InsertParameters>
Note: "lblEmailTrackNumber" is a label control that used to display retrieved new id.
message.Body = "Track Back Number:" + lblEmailTrackNumber.text;
ASKER
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 :)
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 :)
Dear megatherian,
Instead of SELECT SCOPE_IDENTITY() AS trackBack,
Change it to
SELECT @trackBack=scope_identity(
Instead of SELECT SCOPE_IDENTITY() AS trackBack,
Change it to
SELECT @trackBack=scope_identity(
ASKER
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.
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.
if your using MSSQL as the backend replace scope_identity() with @@identity and it should replace the identity for that row.
ASKER
Still nothing. This is getting ridiculous. It shouldn't be this hard.
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.
If not that will be the most likely cause to the problem.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
refer this link
https://www.experts-exchange.com/questions/22706821/Getting-Identity-after-Inserting-new-row.html