How to pass Byte Array in a Query using C#?

Question

I'm trying to insert bytes of byte array in the database. using following code.

String query = String.Format(@"INSERT INTO [Documents]
                              ([InsertedBy], [DocumentName], [Document])
                              VALUES
                              ('{0}','{1}',{2})",
                              insertedBy, docName, docBytes);

Cmd.CommandText = query;
Cmd.ExecuteNonQuery();

Following exception is occured:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. Incorrect syntax near ''.

I'm not getting what the reason is.

What are the types of these columns? What are the values of your insertedBy, docName and docBytes exactly? — Soner Gönül
– Soner Gönül, Commented Feb 15, 2014 at 11:29
insertedBy is int, documentname is varchar(100) and Document is VarBinary(max) — Salman
– Salman, Commented Feb 15, 2014 at 11:30
I would always insert the varbinary as a parameter in this way: stackoverflow.com/a/1088630/812598, since it seems to be a raw byte[] type by it's name. — GoRoS
– GoRoS, Commented Feb 15, 2014 at 11:53
You should not code SQL this way, it is susceptible to SQL Injection Attacks. — Erik Philips
– Erik Philips, Commented Feb 15, 2014 at 12:37
I second that. Don't forge SQL queries yourself. Use ADO.NET or some similar library that do the translation to the underlying Database dialect — Tseng
– Tseng, Commented Feb 15, 2014 at 13:47

Jean Hominal · Accepted Answer · 2014-02-15 14:35:24Z

Never use string concatenation or string functions to make parametrized queries.

Also, because (I suspect that) docBytes is a byte[], string concatenation will not have the results that you hope for.

Here is how I would do it:

private static void InsertDocument(SqlCommand cmd, int insertedBy, string docName, byte[] docBytes)
{
    cmd.CommandText = @"INSERT INTO [Documents]
                        ([InsertedBy], [DocumentName], [Document])
                        VALUES
                        (@insertedBy,@docName,@docBytes)";
    cmd.Parameters.Add("insertedBy", SqlDbType.Int).Value = insertedBy;
    // Note: consider using `nvarchar` instead of `varchar`;
    cmd.Parameters.Add("docName", SqlDbType.VarChar, 100).Value = docName;
    // Note: -1 maps to the nvarchar(max) length;
    cmd.Parameters.Add("docBytes", SqlDbType.VarBinary, -1).Value = docBytes;

    // The following call presupposes that the associated `SqlConnection` is open
    cmd.ExecuteNonQuery();
}

Soner Gönül · Accepted Answer · 2014-02-15 11:33:54Z

1

If your insertedBy column is an int, you don't need to use single quotes with it. Because you are try to insert characters to your int typed column.

Just use it like;

string query = String.Format(@"INSERT INTO [Documents]
                              ([InsertedBy], [DocumentName], [Document])
                              VALUES
                              ({0},'{1}',{2})",
                              insertedBy, docName, docBytes);

But since we don't know your values, this is the only suggestion I have.

edited Feb 15, 2014 at 11:33

answered Feb 15, 2014 at 11:27

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

2 Comments

Dirk Over a year ago

I'm not an expert but I would assume docBytes could be a problem too, e.g. if it's a byte[]. The error message in the question might indicate something like that.

Salman Over a year ago

same error even after removing the quotations, this is for sure that those quotation are not the cause of problem

Collectives™ on Stack Overflow

How to pass Byte Array in a Query using C#?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related