ASP.NET

ASP.NET RSS

WizardSteps-> SQL Insert -> Grab ID field

Please support our ASP.NET advertiser: $4.95 a Month - ASP.NET Web Hosting – Click Here!

•

Join Date: Oct 2006

Posts: 3

Reputation: XYXOXY is an unknown quantity at this point

Solved Threads: 0

XYXOXY

Offline

Newbie Poster

WizardSteps-> SQL Insert -> Grab ID field

Oct 19th, 2006

Hi all...
I have an issue that I've been puzzling over here and there for about a week. I should say that I'm new to ASP.NET and new to this forum. I Had some experience with VB6 some years back then my career took a left turn. Now I'm back, teaching myself ASP.NET/VB.NET with Visual Studio 2005. (Also learning classic ASP... don't ask). So it's likely that the problem is simply my approach and inexperience. With that said and an apology for a long post :
I created a web app that is primarily a multi-step wizard control which gathers input, takes the user down several possible paths, offers choices, and calculates some variables based on those choices. So far so good.
Now I have lots of validated data and I'm ready to add a record to my SQL 2000 table. So in my aspx.vb file I capture the click event of the wizard's FINISH button. I create a SQLDataSource object from a connectionstring I already have in my web.config file. I use a well thought out and thoroughly tested text command as the data source... (I know I should use a stored procedure to avoid injection attacks but I would prefer to just get this working and deal with that in ver.2. This is an internal app for a very small group).
Anyway, I setup all of the necessary InsertParameters etc and inside of a try/catch I perform the insert command and grab the number of rows affected with :

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
 RowsAffected = SqlDataSource1.Insert()
 RowsAffected = SqlDataSource1.Insert()

And my record inserts into my table beautifully.
Here's the problem...
I need to grab the Identity field of the record I just added. I've done a lot of research and as a result I've added

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
 
...SELECT @MY_ID = SCOPE_IDENTITY();
 
...SELECT @MY_ID = SCOPE_IDENTITY();

to my query string. I just can't figure out how to get that ID's data back. I need to use it to change a text property of a label in the next Wizardstep. I also had errors telling me that "MY_ID" wasn't declared so I tried adding it to the InsertParameters list. Kinda clunky and it only stopped the error message... it didn't give me any value back.
I tried :

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
VNewID = SqlDataSource1.InsertParameters.Item("My_ID")
VNewID = SqlDataSource1.InsertParameters.Item("My_ID")

Knowing that wouldn't work but I just can't seem to find a working example or figure it out. Which now leaves me thinking that my approach is just wrong.
Any suggestions ?
Thanks in advance and also once again... my apologies for giving more info than you probably needed.