Getting an error when calling stored procedure from C#

Question

I am getting following error when calling a stored procedure in SQL Server from C#:

Line 1: Incorrect syntax near 'spGet_Data'.

Here is my code:

public string GetData (string destinationFile)
{
    string conectionString = "uid=One_User;pwd=One_Password;database=One_Database;server=One_Server";

    SqlConnection con = new SqlConnection(conectionString);
    SqlCommand sqlCmd = new SqlCommand();

    string returnValue = string.Empty;
    string procedureName = "spGet_Data";

    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd = new SqlCommand(procedureName, con);

    sqlCmd.Parameters.AddWithValue("@FileName", destinationFile);
    con.Open();
    var returnParameter = sqlCmd.Parameters.Add("@ret", SqlDbType.VarChar);
    returnParameter.Direction = ParameterDirection.ReturnValue;

    sqlCmd.ExecuteNonQuery();
    returnValue = returnParameter.Value.ToString();

    con.Close();
    return returnValue;
}

Procedure itself returning data properly, I checked connection it is in Open state.

What else it can be?

Thank you.

Answer 1

The problem lies in the fact that you create the command two times.
After the first initialization you set correctly the CommandType to StoredProcedure, but once again you created the command and this time you forgot to set the CommandType

Just remove the first initialization, leave only the second one and move the CommandType setting after the initialization

SqlConnection con = new SqlConnection(conectionString);
string returnValue = string.Empty;
string procedureName = "spGet_Data";
SqlCommand sqlCmd = new SqlCommand(procedureName, con);
sqlCmd.CommandType = CommandType.StoredProcedure;