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
10
Medium Priority
?
442 Views
Last Modified: 2012-08-13
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
  • 6
  • 2
9 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8026784
notice the difference in these two lines:

cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = sUser;          
cmd.Parameters.Add("@data", tb.data);


in the first line, you assign the VALUE of the input parameter to be sUser

in the Second, you simply add the parameter, with NO VALUE Associated with it.

in turn, the second argument of the Add Method is SUPPOSED to be the TYPE of data to badded, NOT the Value of THAT data.

see if that points you in the right direction.

AW
0
 
LVL 1

Author Comment

by:HarmnKardn
ID: 8028400
No...my c# code is fine.  I was simply experimenting with a different overload for the .Add method there.  The stored procedure is whats actually broken.  When I run it from Query Analyzer it doesn't work.

Still looking for an answer...
0
 
LVL 1

Author Comment

by:HarmnKardn
ID: 8028444
Here's an updated copy of my C# code, even though I don't think this is the problem.

//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", SqlDbType.VarChar).Value = tb.data;
         
//execute the stored procedure and return the Stuff id
sqlConn.Open();
cmd.ExecuteNonQuery();
sqlConn.Close();
return (int)cmd.Parameters["RETVAL"].Value;
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 1

Author Comment

by:HarmnKardn
ID: 8028456
Grrr...ignore that last post: this is the current 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", SqlDbType.Binary).Value = tb.data;
         
//execute the stored procedure and return the Stuff id
sqlConn.Open();
cmd.ExecuteNonQuery();
sqlConn.Close();
return (int)cmd.Parameters["RETVAL"].Value;
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8030860
by the way, stopping hitting the REFRESH button on your browser, and instead, using the Reload This Question link on the EE Menu at the Top Left of the Screen.  You have posted a large number of DUPLICATES of this question, each one coting you 50 points.  Post a 0 point question to Community Support requesting that the un-intended duplicated be deleted, and the points will be refunded to you.

AW
0
 
LVL 1

Author Comment

by:HarmnKardn
ID: 8033544
I already noticed this and posted a message to community support.  Very annoying.  I'm sure a few more lines of Java would solve that problem.
0
 
LVL 1

Author Comment

by:HarmnKardn
ID: 8034220
Nevermind, I found the answer to my problem.  The size of the binary object in the stored procedure needs to be explicitly set for some reason.  The following code works fine:

CREATE PROCEDURE dbo.sp_InsertButton
     @trader AS VARCHAR(4),
     @data AS BINARY(4096)
AS

INSERT INTO TraderButtons (TraderId, Data)
VALUES (@trader, @data)

RETURN @@identity
GO
0
 
LVL 1

Author Comment

by:HarmnKardn
ID: 8034227
Nevermind, I found the answer to my problem.  The size of the binary object in the stored procedure needs to be explicitly set for some reason.  The following code works fine:

CREATE PROCEDURE dbo.sp_InsertButton
     @trader AS VARCHAR(4),
     @data AS BINARY(4096)
AS

INSERT INTO TraderButtons (TraderId, Data)
VALUES (@trader, @data)

RETURN @@identity
GO

0
 
LVL 1

Accepted Solution

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

Computer101
E-E Admin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month11 days, 21 hours left to enroll

564 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