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.StoredProcedur e;
//set up the parameters
SqlParameter parm = cmd.Parameters.Add("RETVAL ", SqlDbType.Int);
parm.Direction = ParameterDirection.ReturnV alue;
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(binar yShit);
//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["RETVA L"].Value;
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
cmd.CommandType = CommandType.StoredProcedur
//set up the parameters
SqlParameter parm = cmd.Parameters.Add("RETVAL
parm.Direction = ParameterDirection.ReturnV
cmd.Parameters.Add("@user"
cmd.Parameters.Add("@data"
// tried this instead, didn't help
//SqlParameter binaryShit = new SqlParameter("@data", tb.data);
//cmd.Parameters.Add(binar
//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["RETVA
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
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 **
** 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....
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
cmd.Parameters.Add("@data"