?
Solved

Stored procedure not working with an ASP.Net page

Posted on 2012-03-18
9
Medium Priority
?
370 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

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!

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…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
Suggested Courses
Course of the Month8 days, 9 hours left to enroll

621 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