CODEWITHSUNDEEP
c#sqlparameters
Tim
Tim

Reputation: 2911

run stored sql procedure with output parameter from c#

I realize that this question has been answered before, and I have looked through the answers, trying to find the error of my ways, but am at a standstill. I have the following stored procedure that should return a unique key. 

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER   PROCEDURE [dbo].[newid] @tablename AS CHAR(10), @fieldname AS CHAR(15) = '', @ioffset AS INT, @theID as int = 0 output 
AS
BEGIN
  SET NOCOUNT ON

 DECLARE @value AS INT
 IF @ioffset < 1 SET @ioffset = 1
 IF LEN(@fieldname) > 0
  BEGIN
    UPDATE id SET @value = CONVERT(INT,cvalue)+@ioffset-1, cvalue = CONVERT(CHAR,(CONVERT(INT,cvalue)+@ioffset)) WHERE UPPER(ctablename) = UPPER(@tablename) AND UPPER(cfieldname) = UPPER(@fieldname)
   SELECT @value
  END
 ELSE
  BEGIN
   UPDATE id SET @value = CONVERT(INT,cvalue)+@ioffset-1, cvalue = CONVERT(CHAR,(CONVERT(INT,cvalue)+@ioffset)) WHERE UPPER(ctablename) = UPPER(@tablename)
   SELECT @value 
  End
 SET NOCOUNT OFF
set @theID = @value
END

I am using the following code to try to access the procedure:

using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand command = new SqlCommand("dbo.NewID", connection)
     { CommandType = CommandType.StoredProcedure } )
{
     command.Parameters.Add( new SqlParameter("@tablename",table));
     command.Parameters.Add( new SqlParameter("@fieldname",field));
     command.Parameters.Add( new SqlParameter("@ioffset",1));
     SqlParameter key = new SqlParameter("@theID", SqlDbType.Int);
     key.Direction = ParameterDirection.Output;
     command.Parameters.Add(key);

     connection.Open();
     result = command.ExecuteNonQuery();
     connection.Close();
}

key.Value is coming back empty, and result is -1, insinuating that there was an error running the procedure. If anyone sees what I might be doing wrong, I would greatly appreciate the assistance.

Just a note: I am not able to edit the actual procedure, as there are several classes that already make use of it.

Thank you very much in advance for any assistance.

Upvotes: 3

Views: 2439

Answers (3)

Tim
Tim

Reputation: 2911

RESOLVED

Thank you to all of the many replies that I got. I took Steve's advice, and ran the procedure from SQL Management Studio, and ran into problems there as well. After fiddling around with my input a bit, I found that I was using

String table = "dbo.tablename";

When I changed this to

String table = "tablename";

everything started working as expected.

This was a stupid mistake on my part, cause by not having a firm enough grasp of SQL. Thank you again everyone for your wonderful suggestions.

Upvotes: 1

Steve
Steve

Reputation: 216293

I will try to change this line:

command.Parameters.Add(new SqlParameter("@ioffset",1));

in

SqlParameter pOffset = command.Parameters.Add( new SqlParameter("@ioffset", SqlDbType.Int)); 
pOffset.Value = 1;

There is a known bug in the constructor you are using. Look at the community comments on MSDN
This problem has been somewhat resolved by the new method SqlParameter.AddWithValue that removed the ambiguity of the standard constructor

Upvotes: 2

Hogan
Hogan

Reputation: 70523

 command.Parameters.Add( new SqlParameter("@tablename",table));
 command.Parameters.Add( new SqlParameter("@fieldname",field));
 command.Parameters.Add( new SqlParameter("@ioffset",1));

If you add to the parameter list like this you should specify the type of the parameter.

Or you could do it like this which will probably work:

 command.Parameters.AddWithValue("@tablename",table);
 command.Parameters.AddWithValue("@fieldname",field);
 command.Parameters.AddWithValue("@ioffset",1);

Try it.

Upvotes: 0

Related Questions