?
Solved

ADO.NET Stored procedure won't INSERT Binary data

Posted on 2003-02-26
10
Medium Priority
?
436 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
[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
  • 6
  • 2
10 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
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.

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

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