How to send consecutive stored procedures and its parameters
 

Hi Community

I have an ASP.NET web project. In this project I am consecutively
sending and passing 5 different stored procedures along with a parameter to
a receiving method. The first stored procedure (sp1) and its parameter gets
processed by the receiving method without a problem but when the second
stored procedure (sp2) gets passed with its parameter to the receiving method
the receiving method crashes and the program aborts. Below is an example of
how I am trying to pass the 5 stored procedures along with its parameter to
the receiving method,
the lines that it seems to crash on are marked:

***below is the are the stored procedures getting passed consecutively

Public DataSet getDS()
{
CreateCmd ("sp1", @Params, ref ds);
CreateCmd ("sp2", @Params, ref ds);
CreateCmd ("sp3", @Params, ref ds);
CreateCmd ("sp4", @Params, ref ds);
CreateCmd ("sp5", @Params, ref ds);
}


***below is the method that receives and processes the stored procedures
sent above

private SqlCommand CreateCmd(string strSql, SqlParameter[] Param,
CommandType SQLCmdType)
{
Conn = new SqlConnection(GetConn ());
SqlCommand Createcmd = new SqlCommand(strSql, Conn);


Createcmd.CommandType = SQLCmdType;

if (Param != null) <<< crash
{
foreach (SqlParameter p1 in Param) ) <<< crash

{
Createcmd.Parameters.Add(p1); ) <<< crash

}
}
Conn.Open();
return Createcmd;

}

My question is how can I pass consecutive stored procedurs with their
parameters to another method to be processed?

Re: How to send consecutive stored procedures and its parameters
 

Sharon wrote:
> Hi Community
>
> I have an ASP.NET web project. In this project I am consecutively
> sending and passing 5 different stored procedures along with a parameter to
> a receiving method. The first stored procedure (sp1) and its parameter gets
> processed by the receiving method without a problem but when the second
> stored procedure (sp2) gets passed with its parameter to the receiving method
> the receiving method crashes and the program aborts. Below is an example of
> how I am trying to pass the 5 stored procedures along with its parameter to
> the receiving method,
> the lines that it seems to crash on are marked:
>

So why can't you use one SP1, pass its parms, and all the other parms to
all the SP(s) to SP1. Then SP1 does its resultset and calls the other
SP(s)to produce their resultsets, a multiple results resultset?

Then you use a SQL DataReader read the first resultset, get the
infromation, do reader.nextresult read the data, etc, etc.

Re: How to send consecutive stored procedures and its parameters
 

On Feb 4, 12:29*am, Sharon <Sha...@discussions.microsoft.com> wrote:
> Hi Community
>
> * * I have an ASP.NET web project. *In this project I am consecutively
> sending and *passing 5 different stored procedures along with a parameter to
> a receiving method. *The first stored procedure (sp1) *and its parameter gets
> processed by the receiving method without a problem but when the second
> stored procedure (sp2) gets passed with its parameter to the receiving method
> the receiving method crashes and the program aborts. Below is an example of
> how I am trying to pass the 5 stored procedures along with its parameter to
> the receiving method,
> the lines that it seems to crash on are marked:
>
> ***below is the are the stored procedures getting passed consecutively
>
> Public DataSet getDS()
> {
> * * CreateCmd ("sp1", @Params, ref ds);
> * * CreateCmd ("sp2", @Params, ref ds);
> * * CreateCmd ("sp3", @Params, ref ds);
> * * CreateCmd ("sp4", @Params, ref ds);
> * * CreateCmd ("sp5", @Params, ref ds);
>
> }
>
> ***below is the method that receives and processes the stored procedures
> sent above
>
> * * * *private SqlCommand CreateCmd(string strSql, SqlParameter[] Param,
> CommandType SQLCmdType)
> * * * * {
> * * * * * * Conn = new SqlConnection(GetConn ());
> * * * * * * SqlCommand Createcmd = new SqlCommand(strSql, Conn);
>
> * * * * * * Createcmd.CommandType = SQLCmdType;
>
> * * * * * * if (Param != null) * * * * * * * * * * * * *<<< crash
> * * * * * * {
> * * * * * * * * foreach (SqlParameter p1 in Param) ) * *<<< crash
>
> * * * * * * * * {
> * * * * * * * * * * Createcmd.Parameters.Add(p1); ) * * <<< crash
>
> * * * * * * * * }
> * * * * * * }
> * * * * * * Conn.Open();
> * * * * * * return Createcmd;
>
> * * * * }
>
> My question is how can I pass consecutive stored procedurs with their
> parameters to another method to be processed?

I don't get what does "@Params" mean when you call CreateCmd()? You
need to pass an array of SqlParameter[]

CreateCmd ("sp1", @Params, ref ds);

And why "ref ds" is used? I think you don't need to refer to the same
variable there. Get rid of ref.