I want to INSERT INTO a table in a record varbinary(max) a Byte array How can I do that?
Add a comment
|
2 Answers
Using a stored procedure, just create a parameter of type varbinary(max) and insert it into the table as you would any data type.
In your c# (or vb or whatever) code, add a parameter to your sql command object and set the byte array as the parameter value:
command.Parameters.AddWithValue("@parameter_name", myByteArray);
If not using a stored procedure, you can probably do the same with a parameterized sql statement, but I have never tried that, so I can't give an example.
Edit:
You are using a parameterized query, which is not my thing, so I can't guarantee this will work. But, here is some code that should get you going.
RemoteSQLcmd = New SqlCommand("INSERT INTO Table(1) Values (newid(), ProductID, @bin_value", RemoteSQLConn)
RemoteSQLcmd.Parameters.AddWithValue(@bin_value", imSource) ;
The binary value must be represented as a parameter (@bin_value), and the value is set by the AddWithValue statement. The parameter name does not have to match the column name.
A couple of notes: I would suggest using columns names in your insert statement rather than depending on column position, Also, I don't know what you mean by 'table(1)' - is that actually the name of the table?
6 Comments
Lefteris Gkinis
Does this '@parameter_name' has got to do with the column name? Or else how this command will understand that the myByteArray will passed to the particular column?
Lefteris Gkinis
I'm Using this code:________ RemoteSQLcmd = New SqlCommand("INSERT INTO Table(1) Values (newid(), ProductID, CAST('' as varbinary(max)), RemoteSQLConn) --------------------------------------------------------------- And now i'm puting your line ------------------------------------ RemoteSQLcmd.Parameters.AddWithValue("@Column_name", imSource) ------------------------------------ But that doesn't work always write 0x on the record
Lefteris Gkinis
Dear Ray I did the whay you write me to do and returns me the error of:----- Incorrect syntax near '@bin_value' and the code i use is:--------------------------------------------- VALUES (newid(), " & ProductID & ", @bin_value" And the Parameter is:---------------------------------------------- RemoteSQLcmd.Parameters.AddWithValue("@bin_value", imSource)
Lefteris Gkinis
But i use your instraction insite to my code, i don't use stored procedure
Lefteris Gkinis
I really ask myselfe if i will not use the '@' sign what will happen
|
Assuming:
CREATE TABLE [dbo].[varb_table] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[BinaryData] [varbinary](max) NULL,
CONSTRAINT [PK_varb_table] PRIMARY KEY CLUSTERED
)
Use:
INSERT INTO varb_table
(ID, BinaryData)
VALUES
(NULL, 0x);
DECLARE @pk
SET @pk = @@IDENTITY;
UPDATE varb_table
SET BinaryData.Write (@your_byte_array, 0, DATALENGTH(BinaryData))
WHERE ID = @pk
