Problem in inserting binary data in sql;

Hi,

I am facing problem inserting binary data into sql data type varbinary.

I want to save an object data type in var binary data type into sql.It gives a followin error.

The name 'DocumentData' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

I do not use ASP Upload file.

Any type of help .

Thanks in advance

Friend

Hello my friend,

The variable is not being passed to the SQL correctly. For example: -

INSERT INTO tblCountry (CountryName) VALUES ('Italy') - this is fine

INSERT INTO tblCountry (CountryName) VALUES (Italy) - not right because single quotes are missing. However, SQL thinks I am referring to a column name when I say Italy. Check your SQL.

Kind regards

Scotty

|||

Hi,

Thanks for your reply.Follwoing is my code plz review and help me if i am making any mistake.

query =

"Insert into Documents(DocumentID,DocumentName,DocumentData,DocumentType,FileID,Field1,Field2,Field3,Field4)values (@.DocumentID,@.DocumentName,DocumentData,DocumentType,FileID,Field1,Field2,Field3,Field4,)";

sc.Parameters.Add(

"@.DocumentID",SqlDbType.Int, 10);

sc.Parameters.Add(

"@.DocumentName",SqlDbType.VarChar, 15);

sc.Parameters.Add(

"@.DocumentData",SqlDbType.Binary, 15);

sc.Parameters.Add(

"@.DocumentType",SqlDbType.VarChar, 10);

sc.Parameters.Add(

"@.FileID",SqlDbType.Int, 10);

sc.Parameters.Add(

"@.Field1",SqlDbType.VarChar, 15);

sc.Parameters.Add(

"@.Field2",SqlDbType.VarChar, 15);

sc.Parameters.Add(

"@.Field3",SqlDbType.VarChar, 15);

sc.Parameters.Add(

"@.Field4",SqlDbType.VarChar, 15);

sc.Parameters[

"@.DocumentID"].Value = documentid;

sc.Parameters[

"@.DocumentName"].Value = docname;

sc.Parameters[

"@.documentData"].Value=fileimg;

sc.Parameters[

"@.documentType"].Value = doctype;

sc.Parameters[

"@.FileId"].Value = fileid;

sc.Parameters[

"@.Field1"].Value = f1;

sc.Parameters[

"@.Field2"].Value = f2;

sc.Parameters[

"@.Field3"].Value = f3;

sc.Parameters[

"@.Field4"].Value = f4;

sc.CommandText = query;

sc.ExecuteNonQuery();

|||

Hello again my friend,

I see it now. You are missing the @. symbols within the insert statement at the top of your code for every parameter after @.DocumentName. Besides this, have a space between ) and values. Also, you need to take out the comma after Field4 at the end of the values group so it ends in a bracket, not a comma with a bracket. I amend it as follows: -

query ="Insert into Documents (DocumentID,DocumentName,DocumentData,DocumentType,FileID,Field1,Field2,Field3,Field4) values (@.DocumentID,@.DocumentName, @.DocumentData, @.DocumentType, @.FileID, @.Field1, @.Field2, @.Field3, @.Field4)";

Kind regards

Scotty

|||

Hi,

Thanks, thank you very much..

Best Regards,

Adnan

Problem displaying image data from SQL Server

Hello,

I'm having problems saving and then displaying binary data in sql server.

I have a form that takes a file specified by the user and inserts this into sql server:

protected void btnUploadFile_Click(object sender, EventArgs e)
{
if (theFile.PostedFile != null)
{
if (theFile.PostedFile.ContentLength > 0)
{
byte[] docBuffer = new byte[theFile.PostedFile.ContentLength];
Response.Write(theFile.PostedFile.ContentType.ToString());

if (docBuffer.Length > 0)
{
// save to db
DbAccess dbAccess = new DbAccess(); // my helper function for all db access etc

try
{
dbAccess.BuildCommand("Incentives_SaveDocument");
dbAccess.Parameters.Add("@.docImage", SqlDbType.Image).Value = docBuffer;
dbAccess.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
}
}
}

Stored proc:

ALTER PROCEDURE Incentives_SaveDocument
@.docImage image
AS
SET NOCOUNT ON

INSERT INTO Table1 (theData) VALUES (@.docImage)

RETURN

This appears to work fine. I store the binary data in a image column and if I query the db it shows the row as <Binary>.

The problem I have is with retrieving the data and saving it to a file. The file saves OK but when I open it is contains lots of "squares" that I suppose are the binary - it doesn't show the text.

The code for retrieving/displaying the doc is:

protected void btnView_Click(object sender, EventArgs e)
{
DbAccess dbAccess = new DbAccess();
byte[] byteArray = null;

try
{
dbAccess.BuildCommand("Incentives_RetrieveDocument");
dbAccess.Parameters.Add("@.id", SqlDbType.Int).Value = 6; // id for the doc to return
SqlDataReader reader1 = dbAccess.ReturnDataReader();

while (reader1.Read())
{
if (reader1.HasRows)
{
byteArray = (byte[])reader1["theData"];
}
}
reader1.Close();

FileStream fs = new FileStream("file1", FileMode.CreateNew, FileAccess.Write);
fs.Write(byteArray, 0, byteArray.Length);
fs.Flush();
fs.Close();

FileInfo fileInfo = new FileInfo("file1");

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileInfo.Name);
HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
HttpContext.Current.Response.ContentType = "application/msword";
HttpContext.Current.Response.WriteFile(fileInfo.FullName);
HttpContext.Current.Response.End();

}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
dbAccess.CloseDbConnection();
}

The stored proc:

ALTER PROCEDURE Incentives_RetrieveDocument
@.id int
AS
SET NOCOUNT ON

SELECT * FROM Table1 WHERE id = @.id

RETURN

I would be grateful for any advice on this - its the first time I've worked with BLOB data.

Thanks

SiWhen you recreate a file from the image stored on the database, does it have the same extension?|||

Hello,

I've worked out what is was. I was uploading a byte array without the binary data in it. I needed the following to get it to work:

Stream dataStream = theFile.PostedFile.InputStream;
byte[] docBuffer = new byte[theFile.PostedFile.ContentLength];
int n = dataStream.Read(docBuffer, 0, theFile.PostedFile.ContentLength);

Si