Solved

Stored procedure not working with an ASP.Net page

Posted on 2012-03-18
9
359 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Suggested Courses

737 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