Sql Server, Insert and/or Select binary data issue

Question

I'm having a problem either inserting or selecting data from a varbinary(max) column in my database (it is a localDB) and I can't seem to figure out why.

I'm adding the binary data from a C# application the following way.

SqlConnection con = new SqlConnection("<connection string...>");            
SqlCommand cmd = new SqlCommand("sp_test_varbinary", con);

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@content", SqlDbType.VarBinary, -1).Value = Convert.FromBase64String("<base64 string>");

con.Open();
cmd.ExecuteNonQuery();
con.Close();

The sp_test_varbinary stored procedure takes just the one parameter @content and inserts it into a varbinary(max) column - so far all seems to work as expected. I can't tell what is in the varbinary(max) column, since it just says binary data, but something is there at least.

Then when I try to select the row I just inserted and attempt to get back the base64 string i originally had, something goes wrong or the data in the field is wrong.

I'm doing the following (file_id is the ID of the row I added).

SqlDataAdapter da = new SqlDataAdapter("SELECT [content] FROM [BaseFiles] WHERE ([file_id] = '" + file_id + "');", con);
DataSet ds = new DataSet();

da.Fill(ds);

string base64 = Convert.ToBase64String((byte[])ds.Tables[0].Rows["content"]);

At this point however, the base64 variable only contains the value:

7w==

Any idea what I'm missing to make this work?

Edmond Quinton · Accepted Answer · 2018-04-16 15:01:06Z

1

Looking at your result you more than likely did not specify the length of the VARBINARY in either your stored procedure, table or code within the stored procedure.

Make sure that when you declare a VARBINARY variable that you included the MAX definition in your declaration. Example your stored procedure should be declared as follow:

CREATE PROCEDURE [dbo].[sp_test_varbinary]
    @content AS VARBINARY(MAX)
AS
BEGIN

    SET NOCOUNT ON;

    -- YOUR INSERT STATEMENT HERE

END

And not declared as follow:

CREATE PROCEDURE [dbo].[sp_test_varbinary]
    @content AS VARBINARY
AS
BEGIN

    SET NOCOUNT ON;

    -- YOUR INSERT STATEMENT HERE

END

Similar make sure that if you use a local VARBINARY variables within your stored procedure that their lengths is also defined. Final make sure that the length of the VARBINARY is defined on the [content] column within your table.

edited Apr 16, 2018 at 15:01

answered Apr 16, 2018 at 14:51

Edmond Quinton

1,74910 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Sql Server, Insert and/or Select binary data issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related