none
Stored procedure update RRS feed

  • Question

  • My stored procedure ( SQL 2012 )works fine from  SQL Server Management Studio.  However when I use C# with System.Data.SQLClient to invoke the stored procedure, no update takes place and no error is raised.

    The C# Function is below - any ideas ?? 

     public int UpdateValuesbySP(string FunctionName, Hashtable theParams)
            {  
                int retVal=-1;
                SqlCommand command = new SqlCommand();

                if (staticSQLConn.State == ConnectionState.Closed)
                {
                    staticSQLConn.Open();
                }
                // set command
                try
                {
                    command.Connection = staticSQLConn;
                    command.Parameters.Clear();
                    if (theParams != null)
                    {
                        foreach (DictionaryEntry item in theParams)
                        {
                            command.Parameters.AddWithValue(item.Key.ToString(), item.Value);
                        }
                    }

                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = FunctionName;
                    retVal = command.ExecuteNonQuery();
                  
                }

                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
              
                return retVal;
            }


    andrew

    Saturday, January 4, 2014 10:48 PM

Answers

  • First check if there is some error handling code is there in Stored procedure. If there is some error handling code  then Stored procedure must return valid error code back to the client rather than swallowing error. <o:p></o:p>

    Secondly, try printing out the prams inside SP which you are passing on to C# ADO.NET to confirm for any marshaling issue. <o:p></o:p>


    Sunil Sourabh Senior Software Engineer Wipro Technologies

    Monday, January 6, 2014 4:19 AM

All replies

  • If Management Studio menu contains the Tools -> SQL Server Profiler utility, then use it to check that the stored procedure is executed, with expected parameters.

    Sunday, January 5, 2014 10:16 AM
  • Made a new table and simplified my update query (SP) and the the code worked. I can only guess that the parameters for  the where clause & IF clause are not represented the same as when I enter by the Sql Server Management Studio interface.  Will check for padding of items.

    andrew

    Sunday, January 5, 2014 6:11 PM
  • First check if there is some error handling code is there in Stored procedure. If there is some error handling code  then Stored procedure must return valid error code back to the client rather than swallowing error. <o:p></o:p>

    Secondly, try printing out the prams inside SP which you are passing on to C# ADO.NET to confirm for any marshaling issue. <o:p></o:p>


    Sunil Sourabh Senior Software Engineer Wipro Technologies

    Monday, January 6, 2014 4:19 AM
  • Yikes - one of the input parameters was not quite like I had thought - adding some logic to check and throw an error allowed me to see what I overlooked. Learned a new tool ( SQL error handling ).  Thanks !

    andrew

    Monday, January 6, 2014 8:10 PM