Incorrect syntax error when accessing remote stored procedure

Question

Hello again, StackOverflow - I'm still trying to deploy this site, but with every problem I solve, another arises. Anyways - I've set up the database at my hosting to allow remote connections, and it is running Sql Server 2005. On my development machine, I am working with Sql Server 2008.

I've installed the asp.net schema on my hosted database, and have created several users from the ASP.NET web administration interface, as well as tested that the login works. Running the application locally with the remote connection string nets the same results. However - I'm able to run my scripts and generate my tables and stored procedures without errors - but when the site is run I get the following error on all of my .aspx pages that try to access a stored procedure:

Server Error in '/' Application.
Incorrect syntax near 'LoadProfileData'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'LoadProfileData'.

Source Error:

Line 62:             adapter.SelectCommand.Parameters.Add("@ProfessionalName", SqlDbType.VarChar).Value = professionalName;
Line 63:             DataSet profile = new DataSet();
Line 64:             adapter.Fill(profile, "Profile");
Line 65:             return profile;
Line 66:         }

Is this a possible Sql 2005 vs 2008 issue? I'm hoping someone else has seen this issue in a similar scenario and can point me in the right direction. The server is running asp.net 2.0, 3.0 and 3.5 and IIS 7.0.

Stored Procedure:

-- =============================================
-- Create date: <July 2010>
-- Description: <Create default Professional Profile>
-- =============================================
Drop procedure CreateDefaultProfile
GO
Create procedure CreateDefaultProfile(@UserName varchar(256))
as
    declare @ProfessionalID uniqueidentifier
    set @ProfessionalID = (select UserId from aspnet_Users where UserName = @UserName)


    declare @email varchar(256)
    set @email = (select Email from aspnet_Membership where UserId = @ProfessionalID)

    insert into Professional(ProfessionalID, Name, Email, Phone, TypeID, DisplayPictureUrl, ProfileHeader, ProfileSubHeader, ProfileContent, ServicesHeader, ServicesContent)
    values (@ProfessionalID, '', @email, '', '', 'css/img/profilepicture_default.jpg', '', '', '', '', '')


    GO

    -- exec CreateDefaultProfile 'Mounir'
    -- select * from Professional

Data Access Layer Method:

//"Data Source=localhost;Initial Catalog=MHNProServices;Integrated Security=SSPI"
        const string ConnectionString =
            "Data Source=mhnproservices.db.5322869.hostedresource.com; Initial Catalog=mhnproservices; User ID=mhnproservices; Password='***********'";

        internal static void CreateDefaultProfile(string professionalName)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter adapter = new SqlDataAdapter("CreateDefaultProfile @ProfessionalName", conn);
            adapter.SelectCommand.Parameters.Add("@ProfessionalName", SqlDbType.VarChar).Value = professionalName;

            conn.Open();
            adapter.SelectCommand.ExecuteNonQuery();
            conn.Close();
        }

Answer 1

The error message is telling you that your SQL is incorrect. The c# code shown is where the SQL is executed. In order to assist you I would need sight of the SQL code, presumably the stored procedure source.

Perhaps you could update the question to show the SQL?

Answer 2

I needed to add exec before the procedure call. Is that just a syntax difference between 2005 and 2008? Anyways, the following works:

 SqlDataAdapter adapter = new SqlDataAdapter("exec CreateDefaultProfile @ProfessionalName", conn);

Answer 3

If you want to execute a stored procedure (in general) you're doing it way too complicated - why use a SqlDataAdapter to call ExecuteNonQuery in the end??

Try this code instead:

 internal static void CreateDefaultProfile(string professionalName)
 {
     using(SqlConnection conn = new SqlConnection(ConnectionString))
     {
         using(SqlCommand cmd = new SqlCommand("CreateDefaultProfile", conn))
         {
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.Add("@ProfessionalName", 
                     SqlDbType.VarChar, 100).Value = professionalName;

             conn.Open();
             cmd.ExecuteNonQuery();
             conn.Close();
         }
    }
}

If you're ever only calling ExecuteNonQuery, just use a SqlCommand to do that - you can tell it that it's calling a stored procedure, too! Also wrap your ADO.NET stuff into using() {....} blocks to make sure it gets disposed nicely. And last but not least: define a length for your VARCHAR parameter.

With those changes, everything should work just fine and very smoothly.