A Question on Varbinary datatype usage in asp.net

[Vinod]

Hi,
I have a stored procedure which expects a varbinary datatype. How
can i pass a varbinary datatype from asp.net
directly to the stored procedure. I tried using the Convert function in Sql
server to convert the string to varbinary but it gives
a different value. Can you give me suggestions on how to approach this
problem.

The SQL server column datatype was made to varbinary because the data is in
hexadecimal format.


Thanks,
Vinod

 

[Hermit Dave]

to store in binary you should use byte[] bytesBArray
SqlParameter parameterBArray = new SqlParameter("@BinaryParam",
SqlDbType.VarBinary, 4000);
parameterBArray.value = bytesBArray;
myCommand.Parameter.Add(parameterBArray);

 

[Vinod]

Thanks for the inputs Hermit.

i have a string value in this format
'0x210000000001A9923E000000'
i want this same value to be passed to the stored procedure which is
expecting the varbinary data
when i used byte[] , this value got changed. How do i pass the same value to
the Stored Proc.


Thanks,
Vinod

to store in binary you should use byte[] bytesBArray
SqlParameter parameterBArray = new SqlParameter("@BinaryParam",
SqlDbType.VarBinary, 4000);
parameterBArray.value = bytesBArray;
myCommand.Parameter.Add(parameterBArray);

--
Regards,
HD
Once a Geek.... Always a Geek

Hi,
I have a stored procedure which expects a varbinary datatype. How
can i pass a varbinary datatype from asp.net
directly to the stored procedure. I tried using the Convert function in
Sql
server to convert the string to varbinary but it gives
a different value. Can you give me suggestions on how to approach this
problem.

The SQL server column datatype was made to varbinary because the data is
in
hexadecimal format.


Thanks,
Vinod

 

[Hermit Dave]

Vinod,

try using return
Encoding.Unicode.GetBytes(yourstringhere);
to get the byte array
and
useEncoding.Unicode.GetString(yourbytearray);
to convert the byte array to your string again
--
Regards,
HD
Once a Geek.... Always a Geek

Thanks for the inputs Hermit.

i have a string value in this format
'0x210000000001A9923E000000'
i want this same value to be passed to the stored procedure which is
expecting the varbinary data
when i used byte[] , this value got changed. How do i pass the same value
to
the Stored Proc.


Thanks,
Vinod

to store in binary you should use byte[] bytesBArray
SqlParameter parameterBArray = new SqlParameter("@BinaryParam",
SqlDbType.VarBinary, 4000);
parameterBArray.value = bytesBArray;
myCommand.Parameter.Add(parameterBArray);

--
Regards,
HD
Once a Geek.... Always a Geek

Hi,
I have a stored procedure which expects a varbinary datatype. How
can i pass a varbinary datatype from asp.net
directly to the stored procedure. I tried using the Convert function in
Sql
server to convert the string to varbinary but it gives
a different value. Can you give me suggestions on how to approach
this
problem.

The SQL server column datatype was made to varbinary because the data is
in
hexadecimal format.


Thanks,
Vinod

 

[Hermit Dave]

sorry copy pasted a line from my code and missed out removing the return
statement

--
Regards,
HD
Once a Geek.... Always a Geek

Vinod,

try using return
Encoding.Unicode.GetBytes(yourstringhere);
to get the byte array
and
useEncoding.Unicode.GetString(yourbytearray);
to convert the byte array to your string again
--
Regards,
HD
Once a Geek.... Always a Geek

Thanks for the inputs Hermit.

i have a string value in this format
'0x210000000001A9923E000000'
i want this same value to be passed to the stored procedure which is
expecting the varbinary data
when i used byte[] , this value got changed. How do i pass the same value
to
the Stored Proc.


Thanks,
Vinod

to store in binary you should use byte[] bytesBArray
SqlParameter parameterBArray = new SqlParameter("@BinaryParam",
SqlDbType.VarBinary, 4000);
parameterBArray.value = bytesBArray;
myCommand.Parameter.Add(parameterBArray);

--
Regards,
HD
Once a Geek.... Always a Geek
Hi,
I have a stored procedure which expects a varbinary datatype. How
can i pass a varbinary datatype from asp.net
directly to the stored procedure. I tried using the Convert function
in
Sql
server to convert the string to varbinary but it gives
a different value. Can you give me suggestions on how to approach
this
problem.

The SQL server column datatype was made to varbinary because the data is
in
hexadecimal format.


Thanks,
Vinod

 

[Steven Cheng[MSFT]]

Hi Vinod,


Thanks for posting in the community! My name is Steven, and I'll be
assisting you on this issue.
From your description, you used ADO.NET component to execute a stored
procedure and the sp has a parameter whose type is varbinary. And you want
to set a certain string as the value of the parameter.
If there is anything I misunderstood, please feel free to let me know.

As for this problem, I quite agree to Hermit's suggestion. The
"SqlDbType.VarBinary" is mapped to the a byte array(byte[] in c#). So you
need to transmit a byte array as the value of the certain sp's paramter.
Considering that the original object you want store into the
"SqlDbType.VarBinary" type column is a string, I think you need to first
convert the string into a byte[] just as Hermit mentioned:
For example, using
byte[] barr = System.Text.Encoding.Unicode.GetBytes( stringobject );
then, add this byte[] as the stored procedure's VarBinary parameter.

Also, when you retrieve the VarBinary column data out from the database and
set into a byte[], again you need to convert the byte[] back to string so
as for further use:
string output = System.Text.Encoding.Unicode.GetString(bytearrary);

In addtion, I've searched some references and tech articles on programing
with binray data types using ADO.NET and stored procudure:
#How to read and write a file to or from a BLOB column by using ADO.NET and
Visual C# .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;317016

#HOW TO: Copy a Picture from a Database Directly to a PictureBox Control
with Visual C#
http://support.microsoft.com/?id=317701

Reference on the System.Text.Encoding class's GetBytes and GetString method:
#Encoding.GetBytes Method
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemTextEncodingCl
assGetBytesTopic.asp?frame=true

#Encoding.GetString Method
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemTextEncodingCl
assGetStringTopic.asp?frame=true

Please check them out if you feel needed. If you have any further
questions, please feel free to post here.



Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Vinod]

Hello,
I tried using the encoding methods but still am not able to get
any results. There are no errors thrown but no values are returned.

I will post the example code here

Dim checkbyte As Byte() =
System.Text.Encoding.Unicode.GetBytes("0x210000000001A9923E000000")
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim ada As SqlDataAdapter
Dim mds As DataSet
Dim r As DataRow
Try
cn = New SqlConnection(connectionstring)
cn.Open()
cmd = New SqlCommand("checkproc", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@EPCID", Data.SqlDbType.VarBinary, 8000)
cmd.Parameters("@EPCID").Value = checkbyte
ada = New SqlDataAdapter
ada.SelectCommand = cmd
mds = New DataSet
ada.Fill(mds, "EPCT_EPC")

For Each r In mds.Tables("EPCT_EPC").Rows
ListBox1.Items.Add(r.Item("appln_id"))
ListBox1.Items.Add(r.Item("loc_id"))
Next

Catch ex As Exception
Throw ex
End Try

The stored procedure checkproc is

create procedure checkproc(
@EPCID varbinary(8000)

)
AS
select appln_id,loc_id from epct_epc where epc_id = @EPCID


The values for epcid are like 0x210000000001A9923E000000

Could you please tell me where iam missing something? and how to resolve
this.



Thanks,

Vinod

Hi Vinod,


Thanks for posting in the community! My name is Steven, and I'll be
assisting you on this issue.
From your description, you used ADO.NET component to execute a stored
procedure and the sp has a parameter whose type is varbinary. And you want
to set a certain string as the value of the parameter.
If there is anything I misunderstood, please feel free to let me know.

As for this problem, I quite agree to Hermit's suggestion. The
"SqlDbType.VarBinary" is mapped to the a byte array(byte[] in c#). So you
need to transmit a byte array as the value of the certain sp's paramter.
Considering that the original object you want store into the
"SqlDbType.VarBinary" type column is a string, I think you need to first
convert the string into a byte[] just as Hermit mentioned:
For example, using
byte[] barr = System.Text.Encoding.Unicode.GetBytes( stringobject );
then, add this byte[] as the stored procedure's VarBinary parameter.

Also, when you retrieve the VarBinary column data out from the database and
set into a byte[], again you need to convert the byte[] back to string so
as for further use:
string output = System.Text.Encoding.Unicode.GetString(bytearrary);

In addtion, I've searched some references and tech articles on programing
with binray data types using ADO.NET and stored procudure:
#How to read and write a file to or from a BLOB column by using ADO.NET and
Visual C# .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;317016

#HOW TO: Copy a Picture from a Database Directly to a PictureBox Control
with Visual C#
http://support.microsoft.com/?id=317701

Reference on the System.Text.Encoding class's GetBytes and GetString method:
#Encoding.GetBytes Method
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemTextEncodingCl
assGetBytesTopic.asp?frame=true

#Encoding.GetString Method
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemTextEncodingCl
assGetStringTopic.asp?frame=true

Please check them out if you feel needed. If you have any further
questions, please feel free to post here.



Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Steven Cheng[MSFT]]

Hi Vinod,


Thank you for the response. I've viewed the code you provided. Since there
is no error or exception occur. I suspect that the problem is likely due to
the parameter's value is not correct and I'd like to confirm some further
questions on your program:
1. Since your stored procedure is defined as:
---------------------------------------------------
create procedure checkproc(
@EPCID varbinary(8000)
)
AS
select appln_id,loc_id from epct_epc where epc_id = @EPCID
----------------------------------------------------

What's the datatype of the "epc_id" in SqlServer, is it "varbinary"?

2. If the epc_id column's sqlserver db type is varbinary, then how do you
get the string value such as
"0x210000000001A9923E000000", did you get this via copying from the
SQLQuery Analyzer? If so, I think the problem is just cause by this,
because the 0x210000000001A9923E000000 is a binary value, we should
directly generate a byte() via the 0x210000000001A9923E000000 rather than
put it into a string and than convert to byte(). To initialize a byte() in
VB.NET you can use the below code:

Dim checkbyte() As Byte = New Byte() {&H21, &H0, &H0, &H0, &H0, &H1, &HA9,
&H92, &H3E, &H0, &H0, &H0}

This is the correct way to generate a byte array in VB.NET via its binray
value.

In addition, I still think the above way is not very flexible for
programing, you may need to get the value by query from the database, since
the ADO.NET component can return binary type column's value directly as a
byte(). Do you think so?

Please check out the above suggestions. If you have any questions, please
feel free to let me know.



Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Steven Cheng[MSFT]]

Hi Vinod,


Have you had a chance to check out my suggestions or have you got any
progress on this issue? If you have any questions, please feel free to let
me know.


Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[nivs1978]

Convert the hex string correctly to a byte array

I know the post is old, but I was just searching for how to use the SqlDbType.VarBinary in an SQLCommand parameter. So in case others need the answer how to convert the hexadecimal string to a byte array you do this (pseudo code):

hexstr = right(hex, hex.length-2); // Remove "0x" from string
byte[hextre.length/2] bytes; // Create byte array hald the size of the string (two hex chars=1 byte)
for (int i=0; i<hexstr/2; i++) // Loop trough the string
{
String h = hexstr.substring(i*2, (i+2)*2) // Grab two chars from the string
Byte b = Convert.ToInt32(h); // Convert hex number to byte
bytes = b; // Store in byte array
}
Bytes now contains the correct byte array

Regards Hans Milling