Link to home
Start Free TrialLog in
Avatar of HarmnKardn
HarmnKardn

asked on

ADO.NET Stored procedure won't INSERT Binary data

Okay, basically I have a stored procedure that doesn't seem to work.  The SP should insert a new row (updating a username and data column) into the DB and return an identity value.  Well, it inserts the username okay, returns the unique identifier, but never sets the @data passed to it.  The column type is Binary, size 4096, I am passing it a (c#) SqlBinary of the same size.

Looked at the SqlCommand right before I execute, the parameters look fine.  Any ideas?


Here is my SP:

CREATE PROCEDURE dbo.sp_InsertStuff
     @user AS VARCHAR(4),
     @data AS BINARY
AS

INSERT INTO UserStuff (UserId, Data)
VALUES (@user , @data)

RETURN @@identity
GO


Here is the C# code:

//stuff does not exist, call stored procedure to insert it
SqlCommand cmd = new SqlCommand("sp_InsertStuff", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;

//set up the parameters
SqlParameter parm = cmd.Parameters.Add("RETVAL", SqlDbType.Int);
parm.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = sUser;          
cmd.Parameters.Add("@data", tb.data);
// tried this instead, didn't help
//SqlParameter binaryShit = new SqlParameter("@data", tb.data);
//cmd.Parameters.Add(binaryShit);
         
//execute the stored procedure and return the stuff id
sqlConn.Open();
//note that the data in the third parameter of cmd looks fine here
cmd.ExecuteNonQuery();
//no exception is thrown, record inserted into DB, but nothing in the data column
sqlConn.Close();
return (int)cmd.Parameters["RETVAL"].Value;
Avatar of naveenkohli
naveenkohli

Did you ttry this....

cmd.Parameters.Add("@data", SqlDbType.Binary).Value = tb.data;
Avatar of HarmnKardn

ASKER

Yes, that's what i'm using right now.  It's not a problem with the C# code (I don't think).  Testing the SP in query analyzer yields the same results...it won't insert the binary data.
I have never used BINARY but have u tryed to test the data which you INSERT is infact BINARY, uh no wait if it wasnt .NET would have told you its wrong hmmmm.... I did this I dont know if it is right but it worked... just as a test I created the db abd all with every thing you described... maybe you are passing weird data... I dont know anyway tell me how it goes.

DECLARE @user VARCHAR(4)
SET @user = 'Ones'
DECLARE @data BINARY
SET @data = 0x4F

INSERT INTO UserStuff (UserId, Data)
VALUES (@user , @data)

RETURN @@identity
GO
Folks please repost in https://www.experts-exchange.com/questions/20530703/ADO-NET-Stored-procedure-won't-INSERT-Binary-data.html . This question is a dupliacte and will be deleted soon.

** Mindphaser - Community Support Moderator **
P.S I know that isnt how you do an SP I just wanted to show a hardcode what I did....
I solved the problem...I had to explicitly label the size of the Binary parameter in the SP.

CREATE PROCEDURE dbo.sp_InsertStuff
    @user AS VARCHAR(4),
    @data AS BINARY(4096)
AS
INSERT INTO UserStuff (UserId, Data)
VALUES (@user , @data)
RETURN @@identity
GO
ASKER CERTIFIED SOLUTION
Avatar of Computer101
Computer101
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial