SqlDataSource, SQL working, Stored procedure doesn't. Parameter hell

[Jim Andersen]

Just to let you know, and to help any future sorry sods who gets trapped in
the same black hole......

You can't just copy/move a working sql-statement into a stored procedure.

Working with a sqldatasource. Conflictdetection set to compareallvalues.
Oldvaluesparameterformatstring set to original_{0}
tblA has 2 fields. ID and MyText.

Deletecommand="Delete from tblA where ID=@original_ID"
No deleteparameters.

Works great.

Ok, so I've finished testing, and wanna move from embedded sql to using
stored procs instead.
So I change
Deletecommand="DelProc"
and
create DelProc (
@original_ID as nvarchar (255)
)
as
Delete from tblA where ID=@original_ID

BUT... I get errors stating there are too many arguments (or parameters) to
DelProc. I have to change DelProc to
create DelProc (
@original_ID as nvarchar (255),
@original_MyText as nvarchar (255)
)

even though I don't use @original_MyText but have the same Delete statement
as before.

/jim

 

[Alex D.]

How are you calling the stored procedure? you need something like below:

SqlConnection conn = new SqlConnection(myConnectionString);
SqlCommand execProc = new SqlCommand("DelProc", conn);
execProc.CommandType = CommandType.StoredProcedure;

execProc.Parameters.Add("@original_ID ", SqlDbType.NVarChar, 255).Value =
YourValue;
execProc.ExecuteScalar();

Hope that helps,
Alex.

 

[Jim Andersen]

How are you calling the stored procedure? you need something like below:

SqlConnection conn = new SqlConnection(myConnectionString);
SqlCommand execProc = new SqlCommand("DelProc", conn);
execProc.CommandType = CommandType.StoredProcedure;

execProc.Parameters.Add("@original_ID ", SqlDbType.NVarChar, 255).Value =
YourValue;
execProc.ExecuteScalar();

But _I_ don't call the stored procedure. The SqlDataSource calls it. I just
tell it:

Deletecommand="DelProc"

I am working with a master-detail scenario using a GridView-DetailsView. And
the DetailsView is bound to SqlDataSource. I hit the Delete button in the
DetailsView, and the record gets deleted.

So I don't call my proc. The DetailsView tells the SqlDataSource to delete a
record. But SqlDataSource apparently uses parameters differently (or calls
differently, or handles errors differently) when the DeleteCommand has
Commandtype.Text instead of Commandtype.Storedprocedure.

As far as I can tell, the SqlDataSource does the same as you suggested, BUT
it also does:
execProc.Parameters.Add("@original_sortno ", SqlDbType.Int, 4).Value =
YourValue;

And my stored proc don't have, or need, a @original_sortno parameter. And
thats why I get a "U're calling DelProc with too many parameters, Dude!"
error message.

/jim

 

[Alex D.]

I think that is your mistake, you need to do something like I stated before.
SqlCommand need to be set to CommandType.StoredProcedure. What happens is
that the way you are doing it is for TransactSQL constructions but not for
calling stored procedures.