Determine the identity after INSERT and pass to SELECT

FionaDM

I want insert a record in FormView and then revert to readonly while displaying the just inserted record. I know how to determine the identity of a just inserted record but not how to pass this to the SelectCommand. I've read Fredrik Normen's blog on the subject but am truly stuck. His blog is at http://fredrik.nsquared2.com/viewpost.aspx?PostID=318 Can anyone tell me what I'm missing - what more I need to make this work. Does it involve SelectParameters perhaps? I don't actually see the value of the OnInserted method. It just seems to be displaying the NewID. I nust be mistaken! Here's Fredrik's code Name:
protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e) { object newId = e.Command.Parameters["@NewId"].Value; } Many Thanks for any help Sylvie

Guffa

What do you mean? What happens, and how does that differ from what you expect? --- b { font-weight: normal; }

FionaDM

When returned to readonly it displays first record in db. I want to see the record just inserted.

Ista

call the databind method again myDatagrid.DataBind(); 1 line of code equals many bugs. So don't write any!!

minhpc_bk

Hi there, What you might need to do is to keep the newly-inserted id and pass it to a select parameter in the Selecting event of the SqlDataSource control. The sample code looks like this:

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
//m_newId is an instance member.
m_newId = (int)e.Command.Parameters["@NewId"].Value;
}

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
if (m_newId != 0)
e.Command.Parameters["@Id"].Value = m_newId;
}

You also need to declare a select parameter in the SelectParameters collection, and update the select command a bit:

SelectCommand="SELECT [Id], [Name] FROM [Customer] WHERE ([Id] = @Id)"

FionaDM

I'm afraid that doesn't work. W.r.t SqlDataSource1_Inserted I get "name m_newId" does not exist in the current context". I can get a bit further if I declare m_newId as an object in SqlDataSource1_Inserted. The identity is worked out ok then . Problem is that it isn't passed to SqlDataSource1_Selecting so get "name m_newId" does not exist in the current context" again. Do I need to declare m_newId as a global variable? If so how? Any other suggestions?

minhpc_bk

Like I commented above the sample code, the m_newId is an instance member that you simply declare in code-behind:

public partial class Default : System.Web.UI.Page
{
private int m_newId = 0;

protected void Page\_Load(object sender, EventArgs e)
{

}
                    
protected void SqlDataSource1\_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
   m\_newId = (int)e.Command.Parameters\["@NewId"\].Value; 
}
                       
protected void SqlDataSource1\_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    if (m\_newId != 0)
        e.Command.Parameters\["@CategoryID"\].Value = m\_newId;
}

}

The class member m_newId is intended to temporaryly store the new id value which you can use in the later event Selecting of the datasource control when the FormView control changes its mode to ReadOnly.

FionaDM

I think I’m nearly there with this but I still have a problem: when I run the application the browser displays a blank page. I think it’s a problem with the SelectCommand because if I revert to SelectCommand="SELECT [ID], [Name] FROM [Customer]” without the WHERE clause the page displays OK. I know this is no use in the long term but it has enabled me to debug and show that m_newId is being set to the correct value in both SqlDataSource1_Inserted and SqlDataSource1_Selecting. Here’s my code to date: public partial class TestInsertCustomers : System.Web.UI.Page { private int m_newId = 0; protected void Page_Load(object sender, EventArgs e) { } protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e) { //m_newId is an instance member. m_newId = (int) e.Command.Parameters["@NewId"].Value; } protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { if (m_newId != 0) e.Command.Parameters["@ID"].Value = m_newId; } } Is the problem with the SelectCommand? Just feeling a bit worn out with this and suspecting it’s really obvious!! Sorry in advance. Maj

FionaDM

Sorry about the previous post. I had somehow managed to lose line 1 from my table which explains why the Selectcommand wasn't working - default for Selectparameters was set to 1. So it works! Great. Thank you so much minhpc_bk for all your help. I think this one might come in useful! Maj