Retrieve varbinary from SqlServer using OUTPUT params in StoredPro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a stored proc with a varbinary OUTPUT param and I am trying to
retrieve the value of this variable in the C# using SqlCommand and setting
its parameters. However, I am not able to retrieve the value of varbinary
variable in C# for some unknown reasons. I would really appreciate your help.
Thanks

Alvin


Byte [] testBinary = new Byte[100];
SqlParameter param1 = SqlCommand,Parameters.Add("@testBinary",
SqlDBType.Binary, 100);
param1.value = testBinary
SqlCommand.CommandType = CommandType.StoredProcedure
SqlCommand.Direction = ParameterDirection.Output
SqlCommand.ExecureNonQuery()
Console.Writeline("Output param is {0}", Convert.ToBase64(testBinary));
====> Don't see the value from Stored Proc

StoredProc
CREATE PROCEDURE test
testBinary (100) OUTPUT
AS
BEGIN
select @testBinary = testbin from testTable
END
 
Alvin said:
Hi

I have a stored proc with a varbinary OUTPUT param and I am trying to
retrieve the value of this variable in the C# using SqlCommand and
setting its parameters. However, I am not able to retrieve the value
of varbinary variable in C# for some unknown reasons. I would really
appreciate your help. Thanks

Alvin


Byte [] testBinary = new Byte[100];
SqlParameter param1 = SqlCommand,Parameters.Add("@testBinary",
SqlDBType.Binary, 100);
param1.value = testBinary
SqlCommand.CommandType = CommandType.StoredProcedure
SqlCommand.Direction = ParameterDirection.Output
SqlCommand.ExecureNonQuery()
Console.Writeline("Output param is {0}",
Convert.ToBase64(testBinary)); ====> Don't see the value from Stored
Proc

StoredProc
CREATE PROCEDURE test
testBinary (100) OUTPUT
AS
BEGIN
select @testBinary = testbin from testTable
END
Click to expand...

Shouldn't you declare your parameter in the proc as 'varbinary(100)
and the parameter in the C# code as SqlDBType.VarBinary ?

FB


--
 
How do I do that in C#. There is a typo in the Proc declaration. The output
param is indeed declared as varbinary(100)

Thanks

Frans Bouma said:
Alvin said:
Hi

I have a stored proc with a varbinary OUTPUT param and I am trying to
retrieve the value of this variable in the C# using SqlCommand and
setting its parameters. However, I am not able to retrieve the value
of varbinary variable in C# for some unknown reasons. I would really
appreciate your help. Thanks

Alvin


Byte [] testBinary = new Byte[100];
SqlParameter param1 = SqlCommand,Parameters.Add("@testBinary",
SqlDBType.Binary, 100);
param1.value = testBinary
SqlCommand.CommandType = CommandType.StoredProcedure
SqlCommand.Direction = ParameterDirection.Output
SqlCommand.ExecureNonQuery()
Console.Writeline("Output param is {0}",
Convert.ToBase64(testBinary)); ====> Don't see the value from Stored
Proc

StoredProc
CREATE PROCEDURE test
testBinary (100) OUTPUT
AS
BEGIN
select @testBinary = testbin from testTable
END
Click to expand...

Shouldn't you declare your parameter in the proc as 'varbinary(100)
and the parameter in the C# code as SqlDBType.VarBinary ?

FB


--
Click to expand...
 
Alvin said:
How do I do that in C#. There is a typo in the Proc declaration. The
output param is indeed declared as varbinary(100)
Click to expand...

change:
SqlParameter param1 = SqlCommand,Parameters.Add("@testBinary",
SqlDBType.Binary, 100);

in:
SqlParameter param1 = SqlCommand,Parameters.Add("@testBinary",
SqlDBType.VarBinary, 100);

The rest of the code seems to be ok, though what I'd do for testing
first is to see if the byte array does contain a value or not before
using base64

FB

Thanks

Frans Bouma said:
Alvin said:
Hi

I have a stored proc with a varbinary OUTPUT param and I am
trying to retrieve the value of this variable in the C# using
SqlCommand and setting its parameters. However, I am not able to
retrieve the value of varbinary variable in C# for some unknown
reasons. I would really appreciate your help. Thanks

Alvin


Byte [] testBinary = new Byte[100];
SqlParameter param1 = SqlCommand,Parameters.Add("@testBinary",
SqlDBType.Binary, 100);
param1.value = testBinary
SqlCommand.CommandType = CommandType.StoredProcedure
SqlCommand.Direction = ParameterDirection.Output
SqlCommand.ExecureNonQuery()
Console.Writeline("Output param is {0}",
Convert.ToBase64(testBinary)); ====> Don't see the value from
Stored Proc

StoredProc
CREATE PROCEDURE test
testBinary (100) OUTPUT
AS
BEGIN
select @testBinary = testbin from testTable
END
Click to expand...

Shouldn't you declare your parameter in the proc as 'varbinary(100)
and the parameter in the C# code as SqlDBType.VarBinary ?
Click to expand...
Click to expand...


--
 
Back
Top