Post
Ask Question

Tip related to sub containers and SqlDataSource


This article provides some tips when working with Master page or SqlDataSource insert function in VS 2005.

For better understanding please download application files attached with this posting, whose reference is used through this posting. 

Today I was doing some practice work with CreateUserControl, and in the customization process I thought to add a UserControl (BillingAddressUserControl.ascx) to collect data for billing address. Very simple one, just 4 values Address, city, state, and zip from user.

It is OK, as we have CreateUserWizard as Wizard control u can place your steps very easily. So I placed my control as first step then it will follow normal steps as defined by this control.

To see working please open RegisterUsers.aspx, switch to HTML of the page, you will see

<asp:CreateUserWizard ID="CreateUserWizard1" runat="server" OnCreatedUser="CreateUserWizard1_CreatedUser"> 

            <WizardSteps> 

                  <asp:WizardStep ID="BillingWizard" runat="server"> 

                       <Bill:BillingInfo ID="BillingInfo1" runat="server" EnableViewState="true" /> 

                  </asp:WizardStep> 

                  <asp:CreateUserWizardStep runat="server" ID="CreateUserWizardStep" Title="UserInfo"> 

                  </asp:CreateUserWizardStep> 

                  <asp:CompleteWizardStep ID="CompleteWizardStep1" runat="server"> 

                  </asp:CompleteWizardStep> 

            </WizardSteps> 

</asp:CreateUserWizard>

I intentionally made this node bold you can see this registration process need to go through BillingInfo before filling information for CreateUserWizard. 

[ For the initial setup use AspNetSqlReg exe provided with framework, open VS 2005 command prompt and type this exe's name, you will be switched to a UI for configuration] 

Now I used simple business logic, when ever a user is created using CreateUserWizard, I want to store billing information in a separate Table but having in same Database created by Membership Configuration Wizard, no doubt you can store it any other place.

CREATE TABLE [dbo].[ExtraInfo]
(   
      [UserID] [uniqueidentifier] NOT NULL, 

      [Address] [nvarchar](50), 

      [City] [nvarchar](50)  , 

      [State] [nvarchar](50)  , 

      [Zip] [nvarchar](50) , 

      [Contact] [nchar](10)  

)     ON [PRIMARY] 

 

GO 

 

ALTER TABLE [dbo].[Extrainfo]  WITH CHECK ADD FOREIGN KEY([UserID]) 

REFERENCES [dbo].[aspnet_Users] ([UserId])
 

GO

Now you have :

  1. Database ready to hold extra information (Billing info.) +  Membership information.
  2. Page RegisterUsers.aspx which contains CreateUSerWizard and a SqlDataSource Object to insert values in ExtraInfo Table.
  3. BillingAddressUserControl.ascx - User Control for capturing billing information.

Ideally we are done. 

Now there are two things that will frustrate you 

  1. When you bind the values for the insert parameter of SqlDataSource to insert into extraInfo table. As these values are coming from a user control (BillingAddressUserControl) means definitely you are going to use

    <asp:ControlParameter Name="Address" ControlID="BillingAddress" Type="String" PropertyName="Text" Direction="Input" />

    but the thing is that, this control must belong to same container where you have sqlDataSource Control, form generally, but this time this BillingAddress and all others are placed inside another container (UserControl). Means Container inside a container. And if you run the code, you will get an error related to control not find as this by default search into this container only, means page(better say form). So in order to search into other container place full path of the control means like this container1$container2$Container3$controlID.  As it is used in my HTML binding. So the correct way to use it is.

    <asp:ControlParameter Name="Address" ControlID="CreateUserWizard1$BillingInfo1$BillingAddress" Type="String" PropertyName="Text" Direction="Input" />

    Note this is very helpful when you are working with master pages and accessing cross container elements. Use full signature of the control.

  2. This problem will be encountered at the time of binding values to the insert parameter.

    See this 

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ 
      ConnectionStrings:OnNetDBConnectionString %>    "

            SelectCommand="SELECT * FROM [Extrainfo]" InsertCommand="insert into ExtraInfo(UserID , Address , City ,      
            State ,Zip) values (UserID ,  Address, City , State , Zip ) ">

            <InsertParameters>

                <asp:ControlParameter Name="Address" ControlID="CreateUserWizard1$BillingInfo1$BillingAddress"

                    Type="String" PropertyName="Text" Direction="Input" />

                    .. ..

                <asp:ControlParameter Name="Zip" ControlID="CreateUserWizard1$BillingInfo1$BillingZip"

                    PropertyName="Text" Type="string" />

            </InsertParameters>

        </asp:SqlDataSource>


    Looks very simple. You wrote a insert query and will bind all values to corresponding controls which are placed any where in the page container. So to bind with controls we will use like this:

    <asp:ControlParameter Name="Zip" ... />

    Means this will place the value for Zip in the query at the time of execution. Simple, but not really when you will run this code you will get a very strange error like this: 

    The name "Zip" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

    Let's revisit the insert command text

    InsertCommand ="insert into ExtraInfo(UserID , Address , City , State ,Zip) values (UserID ,  Address, City , State , Zip)"> 

    This looks like userid, address, City, State and Zip are values instead of variables. 

    To make it proper just use SQL Server variables, i.e. place @ sign before of each variable which make them behave like parameters instead of values.

    Means correct text is 

    InsertCommand = "insert into ExtraInfo(UserID , Address , City , State ,Zip) values (@UserID ,  @Address, @City, @State , @Zip ) "> 

    You can even fill these variables from any other source may be using a variable as is the case with @userID in this code.

    Means 

    SqlDataSource.InsertParameters.Add("UserID",TypeCode.String ,Guid.ToString());

    Where Guid is an object of Object class.

Please go through code placed inside  CreateUserWizard1_CreatedUser event handler and Web.Config files, food for thinking !!!

To run this application you need to change connection string in web.config and also the DataSource.

 Go just do it,


Similar Articles