Solved

Stored procedure not working with an ASP.Net page

Posted on 2012-03-18
9
357 Views
Last Modified: 2012-03-23
I have a stored procedure I'm using in an asp.net page, with C# code behind. When I click the update button, the page refreshes and I do not get an error, but the update does not work. When I debug it, I get this error:
ServerVersion = 'connection.ServerVersion' threw an exception of type 'System.InvalidOperationException'
I do not know what it means, and I can;t seem to get it straightened out. Any help is appreciated. I'm using VS 2008 and SQL Server 2005. My code is below:

Update procedure in code behind:
   protected void Update_Click(object sender, EventArgs e)
    {
      TextBox HomePhoneTextBox = (TextBox)frmProfile.FindControl("HomePhoneTextBox");
        TextBox TextBox1 = (TextBox)frmProfile.FindControl("TextBox1");
        TextBox TextBox2 = (TextBox)frmProfile.FindControl("TextBox2");
        TextBox TextBox3 = (TextBox)frmProfile.FindControl("TextBox3");

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
        {
            SqlCommand command = connection.CreateCommand();

            command.CommandText = "UpdateProfile";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@UserName", SessionHelper.GetOwner());
            command.Parameters.AddWithValue("@HomePhone", HomePhoneTextBox.Text);
            command.Parameters.AddWithValue("@WorkPhone", TextBox1.Text);
            command.Parameters.AddWithValue("@PreferredPhone", TextBox2.Text);
            command.Parameters.AddWithValue("@email", TextBox3.Text);
            connection.Open();
            command.ExecuteNonQuery();
        }  

ASP.Net page to update user data:
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="UpdateProfile.aspx.cs" Inherits="Account_UpdateProfile" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="Server">
    <br />
    <table width="80%" align="center" cellpadding="3" border="0">
        <tr>
            <td align="center">
                <asp:Label ID="title" runat="server" Text="Owner Profile" CssClass="title2" />
            </td>
        </tr>
    </table>
    <center>
        <asp:FormView ID="frmProfile" runat="server" DataKeyNames="OwnerID" DefaultMode="Edit">
            <EditItemTemplate>
                <table border="1" cellpadding="6" cellspacing="6">
                    <tr>
                        <td align="left">
                            Name:
                        </td>
                        <td align="left">
                            <asp:Label ID="lblTeam" runat="server" Text='<%# Bind("FName") %>' />
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("LName") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td align="left">
                            Team:
                        </td>
                        <td align="left">
                            <asp:Label ID="TeamNameTextBox" runat="server" Text='<%# Bind("Name2") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td align="left">
                            Home Phone:
                        </td>
                        <td align="left">
                            <asp:TextBox ID="HomePhoneTextBox" runat="server" Text='<%# Bind("HomePhone") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td align="left">
                            Work Phone:
                        </td>
                        <td align="left">
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("WorkPhone") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td align="left">
                            Preferred Phone:
                        </td>
                        <td align="left">
                            <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("PreferredPhone") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td align="left">
                            EMail:
                        </td>
                        <td align="left">
                            <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("EMail") %>' Width="200px" />
                        </td>
                    </tr>
                </table>
            </EditItemTemplate>
            <ItemTemplate>
                <table>
                    <tr>
                        <td>
                            Team:
                        </td>
                        <td>
                            <asp:TextBox ID="TeamNameTextBox" runat="server" Text='<%# Bind("Name2") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Name:
                        </td>
                        <td>
                            <asp:TextBox ID="FNameTextBox" runat="server" Text='<%# Bind("FName") %>' />
                            <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("LName") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Home Phone:
                        </td>
                        <td>
                            <asp:TextBox ID="HomePhoneTextBox" runat="server" Text='<%# Bind("HomePhone") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Work Phone:
                        </td>
                        <td>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("WorkPhone") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Preferred Phone:
                        </td>
                        <td>
                            <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("PreferredPhone") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td>
                            EMail:
                        </td>
                        <td>
                            <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("EMail") %>' />
                        </td>
                    </tr>
                </table>
            </ItemTemplate>
        </asp:FormView>
        <asp:Button ID="Update" runat="server" Text="Update" OnClick="Update_Click" />
    </center>
</asp:Content>
0
Comment
Question by:dodgerfan
  • 3
  • 3
  • 3
9 Comments
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37736199
protected void Update_Click(object sender, EventArgs e)
    {
      TextBox HomePhoneTextBox = (TextBox)frmProfile.FindControl("HomePhoneTextBox");
        TextBox TextBox1 = (TextBox)frmProfile.FindControl("TextBox1");
        TextBox TextBox2 = (TextBox)frmProfile.FindControl("TextBox2");
        TextBox TextBox3 = (TextBox)frmProfile.FindControl("TextBox3");

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
        {
            SqlCommand command = connection.CreateCommand();

            command.CommandText = "UpdateProfile";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@UserName", SessionHelper.GetOwner());
            command.Parameters.AddWithValue("@HomePhone", HomePhoneTextBox.Text);
            command.Parameters.AddWithValue("@WorkPhone", TextBox1.Text);
            command.Parameters.AddWithValue("@PreferredPhone", TextBox2.Text);
            command.Parameters.AddWithValue("@email", TextBox3.Text);
            connection.Open();
            command.ExecuteNonQuery();
        }  

Open in new window

can you able to say on which line you ae getting error?
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37736201
Or try below code
protected void Update_Click(object sender, EventArgs e)
    {
      TextBox HomePhoneTextBox = (TextBox)frmProfile.FindControl("HomePhoneTextBox");
        TextBox TextBox1 = (TextBox)frmProfile.FindControl("TextBox1");
        TextBox TextBox2 = (TextBox)frmProfile.FindControl("TextBox2");
        TextBox TextBox3 = (TextBox)frmProfile.FindControl("TextBox3");

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
        {
            SqlCommand command = new SqlCommand("UpdateProfile",connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@UserName", SessionHelper.GetOwner());
            command.Parameters.AddWithValue("@HomePhone", HomePhoneTextBox.Text);
            command.Parameters.AddWithValue("@WorkPhone", TextBox1.Text);
            command.Parameters.AddWithValue("@PreferredPhone", TextBox2.Text);
            command.Parameters.AddWithValue("@email", TextBox3.Text);
            connection.Open();
            command.ExecuteNonQuery();
        }  

Open in new window


Regards,
nishant
0
 

Author Comment

by:dodgerfan
ID: 37736244
That code gave the same error. I see it in debug when I put a break on the connection.OPen() line.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 14

Expert Comment

by:nishant joshi
ID: 37736266
might having an issue of your connection string or connection to the database.check your connection string @Connection string
0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 37737254
Verify that the connectionstring in the code
using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))

Open in new window

is correct and you could connect to the database with the credentials in the Connection string.
0
 

Author Comment

by:dodgerfan
ID: 37738690
I verified it again, so the connection string is correct. I've used it on other pages to veiw gridviews, etc.
0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 37738741
Do you get exception? If so, please share the details.
0
 

Author Comment

by:dodgerfan
ID: 37740653
I actually do not get an exception when I run it. When I click the Update button, the page refreshes but the changes do not go through. I still see the old values. When I put a break in the code at connection.Open(). When I look throug there I see the exception. Expanding it shows the first exception. When I keep looking, I also see this one:
base {System.Exception} = {"Invalid operation. The connection is closed."}
How could the connection be closed?
0
 
LVL 16

Accepted Solution

by:
Easwaran Paramasivam earned 500 total points
ID: 37740895
Use DataAdapter instead of directly using SQLCommand http://support.microsoft.com/kb/308055.

As soon as the using block ends the connection will be closed automatically. Please do refer http://www.dotnetperls.com/sqlconnection
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

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…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

840 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