SQL Server 2005 vs SQL Server 2008

[Elmo_loves_you]

Hi.

I have previously designed an ASP.NET system that interacted with SQL Server 2005. I now want to design a desktop application using Visual Studio 2008 that interacts with an SQL database. I had planned to use the same sort of coding to implement this new system.

For example - I have stored procedures that successfully add new data to the database tables. Previously I used the following code which works but similar code doesnt work in 2008.

SqlConnection myConnection = new SqlConnection("THE LONG CONNECTION STRING");
SqlDataReader myReader;
SqlCommand myCommand;

public void getCustomerDetails()
{
            //Declare myCommand properties
            myCommand = new SqlCommand("CreateCustomer", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            //Open connection to the database
            myConnection.Open();
            
            //Declare and Initialise the stored procedure parameters
            myCommand.Parameters.Add(new SqlParameter("@companyName", SqlDbType.VarChar, 50, "CompanyName"));
            myCommand.Parameters["@companyName"].Value = txt_CompanyName.Text;

            myCommand.Parameters.Add(new SqlParameter("@forename", SqlDbType.VarChar, 50, "ContactForename"));
            myCommand.Parameters["@forename"].Value = txt_ContactForename.Text;

            myCommand.Parameters.Add(new SqlParameter("@surname", SqlDbType.VarChar, 50, "ContactSurname"));
            myCommand.Parameters["@surname"].Value = txt_ContactSurname.Text;

            myCommand.Parameters.Add(new SqlParameter("@phoneNo", SqlDbType.NVarChar,50,"PhoneNo"));
            myCommand.Parameters["@phoneNo"].Value = txt_PhoneNo.Text;

            myCommand.Parameters.Add(new SqlParameter("@mobileNo", SqlDbType.NVarChar,50,"MobileNo"));
            myCommand.Parameters["@mobileNo"].Value = txt_MobileNo.Text;

            myCommand.Parameters.Add(new SqlParameter("@altNo", SqlDbType.NVarChar, 50, "AlternativeNo"));
            myCommand.Parameters["@altNo"].Value = txt_AlternativeNo.Text;

            //Close and dispose of open properties
            myCommand.ExecuteReader();  //**** ERROR here ******
            myCommand.Dispose();
            myConnection.Close();
}

In debug mode it throws an InvalidOperationException at the line indicated above ****

"ExecuteReader requires an open and available Connection. The connections current state is closed."

Although when I step through the code it states that the connection is open.

Has anyone got any ideas??

[Ramy Mahrous]

Are you sure that the connection string is correct?

[Elmo_loves_you]

Yes - the connection string is correct... I just typed that in for Brevity.

Stepping through the code indicates that the connection is open when it should be. I am trying to make a windows forms application and have written stored procedures that successfully insert / update the database.

I have used this code before for an ASP.NET website and it worked perfectly (2005) but now I want to use the same method of inserting/updating except that it is for (2008) desktop application

[Ramy Mahrous]

working on Windows Vista? maybe you need to run this application as administrator.. really I don't know but I give some assumptions.. it's really strange!

[Elmo_loves_you]

Perhaps you could guide me on how to use datasets and adapters with stored procedures :)

[Ramy Mahrous]

OK, just give me time to do it I am kinda busy right now..

[Ramy Mahrous]

The answer in this post http://fci-h.blogspot.com/2008/08/how-to-use-dataset-and-adapters-with.html