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

Mar 2 '06 #1

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" <no****@nospam.dk> wrote in message
news:e5**************@TK2MSFTNGP11.phx.gbl...

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

Mar 2 '06 #2

Jim Andersen

"Alex D." <al********@hotmail.com> skrev i en meddelelse
news:Oz*************@TK2MSFTNGP15.phx.gbl...

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

"Jim Andersen" <no****@nospam.dk> wrote in message
news:e5**************@TK2MSFTNGP11.phx.gbl...

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

Mar 3 '06 #3

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.

"Jim Andersen" <no****@nospam.dk> wrote in message
news:ex*************@TK2MSFTNGP14.phx.gbl...

"Alex D." <al********@hotmail.com> skrev i en meddelelse
news:Oz*************@TK2MSFTNGP15.phx.gbl...

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

"Jim Andersen" <no****@nospam.dk> wrote in message
news:e5**************@TK2MSFTNGP11.phx.gbl...

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

Mar 3 '06 #4