Solved

Insert problem with sqldatasource and stored procedure

Posted on 2006-11-24
12
338 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

13 Experts available now in Live!

Get 1:1 Help Now