Output to C# from SQL Server 2008 Stored Proc Through ODBC

Question

I am trying to connect to a SQL Server 2008 database through a C# program using a ODBC connection. I am sending a stored procedure some data in order to update some records. I would like to get back some sort of output messages to ensure my Proc is running. Before I started messing around with the output message, my proc ran, according to my C# program, it never returned an error. But the database was not updated. Once I added the code to allow for output, I got an error, something along the lines of HY105. Here comes the code:

Proc-

ALTER PROCEDURE [dbo].[Proc]
(   @userid                                 char(10),
@sql_userid                         varchar(50),
@user_encrypted_password        varchar(50),
@user_old_sql_guid_password     varchar(50),
@user_new_sql_guid_password     varchar(50),
    @user_new_sql_guid_encrypted_password   varchar(50),
    @errmsg                 int OUTPUT

)

as

DECLARE @now            datetime,
    @status         int,
    @InProcErrMsg   varchar(255)

SET @userid = UPPER(@userid)

BEGIN TRY  

EXEC ("ALTER LOGIN" + @sql_userid + "WITH PASSWORD  = " +
    @user_new_sql_guid_password + 
            " old_password = " + @user_old_sql_guid_password);

SET @errmsg = 0

IF @@ERROR = 0
    BEGIN
    UPDATE  Table
    Set     SERVER_OTHER = @user_new_sql_guid_encrypted_password
    WHERE   PC_LOGIN = @userid
            and PC_OTHER = @user_encrypted_password

    END 
SET @errmsg = 1
End try

begin catch
if @@trancount > 0
    rollback transaction

select @InProcErrMsg = left( "Proc: (" + cast( error_line() as varchar(10) ) + ") " 
+ error_message(), 255 )
raiserror 50000 @InProcErrMsg

end catch

return 0`

C#-

using (OdbcConnection databaseConnection = new OdbcConnection
                     ("Driver={SQLServer};Server=server;UID=id;PWD=pw;Database=db;"))
   {
     try
        {
         OdbcCommand SQLUserUpdateCommand = new OdbcCommand
           ("{? = CALL USP_PHD_SQLUSER_UPDATE(?,?,?,?,?,?)}", databaseConnection);

         SQLUserUpdateCommand.CommandType = CommandType.StoredProcedure;

         OdbcParameter SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                                             ("@userid", OdbcType.Char, 10);
         SQLUserUpdateParam.Value = id;

         SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                              ("@sql_userid", OdbcType.VarChar, 50);
         SQLUserUpdateParam.Value = sqlid;

         SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                              ("@user_encrypted_password", OdbcType.VarChar, 50);
         SQLUserUpdateParam.Value = pw;

         SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                              ("@user_old_sql_guid_password", OdbcType.VarChar, 50);
         SQLUserUpdateParam.Value = oldpw;

         SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                              ("@user_new_sql_guid_password", OdbcType.VarChar, 50);
         SQLUserUpdateParam.Value = newpw;

         SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                       ("@user_new_sql_guid_encrypted_password", OdbcType.VarChar, 50);
         SQLUserUpdateParam.Value = as_encrypted;

         SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                              ("@errmsg", OdbcType.VarChar, 255);
         SQLUserUpdateParam.Direction = ParameterDirection.Output;

         databaseConnection.Open();

         SQLUserUpdateCommand.ExecuteNonQuery();

         SQLUserUpdateCommand.Dispose();
         databaseConnection.Close();
         }
         catch (OdbcException OEx)
         {
         Trace.WriteLine("Failed to call USP_PHD_SQLUSER_UPDATE");
         Trace.WriteLine("ODBC Exception Message: " + OEx.Message);
         Trace.WriteLine("ODBC Exception Source: " + OEx.Source);
         Trace.WriteLine("ODBC Exception StackTrace: " + OEx.StackTrace);
         Trace.WriteLine("ODBC Exception TargetSite: " + OEx.TargetSite);
         Trace.WriteLine("ODBC Exception Data: " + OEx.Data);
         Trace.WriteLine("ODBC Exception Error Code: " + OEx.ErrorCode);
         Trace.WriteLine("ODBC Exception Errors: " + OEx.Errors);
         }
       }

Error-

A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll
Failed to call Proc
ODBC Exception Message: ERROR [HY105] [Microsoft][ODBC SQL Server Driver]Invalid parameter type
ODBC Exception Source: SQLSRV32.DLL
ODBC Exception StackTrace:
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at nvo_connect1.of_update(String as_process_name, String as_switch, String as_server_signon, String as_server, String as_database, String as_password, String as_decrypted, String as_new_password_guid, String as_encrypted, String as_400_connection_string)
ODBC Exception TargetSite: Void HandleError(System.Data.Odbc.OdbcHandle, RetCode)
ODBC Exception Data: System.Collections.ListDictionaryInternal
ODBC Exception Error Code: -2146232009
ODBC Exception Errors: System.Data.Odbc.OdbcErrorCollection

It doesn't give me a line number, but through testing, I know the error occurs at SQLUserUpdateCommand.ExecuteNonQuery();.

If there is such thing as too much info on this site, this is it. If so, apologies. First time user.

I have scoured Google for help way too much this week with no results. I know its gonna be a forehead slapper but that's better than the current table-head-bang.

Thank you for any help.

Edit:

The @errmsg thing was important, so thank you for pointing that out. I changed my C# a bit to get this to finally work:

SQLUserUpdateCommand.Connection = databaseConnection;

//*****Open ODBC Connection
databaseConnection.Open();

SQLUserUpdateCommand.CommandType = CommandType.StoredProcedure;

SQLUserUpdateCommand.Parameters.AddWithValue("@userid", as_login_name);
SQLUserUpdateCommand.Parameters.AddWithValue("@sql_userid", as_server_signon);

SQLUserUpdateCommand.Parameters.AddWithValue("@user_encrypted_password", as_password);

SQLUserUpdateCommand.Parameters.AddWithValue("@user_old_sql_guid_password",
                                                                    as_decrypted);

SQLUserUpdateCommand.Parameters.AddWithValue("@user_new_sql_guid_password",
                                                              as_new_password_guid);

SQLUserUpdateCommand.Parameters.AddWithValue("@user_new_sql_guid_encrypted_password",
                                                              as_encrypted);

SQLUserUpdateCommand.ExecuteNonQuery();

Instead of using the ODBCParameter object to add my parameters, I added the parameters with the AddValue() method of the ODBCCommand object.

I got rid of the output parameter all together. Decided to retrieve error msgs by inserting records into a table. There were some issues with my alter statement as well. Overall, I do not know what the problem was. I am working with another proc in this same program that will be using output parameters, so any suggestions would be appreciated!

Answer 1

In your SP it defines:

 @errmsg                 int OUTPUT

but in you C# you make it:

SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                          ("@errmsg", OdbcType.VarChar, 255);

So it says invalid parameter type

Answer 2

This is wrong

SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                              ("@errmsg", OdbcType.VarChar, 255);

Because the parameter is declared this way in the procedure

      @errmsg        int OUTPUT

You should update the C# code to match e.g.

SQLUserUpdateParam = SQLUserUpdateCommand.Parameters.Add
                              ("@errmsg", OdbcType.Int,);
SQLUserUpdateParam.Direction = ParameterDirection.Output; 

Answer 3

use the sql profiler tool to see what parameters are being passed to the procedure. capture those and try and execute the stored proc alone or may be debug it. You should be able to zero in on the error.