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. Conflictdetecti on set to compareallvalue s.
Oldvaluesparame terformatstring set to original_{0}
tblA has 2 fields. ID and MyText.

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

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_MyTex t as nvarchar (255)
)

even though I don't use @original_MyTex t 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(m yConnectionStri ng);
SqlCommand execProc = new SqlCommand("Del Proc", conn);
execProc.Comman dType = CommandType.Sto redProcedure;

execProc.Parame ters.Add("@orig inal_ID ", SqlDbType.NVarC har, 255).Value =
YourValue;
execProc.Execut eScalar();

Hope that helps,
Alex.
"Jim Andersen" <no****@nospam. dk> wrote in message
news:e5******** ******@TK2MSFTN GP11.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. Conflictdetecti on set to compareallvalue s.
Oldvaluesparame terformatstring set to original_{0}
tblA has 2 fields. ID and MyText.

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

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_MyTex t as nvarchar (255)
)

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

/jim

[Jim Andersen]

"Alex D." <al********@hot mail.com> skrev i en meddelelse
news:Oz******** *****@TK2MSFTNG P15.phx.gbl...

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

SqlConnection conn = new SqlConnection(m yConnectionStri ng);
SqlCommand execProc = new SqlCommand("Del Proc", conn);
execProc.Comman dType = CommandType.Sto redProcedure;

execProc.Parame ters.Add("@orig inal_ID ", SqlDbType.NVarC har, 255).Value =
YourValue;
execProc.Execut eScalar();
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.Tex t instead of Commandtype.Sto redprocedure.

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

And my stored proc don't have, or need, a @original_sortn o 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******** ******@TK2MSFTN GP11.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. Conflictdetecti on set to compareallvalue s.
Oldvaluesparame terformatstring set to original_{0}
tblA has 2 fields. ID and MyText.

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

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_MyTex t as nvarchar (255)
)

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

/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.Sto redProcedure. 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******** *****@TK2MSFTNG P14.phx.gbl...

"Alex D." <al********@hot mail.com> skrev i en meddelelse
news:Oz******** *****@TK2MSFTNG P15.phx.gbl...

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

SqlConnection conn = new SqlConnection(m yConnectionStri ng);
SqlCommand execProc = new SqlCommand("Del Proc", conn);
execProc.Comman dType = CommandType.Sto redProcedure;

execProc.Parame ters.Add("@orig inal_ID ", SqlDbType.NVarC har, 255).Value =
YourValue;
execProc.Execut eScalar();

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.Tex t instead of Commandtype.Sto redprocedure.

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

And my stored proc don't have, or need, a @original_sortn o 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******** ******@TK2MSFTN GP11.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. Conflictdetecti on set to compareallvalue s.
Oldvaluesparame terformatstring set to original_{0}
tblA has 2 fields. ID and MyText.

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

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_MyTex t as nvarchar (255)
)

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

/jim