?
Solved

ADO.NET Stored procedure won't INSERT Binary data

Posted on 2003-02-26
7
Medium Priority
?
438 Views
Last Modified: 2010-05-18
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;
0
Comment
Question by:HarmnKardn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 23

Expert Comment

by:naveenkohli
ID: 8028913
Did you ttry this....

cmd.Parameters.Add("@data", SqlDbType.Binary).Value = tb.data;
0
 
LVL 1

Author Comment

by:HarmnKardn
ID: 8033799
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.
0
 

Expert Comment

by:CodeRat
ID: 8034128
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:Mindphaser
ID: 8034155
Folks please repost in http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_20530703.html . This question is a dupliacte and will be deleted soon.

** Mindphaser - Community Support Moderator **
0
 

Expert Comment

by:CodeRat
ID: 8034160
P.S I know that isnt how you do an SP I just wanted to show a hardcode what I did....
0
 
LVL 1

Author Comment

by:HarmnKardn
ID: 8034572
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
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 8045269
Points refunded and placed in PAQ

Computer101
E-E Admin
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question