Update sqldatasource with ControlParameter and Stored Procedure : The Official Microsoft ASP.NET Forums

Reply

yanivz

Member

4 Points

9 Posts

Update sqldatasource with ControlParameter and Stored Procedure

May 04, 2012 01:25 AM|LINK

hello i have a form with some controls (text box,select box and radio button) :

<div class="rowElem" ><label>email:</label><asp:TextBox ID="txtEmail" ClientIDMode="Static" runat="server" /></div>
        <div class="rowElem"><label>user name:</label><asp:TextBox ID="txtUserName" ClientIDMode="Static"  CssClass="input-rtl" MaxLength="30" runat="server" /> <span id="UserNameMsg" runat="server" class="msg3">*&nbsp;please write username</span></div>
		<div class="rowElem"><label>first name:</label><asp:TextBox ID="txtFirstName" ClientIDMode="Static" CssClass="input-rtl required" maxlength="30" minlength="2" runat="server" /><span id="FirstNameMsg" runat="server" class="msg3">*&nbsp;please write first name</span></div>
        <div class="rowElem"><label>last name:</label><asp:TextBox ID="txtLastName" ClientIDMode="Static" CssClass="input-rtl required" maxlength="30" minlength="2" runat="server" /><span id="LastNameMsg" runat="server" class="msg3">*&nbsp;please write first name</span></div>

i have sqldatasource with those controls bound to it:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"  ConnectionString="<%$ConnectionStrings:SqlConnection %>"  UpdateCommand="spBasics_UPDATE"  ProviderName="<%$ ConnectionStrings:SqlConnection.ProviderName %>" UpdateCommandType="StoredProcedure">

          <UpdateParameters>

            <asp:ControlParameter Name="Email"  ControlID="txtEmail"  DefaultValue="0"  PropertyName="Text"          Type="String" />

            <asp:ControlParameter Name="UserName"  ControlID="txtUserName"  DefaultValue="0"  PropertyName="Text"          Type="String" />
            <asp:ControlParameter Name="FirstName" ControlID="txtFirstName" DefaultValue="0"  PropertyName="Text"          Type="String" />
            <asp:ControlParameter Name="LastName"  ControlID="txtLastName"  DefaultValue="0"  PropertyName="Text"          Type="String"/>
            <asp:ControlParameter Name="Byear"     ControlID="ddlyear"      DefaultValue="0"  PropertyName="SelectedValue" Type="String"/>
            <asp:ControlParameter Name="Bmonth"    ControlID="ddlmonth"     DefaultValue="0"  PropertyName="SelectedValue" Type="String"/>
            <asp:ControlParameter Name="Bday"      ControlID="ddlday"       DefaultValue="0"  PropertyName="SelectedValue" Type="String"/>
            <asp:ControlParameter Name="Sex"       ControlID="rdoSex"       DefaultValue="0"  PropertyName="SelectedValue" Type="Boolean"/>  
            <asp:ControlParameter Name="City"      ControlID="ddlcity"      DefaultValue="0"  PropertyName="SelectedValue" Type="Int32"/>
            <asp:ControlParameter Name="Status"    ControlID="ddlstatus"    DefaultValue="0"  PropertyName="SelectedValue" Type="Int32"/>
            <asp:ControlParameter Name="Children"  ControlID="ddlchildern"  DefaultValue="0"  PropertyName="SelectedValue" Type="Int32"/>
        </UpdateParameters>

</asp:SqlDataSource>

on post back the data goes to Stored Procedure that is declared to the sqldatasourde (UpdateCommand="spBasics_UPDATE" )

ALTER PROCEDURE [dbo].[spBasics_UPDATE]
@Email nvarchar(50),
@FirstName nvarchar(50), 
@LastName nvarchar(50), 
@UserName nvarchar(50), 
@Sex bit, 
@SexInterest bit, 
@Byear nvarchar(50), 
@Bmonth nvarchar(50),
@Bday nvarchar(50), 
@Status int,
@Children int,
@City int 
AS
DECLARE @ID nvarchar(50)
SET @ID = 'SELECT ID FROM Users WHERE Email = ''' + @Email + ''''	   
UPDATE Users SET 
FirstName=SUBSTRING(LTRIM(RTRIM(@FirstName)),0,50), 
LastName=SUBSTRING(LTRIM(RTRIM(@LastName)),0,50), 
UserName=SUBSTRING(FirstName+@ID,0,50), 
City=@City, 
Sex=@Sex, 
SexInterest=@SexInterest, 
Byear=@Byear, 
Bmonth=@Bmonth, 
Bday=@Bday, 
Status=@Status, 
Children=@Children

FROM         Users 
WHERE Email = @Email

i dont know how to debug it...

why the i cant save the data?