CODEWITHSUNDEEP
c#asp.net
Prescient
Prescient

Reputation: 1061

Return scope identity using sqldatasource in code behind

Currently I'm doing an insert into the db from codebehind with a sqldatasource in the following way.

SqlDataSource13.InsertParameters["name"].DefaultValue = ufn;
            SqlDataSource13.InsertParameters["mime_type"].DefaultValue = FileUpload1.PostedFile.ContentType;
            SqlDataSource13.InsertParameters["size"].DefaultValue = FileUpload1.PostedFile.ContentLength.ToString();
            SqlDataSource13.InsertParameters["extension"].DefaultValue = "";
            SqlDataSource13.InsertParameters["date"].DefaultValue = dt.ToShortDateString();
            SqlDataSource13.InsertParameters["type"].DefaultValue = ddlDocType.SelectedValue;
            SqlDataSource13.InsertParameters["description"].DefaultValue = tbDescription.Text;
            SqlDataSource13.Insert();

This is working fine. However I need to now get the insert id. I added select scope_identity to the end of the insert query. and the parameter to the insertparameters with direction=ouput.

SELECT @id = SCOPE_IDENTITY()
<asp:Parameter Name="id" Direction="Output" />

however I need to figure out how to get the output parameter with the setup i have above.

Upvotes: 1

Views: 618

Answers (2)

Prescient
Prescient

Reputation: 1061

In order to make things easier on myself I switched to a linq to sql query.

tableDataContext tdc = new tableDataContext();
doc d = new doc();
d.dpi = qs;
d.dn = ufn;
etc...
tdc.docs.InsertOnSubmit(d);
tdc.SubmitChanges();

// get current row insert id
int dID = d.id;

So now I have the recently inserted doc id to use. Easy.

Thanks to whoever wrote this example

Upvotes: 0

Felipe Pereira
Felipe Pereira

Reputation: 12320

Try this:

protected void SqlDataSource13_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
    string sID = e.Command.Parameters["@Identity"].Value.ToString();
}

Taken from here (not C# but the idea is there)

Upvotes: 1

Related Questions