Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ADO.NET Stored procedure won't INSERT Binary data

Posted on 2003-02-26
7
Medium Priority
?
441 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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

580 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