c# and sql hexadecimal to varbinary

Question

This has had me puzzled for quite some time, and I have not been able to find a clear answer..

Why is it that while using the Query analyzer in SQL Server 2005 express, I am able to submit a hexadecimal value eg:

UPDATE Table_A
SET    COLUMN_B = 0xabc123ff (example)
WHERE  (COLUMN_A = 'hello')

When I use that in c# it gives me the error "Incorrect syntax near COLUMN_B"

And when I make a stored procedure it still (seems to) work as well, when just opening it through visual studio..

However when I call this stored procedure in visual studio through c# I get the error: "Incorrect syntax near COLUMN_B"

===EXTRA INFO===

COLUMN_B is a varbinary(1740).

I tried receiving the input as a varchar and then converting it, but it doesn't like this either. also converting it does not work..

I have seen some queries out there that seems to do what I want, but they are rather large. How can I ensure that my c# code would handle exactly the same as when entering the data through a query?

Apologies if this seems a bit unclear, I'll provide more code later if required as I do not have it on hand currently..

UPDATE

The bit shown below is what I use for to call my sql stored procedure, this has worked fine with all my other stored procedures so far..

 using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WebDB"].ConnectionString))
                {
                    //State the Stored Proc and add Values to 'cmd' to pass to the Stored Proc
                    SqlCommand cmd = new SqlCommand("_USP_inv", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("Name", C5_name.Text);
                    cmd.Parameters.AddWithValue("Inventory", inventory);

                    try
                    {
                        // Open Connection and execute Stored Proc
                        conn.Open();
                        cmd.ExecuteScalar();

                    ....
                    finally
                    {
                        if (conn.State == System.Data.ConnectionState.Open)
                        {
                            //Close connection IF open
                            conn.Close();
                        }
                    }
                }

and the stored procedure as it is now

    @Name varchar(10),
    @Inventory varbinary(1740)
AS  
    UPDATE Inventory
    SET Inventory = @Inventory
    WHERE (Name = @Name)

Your C# code must be submitting something different to what you are submitting manually. Without seeing the code impossible to say why. But you should be using parameterised queries anyway. — Martin Smith
– Martin Smith, Commented Dec 6, 2011 at 14:10
Please let us know the stored proc's input parameters (if not the entire procedure) and show us the C# code that is calling the stored procedure. — sfuqua
– sfuqua, Commented Dec 6, 2011 at 23:36
@MartinSmith I did use parameters, though maybe the wrong type — Raskaroth
– Raskaroth, Commented Dec 8, 2011 at 23:25
@sfuqua I have provided both, can you provide an answer to my problem now? — Raskaroth
– Raskaroth, Commented Dec 8, 2011 at 23:25

sfuqua · Accepted Answer · 2011-12-09 04:16:53Z

1

Your sample value is an Int64 but MSBuild is using the Int32 version of AddWithValue.

cmd.Parameters.AddWithValue("Inventory", 0xabc123ff).DbType = DbType.Int64;

That will compile and the insert will work. However, I'm not entirely sure that's what you want. When using 0xabc123ff in Management Studio, the table loads with 0xABC123FF. But when executed through C# it loads as 0x00000000ABC123FF.

I think what we really need is a byte array. Yep, that does the trick -- saves 0xABC123FF, just like Management Studio:

byte[] j = new byte[] { 0xab, 0xc1, 0x23, 0xff };
cmd.Parameters.AddWithValue("Inventory", j);

If the array can be null, then the simplest thing to do is default the parameter to NULL in your stored procedure, and simply don't add the parameter in C# if null:

if (j != null) {   cmd.Parameters.AddWithValue("Inventory", j);   }

answered Dec 9, 2011 at 4:16

sfuqua

5,8731 gold badge36 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raskaroth Over a year ago

Thanks, I'll have a look and see if this will help, much better than those extremely long answers. No the value of the array to be stored has been taken from the database to be modified and then store the modified array, therefore it can't be null.

Raskaroth Over a year ago

For the past week, I've been trying to figure out why i cannot enter that byte array.. I can convert it to a byte array no problem, but it ends up saving just 1 byte out 1728, why is that?

Matt · Accepted Answer · 2011-12-06 14:05:22Z

0

If you have to take the information as Hex then it may be worth building a small worker class to convert the data on the C# app side before passing it into SQL, this can be done by converting the hex two characters at a time into its ASCII equivalent with Int32.Parse(string, NumberStyle.HexNumber)

Not sure if this applies to your requirement but its one option!

answered Dec 6, 2011 at 14:05

Matt

4,1273 gold badges31 silver badges38 bronze badges

2 Comments

Raskaroth Over a year ago

I've seen some snippets that do that, but they seem very big.. I mean if the query analyser can take it as just Inventory = 0xABC123, then why can't it through c#?

Raskaroth Over a year ago

Hmm it might be worth looking it to actually to see what char set is being used.. unless converting it to a byte array would be an better option?

Collectives™ on Stack Overflow

c# and sql hexadecimal to varbinary

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related