CODEWITHSUNDEEP
c#sql
Salman
Salman

Reputation: 1380

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

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.

Upvotes: 6

Views: 5261

Answers (2)

Jean Hominal
Jean Hominal

Reputation: 16796

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();
}

Upvotes: 5

Soner Gönül
Soner Gönül

Reputation: 98750

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.

Upvotes: 1

Related Questions