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 creating varbinary(max) data type

I'm unable to create a field with the type varbinary(max). When I try doing this with Management Studio, it tells me that the maximum length is 8000 bytes. I've also tried creating the field with DDL as shown below, but that doesn't work either. If I create the varbinary field with a length of 8000 or less, it works fine. Is there some trick to using varbinary(max)?

Thank you.

create table images (filename nvarchar(250) primary key,photo varbinary(max))The T-SQL folks may be able to help here.|||

Sounds like compatibility level is set to 80(2000) instead of 90 (2005). You can get the level by right clicking on your database and going to properties\options page.

You can use the system procedure sp_dbcmptlevel to adjust your database. See BOL here for more detail. http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||Whitney:

Your suggestion worked. Thank you very much!

problem creating varbinary(max) data type

I'm unable to create a field with the type varbinary(max). When I try doing this with Management Studio, it tells me that the maximum length is 8000 bytes. I've also tried creating the field with DDL as shown below, but that doesn't work either. If I create the varbinary field with a length of 8000 or less, it works fine. Is there some trick to using varbinary(max)?

Thank you.

create table images (filename nvarchar(250) primary key,photo varbinary(max))
The T-SQL folks may be able to help here.|||

Sounds like compatibility level is set to 80(2000) instead of 90 (2005). You can get the level by right clicking on your database and going to properties\options page.

You can use the system procedure sp_dbcmptlevel to adjust your database. See BOL here for more detail. http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||Whitney:

Your suggestion worked. Thank you very much!

Problem convert varbinary to float

Hello I have some problems with converting varbinary to float in T-SQL
stored procedure. In my C# application i have table of structures with
double type fields. Size of this table is variant. I have to send this
table to SP. Because of performance i want to send it only once and
whole. So I have to cut this varbinary blob in my Stored procedure to
receive structure field values. But I have problems with double fields
- conversion from varbinary to float in T-SQL is not allowed. Please
halp me - thx for any advise.
MaciejMaciej (maciej_ty@.poczta.onet.pl) writes:
> Hello I have some problems with converting varbinary to float in T-SQL
> stored procedure. In my C# application i have table of structures with
> double type fields. Size of this table is variant. I have to send this
> table to SP. Because of performance i want to send it only once and
> whole. So I have to cut this varbinary blob in my Stored procedure to
> receive structure field values. But I have problems with double fields
> - conversion from varbinary to float in T-SQL is not allowed. Please
> halp me - thx for any advise.

It is not clear how you send this table to SQL Server, but it sounds
like you are sending a binary string. This sounds messy.

The best is probably to format the data as XML, and then use
sp_xml_prepearedocument and OPENXML to unpack the data on the SQL Server
side.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp