> cmd.Parameters("@SessionID").Value = DBNull.Value;

"Maris Janis Vasilevskis" <mahris@myself.com> wrote in message
news:%23gU3xsCuFHA.2540@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> My task is to call a stored procedure with output parameters.
> I use auto detection methods to obtain the parameter list.
>
> I had a working solution with ActiveX ODBC. I tried to convert it into
> NET.
>
> No success, after a lot of attempts.
>
> Then, I tried NET SQL Server classes. I had success, but I had to change
> one of autodetected directions.
>
> So, my last solution works for me. However, I wonder whether it is really
> impossible to use ODBC in NET.
>
>
> More details.
>
> The procedure (it is very long, only parameter definitions copied).
> CREATE PROCEDURE insertKSESSION
> @UserName LONGSTRING = NULL,
> @ClientWorkStation LONGSTRING = NULL,
> @ServerHostName LONGSTRING = NULL,
> @Expirable BOOLEAN = 0,
> @UserPrincipalName LONGSTRING = NULL,
> @SessionID INTEGER OUTPUT,
> @UserID INTEGER = NULL OUTPUT,
> @GUID SYSTEMID = NULL OUTPUT,
> @ConnectionType ENUM = 0,
> @userRealName LONGSTRING = NULL OUTPUT
> AS
> SET NOCOUNT ON
> BEGIN
> ...
> END
>
> You can notice that @SessionID differs from the rest OUTPUT parameters: it
> has no default value.
> The user defined type LONGSTRING is VARCHAR(255) with a specific
> collation. It is correctly
> recognized as DbType=AnsiString and OdbcType/SqlDbType=VarChar during the
> parameter detection.
> Please, do not recommend me to change anything in the procedure. It is a
> part of a large 3rd
> party system running on multiple servers.
>
> My working solution in ADO:
> var cn,cmd;
> var adCmdStoredProc = 4;
> cn = new ActiveXObject('ADODB.Connection');
> cn.Open('DSN=MyOdbc;UID=sa;PWD=asas');
> cmd = new ActiveXObject('ADODB.Command');
> cmd.ActiveConnection = cn;
> cmd.CommandText = 'insertKSESSION';
> cmd.CommandType = adCmdStoredProc;
> cmd.Parameters.Refresh; // now, I have 11 parameters
> cmd.Parameters("@UserName") = MyUserName;
> cmd.Parameters("@ClientWorkStation") = MyClientWorkStation;
> cmd.Parameters("@ServerHostName") = MyServerHostName;
> cmd.Parameters("@Expirable") = 1;
> cmd.Parameters("@UserPrincipalName") = "";
> cmd.Execute();
> // And now I have all required output values in cmd.Parameters
>
> My working solution with System.Data.SqlClient:
> var cn:SqlConnection, cmd:SqlCommand;
> cn = new SqlConnection('Server=MyServer;Database=MyDatabase;User
> ID=sa;Password=asas');
> cn.Open();
> cmd= new SqlCommand();
> cmd.Connection = cn;
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
> cmd.CommandText = 'insertKSESSION';
> SqlCommandBuilder.DeriveParameters(cmd); // now, I have 11 parameters
> cmd.Parameters("@SessionID").Direction = ParameterDirection.Output; // !!!
> cmd.Parameters("@UserName").Value = MyUserName;
> cmd.Parameters("@ClientWorkStation").Value = MyClientWorkStation;
> cmd.Parameters("@ServerHostName").Value = MyServerHostName;
> cmd.Parameters("@Expirable").Value = 1;
> cmd.Parameters("@UserPrincipalName").Value = "";
> cmd.ExecuteNonQuery();
>
> One problem: if I try to do it without assignment for
> cmd.Parameters("@SessionID").Direction, I get
> Procedure 'insertKSESSION' expects parameter '@SessionID', which was not
> supplied.
>
> Really, DeriveParameters return InputOutput instead of Output in all
> cases.
> However, Refresh in ADO had the same problem, but it worked.
>
>
> I will not give the text for my attempts with System.Data.Odbc.
> The main version is an exact copy of the previous text, with all Sql
> changed into Odbc.
> I performed a large number of additional tests.
>
> I never could go over the exception
> ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
> 'insertKSESSION' expects parameter '@SessionID', which was not supplied.
>
> Then, I tried to change procedure parameter definition into
> @SessionID INTEGER = NULL OUTPUT
> (as I mentioned, the change is not acceptable for work, I did it only for
> testing purposes).
> Now, the procedure was entered. But the first parameter, @UserName, was
> incorrect,
> and the procedure failed.
>
> I definitely wonder whether anybody has success in NET ODBC using stored
> procedures with output parameters.
>
> Thank you,
>
> Mahris