?
Solved

Insert problem with sqldatasource and stored procedure

Posted on 2006-11-24
12
Medium Priority
?
385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 2000 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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

764 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