Solved

How do I get a newly inserted ID for use in another insert?

Posted on 2009-07-07
24
509 Views
Last Modified: 2012-08-14
I have an application whereby a user creates a new agency.  Because of a many-to-many relationship between users and agencies, I want to store the relationship in a UserAgencies table.  I need to get the newly created ID from the Agencies table for use in inserting the relational record into the UserAgencies table.  I've looked over several examples online and I've modified my code to look like theirs as best I could - but whether I use a returned value of @@IDENTITY or a scope_identity() I keep getting back a null value after the insert.  I'm wondering if I'm performing the check too soon?  Although I'm grabbing the data in the _Inserted function of the SQLDATASOURCE.  Please advise on what I may be doing wrong.
ASP.NET:
 

    <asp:LoginView ID="LoginView1" runat="server">

        <RoleGroups>

            <asp:RoleGroup Roles="Administrators">

                <ContentTemplate>

                    <h3><asp:Label ID="Label1" ForeColor="Red" OnLoad="Label1Load" runat="server" Visible="false">Record Added Successfully.</asp:Label></h3>

                    <h3>To add a new Agency - enter the data below and click "Insert":</h3>

                    <asp:FormView ID="FormView1" DefaultMode="Insert" runat="server" 

                        DataKeyNames="AgencyID" DataSourceID="SqlDataSource1">

                        <EditItemTemplate>

                            AgencyID:

                            <asp:Label ID="AgencyIDLabel1" runat="server" Text='<%# Eval("AgencyID") %>' />

                            <br />

                            Name:

                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />

                            <br />

                            Phone:

                            <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                            <br />

                            Fax:

                            <asp:TextBox ID="FaxTextBox" runat="server" Text='<%# Bind("Fax") %>' />

                            <br />

                            Address:

                            <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />

                            <br />

                            City:

                            <asp:TextBox ID="CityTextBox" runat="server" Text='<%# Bind("City") %>' />

                            <br />

                            State:

                            <asp:TextBox ID="StateTextBox" runat="server" 

                                Text='<%# Bind("State") %>' />

                            <br />

                            Zip:

                            <asp:TextBox ID="ZipTextBox" runat="server" 

                                Text='<%# Bind("Zip") %>' />

                            <br />

                            CustomForms:

                            <asp:TextBox ID="CustomFormsTextBox" runat="server" 

                                Text='<%# Bind("CustomForms") %>' />

                            <br />

                            OnlineAccess:

                            <asp:TextBox ID="OnlineAccessTextBox" runat="server" 

                                Text='<%# Bind("OnlineAccess") %>' />

                            <br />

                            <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 

                                CommandName="Update" Text="Update" />

                            &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" 

                                CausesValidation="False" CommandName="Cancel" Text="Cancel" />

                        </EditItemTemplate>

                        <InsertItemTemplate>

                            <table>

                            <tr><td>

                            Name:

                            </td><td>

                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />

                            </td></tr><tr><td>

                            Phone:

                            </td><td>

                            <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                            </td></tr><tr><td>

                            Fax:

                            </td><td>

                            <asp:TextBox ID="FaxTextBox" runat="server" Text='<%# Bind("Fax") %>' />

                            </td></tr><tr><td>

                            Address:

                            </td><td>

                            <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />

                            </td></tr><tr><td>

                            City:

                            </td><td>

                            <asp:TextBox ID="CityTextBox" runat="server" Text='<%# Bind("City") %>' />

                            </td></tr><tr><td>

                            State:

                            </td><td>

                            <asp:TextBox ID="StateTextBox" runat="server" Text='<%# Bind("State") %>' />

                            </td></tr><tr><td>

                            Zip:

                            </td><td>

                            <asp:TextBox ID="ZipTextBox" runat="server" Text='<%# Bind("Zip") %>' />

                            </td></tr><tr><td>

                            CustomForms:

                            </td><td>

                            <asp:DropDownList ID="DropDownList1" runat="server" 

                                SelectedValue='<%# Bind("CustomForms") %>'>

                                <asp:ListItem>Yes</asp:ListItem>

                                <asp:ListItem>No</asp:ListItem>

                            </asp:DropDownList>

                            </td></tr><tr><td>

                            OnlineAccess:

                            </td><td>

                            <asp:DropDownList ID="DropDownList2" runat="server" 

                                SelectedValue='<%# Bind("OnlineAccess") %>'>

                                <asp:ListItem>Yes</asp:ListItem>

                                <asp:ListItem>No</asp:ListItem>

                            </asp:DropDownList>

                            </td></tr>

                            <tr><td></td><td>

                            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 

                                CommandName="Insert" Text="Insert" />

                                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" 

                                Text="Cancel" />

                            </td></tr>

                            </table>

                        </InsertItemTemplate>

                        <ItemTemplate>

                            AgencyID:

                            <asp:Label ID="AgencyIDLabel" runat="server" Text='<%# Eval("AgencyID") %>' />

                            <br />

                            Name:

                            <asp:Label ID="NameLabel" runat="server" Text='<%# Bind("Name") %>' />

                            <br />

                            Phone:

                            <asp:Label ID="PhoneLabel" runat="server" Text='<%# Bind("Phone") %>' />

                            <br />

                            Fax:

                            <asp:Label ID="FaxLabel" runat="server" Text='<%# Bind("Fax") %>' />

                            <br />

                            Address:

                            <asp:Label ID="AddressLabel" runat="server" Text='<%# Bind("Address") %>' />

                            <br />

                            City:

                            <asp:Label ID="CityLabel" runat="server" Text='<%# Bind("City") %>' />

                            <br />

                            State:

                            <asp:Label ID="StateLabel" runat="server" 

                                Text='<%# Bind("State") %>' />

                            <br />

                            Zip:

                            <asp:Label ID="ZipLabel" runat="server" 

                                Text='<%# Bind("Zip") %>' />

                            <br />

                            CustomForms:

                            <asp:Label ID="CustomFormsLabel" runat="server" 

                                Text='<%# Bind("CustomForms") %>' />

                            <br />

                            OnlineAccess:

                            <asp:Label ID="OnlineAccessLabel" runat="server" 

                                Text='<%# Bind("OnlineAccess") %>' />

                            <br />

                            <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" 

                                CommandName="New" Text="New" />

                        </ItemTemplate>

                    </asp:FormView>

                    <asp:SqlDataSource ID="SqlDataSource1" runat="server"

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

                        InsertCommand="INSERT INTO Agencies(Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess) VALUES (@Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess); SET @AGENCYID = scope_identity();" 

                        SelectCommand="SELECT Agencies.* FROM Agencies" 

                        OnInserted="SqlDataSource1_Inserted">

                        <InsertParameters>

                            <asp:Parameter Name="Name" />

                            <asp:Parameter Name="Phone" />

                            <asp:Parameter Name="Fax" />

                            <asp:Parameter Name="Address" />

                            <asp:Parameter Name="City" />

                            <asp:Parameter Name="State" />

                            <asp:Parameter Name="Zip" />

                            <asp:Parameter Name="CustomForms" />

                            <asp:Parameter Name="OnlineAccess" />

                            <asp:Parameter Direction="Output" Name="AgencyID" Size="4" Type="Int32" />

                        </InsertParameters>

                    </asp:SqlDataSource>

                    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

                        InsertCommand="INSERT INTO UserAgencies(AgencyID, UserID) VALUES (@AgencyID, @UserID)" 

                        SelectCommand="SELECT UserAgencies.* FROM UserAgencies">

                        <InsertParameters>

                        </InsertParameters>

                    </asp:SqlDataSource>

                    <br />

                </ContentTemplate>

            </asp:RoleGroup>

        </RoleGroups>

        <LoggedInTemplate>

        You are not logged in with sufficient permission to manage items.<br />

        </LoggedInTemplate>

        <AnonymousTemplate>

            You are not logged in.&nbsp; <p>Please <a href="login.aspx">login</a>.</p>

        </AnonymousTemplate>

    </asp:LoginView>
 

Code-behind (C#):
 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.Sql;

using System.Data.Query;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Configuration;

using System.Web.Security;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;
 

public partial class AddAgency : System.Web.UI.Page

{

    protected void Label1Load(object sender, EventArgs e)

    {

        Label label = (Label)sender;

        Label Label1 = (Label)label.FindControl("Label1");
 

        if (Page.IsPostBack)

        {

            Label1.Visible = true;

        }

        else

        {

            Label1.Visible = false;
 

        }

    }
 

    protected void Page_Load(object sender, EventArgs e)

    {
 

    }
 

    protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)

    {

        SqlDataSource sq2 = (SqlDataSource)LoginView1.FindControl("SqlDataSource2");
 

        MembershipUser user = Membership.GetUser();
 

        Int32 AgencyID = Convert.ToInt32(e.Command.Parameters["@AgencyID"].Value);        

        

        object id = user.ProviderUserKey;
 

        if (Page.IsPostBack)

        {

            sq2.InsertParameters.Add("AgencyID", AgencyID.ToString());

            sq2.InsertParameters.Add("UserID", id.ToString());

            sq2.Insert();

        }

    }

}

Open in new window

0
Comment
Question by:GY1680
  • 11
  • 11
  • 2
24 Comments
 
LVL 41

Expert Comment

by:guru_sami
ID: 24796560
Try SELECT instead of SET
SELECT @AGENCYID = @@IDENTITY

Use SP to do this work.
For more info check:
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserting.aspx
0
 
LVL 18

Expert Comment

by:David Robitaille
ID: 24796906
you could also use SELECT SCOPE_IDENTITY()  and use a "output" parameter.
http://www.4guysfromrolla.com/articles/050207-1.aspx
 also check this
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_22838704.html
0
 

Author Comment

by:GY1680
ID: 24797151
Ok, thank you both for your responses.  I've decided to take a stap at an sp.  However, unfortunately I'm still getting a null value response.

Code updates attached.

I continue to get:

"Cannot insert the value NULL into column 'AgencyID'....."

Please advise if I'm missing something obvious.
Here is my modified asp for the sqldatasource:
 

                    <asp:SqlDataSource ID="SqlDataSource1" runat="server"

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

                        InsertCommand="sp_AddAgency"

                        SelectCommand="SELECT Agencies.* FROM Agencies" 

                        OnInserted="SqlDataSource1_Inserted"

                        OnInserting="SqlDataSource1_Inserting">

                        <InsertParameters>

                            <asp:Parameter Name="Name" />

                            <asp:Parameter Name="Phone" />

                            <asp:Parameter Name="Fax" />

                            <asp:Parameter Name="Address" />

                            <asp:Parameter Name="City" />

                            <asp:Parameter Name="State" />

                            <asp:Parameter Name="Zip" />

                            <asp:Parameter Name="CustomForms" />

                            <asp:Parameter Name="OnlineAccess" />

                            <asp:Parameter Name="AgencyID" type="Int32" Direction="Output"/>

                        </InsertParameters>

                    </asp:SqlDataSource>

                    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

                        InsertCommand="INSERT INTO UserAgencies(AgencyID, UserID) VALUES (@AgencyID, @UserID)" 

                        SelectCommand="SELECT UserAgencies.* FROM UserAgencies">

                        <InsertParameters>

                        </InsertParameters>

                    </asp:SqlDataSource>
 

My sp_AddAgency:
 

ALTER PROCEDURE sp_AddAgency

	/*

	(

	@parameter1 int = 5,

	@parameter2 datatype OUTPUT

	)

	*/

	@Name nvarchar(50),

	@Phone nvarchar(50), 

	@Fax nvarchar(50), 

	@Address nvarchar(50), 

	@City nvarchar(50), 

	@State nvarchar(50), 

	@Zip nvarchar(50), 

	@CustomForms nvarchar(50), 

	@OnlineAccess nvarchar(50),

	@AgencyID int OUTPUT

AS

	INSERT INTO Agencies (Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess) 

	VALUES (@Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)

    SET @AgencyID = SCOPE_IDENTITY()

    RETURN @AgencyID 

	/* SET NOCOUNT ON */
 

And my code-behind (c#):
 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.Common;

using System.Data.Sql;

using System.Data.Query;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Configuration;

using System.Web.Security;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;
 

public partial class AddAgency : System.Web.UI.Page

{

    protected void Label1Load(object sender, EventArgs e)

    {

        Label label = (Label)sender;

        Label Label1 = (Label)label.FindControl("Label1");
 

        if (Page.IsPostBack)

        {

            Label1.Visible = true;

        }

        else

        {

            Label1.Visible = false;
 

        }

    }
 

    protected void Page_Load(object sender, EventArgs e)

    {
 

    }
 

    protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)

    {

        SqlParameter insertedKey = new SqlParameter("@AgencyID", SqlDbType.Int);

        insertedKey.Direction = ParameterDirection.Output;

        e.Command.Parameters.Add(insertedKey);

    }

    

    protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)

    {
 

        DbCommand command = e.Command; 
 

        SqlDataSource sq2 = (SqlDataSource)LoginView1.FindControl("SqlDataSource2");
 

        MembershipUser user = Membership.GetUser();
 

        object id = user.ProviderUserKey;
 

        if (Page.IsPostBack)

        {

            sq2.InsertParameters.Add("AgencyID", command.Parameters["@AgencyID"].Value.ToString());

            sq2.InsertParameters.Add("UserID", id.ToString());

            sq2.Insert();

        }

    }

}

Open in new window

0
 
LVL 18

Expert Comment

by:David Robitaille
ID: 24797227
you dont need to use the @ when retriving a parameter :  ["AgencyID"]
0
 

Author Comment

by:GY1680
ID: 24798385
Modified parameter as advised:

From:

    protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    {
        SqlParameter insertedKey = new SqlParameter("@AgencyID", SqlDbType.Int);
        insertedKey.Direction = ParameterDirection.Output;
        e.Command.Parameters.Add(insertedKey);
    }


To:

    protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    {
        SqlParameter insertedKey = new SqlParameter("AgencyID", SqlDbType.Int);
        insertedKey.Direction = ParameterDirection.Output;
        e.Command.Parameters.Add(insertedKey);
    }


Still the same result - cannot insert null value.

Also tried removing @ from:

    protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {

        DbCommand command = e.Command;

        SqlDataSource sq2 = (SqlDataSource)LoginView1.FindControl("SqlDataSource2");

        MembershipUser user = Membership.GetUser();

        object id = user.ProviderUserKey;

        if (Page.IsPostBack)
        {
            sq2.InsertParameters.Add("AgencyID", command.Parameters["@AgencyID"].Value.ToString());
            sq2.InsertParameters.Add("UserID", id.ToString());
            sq2.Insert();
        }
    }

But got "object reference not set to an instance of an object".  When I tried to define AgencyID and convert to int it failed with a conversion error converting null to int.

Please advise.
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24798517
There is a conflict between
RETURN @AgencyID
 and
insertedKey.Direction = ParameterDirection.Output;

So as in the example here:
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserting.aspx
try using
SELECT @AgencyID= @@IDENTITY
RETURN (1)  

Also check this discussion:
http://stackoverflow.com/questions/706361/getting-return-value-from-stored-procedure-in-c
0
 

Author Comment

by:GY1680
ID: 24799193
Made the suggested change.  Now I get "Object reference not set to an instance of an object" in SqlDataSource1_Inserted for "            sq2.InsertParameters.Add("AgencyID", command.Parameters["AgencyID"].Value.ToString());"

Full code attached.


Asp:
 

    <asp:LoginView ID="LoginView1" runat="server">

        <RoleGroups>

            <asp:RoleGroup Roles="Administrators">

                <ContentTemplate>

                    <h3><asp:Label ID="Label1" ForeColor="Red" OnLoad="Label1Load" runat="server" Visible="false">Record Added Successfully.</asp:Label></h3>

                    <h3>To add a new Agency - enter the data below and click "Insert":</h3>

                    <asp:FormView ID="FormView1" DefaultMode="Insert" runat="server" 

                        DataKeyNames="AgencyID" DataSourceID="SqlDataSource1">

                        <EditItemTemplate>

                            AgencyID:

                            <asp:Label ID="AgencyIDLabel1" runat="server" Text='<%# Eval("AgencyID") %>' />

                            <br />

                            Name:

                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />

                            <br />

                            Phone:

                            <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                            <br />

                            Fax:

                            <asp:TextBox ID="FaxTextBox" runat="server" Text='<%# Bind("Fax") %>' />

                            <br />

                            Address:

                            <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />

                            <br />

                            City:

                            <asp:TextBox ID="CityTextBox" runat="server" Text='<%# Bind("City") %>' />

                            <br />

                            State:

                            <asp:TextBox ID="StateTextBox" runat="server" 

                                Text='<%# Bind("State") %>' />

                            <br />

                            Zip:

                            <asp:TextBox ID="ZipTextBox" runat="server" 

                                Text='<%# Bind("Zip") %>' />

                            <br />

                            CustomForms:

                            <asp:TextBox ID="CustomFormsTextBox" runat="server" 

                                Text='<%# Bind("CustomForms") %>' />

                            <br />

                            OnlineAccess:

                            <asp:TextBox ID="OnlineAccessTextBox" runat="server" 

                                Text='<%# Bind("OnlineAccess") %>' />

                            <br />

                            <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 

                                CommandName="Update" Text="Update" />

                            &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" 

                                CausesValidation="False" CommandName="Cancel" Text="Cancel" />

                        </EditItemTemplate>

                        <InsertItemTemplate>

                            <table>

                            <tr><td>

                            Name:

                            </td><td>

                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />

                            </td></tr><tr><td>

                            Phone:

                            </td><td>

                            <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                            </td></tr><tr><td>

                            Fax:

                            </td><td>

                            <asp:TextBox ID="FaxTextBox" runat="server" Text='<%# Bind("Fax") %>' />

                            </td></tr><tr><td>

                            Address:

                            </td><td>

                            <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />

                            </td></tr><tr><td>

                            City:

                            </td><td>

                            <asp:TextBox ID="CityTextBox" runat="server" Text='<%# Bind("City") %>' />

                            </td></tr><tr><td>

                            State:

                            </td><td>

                            <asp:TextBox ID="StateTextBox" runat="server" Text='<%# Bind("State") %>' />

                            </td></tr><tr><td>

                            Zip:

                            </td><td>

                            <asp:TextBox ID="ZipTextBox" runat="server" Text='<%# Bind("Zip") %>' />

                            </td></tr><tr><td>

                            CustomForms:

                            </td><td>

                            <asp:DropDownList ID="DropDownList1" runat="server" 

                                SelectedValue='<%# Bind("CustomForms") %>'>

                                <asp:ListItem>Yes</asp:ListItem>

                                <asp:ListItem>No</asp:ListItem>

                            </asp:DropDownList>

                            </td></tr><tr><td>

                            OnlineAccess:

                            </td><td>

                            <asp:DropDownList ID="DropDownList2" runat="server" 

                                SelectedValue='<%# Bind("OnlineAccess") %>'>

                                <asp:ListItem>Yes</asp:ListItem>

                                <asp:ListItem>No</asp:ListItem>

                            </asp:DropDownList>

                            </td></tr>

                            <tr><td></td><td>

                            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 

                                CommandName="Insert" Text="Insert" />

                                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" 

                                Text="Cancel" />

                            </td></tr>

                            </table>

                        </InsertItemTemplate>

                        <ItemTemplate>

                            AgencyID:

                            <asp:Label ID="AgencyIDLabel" runat="server" Text='<%# Eval("AgencyID") %>' />

                            <br />

                            Name:

                            <asp:Label ID="NameLabel" runat="server" Text='<%# Bind("Name") %>' />

                            <br />

                            Phone:

                            <asp:Label ID="PhoneLabel" runat="server" Text='<%# Bind("Phone") %>' />

                            <br />

                            Fax:

                            <asp:Label ID="FaxLabel" runat="server" Text='<%# Bind("Fax") %>' />

                            <br />

                            Address:

                            <asp:Label ID="AddressLabel" runat="server" Text='<%# Bind("Address") %>' />

                            <br />

                            City:

                            <asp:Label ID="CityLabel" runat="server" Text='<%# Bind("City") %>' />

                            <br />

                            State:

                            <asp:Label ID="StateLabel" runat="server" 

                                Text='<%# Bind("State") %>' />

                            <br />

                            Zip:

                            <asp:Label ID="ZipLabel" runat="server" 

                                Text='<%# Bind("Zip") %>' />

                            <br />

                            CustomForms:

                            <asp:Label ID="CustomFormsLabel" runat="server" 

                                Text='<%# Bind("CustomForms") %>' />

                            <br />

                            OnlineAccess:

                            <asp:Label ID="OnlineAccessLabel" runat="server" 

                                Text='<%# Bind("OnlineAccess") %>' />

                            <br />

                            <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" 

                                CommandName="New" Text="New" />

                        </ItemTemplate>

                    </asp:FormView>

                    <asp:SqlDataSource ID="SqlDataSource1" runat="server"

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

                        InsertCommandType="StoredProcedure"

                        InsertCommand="NewAgency"

                        SelectCommand="SELECT Agencies.* FROM Agencies" 

                        OnInserted="SqlDataSource1_Inserted"

                        OnInserting="SqlDataSource1_Inserting">

                        <InsertParameters>

                            <asp:Parameter Name="Name" />

                            <asp:Parameter Name="Phone" />

                            <asp:Parameter Name="Fax" />

                            <asp:Parameter Name="Address" />

                            <asp:Parameter Name="City" />

                            <asp:Parameter Name="State" />

                            <asp:Parameter Name="Zip" />

                            <asp:Parameter Name="CustomForms" />

                            <asp:Parameter Name="OnlineAccess" />

                            <asp:Parameter Name="AgencyID" type="Int32" Direction="Output"/>

                        </InsertParameters>

                    </asp:SqlDataSource>

                    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

                        InsertCommand="INSERT INTO UserAgencies(AgencyID, UserID) VALUES (@AgencyID, @UserID)" 

                        SelectCommand="SELECT UserAgencies.* FROM UserAgencies">

                        <InsertParameters>

                        </InsertParameters>

                    </asp:SqlDataSource>

                    <br />

                </ContentTemplate>

            </asp:RoleGroup>

        </RoleGroups>

        <LoggedInTemplate>

        You are not logged in with sufficient permission to manage items.<br />

        </LoggedInTemplate>

        <AnonymousTemplate>

            You are not logged in.&nbsp; <p>Please <a href="login.aspx">login</a>.</p>

        </AnonymousTemplate>

    </asp:LoginView>
 

Code-behind (c#):
 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.Common;

using System.Data.Sql;

using System.Data.Query;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Configuration;

using System.Web.Security;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;
 

public partial class AddAgency : System.Web.UI.Page

{

    protected void Label1Load(object sender, EventArgs e)

    {

        Label label = (Label)sender;

        Label Label1 = (Label)label.FindControl("Label1");
 

        if (Page.IsPostBack)

        {

            Label1.Visible = true;

        }

        else

        {

            Label1.Visible = false;
 

        }

    }
 

    protected void Page_Load(object sender, EventArgs e)

    {
 

    }
 

    protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)

    {

        SqlParameter @AgencyID = new SqlParameter("AgencyID", SqlDbType.Int);

        @AgencyID.Direction = ParameterDirection.Output;

        e.Command.Parameters.Add(@AgencyID);

    }

    

    protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)

    {
 

        DbCommand command = e.Command;
 

        SqlDataSource sq2 = (SqlDataSource)LoginView1.FindControl("SqlDataSource2");
 

        MembershipUser user = Membership.GetUser();
 

        object id = user.ProviderUserKey;
 

        if (Page.IsPostBack)

        {

            sq2.InsertParameters.Add("AgencyID", command.Parameters["@AgencyID"].Value.ToString());

            sq2.InsertParameters.Add("UserID", id.ToString());

            sq2.Insert();

        }

    }

}
 

NewAgency (sp):
 

ALTER PROCEDURE NewAgency

	@Name nvarchar(50),

	@Phone nvarchar(50), 

	@Fax nvarchar(50), 

	@Address nvarchar(50), 

	@City nvarchar(50), 

	@State nvarchar(50), 

	@Zip nvarchar(50), 

	@CustomForms nvarchar(50), 

	@OnlineAccess nvarchar(50),

	@AgencyID int OUTPUT

AS

	INSERT INTO Agencies (Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess) 

	VALUES (@Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)

	SELECT @AgencyID = @@IDENTITY

	RETURN (1)
 

ALTER PROCEDURE NewAgency

	@Name nvarchar(50),

	@Phone nvarchar(50), 

	@Fax nvarchar(50), 

	@Address nvarchar(50), 

	@City nvarchar(50), 

	@State nvarchar(50), 

	@Zip nvarchar(50), 

	@CustomForms nvarchar(50), 

	@OnlineAccess nvarchar(50),

	@AgencyID int OUTPUT

AS

	INSERT INTO Agencies (Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess) 

	VALUES (@Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)

	SELECT @AgencyID = @@IDENTITY

	RETURN (1)

Open in new window

0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24799373
i think now there is some confusion in use of @:
How about trying this:
 protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    {
        SqlParameter agencyID = new SqlParameter("@AgencyID", SqlDbType.Int);
        agencyID .Direction = ParameterDirection.Output;
        e.Command.Parameters.Add(agencyID );
    }

And then using it like this:

 sq2.InsertParameters.Add("AgencyID", command.Parameters["@AgencyID"].Value.ToString());"

Also I would suggest you to Execute the storedProc from SQL Sever directly and see what is it returning for  @AgencyID
0
 

Author Comment

by:GY1680
ID: 24799513
Tried the updated code-behind.  Got same result "Object reference not set to an instance of an object."

Good call on the sp.  When I ran it, it gave me this:

Running [dbo].[NewAgency] ( @Name = Test, @Phone = 123-456-7890, @Fax = 123-456-7891, @Address = 1234 E West, @City = Somewhere, @State = US, @Zip = 12345, @CustomForms = No, @OnlineAccess = No, @AgencyID = <DEFAULT> ).

Procedure or function 'NewAgency' expects parameter '@AgencyID', which was not supplied.
No rows affected.
(0 row(s) returned)
@AgencyID = <NULL>
@RETURN_VALUE =
Finished running [dbo].[NewAgency].

Then I tried removing the @AgencyID from the sp definitions:

ALTER PROCEDURE NewAgency
      @Name nvarchar(50),
      @Phone nvarchar(50),
      @Fax nvarchar(50),
      @Address nvarchar(50),
      @City nvarchar(50),
      @State nvarchar(50),
      @Zip nvarchar(50),
      @CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50),
      @AgencyID int OUTPUT             <----------------- this line removed
AS
      INSERT INTO Agencies (Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)
      VALUES (@Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)
      SELECT @AgencyID = @@IDENTITY
      RETURN (1)

And I got:

"Incorrect syntax near the keyword "AS".  Must declare the scalar variable "@AgencyID"."

I think we're getting closer.  It appears like the sp was never returning a value.
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24799718
"Incorrect syntax near the keyword "AS".  
error was due to:
.........
@CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50), <----- this "," it does not like it.if you remove it that error will go
 AS
........

Must declare the scalar variable "@AgencyID"."

is because it needs "@AgencyID variable to be used here
SELECT @AgencyID = @@IDENTITY
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24799838
Also when you execute the SP pass @AgencyID = <NULL> and then execute the code...

Running [dbo].[NewAgency] ( @Name = Test, @Phone = 123-456-7890, @Fax = 123-456-7891, @Address = 1234 E West, @City = Somewhere, @State = US, @Zip = 12345, @CustomForms = No, @OnlineAccess = No, @AgencyID = <NULL> ).

0
 

Author Comment

by:GY1680
ID: 24805630
Removed the comma.
Tried with this format:

ALTER PROCEDURE NewAgency
      @Name nvarchar(50),
      @Phone nvarchar(50),
      @Fax nvarchar(50),
      @Address nvarchar(50),
      @City nvarchar(50),
      @State nvarchar(50),
      @Zip nvarchar(50),
      @CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50)
AS
      INSERT INTO Agencies (Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)
      VALUES (@Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)
      SELECT @AgencyID = @@IDENTITY
      RETURN (1)

Failed with "Must declare the scalar variable "@AgencyID"."

Tried with the previous format passing <NULL> for @AgencyID:

ALTER PROCEDURE NewAgency
      @Name nvarchar(50),
      @Phone nvarchar(50),
      @Fax nvarchar(50),
      @Address nvarchar(50),
      @City nvarchar(50),
      @State nvarchar(50),
      @Zip nvarchar(50),
      @CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50),
      @AgencyID int OUTPUT
AS
      INSERT INTO Agencies (Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)
      VALUES (@Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)
      SELECT @AgencyID = @@IDENTITY
      RETURN (1)

Returned this:

Running [dbo].[NewAgency] ( @Name = Test Agency, @Phone = 123-456-7890, @Fax = 123-456-7891, @Address = 1234 E West, @City = Somewhere, @State = US, @Zip = 12345, @CustomForms = NO, @OnlineAccess = NO, @AgencyID = <NULL> ).

(1 row(s) affected)
(0 row(s) returned)
@AgencyID = <NULL>
@RETURN_VALUE = 1
Finished running [dbo].[NewAgency].

Tried with this format returning @AgencyID rather than (1):

ALTER PROCEDURE NewAgency
      @Name nvarchar(50),
      @Phone nvarchar(50),
      @Fax nvarchar(50),
      @Address nvarchar(50),
      @City nvarchar(50),
      @State nvarchar(50),
      @Zip nvarchar(50),
      @CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50),
      @AgencyID int OUTPUT
AS
      INSERT INTO Agencies (Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)
      VALUES (@Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)
      SELECT @AgencyID = @@IDENTITY
      RETURN @AgencyID

And returned this:

Running [dbo].[NewAgency] ( @Name = Test Agency, @Phone = 123-456-7890, @Fax = 123-456-7891, @Address = 1234 E West, @City = Somewhere, @State = US, @Zip = 12345, @CustomForms = NO, @OnlineAccess = NO, @AgencyID = <NULL> ).

The 'NewAgency' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
(1 row(s) affected)
(0 row(s) returned)
@AgencyID = <NULL>
@RETURN_VALUE = 0
Finished running [dbo].[NewAgency].

Please advise.  Seems like it doesn't want to give the @AgencyID with that particular select method for some reason.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 41

Expert Comment

by:guru_sami
ID: 24805983
Can you make sure AgencyID column in your table is an identity column...
i.e. when you view its properties...


IdentityColumn.bmp
0
 

Author Comment

by:GY1680
ID: 24806079
In both Agencies and User Agencies the "ID" and "AgencyID" fields (respectively) are Uniqueidentifier type - and the "identity specification" cannot be changed (apparently).

So it looks like this is the problem.  

Is there another way to grab the "ID" from a newly inserted Agency without "identity" or would I be forced to change the data types to "int" to be able to specify an identity?
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24806298
In that case your stored proc will change like below.

ALTER PROCEDURE NewAgency
      @Name nvarchar(50),
      @Phone nvarchar(50),
      @Fax nvarchar(50),
      @Address nvarchar(50),
      @City nvarchar(50),
      @State nvarchar(50),
      @Zip nvarchar(50),
      @CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50),
      @AgencyID uniqueidentifier OUTPUT   <--changed here
AS
      SELECT  @AgencyID = NEWID()  <--Added this
<----- insert AgencyID explicilty --->
      INSERT INTO Agencies (AgencyID,Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)
      VALUES (@AgencyID,@Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)
           RETURN (1)

Try to execute the SP again and see if you get @AgencyID..
If it does then...you can try running it from your application but after making following change:
Change...
  SqlParameter agencyID = new SqlParameter("@AgencyID", SqlDbType.Int);
to
  SqlParameter agencyID = new SqlParameter("@AgencyID", SqlDbType.UniqueIdentifier);
0
 

Author Comment

by:GY1680
ID: 24806398
Executing this:
------------------------------------------
ALTER PROCEDURE NewAgency
      @Name nvarchar(50),
      @Phone nvarchar(50),
      @Fax nvarchar(50),
      @Address nvarchar(50),
      @City nvarchar(50),
      @State nvarchar(50),
      @Zip nvarchar(50),
      @CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50),
      @AgencyID uniqueidentifier OUTPUT
AS
      SELECT  @AgencyID = NEWID()
      INSERT INTO Agencies (AgencyID, Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)
      VALUES (@AgencyID, @Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)
      RETURN (1)
------------------------------------------
Throws an error "Invalid column name 'AgencyID'.  

The ID field of the Agencies table is simply "ID".

Executing this:
------------------------------------------
ALTER PROCEDURE NewAgency
      @Name nvarchar(50),
      @Phone nvarchar(50),
      @Fax nvarchar(50),
      @Address nvarchar(50),
      @City nvarchar(50),
      @State nvarchar(50),
      @Zip nvarchar(50),
      @CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50),
      @AgencyID uniqueidentifier OUTPUT
AS
      SELECT  @AgencyID = NEWID()
      INSERT INTO Agencies (ID, Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)
      VALUES (@AgencyID, @Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)
      RETURN (1)
------------------------------------------
Returns this:
------------------------------------------
Running [dbo].[NewAgency] ( @Name = Test Agency, @Phone = 123-456-7890, @Fax = 123-456-7891, @Address = 1234 E West, @City = Somewhere, @State = US, @Zip = 12345, @CustomForms = NO, @OnlineAccess = NO, @AgencyID = <NULL> ).

(1 row(s) affected)
(0 row(s) returned)
@AgencyID = <NULL>
@RETURN_VALUE = 1
Finished running [dbo].[NewAgency].
------------------------------------------
Looks like @AgencyID is still null.  

Executing this:
------------------------------------------
ALTER PROCEDURE NewAgency
      @Name nvarchar(50),
      @Phone nvarchar(50),
      @Fax nvarchar(50),
      @Address nvarchar(50),
      @City nvarchar(50),
      @State nvarchar(50),
      @Zip nvarchar(50),
      @CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50),
      @ID uniqueidentifier OUTPUT
AS
      SELECT  @ID = NEWID()
      INSERT INTO Agencies (ID, Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)
      VALUES (@ID, @Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)
      RETURN (1)
------------------------------------------
Returns this:
------------------------------------------
Running [dbo].[NewAgency] ( @Name = Test Agency, @Phone = 123-456-7890, @Fax = 123-456-7891, @Address = 1234 E West, @City = Somewhere, @State = US, @Zip = 12345, @CustomForms = NO, @OnlineAccess = NO, @ID = <NULL> ).

(1 row(s) affected)
(0 row(s) returned)
@ID = <NULL>
@RETURN_VALUE = 1
Finished running [dbo].[NewAgency].
------------------------------------------
Looks like @ID is null.

Am I missing something obvious or is this much more difficult than it should be? :)
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24809108
Sorry but I think that should work in code...though it is not returning in SqlServer...
Here is my sample code compare it and make proper adjustments:

Stored Proc:
ALTER PROCEDURE NewUser
      @UserName varchar(50),
      @UserId uniqueidentifier output            
AS
  SET @UserId = NEWID()
      INSERT INTO tblUser (UserId,UserName)
      VALUES (@UserId, @UserName)
   Select @UserId
   RETURN(1)

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    {
         SqlParameter userID = new SqlParameter("@UserId",SqlDbType.UniqueIdentifier);
         userID.Direction = ParameterDirection.Output;        
        e.Command.Parameters.Add(userID);
}
    protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {  //This label displayed the correct GUID
        Label1.Text = e.Command.Parameters["@UserId"].Value.ToString();
    }

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"
            SelectCommand="SELECT [UserId], [UserName] FROM [tblUser]"
            InsertCommand="NewUser" InsertCommandType="StoredProcedure"
            oninserted="SqlDataSource1_Inserted"
            oninserting="SqlDataSource1_Inserting">
            <InsertParameters>
                <asp:Parameter Name="UserName" Type="String" />                
            </InsertParameters>
        </asp:SqlDataSource>
0
 

Author Comment

by:GY1680
ID: 24809596
Ok, so I've modified my sp and now I'm seeing the ID being generated, but for some reason directly after the insert when I try to select @AgencyID it's null.

sp:

ALTER PROCEDURE NewAgency
      @Name nvarchar(50),
      @Phone nvarchar(50),
      @Fax nvarchar(50),
      @Address nvarchar(50),
      @City nvarchar(50),
      @State nvarchar(50),
      @Zip nvarchar(50),
      @CustomForms nvarchar(50),
      @OnlineAccess nvarchar(50),
      @AgencyID uniqueidentifier OUTPUT
AS
      SELECT  @AgencyID = NEWID()
      INSERT INTO Agencies (ID, Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)
      VALUES (@AgencyID, @Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)
      SELECT @AgencyID
      RETURN (1)

Result:

Running [dbo].[NewAgency] ( @Name = Test Agency, @Phone = 123-456-7890, @Fax = 123-456-7891, @Address = 1234 E West, @City = Somewhere, @State = US, @Zip = 12345, @CustomForms = NO, @OnlineAccess = NO, @AgencyID = <NULL> ).

Column1                                                                                                                                                                                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15d75b4b-5c58-4a92-891c-44ff2acb2f3f                                                                                                                                                                                                                            
(1 row(s) affected)
(1 row(s) returned)
@AgencyID = <NULL>
@RETURN_VALUE = 1
Finished running [dbo].[NewAgency].

If I could just figure out how to keep the ID that was created in some form I think I'd have the answer.
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24809637
yes thats fine...but it will showup in your CS code...
@AgencyID = <NULL>
///even this is null here it will show up in your CS code
i.e. --> e.Command.Parameters["@UserId"].Value.ToString(); should give your the same id displayed

0
 

Author Comment

by:GY1680
ID: 24825295
Unfortunately it's still returning null to the CS.  Here's an image while debugging (when I hold CTRL to take a screen cap it dims the image so it's hard to read) there are two @AgencyIDs in the parameters.  Do you think that might be the problem?  Both have null values.

Attached is my CS, ASP and SP for reference.
SP:
 

ALTER PROCEDURE NewAgency

      @Name nvarchar(50),

      @Phone nvarchar(50),

      @Fax nvarchar(50),

      @Address nvarchar(50),

      @City nvarchar(50),

      @State nvarchar(50),

      @Zip nvarchar(50),

      @CustomForms nvarchar(50),

      @OnlineAccess nvarchar(50),

      @AgencyID uniqueidentifier OUTPUT

AS

      SELECT  @AgencyID = NEWID()

      INSERT INTO Agencies (ID, Name, Phone, Fax, Address, City, State, Zip, CustomForms, OnlineAccess)

      VALUES (@AgencyID, @Name, @Phone, @Fax, @Address, @City, @State, @Zip, @CustomForms, @OnlineAccess)

      SELECT @AgencyID

      RETURN (1)
 

ASPX:
 

    <asp:LoginView ID="LoginView1" runat="server">

        <RoleGroups>

            <asp:RoleGroup Roles="Administrators">

                <ContentTemplate>

                    <h3><asp:Label ID="Label1" ForeColor="Red" OnLoad="Label1Load" runat="server" Visible="false">Record Added Successfully.</asp:Label></h3>

                    <h3>To add a new Agency - enter the data below and click "Insert":</h3>

                    <asp:FormView ID="FormView1" DefaultMode="Insert" runat="server" 

                        DataKeyNames="AgencyID" DataSourceID="SqlDataSource1">

                        <EditItemTemplate>

                            AgencyID:

                            <asp:Label ID="AgencyIDLabel1" runat="server" Text='<%# Eval("AgencyID") %>' />

                            <br />

                            Name:

                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />

                            <br />

                            Phone:

                            <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                            <br />

                            Fax:

                            <asp:TextBox ID="FaxTextBox" runat="server" Text='<%# Bind("Fax") %>' />

                            <br />

                            Address:

                            <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />

                            <br />

                            City:

                            <asp:TextBox ID="CityTextBox" runat="server" Text='<%# Bind("City") %>' />

                            <br />

                            State:

                            <asp:TextBox ID="StateTextBox" runat="server" 

                                Text='<%# Bind("State") %>' />

                            <br />

                            Zip:

                            <asp:TextBox ID="ZipTextBox" runat="server" 

                                Text='<%# Bind("Zip") %>' />

                            <br />

                            CustomForms:

                            <asp:TextBox ID="CustomFormsTextBox" runat="server" 

                                Text='<%# Bind("CustomForms") %>' />

                            <br />

                            OnlineAccess:

                            <asp:TextBox ID="OnlineAccessTextBox" runat="server" 

                                Text='<%# Bind("OnlineAccess") %>' />

                            <br />

                            <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 

                                CommandName="Update" Text="Update" />

                            &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" 

                                CausesValidation="False" CommandName="Cancel" Text="Cancel" />

                        </EditItemTemplate>

                        <InsertItemTemplate>

                            <table>

                            <tr><td>

                            Name:

                            </td><td>

                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />

                            </td></tr><tr><td>

                            Phone:

                            </td><td>

                            <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                            </td></tr><tr><td>

                            Fax:

                            </td><td>

                            <asp:TextBox ID="FaxTextBox" runat="server" Text='<%# Bind("Fax") %>' />

                            </td></tr><tr><td>

                            Address:

                            </td><td>

                            <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />

                            </td></tr><tr><td>

                            City:

                            </td><td>

                            <asp:TextBox ID="CityTextBox" runat="server" Text='<%# Bind("City") %>' />

                            </td></tr><tr><td>

                            State:

                            </td><td>

                            <asp:TextBox ID="StateTextBox" runat="server" Text='<%# Bind("State") %>' />

                            </td></tr><tr><td>

                            Zip:

                            </td><td>

                            <asp:TextBox ID="ZipTextBox" runat="server" Text='<%# Bind("Zip") %>' />

                            </td></tr><tr><td>

                            CustomForms:

                            </td><td>

                            <asp:DropDownList ID="DropDownList1" runat="server" 

                                SelectedValue='<%# Bind("CustomForms") %>'>

                                <asp:ListItem>Yes</asp:ListItem>

                                <asp:ListItem>No</asp:ListItem>

                            </asp:DropDownList>

                            </td></tr><tr><td>

                            OnlineAccess:

                            </td><td>

                            <asp:DropDownList ID="DropDownList2" runat="server" 

                                SelectedValue='<%# Bind("OnlineAccess") %>'>

                                <asp:ListItem>Yes</asp:ListItem>

                                <asp:ListItem>No</asp:ListItem>

                            </asp:DropDownList>

                            </td></tr>

                            <tr><td></td><td>

                            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 

                                CommandName="Insert" Text="Insert" />

                                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" 

                                Text="Cancel" />

                            </td></tr>

                            </table>

                        </InsertItemTemplate>

                        <ItemTemplate>

                            AgencyID:

                            <asp:Label ID="AgencyIDLabel" runat="server" Text='<%# Eval("AgencyID") %>' />

                            <br />

                            Name:

                            <asp:Label ID="NameLabel" runat="server" Text='<%# Bind("Name") %>' />

                            <br />

                            Phone:

                            <asp:Label ID="PhoneLabel" runat="server" Text='<%# Bind("Phone") %>' />

                            <br />

                            Fax:

                            <asp:Label ID="FaxLabel" runat="server" Text='<%# Bind("Fax") %>' />

                            <br />

                            Address:

                            <asp:Label ID="AddressLabel" runat="server" Text='<%# Bind("Address") %>' />

                            <br />

                            City:

                            <asp:Label ID="CityLabel" runat="server" Text='<%# Bind("City") %>' />

                            <br />

                            State:

                            <asp:Label ID="StateLabel" runat="server" 

                                Text='<%# Bind("State") %>' />

                            <br />

                            Zip:

                            <asp:Label ID="ZipLabel" runat="server" 

                                Text='<%# Bind("Zip") %>' />

                            <br />

                            CustomForms:

                            <asp:Label ID="CustomFormsLabel" runat="server" 

                                Text='<%# Bind("CustomForms") %>' />

                            <br />

                            OnlineAccess:

                            <asp:Label ID="OnlineAccessLabel" runat="server" 

                                Text='<%# Bind("OnlineAccess") %>' />

                            <br />

                            <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" 

                                CommandName="New" Text="New" />

                        </ItemTemplate>

                    </asp:FormView>

                    <asp:SqlDataSource ID="SqlDataSource1" runat="server"

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

                        InsertCommandType="StoredProcedure"

                        InsertCommand="NewAgency"

                        SelectCommand="SELECT Agencies.* FROM Agencies" 

                        OnInserted="SqlDataSource1_Inserted"

                        OnInserting="SqlDataSource1_Inserting">

                        <InsertParameters>

                            <asp:Parameter Name="Name" />

                            <asp:Parameter Name="Phone" />

                            <asp:Parameter Name="Fax" />

                            <asp:Parameter Name="Address" />

                            <asp:Parameter Name="City" />

                            <asp:Parameter Name="State" />

                            <asp:Parameter Name="Zip" />

                            <asp:Parameter Name="CustomForms" />

                            <asp:Parameter Name="OnlineAccess" />

                            <asp:Parameter Name="AgencyID" type="String"/>

                        </InsertParameters>

                    </asp:SqlDataSource>

                    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

                        InsertCommand="INSERT INTO UserAgencies(AgencyID, UserID) VALUES (@AgencyID, @UserID)" 

                        SelectCommand="SELECT UserAgencies.* FROM UserAgencies">

                        <InsertParameters>

                        </InsertParameters>

                    </asp:SqlDataSource>

                    <br />

                </ContentTemplate>

            </asp:RoleGroup>

        </RoleGroups>

        <LoggedInTemplate>

        You are not logged in with sufficient permission to manage items.<br />

        </LoggedInTemplate>

        <AnonymousTemplate>

            You are not logged in.&nbsp; <p>Please <a href="login.aspx">login</a>.</p>

        </AnonymousTemplate>

    </asp:LoginView>
 

CS:
 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.Common;

using System.Data.Sql;

using System.Data.Query;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Configuration;

using System.Web.Security;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;
 

public partial class AddAgency : System.Web.UI.Page

{

    protected void Label1Load(object sender, EventArgs e)

    {

        Label label = (Label)sender;

        Label Label1 = (Label)label.FindControl("Label1");
 

        if (Page.IsPostBack)

        {

            Label1.Visible = true;

        }

        else

        {

            Label1.Visible = false;
 

        }

    }
 

    protected void Page_Load(object sender, EventArgs e)

    {
 

    }
 

    protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)

    {

        SqlParameter AgencyID = new SqlParameter("@AgencyID", SqlDbType.UniqueIdentifier);

        AgencyID.Direction = ParameterDirection.Output;

        e.Command.Parameters.Add(AgencyID);

    }

    

    protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)

    {

        DbCommand command = e.Command;
 

        SqlDataSource sq2 = (SqlDataSource)LoginView1.FindControl("SqlDataSource2");
 

        MembershipUser user = Membership.GetUser();
 

        object id = user.ProviderUserKey;
 

        if (Page.IsPostBack)

        {

            sq2.InsertParameters.Add("AgencyID", command.Parameters["@AgencyID"].Value.ToString());

            sq2.InsertParameters.Add("UserID", id.ToString());

            sq2.Insert();

        }

    }

}

Open in new window

im1.JPG
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24825379
For troubleshooting how about not doing any insert at this time and see if value is returned like below:
protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {  //This label displayed the correct GUID
       String s = e.Command.Parameters["@UserId"].Value.ToString();
        Response.Write(s);
//Do not perform any sql insert
    }
set breakpoint at Response.Write(s) and see what value is assigned to 's'.
In the mean while let me have a look at your code.
0
 
LVL 41

Accepted Solution

by:
guru_sami earned 500 total points
ID: 24825615
alrite time to wrap up:
remove this:    
 <asp:Parameter Name="AgencyID" type="String"/>

from:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
                        InsertCommandType="StoredProcedure"
                        InsertCommand="NewAgency"
                        SelectCommand="SELECT Agencies.* FROM Agencies"
                        OnInserted="SqlDataSource1_Inserted"
                        OnInserting="SqlDataSource1_Inserting">
                        <InsertParameters>
                            <asp:Parameter Name="Name" />
                            <asp:Parameter Name="Phone" />
                            <asp:Parameter Name="Fax" />
                            <asp:Parameter Name="Address" />
                            <asp:Parameter Name="City" />
                            <asp:Parameter Name="State" />
                            <asp:Parameter Name="Zip" />
                            <asp:Parameter Name="CustomForms" />
                            <asp:Parameter Name="OnlineAccess" />
                            <asp:Parameter Name="AgencyID" type="String"/>
                        </InsertParameters>
                    </asp:SqlDataSource>
0
 

Author Comment

by:GY1680
ID: 24825738
Actually, removing that one line from the InsertParameters did the trick.  Worked like a charm now.  Attached final ASPX and CS just for reference.

Thank you, you really are a guru. :)
ASPX:
 

    <asp:LoginView ID="LoginView1" runat="server">

        <RoleGroups>

            <asp:RoleGroup Roles="Administrators">

                <ContentTemplate>

                    <h3><asp:Label ID="Label1" ForeColor="Red" OnLoad="Label1Load" runat="server" Visible="false">Record Added Successfully.</asp:Label></h3>

                    <h3>To add a new Agency - enter the data below and click "Insert":</h3>

                    <asp:FormView ID="FormView1" DefaultMode="Insert" runat="server" 

                        DataKeyNames="AgencyID" DataSourceID="SqlDataSource1">

                        <EditItemTemplate>

                            AgencyID:

                            <asp:Label ID="AgencyIDLabel1" runat="server" Text='<%# Eval("AgencyID") %>' />

                            <br />

                            Name:

                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />

                            <br />

                            Phone:

                            <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                            <br />

                            Fax:

                            <asp:TextBox ID="FaxTextBox" runat="server" Text='<%# Bind("Fax") %>' />

                            <br />

                            Address:

                            <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />

                            <br />

                            City:

                            <asp:TextBox ID="CityTextBox" runat="server" Text='<%# Bind("City") %>' />

                            <br />

                            State:

                            <asp:TextBox ID="StateTextBox" runat="server" 

                                Text='<%# Bind("State") %>' />

                            <br />

                            Zip:

                            <asp:TextBox ID="ZipTextBox" runat="server" 

                                Text='<%# Bind("Zip") %>' />

                            <br />

                            CustomForms:

                            <asp:TextBox ID="CustomFormsTextBox" runat="server" 

                                Text='<%# Bind("CustomForms") %>' />

                            <br />

                            OnlineAccess:

                            <asp:TextBox ID="OnlineAccessTextBox" runat="server" 

                                Text='<%# Bind("OnlineAccess") %>' />

                            <br />

                            <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 

                                CommandName="Update" Text="Update" />

                            &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" 

                                CausesValidation="False" CommandName="Cancel" Text="Cancel" />

                        </EditItemTemplate>

                        <InsertItemTemplate>

                            <table>

                            <tr><td>

                            Name:

                            </td><td>

                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />

                            </td></tr><tr><td>

                            Phone:

                            </td><td>

                            <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                            </td></tr><tr><td>

                            Fax:

                            </td><td>

                            <asp:TextBox ID="FaxTextBox" runat="server" Text='<%# Bind("Fax") %>' />

                            </td></tr><tr><td>

                            Address:

                            </td><td>

                            <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />

                            </td></tr><tr><td>

                            City:

                            </td><td>

                            <asp:TextBox ID="CityTextBox" runat="server" Text='<%# Bind("City") %>' />

                            </td></tr><tr><td>

                            State:

                            </td><td>

                            <asp:TextBox ID="StateTextBox" runat="server" Text='<%# Bind("State") %>' />

                            </td></tr><tr><td>

                            Zip:

                            </td><td>

                            <asp:TextBox ID="ZipTextBox" runat="server" Text='<%# Bind("Zip") %>' />

                            </td></tr><tr><td>

                            CustomForms:

                            </td><td>

                            <asp:DropDownList ID="DropDownList1" runat="server" 

                                SelectedValue='<%# Bind("CustomForms") %>'>

                                <asp:ListItem>Yes</asp:ListItem>

                                <asp:ListItem>No</asp:ListItem>

                            </asp:DropDownList>

                            </td></tr><tr><td>

                            OnlineAccess:

                            </td><td>

                            <asp:DropDownList ID="DropDownList2" runat="server" 

                                SelectedValue='<%# Bind("OnlineAccess") %>'>

                                <asp:ListItem>Yes</asp:ListItem>

                                <asp:ListItem>No</asp:ListItem>

                            </asp:DropDownList>

                            </td></tr>

                            <tr><td></td><td>

                            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 

                                CommandName="Insert" Text="Insert" />

                                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" 

                                Text="Cancel" />

                            </td></tr>

                            </table>

                        </InsertItemTemplate>

                        <ItemTemplate>

                            AgencyID:

                            <asp:Label ID="AgencyIDLabel" runat="server" Text='<%# Eval("AgencyID") %>' />

                            <br />

                            Name:

                            <asp:Label ID="NameLabel" runat="server" Text='<%# Bind("Name") %>' />

                            <br />

                            Phone:

                            <asp:Label ID="PhoneLabel" runat="server" Text='<%# Bind("Phone") %>' />

                            <br />

                            Fax:

                            <asp:Label ID="FaxLabel" runat="server" Text='<%# Bind("Fax") %>' />

                            <br />

                            Address:

                            <asp:Label ID="AddressLabel" runat="server" Text='<%# Bind("Address") %>' />

                            <br />

                            City:

                            <asp:Label ID="CityLabel" runat="server" Text='<%# Bind("City") %>' />

                            <br />

                            State:

                            <asp:Label ID="StateLabel" runat="server" 

                                Text='<%# Bind("State") %>' />

                            <br />

                            Zip:

                            <asp:Label ID="ZipLabel" runat="server" 

                                Text='<%# Bind("Zip") %>' />

                            <br />

                            CustomForms:

                            <asp:Label ID="CustomFormsLabel" runat="server" 

                                Text='<%# Bind("CustomForms") %>' />

                            <br />

                            OnlineAccess:

                            <asp:Label ID="OnlineAccessLabel" runat="server" 

                                Text='<%# Bind("OnlineAccess") %>' />

                            <br />

                            <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" 

                                CommandName="New" Text="New" />

                        </ItemTemplate>

                    </asp:FormView>

                    <asp:SqlDataSource ID="SqlDataSource1" runat="server"

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

                        InsertCommandType="StoredProcedure"

                        InsertCommand="NewAgency"

                        SelectCommand="SELECT Agencies.* FROM Agencies" 

                        OnInserted="SqlDataSource1_Inserted"

                        OnInserting="SqlDataSource1_Inserting">

                        <InsertParameters>

                            <asp:Parameter Name="Name" />

                            <asp:Parameter Name="Phone" />

                            <asp:Parameter Name="Fax" />

                            <asp:Parameter Name="Address" />

                            <asp:Parameter Name="City" />

                            <asp:Parameter Name="State" />

                            <asp:Parameter Name="Zip" />

                            <asp:Parameter Name="CustomForms" />

                            <asp:Parameter Name="OnlineAccess" />

                        </InsertParameters>

                    </asp:SqlDataSource>

                    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 

                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

                        InsertCommand="INSERT INTO UserAgencies(AgencyID, UserID) VALUES (@AgencyID, @UserID)" 

                        SelectCommand="SELECT UserAgencies.* FROM UserAgencies">

                        <InsertParameters>

                        </InsertParameters>

                    </asp:SqlDataSource>

                    <br />

                </ContentTemplate>

            </asp:RoleGroup>

        </RoleGroups>

        <LoggedInTemplate>

        You are not logged in with sufficient permission to manage items.<br />

        </LoggedInTemplate>

        <AnonymousTemplate>

            You are not logged in.&nbsp; <p>Please <a href="login.aspx">login</a>.</p>

        </AnonymousTemplate>

    </asp:LoginView>
 

CS:
 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.Common;

using System.Data.Sql;

using System.Data.Query;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Configuration;

using System.Web.Security;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;
 

public partial class AddAgency : System.Web.UI.Page

{

    protected void Label1Load(object sender, EventArgs e)

    {

        Label label = (Label)sender;

        Label Label1 = (Label)label.FindControl("Label1");
 

        if (Page.IsPostBack)

        {

            Label1.Visible = true;

        }

        else

        {

            Label1.Visible = false;
 

        }

    }
 

    protected void Page_Load(object sender, EventArgs e)

    {
 

    }
 

    protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)

    {

        SqlParameter AgencyID = new SqlParameter("@AgencyID", SqlDbType.UniqueIdentifier);

        AgencyID.Direction = ParameterDirection.Output;

        e.Command.Parameters.Add(AgencyID);

    }

    

    protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)

    {

        DbCommand command = e.Command;
 

        SqlDataSource sq2 = (SqlDataSource)LoginView1.FindControl("SqlDataSource2");
 

        MembershipUser user = Membership.GetUser();
 

        object id = user.ProviderUserKey;
 

        if (Page.IsPostBack)

        {

            sq2.InsertParameters.Add("AgencyID", command.Parameters["@AgencyID"].Value.ToString());

            sq2.InsertParameters.Add("UserID", id.ToString());

            sq2.Insert();

        }

    }

}

Open in new window

0
 

Author Closing Comment

by:GY1680
ID: 31600701
HUGE help.  guru-sami is awesome.  Great work.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
C# Error - Add Failed 12 53
Script to randomly create characters in MS SQL 6 18
IN with @variable 5 20
BULK INSERT most recent CSV 19 21
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now