• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

Insert problem with sqldatasource and stored procedure

hello experts,

I am Coding in c# and New to stored procedures.

I am using a formview control with sqldatasource and stored procedure. however, my insert stateent isn't woring. It is not doing anything at all.

I have executed the stored procedure via the database and it wors.

My code below;-
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="Recipients.ascx.cs" Inherits="Controls_Recipients" %>
<asp:FormView ID="fvwReceipient" runat="server" DefaultMode="Insert" OnItemInserted="fvwReceipient_ItemInserted" DataSourceID="sdsReceipient">
    <InsertItemTemplate>
               <table cellpadding="2" cellspacing="2" width="100%">
                <tr>
                    <td colspan="3"><asp:TextBox ID="hfdUserName" runat="server" Text='<%# Bind("UserName") %>' />Receipient's Name and location</td>
                </tr>
                <tr>
                    <td style="width: 50%">
                        Recipient's First Name</td>
                    <td style="width: 40%">
                        <asp:TextBox ID="txtRecFirstName" runat="server" SkinID="Wide" Text='<%# Bind("FirstName") %>' ValidationGroup="AddReceipient"></asp:TextBox></td>
                    <td style="width: 10%">
                        <asp:RequiredFieldValidator ID="rfvFirstName" runat="server" ControlToValidate="txtRecFirstName"
                            ErrorMessage="*" SkinID="Wide" ValidationGroup="AddReceipient"></asp:RequiredFieldValidator></td>
                </tr>
                <tr>
                    <td>
                        Recipient's Last Name</td>
                    <td>
                        <asp:TextBox ID="txtRecLastName" Text='<%# Bind("LastName") %>' runat="server" SkinID="Wide" ValidationGroup="AddReceipient"></asp:TextBox></td>
                    <td>
                        <asp:RequiredFieldValidator ID="rfvLastName" runat="server" ControlToValidate="txtRecLastName"
                            ErrorMessage="*" SkinID="Wide" ValidationGroup="AddReceipient"></asp:RequiredFieldValidator></td>
                </tr>
                <tr>
                    <td>
                        City/Town</td>
                    <td>
                        <asp:TextBox ID="txtRecCity" Text='<%# Bind("City") %>' runat="server" SkinID="Wide" ValidationGroup="AddReceipient"></asp:TextBox></td>
                    <td>
                        <asp:RequiredFieldValidator ID="rfvCity" runat="server" ControlToValidate="txtRecCity"
                            ErrorMessage="*" SkinID="Wide" ValidationGroup="AddReceipient"></asp:RequiredFieldValidator></td>
                </tr>
                <tr>
                    <td>
                        State</td>
                    <td>
                        <asp:TextBox ID="txtRecState"  Text='<%# Bind("State") %>' runat="server" SkinID="Wide" ValidationGroup="AddReceipient"></asp:TextBox></td>
                    <td>
                        <asp:RequiredFieldValidator ID="rfvState" runat="server" ErrorMessage="*" SkinID="Wide" ValidationGroup="AddReceipient" ControlToValidate="txtRecState"></asp:RequiredFieldValidator></td>
                </tr>
                <tr>
                    <td>
                        Country</td>
                    <td>
                        <asp:DropDownList ID="ddlRecCountry" AppendDataBoundItems="True" runat="server" SkinID="Wide" ValidationGroup="AddReceipient" CssClass="inputarial" Width="210px">
                         <asp:ListItem Text="Please select one..." Selected="True" />
                         </asp:DropDownList></td>
                    <td>
                        <asp:RequiredFieldValidator ID="rfvCountry" runat="server" ErrorMessage="*" ControlToValidate="ddlRecCountry" SkinID="Wide" ValidationGroup="AddReceipient"></asp:RequiredFieldValidator></td>
                </tr>
                <tr>
                    <td colspan="3">Contact Information</td>
                </tr>
                <tr>
                    <td colspan="3">Contact Information is optional, but if entered,
                    you can elect for us to notify your receipient of a remit
                    by email or SMS message</td>
                </tr>
                <tr>
                    <td valign="top">
                        Notify Receipient</td>
                    <td>
                        <asp:RadioButtonList ID="RadioButtonList1" runat="server" Width="169px">
                            <asp:ListItem Value="0" Selected="True">Option Not Required</asp:ListItem>
                            <asp:ListItem Value="1">By Email</asp:ListItem>
                            <asp:ListItem Value="2">By SMS</asp:ListItem>
                        </asp:RadioButtonList></td>
                    <td>
                    </td>
                </tr>
                <tr>
                    <td>
                        Email</td>
                    <td>
                        <asp:TextBox ID="txtEmail" Text='<%# Bind("Email") %>' SkinID="Wide" runat="server" ValidationGroup="AddReceipient"></asp:TextBox></td>
                    <td>
                        <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtEmail"
                            ErrorMessage="*" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
                            ValidationGroup="AddReceipient"></asp:RegularExpressionValidator></td>
                </tr>
                <tr>
                    <td>
                        Mobile Telephone</td>
                    <td>
                        <asp:TextBox ID="txtMobileTel" Text='<%# Bind("MobileTelephone") %>' SkinID="Wide" runat="server"></asp:TextBox></td>
                    <td>
                    </td>
                </tr>
                <tr>
                   
                    <td class="alighright" colspan="2">
                        <asp:ImageButton ID="imgCancelBens" runat="server" CausesValidation="False" ImageUrl="~/images/button_continue.gif" SkinID="Wide" ValidationGroup="AddReceipient"  CommandName="Cancel" />
                        <asp:ImageButton ID="imgAddBens" runat="server" ImageUrl="~/images/button_continue.gif" SkinID="Wide" ValidationGroup="AddReceipient"  CommandName="Insert" CausesValidation="true"/>
                    </td>
                    <td>&nbsp;</td>
                </tr>
            </table>
    </InsertItemTemplate>
    <EditItemTemplate>
        BeneficiaryID:
        <asp:Label ID="BeneficiaryIDLabel1" runat="server" Text='<%# Eval("BeneficiaryID") %>'>
        </asp:Label><br />
        FirstName:
        <asp:TextBox ID="FirstNameTextBox" runat="server" Text='<%# Bind("FirstName") %>'>
        </asp:TextBox><br />
        LastName:
        <asp:TextBox ID="LastNameTextBox" runat="server" Text='<%# Bind("LastName") %>'>
        </asp:TextBox><br />
        City:
        <asp:TextBox ID="CityTextBox" runat="server" Text='<%# Bind("City") %>'>
        </asp:TextBox><br />
        State:
        <asp:TextBox ID="StateTextBox" runat="server" Text='<%# Bind("State") %>'>
        </asp:TextBox><br />
        Country:
        <asp:TextBox ID="CountryTextBox" runat="server" Text='<%# Bind("Country") %>'>
        </asp:TextBox><br />
        Email:
        <asp:TextBox ID="EmailTextBox" runat="server" Text='<%# Bind("Email") %>'>
        </asp:TextBox><br />
        MobileTelephone:
        <asp:TextBox ID="MobileTelephoneTextBox" runat="server" Text='<%# Bind("MobileTelephone") %>'>
        </asp:TextBox><br />
        NotifyRecipient:
        <asp:TextBox ID="NotifyRecipientTextBox" runat="server" Text='<%# Bind("NotifyRecipient") %>'>
        </asp:TextBox><br />
        UserID:
        <asp:TextBox ID="UserIDTextBox" runat="server" Text='<%# Bind("UserID") %>'>
        </asp:TextBox><br />
        <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
            Text="Update">
        </asp:LinkButton>
        <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
            Text="Cancel">
        </asp:LinkButton>
    </EditItemTemplate>
    <ItemTemplate>
        BeneficiaryID:
        <asp:Label ID="BeneficiaryIDLabel" runat="server" Text='<%# Eval("BeneficiaryID") %>'>
        </asp:Label><br />
        FirstName:
        <asp:Label ID="FirstNameLabel" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label><br />
        LastName:
        <asp:Label ID="LastNameLabel" runat="server" Text='<%# Bind("LastName") %>'></asp:Label><br />
        City:
        <asp:Label ID="CityLabel" runat="server" Text='<%# Bind("City") %>'></asp:Label><br />
        State:
        <asp:Label ID="StateLabel" runat="server" Text='<%# Bind("State") %>'></asp:Label><br />
        Country:
        <asp:Label ID="CountryLabel" runat="server" Text='<%# Bind("Country") %>'></asp:Label><br />
        Email:
        <asp:Label ID="EmailLabel" runat="server" Text='<%# Bind("Email") %>'></asp:Label><br />
        MobileTelephone:
        <asp:Label ID="MobileTelephoneLabel" runat="server" Text='<%# Bind("MobileTelephone") %>'>
        </asp:Label><br />
        NotifyRecipient:
        <asp:Label ID="NotifyRecipientLabel" runat="server" Text='<%# Bind("NotifyRecipient") %>'>
        </asp:Label><br />
        UserID:
        <asp:Label ID="UserIDLabel" runat="server" Text='<%# Bind("UserID") %>'></asp:Label><br />
        <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
            Text="New">
        </asp:LinkButton>
    </ItemTemplate>
</asp:FormView>
&nbsp;<br />
<asp:SqlDataSource ID="sdsReceipient" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    InsertCommand="setBeneficiaryData" InsertCommandType="StoredProcedure" SelectCommand="getBeneficiaryDataByID"
    SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:QueryStringParameter DefaultValue="-1" Name="BeneficiaryID" QueryStringField="BenID"
            Type="String" />
    </SelectParameters>
    <InsertParameters>
        <asp:Parameter Name="UserName" Type="String" />
        <asp:Parameter Name="FirstName" Type="String" />
        <asp:Parameter Name="LastName" Type="String" />
        <asp:Parameter Name="City" Type="String" />
        <asp:Parameter Name="State" Type="String" />
        <asp:Parameter Name="Country" Type="String" />
        <asp:Parameter Name="Email" Type="String" />
        <asp:Parameter Name="MobileTel" Type="String" />
        <asp:Parameter Name="NotifyRecipient" Type="Int32" />
    </InsertParameters>
</asp:SqlDataSource>





STORED PROCEDURE
ALTER proc [dbo].[setBeneficiaryData]
@UserName nvarchar(256) = NULL ,
@FirstName nvarchar(50) = NULL ,
@LastName nvarchar(50) = NULL ,
@City varchar(50) = NULL ,
@State varchar(50) = NULL ,
@Country varchar(50) = NULL ,
@Email varchar(150) = NULL ,
@MobileTel varchar(20)= NULL ,
@NotifyRecipient int = 0

as

declare @UserId nvarchar(150)
select @userId = UserId
from dbo.vw_aspnet_Users
where LoweredUserName = LOWER(@UserName)

declare @BeneficiaryID int
select @BeneficiaryID = BeneficiaryID
from dbo.BeneficiaryTable
where FirstName = @FirstName
and LastName = @LastName
and userId = @userId


if(@BeneficiaryID IS NULL)
BEGIN
insert dbo.BeneficiaryTable
(
UserId, FirstName,LastName,City,State,Country,Email,MobileTelephone,NotifyRecipient)
values
(@UserId,@FirstName,@LastName,@City,@State,@Country,@Email,@MobileTel,@NotifyRecipient)

SELECT @BeneficiaryID = SCOPE_IDENTITY()
END









What am I doing wrong?
0
claracruz
Asked:
claracruz
1 Solution
 
NightmanCTOCommented:
The only thing that I can see is that if there is already a beneficiary of that name, nothing will execute. Check the values of the parameters against the Beneficiary table to determine if there is already one there.
0
 
claracruzAuthor Commented:
no there is nothing in the database at the moment.

Is there a way I can test that when I clic mm insert button, that the stroed procedure is being executed as I don't thin this is happening
0
 
NightmanCTOCommented:
Yes. Run SQL Profiler against the database - you can then see all commands executed against the server.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
claracruzAuthor Commented:
how do I "Run SQL Profiler against the database". am new to this
0
 
NightmanCTOCommented:
Are you using SQL Server? If so, what version? (2000, SQL Express, etc)
0
 
claracruzAuthor Commented:
SQL Express with vwd
0
 
NightmanCTOCommented:
SQL Profiler should be one of the menu items under Microsoft SQL Server 2005-->Performance Tools

Open that up, Connect to your server (the same way you connect from Management Studio), click File-->New Trace and click OK (don't worry about the filtering options on the Events Selection just yet). Then run your web site. You will be able to see all the commands and parameters being passed to the SQL Server.
0
 
claracruzAuthor Commented:
I do not have Performance Tools in my Microsoft SQL Server 2005
0
 
claracruzAuthor Commented:
also I just found there is no SQL Profiler with Microsoft SQL Server 2005 Express.

Any thing else I can try
0
 
NightmanCTOCommented:
Hi claracruz

Sorry, I had to step out for a while. You could looking at setting a breakpoint in your codebehind at the point where it executes the stored procedure and see if:
a. It is getting to that line of code.
b. That there isn't an exception that has been raised out, handled and discarded.

Alternatively, you can download the full version of SQL Express 2005 from MS, which will include the Profiler - apparently the one that comes with the RTM version of VS2005 does not include this.

Regards
Night
0
 
vbandaruCommented:
Here is something you can do. Change the Connection String to something that is invalid and see if it is throwing up a error message. If it still does not I dont think the problem is with the event
0
 
sandip132Commented:
One suggetion, trap all the values from your webform, and execute your stored procedure with these trapped values in query analyzer... with this you can get the idea for which value or at which statement the problem is happening.

regards,
Sandip Gulve.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now