locked
Web controls not picking up data from sql datasource RRS feed

  • Question

  • User-1165400470 posted

    Hi

    I have three controls a textbox, a drop down and a check box on a webform as below;

    <asp:TextBox ID="txtFirstName" runat="server" ></asp:TextBox>
    
    <asp:DropDownList ID="txtGender" runat="server" >  
       <asp:ListItem Selected="True">Male</asp:ListItem>  
       <asp:ListItem>Female</asp:ListItem>  
    </asp:DropDownList>
    
    <asp:CheckBox ID="chkTaxStatementA" runat="server" Text="Tax Statement" />

    My sql data source looks like this;

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"  
     
        SelectCommand="SELECT [ApplicantID], [first_name], [Gender], [TaxStatementA] FROM [Applicants] " 
        UpdateCommand="UPDATE [Applicants] SET [first_name] = @first_name, [Gender] = @Gender, [TaxStatementA] = @TaxStatementA WHERE [ApplicantID] = @ApplicantID"  
        onselecting="SqlDataSource1_Selecting" > 
     
        <SelectParameters> 
            <asp:ControlParameter Name="ApplicantID" PropertyName="Text" ControlID="txtApplicantID" Type="String" /> 
            <asp:ControlParameter Name="first_name" PropertyName="Text" ControlID="txtFirstName" Type="String" /> 
            <asp:ControlParameter Name="Gender" PropertyName="Text" ControlID="txtGender" Type="String" /> 
            <asp:ControlParameter Name="TaxStatementA" PropertyName="Checked" ControlID="chkStatementA" Type="Boolean" /> 
        </SelectParameters> 
     
        <UpdateParameters> 
            <asp:ControlParameter Name="ApplicantID" PropertyName="Text" ControlID="txtApplicantID" Type="String" /> 
            <asp:ControlParameter Name="first_name" PropertyName="Text" ControlID="txtFirstName" Type="String" /> 
            <asp:ControlParameter Name="Gender" PropertyName="Text" ControlID="txtGender" Type="String" /> 
            <asp:ControlParameter Name="TaxStatementA" PropertyName="Checked" ControlID="chkStatementA" Type="Boolean" /> 
        </UpdateParameters> 
     
    </asp:SqlDataSource>

    Problem is that the data from underlying table (Applicants) is not loaded into controls when WebForm is loaded. Also on when on press of Submit button sqldatsource.Update() is executed no data is saved to backend sql server.

    What can I do to fix or debug this issue?

    Many thanks.

    Regards

    Wednesday, March 16, 2011 9:27 PM

Answers

  • User77042963 posted

    You don't need to call the datasource select in the page load event. In the case you need to do this, you need to wrap the code inside a !IsPostBack block.

     

     protected void Page_Load(object sender, EventArgs e) 
       
    { 
            //
    SqlDataSource1.Select(DataSourceSelectArguments.Empty); 
       
    } 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 18, 2011 10:04 AM

All replies

  • User77042963 posted

    You can try to use a datacontrol to hook up to your data source without the controlparameters for select/updata parameters. For example, DetailsView/FormView.

    Wednesday, March 16, 2011 11:22 PM
  • User-1165400470 posted

    Hi

    Some progress on this.

    I noticed that OnSelecting was firing but not OnSelected. I set the sqldatasource’s CancelSelectonNullParameter to false and now OnSelected is fining. So my first question is when there is no WHERE clause in my SELECT, what difference does it make that any parameter is null?

    Now that OnSelected is firing I have checked e.AffectedRows.ToString() and its values is 1 so one row is being returned. Problem is that web controls are still not being updated from data returned from SELECT. What could be wrong here?

    Many Thanks

    Regards

    Thursday, March 17, 2011 10:40 AM
  • User-1165400470 posted

    Hi Limno

    Adding a gridview and assigning the sqldatasource to it works fine. How can I get the data into web controls? I would like to avoid formview if I can in my application.

    Thanks

    Regards

    Thursday, March 17, 2011 3:40 PM
  • User77042963 posted

    When you use the IDE designer to hook your datacontrol with your SQLDatasource on your page, the control should connect to the data element by itself. If you don't see them, you may need to click on Refresh Schema from the property window to refresh your datacontrol.

    Thursday, March 17, 2011 5:12 PM
  • User-1165400470 posted

    Hi

    This is a very simple sample I am working with;

    <%@ Page Language="C#" %>
    
    <script runat="server">
        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlDataSource1.Update();
        }
    
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlDataSource1.Select(DataSourceSelectArguments.Empty);
        }
    </script>
    <html>
    <head id="Head1" runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:TextBox ID="txtApplicantID" runat="server"></asp:TextBox>
            <br />
            <asp:TextBox ID="txtSurname" runat="server"></asp:TextBox>
        </div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
            SelectCommand="SELECT [ApplicantID], [last_name] FROM [Applicants]" UpdateCommand="UPDATE [Applicants] SET [last_name] = @last_name WHERE [ApplicantID] = @ApplicantID">
            <UpdateParameters>
                <asp:ControlParameter Name="ApplicantID" Type="Int32" ControlID="txtApplicantID"
                    PropertyName="Text" />
                <asp:ControlParameter Name="last_name" Type="String" ControlID="txtSurname" PropertyName="Text" />
            </UpdateParameters>
        </asp:SqlDataSource>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
        </form>
    </body>
    </html>

    Problem is the same; the two controls neither get the data from nor update it back to the backend table. What could be wrong with such a simple code?

    The backend table is defined as below.

    CREATE TABLE [dbo].[Applicants](
    	[ApplicantID] [int] IDENTITY(1,1) NOT NULL,
    	[last_name] [nvarchar](255) NULL,
     CONSTRAINT [PK_Applicants] PRIMARY KEY CLUSTERED 
    (
    	[ApplicantID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    Many Thanks

    Regards

    Thursday, March 17, 2011 9:52 PM
  • User77042963 posted

    You don't need to call the datasource select in the page load event. In the case you need to do this, you need to wrap the code inside a !IsPostBack block.

     

     protected void Page_Load(object sender, EventArgs e) 
       
    { 
            //
    SqlDataSource1.Select(DataSourceSelectArguments.Empty); 
       
    } 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 18, 2011 10:04 AM