By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,558 Members | 1,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,558 IT Pros & Developers. It's quick & easy.

A Question on Varbinary datatype usage in asp.net

P: n/a
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
Nov 18 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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
"Vinod" <vi*********@bestbuy.com> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
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

Nov 18 '05 #2

P: n/a
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

"Hermit Dave" <he************@CAPS.AND.DOTS.hotmail.com> wrote in message
news:u5**************@TK2MSFTNGP12.phx.gbl...
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
"Vinod" <vi*********@bestbuy.com> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
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


Nov 18 '05 #3

P: n/a
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
"Vinod" <vi*********@bestbuy.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
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

"Hermit Dave" <he************@CAPS.AND.DOTS.hotmail.com> wrote in message
news:u5**************@TK2MSFTNGP12.phx.gbl...
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
"Vinod" <vi*********@bestbuy.com> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
> 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
>
>



Nov 18 '05 #4

P: n/a
sorry copy pasted a line from my code and missed out removing the return
statement

--
Regards,
HD
Once a Geek.... Always a Geek
"Hermit Dave" <he************@CAPS.AND.DOTS.hotmail.com> wrote in message
news:%2*****************@TK2MSFTNGP12.phx.gbl...
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
"Vinod" <vi*********@bestbuy.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
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

"Hermit Dave" <he************@CAPS.AND.DOTS.hotmail.com> wrote in message
news:u5**************@TK2MSFTNGP12.phx.gbl...
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
"Vinod" <vi*********@bestbuy.com> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
> 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
>
>



Nov 18 '05 #5

P: n/a
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...b;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...TextEncodingCl
assGetBytesTopic.asp?frame=true

#Encoding.GetString Method
http://msdn.microsoft.com/library/en...TextEncodingCl
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.)
Nov 18 '05 #6

P: n/a
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("0x210000000 001A9923E000000")
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


"Steven Cheng[MSFT]" <v-******@online.microsoft.com> wrote in message
news:gs*************@cpmsftngxa07.phx.gbl...
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...b;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...TextEncodingCl assGetBytesTopic.asp?frame=true

#Encoding.GetString Method
http://msdn.microsoft.com/library/en...TextEncodingCl 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.)

Nov 18 '05 #7

P: n/a
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.)
Nov 18 '05 #8

P: n/a
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.)

Nov 18 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.