Solved

Stored procedure not working with an ASP.Net page

Posted on 2012-03-18
9
356 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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