Stored Procedure

[fadi-ft]

Hello DW Forum, i am a new member between you and this is my first post

i am building a website mostly uses Stored Procedure, but now i am facing a problem such that i want to call the SP more than one time from the .vb page

this is my SQL SP:

ALTER PROCEDURE dbo.plnCs
	@plan int,
	@level int 	
AS
	SELECT p.CNo, c.CLevel FROM Plans p, Course c 
	WHERE p.CNo=c.CNo AND p.[Year]=@plan AND c.CLevel=@level
	RETURN

and this is my VB statements that call the SP:

...
csrDa.SelectCommand.CommandType = CommandType.StoredProcedure
		csrDa.SelectCommand.Parameters.AddWithValue("@plan", plan)
		csrDa.SelectCommand.Parameters.AddWithValue("@level", x)

the values of x range from 1 to 8

please help
thx

[dnanetwork]

so wot's the problem..

r u getting any exceptions..?

why u wanna use store pro more than once..

i need lil more brief explanation..from your site !

[paslanmaz]

I am adding a code previously used

public static void Store_ProcedureRun(string sql, string Haber_icerik, string Glen_Haber_icerik, string DilID, string Haber_Baslik, string Haber_Tarih, string Haber_tur, string Haber_Resim, string HResim_Yer, string Haber_Video, string Haber_Durum)
    {


        SqlCommand sqlCommand = new SqlCommand(sql, sqlBaglanti);
        sqlCommand.CommandType = CommandType.StoredProcedure;

        SqlParameter HaberIcerik = sqlCommand.Parameters.Add(Haber_icerik, SqlDbType.Text);
        HaberIcerik.Value = Glen_Haber_icerik;

        SqlParameter DilID_g = sqlCommand.Parameters.Add("@DilID", SqlDbType.VarChar);
        DilID_g.Value = DilID;

        SqlParameter Haber_Baslik_g = sqlCommand.Parameters.Add("@Haber_Baslik", SqlDbType.Text);
        Haber_Baslik_g.Value = Haber_Baslik;

        SqlParameter Haber_Tarih_g = sqlCommand.Parameters.Add("@Haber_Tarih", SqlDbType.NVarChar);
        Haber_Tarih_g.Value = Haber_Tarih;

        SqlParameter Haber_tur_g = sqlCommand.Parameters.Add("@Haber_tur", SqlDbType.NVarChar);
        Haber_tur_g.Value = Haber_tur;

        SqlParameter Haber_Resim_g = sqlCommand.Parameters.Add("@Haber_Resim", SqlDbType.NVarChar);
        Haber_Resim_g.Value = Haber_Resim;

        SqlParameter HResim_Yer_g = sqlCommand.Parameters.Add("@HResim_Yer", SqlDbType.NVarChar);
        HResim_Yer_g.Value = HResim_Yer;

        SqlParameter Haber_Video_g = sqlCommand.Parameters.Add("@Haber_Video", SqlDbType.Text);
        Haber_Video_g.Value = Haber_Video;

        SqlParameter Haber_Durum_g = sqlCommand.Parameters.Add("@Haber_Durum", SqlDbType.NVarChar);
        Haber_Durum_g.Value = Haber_Durum;

        SqlParameter Haber_Hit_g = sqlCommand.Parameters.Add("@Haber_Hit", SqlDbType.NVarChar);
        Haber_Hit_g.Value = "0";



        if (sqlCommand.Connection.State == ConnectionState.Closed)
        {
            sqlBaglanti.Open();
        }

        sqlCommand.ExecuteNonQuery();
        sqlBaglanti.Close();
    }

[jbisono]

I still dont get what is your problem, but it sounds like you need a recursive function or a loop somewhere. you have to explain a little bit more.

[fadi-ft]

i have a SP that i need to execute more than one time, each time with a different value for its parameters, like in the above code

csrDa.SelectCommand.Parameters.AddWithValue("@level", x)

this statement must exist within a loop and x has to change each iteration !!

that's it

[jbisono]

look i can give you a loop for that right now, but let me ask you this, why if you want to retrieve all the record where the level goes from 1 to 8, why dont you say and your stored procedure "where level between 1 and 8" and that case you make just one request to the server and get all the record you need.

[fadi-ft]

hey Mr. jbisono
but the idea says that each iteration result will be filled in the DataSet and then viewed and so on such that:

level (1) courses
CNo CPlan CLevel
1 2007 1

CNo CPlan CLevel
2 2007 2

CNo CPlan CLevel
3 2007 5
4 2007 5

[reach_yousuf]

Hi fadi

jbisono is right, you can fetch all records at once and fill in single dataset and then you can filter dataset ..,

Here is the code

dim ds as new dataset
ds = GetData() '// you logic which fetches data using SP once
'// Now filter the result offline 
Gridview1.datasource = ds.tables(0).select("Cno=" & 1)
Gridview.DataBind()
//Display in another  Grid 
dim StrYear as string = "2010"
Gridview2.datasource = ds.tables(0).select("Year='" & StrYear & "'")
Gridview.DataBind()

Pls. mark as solved if it helps you!!!

[fadi-ft]

Hey guys,

you know under pressure your thinking process sometimes take a rest. and that what happened with me :-)

jbisono, reach_yousuf:

Sorry for the late reply we are sufferig from electricit. I think your solution fits my needs more and more logical, my solution was very exhausting to the server and a heavy one. so thanx very much.

marked as solved. and goes to you guys (jbisono, reach_yousuf)

regards