Solved

Stored procedure not working with an ASP.Net page

Posted on 2012-03-18
9
358 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
[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
  • 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
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 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

Technology Partners: 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!

Question has a verified solution.

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

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…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 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