Solved

Insert problem with sqldatasource and stored procedure

Posted on 2006-11-24
12
349 Views
Last Modified: 2007-12-19
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
Comment
Question by:claracruz
12 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18008240
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
 
LVL 4

Author Comment

by:claracruz
ID: 18008260
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18008278
Yes. Run SQL Profiler against the database - you can then see all commands executed against the server.
0
 
LVL 4

Author Comment

by:claracruz
ID: 18008280
how do I "Run SQL Profiler against the database". am new to this
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18008310
Are you using SQL Server? If so, what version? (2000, SQL Express, etc)
0
 
LVL 4

Author Comment

by:claracruz
ID: 18008329
SQL Express with vwd
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:Nightman
ID: 18008352
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
 
LVL 4

Author Comment

by:claracruz
ID: 18008433
I do not have Performance Tools in my Microsoft SQL Server 2005
0
 
LVL 4

Author Comment

by:claracruz
ID: 18008450
also I just found there is no SQL Profiler with Microsoft SQL Server 2005 Express.

Any thing else I can try
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18009167
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
 
LVL 7

Expert Comment

by:vbandaru
ID: 18009400
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
 
LVL 12

Accepted Solution

by:
sandip132 earned 500 total points
ID: 18010139
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now