locked
Stored Procedure Help RRS feed

  • Question

  • User-826730287 posted

    hello guys 

    I' am a new member in your wonderful forum, but I was following up since a long time!

    I am building a website that mostly uses Stored Procedures, but now I am facing a problem such that I want to execute the Stored Procedure more than one time each with a different value .

    such that when I am calling it from the .vb page I could not execute it more than one time 

    this is my sql SP code:


    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

    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 code:

    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 ,,,


    could you please help!

    thanx



    Friday, May 14, 2010 10:07 AM

Answers

  • User-1360095595 posted

    I can't really see your code.  But your idea is sound.  Just clear the parameters between calls to the SP and set them to the new values on every iteration.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 14, 2010 10:26 AM
  • User-1360095595 posted

    As I stated before, you'll need to iterate, as many times as you need to run the SP, and clear the Parameters collection on every itereation and providing the new set of parameters/values, and re-execute the command.  Does that make sense?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 14, 2010 11:43 AM
  • User-1360095595 posted

    Well I was thinking it would be in your code behind, the VB code.  That's what I meant by clearing the Parameters collection.  You execute Clear() on the Parameters collections, then you re-add the parameters with the new values, based in your iteration.  So you will use either a For loop, or a ForEach loop for this.  Give it a try and post back your updated code if you have problems.

    Apologies if I have misunderstood your needs...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 14, 2010 12:38 PM
  • User-1360095595 posted

    OK.  I see what you're doing and it makes sense to me perfectly now.

    I don't know if it performs better, but you might try these and see if you notice any difference (I suspect you won't):

    ds.Tables.Clear(); or ds.Tables[0].Rows.Clear();

    And instead of binding the gridview to ds, bind it to ds.Tables[0].

    The last one in particular (ds.Tables[0].Rows.Clear();) I like because it's only clearing the rows, so the table is not being recreated on every iteration.  Once again don't know if you'll see any performance gains, or if it's worth you time, but just in case...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 14, 2010 2:26 PM

All replies

  • User-1360095595 posted

    I can't really see your code.  But your idea is sound.  Just clear the parameters between calls to the SP and set them to the new values on every iteration.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 14, 2010 10:26 AM
  • User-826730287 posted

    thanx for reply, i have updated the post

    please check the code and may you clarify more !!

    Friday, May 14, 2010 10:46 AM
  • User-1360095595 posted

    As I stated before, you'll need to iterate, as many times as you need to run the SP, and clear the Parameters collection on every itereation and providing the new set of parameters/values, and re-execute the command.  Does that make sense?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 14, 2010 11:43 AM
  • User-826730287 posted

    It makes sense, I got the idea but this iteration process should be inside the SP or in the CodeFile ?? 

    if it inside ... this will form another problem to me Frown

    thanx again for reply MetalAsp.Net

    Friday, May 14, 2010 12:31 PM
  • User-1360095595 posted

    Well I was thinking it would be in your code behind, the VB code.  That's what I meant by clearing the Parameters collection.  You execute Clear() on the Parameters collections, then you re-add the parameters with the new values, based in your iteration.  So you will use either a For loop, or a ForEach loop for this.  Give it a try and post back your updated code if you have problems.

    Apologies if I have misunderstood your needs...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 14, 2010 12:38 PM
  • User-826730287 posted

    ok this is much clear, 

    I'll try and reply for you 


    thanx alot

    Friday, May 14, 2010 1:19 PM
  • User-826730287 posted

    Mr. MetalAsp.Net your solution is just working! So I want to thank you alot 

    I have to perform the clear() on DataSet too as it save the result of previous iteration

    I want to ask you about this solution in regarding to performance .... as we clear the parameters set and the dataset 


    God Save You


    this is the final source code for the For Loop


    For i As Integer = 1 To 8
    			gView(i) = New GridView()
    			csrDa.SelectCommand.Parameters.AddWithValue("@plan", plan)
    			csrDa.SelectCommand.Parameters.AddWithValue("@level", i)
    			csrDa.Fill(ds)
    			csrDa.SelectCommand.Parameters.Clear()
    			gView(i).DataSource = ds	
    			gView(i).DataBind()	'Bind to db
    			Panel1.Controls.Add(gView(i)) ' add the dynamically created GridView!
    			ds.Clear()
    		Next




    Friday, May 14, 2010 2:04 PM
  • User-1360095595 posted

    I want to ask you about this solution in regarding to performance .... as we clear the parameters set and the dataset 

     

    Clearing the Parameters is trivial I believe.  You're just clearing a collection.  The DataSet, I don't really know because I don't understand why you are clearing it as well.  But the best thing, I guess, is to test it out and see if the performance seems reasonable to you.

    Friday, May 14, 2010 2:09 PM
  • User-826730287 posted

    Ah, if i don't clear the DS it will repeat the result of the 1st iteration, 2nd iteration in the 3rd iteration for example, like this:

    CNo               CLevel

    1                       5


    CNo               CLevel

    1                       5

    2                       6


    CNo               CLevel

    1                       5

    2                       6

    3                       7


    Clevel is the value of (i) in the for loop, 

    Anyway, i will check it outside as you suggest, thanks


    Friday, May 14, 2010 2:15 PM
  • User-1360095595 posted

    I would think that you'd be iterating over the rows in your datatable within your dataset, so you wouldn't be duplicating your data.  But I have no view into your code behind so I'm just guessing / using my magical powers Smile, which aren't that magical/successful.

    Edit: I must be blind.  You did post your code!  Well I'm an idiot, or blind, or a combination of both.  Let me check it out...

    Friday, May 14, 2010 2:18 PM
  • User-826730287 posted

    believe me i did Frown 

    i am pasting it once again 


    For i As Integer = 1 To 8
    gView(i) = New GridView()
    csrDa.SelectCommand.Parameters.AddWithValue("@plan", plan)
    csrDa.SelectCommand.Parameters.AddWithValue("@level", i)
    csrDa.Fill(ds)
    csrDa.SelectCommand.Parameters.Clear()
    gView(i).DataSource = ds ' Set the DataSource of the gView object to be from stdCs (DataView Object)
    gView(i).DataBind() 'Bind to db
    Panel1.Controls.Add(gView(i)) ' add the dynamically created GridView!
    ds.Clear()
    Next

    For i As Integer = 1 To 8

    gView(i) = New GridView()

    csrDa.SelectCommand.Parameters.AddWithValue("@plan", plan)

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

    csrDa.Fill(ds)

    csrDa.SelectCommand.Parameters.Clear()

    gView(i).DataSource = ds

    gView(i).DataBind() 'Bind to db

    Panel1.Controls.Add(gView(i))

    ds.Clear()

    Next


    you say that i hava to use DataTable which i know nothing about!?

    Can You See the CODE

    You Have a Powerful Magical Power 


    Friday, May 14, 2010 2:24 PM
  • User-1360095595 posted

    OK.  I see what you're doing and it makes sense to me perfectly now.

    I don't know if it performs better, but you might try these and see if you notice any difference (I suspect you won't):

    ds.Tables.Clear(); or ds.Tables[0].Rows.Clear();

    And instead of binding the gridview to ds, bind it to ds.Tables[0].

    The last one in particular (ds.Tables[0].Rows.Clear();) I like because it's only clearing the rows, so the table is not being recreated on every iteration.  Once again don't know if you'll see any performance gains, or if it's worth you time, but just in case...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 14, 2010 2:26 PM
  • User-826730287 posted

    OK.  I see what you're doing and it makes sense to me perfectly now.

    I don't know if it performs better, but you might try these and see if you notice any difference (I suspect you won't):

    ds.Tables.Clear(); or ds.Tables[0].Rows.Clear();

    And instead of binding the gridview to ds, bind it to ds.Tables[0].

    The last one in particular (ds.Tables[0].Rows.Clear();) I like because it's only clearing the rows, so the table is not being recreated on every iteration.  Once again don't know if you'll see any performance gains, or if it's worth you time, but just in case...


    it seems its better like that to me too: clearing the rows instead of the whole table. I tried it and have exactly the same result. 

    Locally I cant notice differences between the two ways due to performance!

    Thanks Man!

    Friday, May 14, 2010 2:39 PM