Solved

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

Posted on 2009-07-07
24
519 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

713 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