How to send consecutive stored procedures and its parameters

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

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?
 
Sharon said:
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:
Click to expand...

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.
 
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?
Click to expand...

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.
 
Back
Top