| Groups | Blog | Home |
sql server programming : problem with looping in code-behind / stored procedure
On Fri, 23 Feb 2007 16:57:16 +0100, "Francis" <fr@nospam.sd> wrote: >Consider this code-behind and below the stored procedure: everything works >fine, except that the three records have all the same data (the last value: >30). Any idea what to change in the code-behind or in the stored procedure >to fix that? > For x = 1 To 3 > comd.Parameters("@field1").Value = 10 * x > comd.Parameters("@field2").Value = 10 * x > Next > connection.Open() > comd.ExecuteNonQuery() > connection.close() The procedure is only called once, not three times, so I don't see how there can be three rows with all the same data. The one row will have the last value - 30 - since the procedure is called at the end. If the intention was to call the stored procedure threee times, with 10, 20 and 30, then the stored procedure would have to be IN the loop, not after it. Roy Harvey
Hi,
Consider this code-behind and below the stored procedure: everything works fine, except that the three records have all the same data (the last value: 30). Any idea what to change in the code-behind or in the stored procedure to fix that? Thanks a lot Francis code-behind: comd.CommandType = CommandType.StoredProcedure comd.Parameters.Add("@field1", SqlDbType.Int) comd.Parameters.Add("@field2", SqlDbType.Int) For x = 1 To 3 comd.Parameters("@field1").Value = 10 * x comd.Parameters("@field2").Value = 10 * x Next connection.Open() comd.ExecuteNonQuery() connection.close() ----------------------------------------------- stored procedure: ALTER PROCEDURE dbo.test ( @field1 int, @field2 int ) AS Declare @nrec int BEGIN TRAN SET @nrec = 1 While (@nrec <= 3) BEGIN INSERT INTO table(field1,field2) VALUES (@field1,@field2) SET @nrec = @nrec +1 End COMMIT TRAN
You only execute the procedure once, and for that execution, your client code assigned the value 30
for both input parameters. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Francis" <fr@nospam.sd> wrote in message news:u3woKN2VHHA.4764@TK2MSFTNGP05.phx.gbl... > Hi, > > Consider this code-behind and below the stored procedure: everything works fine, except that the > three records have all the same data (the last value: 30). Any idea what to change in the > code-behind or in the stored procedure to fix that? > > Thanks a lot > Francis > > > code-behind: > comd.CommandType = CommandType.StoredProcedure > comd.Parameters.Add("@field1", SqlDbType.Int) > comd.Parameters.Add("@field2", SqlDbType.Int) > > For x = 1 To 3 > comd.Parameters("@field1").Value = 10 * x > comd.Parameters("@field2").Value = 10 * x > Next > connection.Open() > comd.ExecuteNonQuery() > connection.close() > ----------------------------------------------- > stored procedure: > ALTER PROCEDURE dbo.test > ( > @field1 int, > @field2 int > ) > AS > > Declare @nrec int > BEGIN TRAN > SET @nrec = 1 > > While (@nrec <= 3) > BEGIN > > INSERT INTO table(field1,field2) > VALUES (@field1,@field2) > SET @nrec = @nrec +1 > > End > > COMMIT TRAN >
Thanks for replying (both).
Yoy are right, the stored procedure is executed once, but within that stored procedure, there is a loop that inserts three times a record. So there are effectively three records (i can see them in the table). i thought that the values in the loop in the stored procedure would be fed by the values in the loop in the code-behind. But appearantly, it's not. Any way to do that? "Roy Harvey" <roy_harvey@snet.net> schreef in bericht news:j46ut25u33qouf31m9vate4gmn2j3l7bik@4ax.com... [quoted text, click to view] > On Fri, 23 Feb 2007 16:57:16 +0100, "Francis" <fr@nospam.sd> wrote: > >>Consider this code-behind and below the stored procedure: everything works >>fine, except that the three records have all the same data (the last >>value: >>30). Any idea what to change in the code-behind or in the stored procedure >>to fix that? > >> For x = 1 To 3 >> comd.Parameters("@field1").Value = 10 * x >> comd.Parameters("@field2").Value = 10 * x >> Next >> connection.Open() >> comd.ExecuteNonQuery() >> connection.close() > > The procedure is only called once, not three times, so I don't see how > there can be three rows with all the same data. The one row will have > the last value - 30 - since the procedure is called at the end. > > If the intention was to call the stored procedure threee times, with > 10, 20 and 30, then the stored procedure would have to be IN the loop, > not after it. > > Roy Harvey > Beacon Falls, CT
Again, you fist loop three times in the client app, but don't do anything in the loop (except
assigning the variables). Then, *after* the loop, you execute the proc. Move the ExecuteNonQuery inside the loop (which will give you 9 rows if you also have a loop in the proc. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Francis" <fr@nospam.sd> wrote in message news:%23W$cUy3VHHA.1200@TK2MSFTNGP04.phx.gbl...
> Thanks for replying (both). > Yoy are right, the stored procedure is executed once, but within that stored procedure, there is a > loop that inserts three times a record. So there are effectively three records (i can see them in > the table). i thought that the values in the loop in the stored procedure would be fed by the > values in the loop in the code-behind. But appearantly, it's not. > Any way to do that? > > "Roy Harvey" <roy_harvey@snet.net> schreef in bericht > news:j46ut25u33qouf31m9vate4gmn2j3l7bik@4ax.com... >> On Fri, 23 Feb 2007 16:57:16 +0100, "Francis" <fr@nospam.sd> wrote: >> >>>Consider this code-behind and below the stored procedure: everything works >>>fine, except that the three records have all the same data (the last value: >>>30). Any idea what to change in the code-behind or in the stored procedure >>>to fix that? >> >>> For x = 1 To 3 >>> comd.Parameters("@field1").Value = 10 * x >>> comd.Parameters("@field2").Value = 10 * x >>> Next >>> connection.Open() >>> comd.ExecuteNonQuery() >>> connection.close() >> >> The procedure is only called once, not three times, so I don't see how >> there can be three rows with all the same data. The one row will have >> the last value - 30 - since the procedure is called at the end. >> >> If the intention was to call the stored procedure threee times, with >> 10, 20 and 30, then the stored procedure would have to be IN the loop, >> not after it. >> >> Roy Harvey >> Beacon Falls, CT > >
This of it this way:
You have a subroutine (the stored procedure) which has a loop in it. The subroutine accepts two parameters, loops three iterations and uses the parameters in the loop to insert into a table. The subroutine use the same parameter values for each iteration in the loop. This is what the subroutine does. Period. Now, how would a caller of the subroutine change the logic inside the subroutine? You can call the subroutine one time, two times etc. But that won't change what the subroutine does for each time you invoke it. Whether you use code behind, a stored procedure etc is irrelevant. If you don't want the subroutine to insert the same value three times (which it is now hard coded to do), you do have to change the subroutine (stored procedure). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Francis" <fr@nospam.sd> wrote in message news:Oegw0N6VHHA.480@TK2MSFTNGP02.phx.gbl...
> So, is this conclusion right: > with this stored procedure (containing a loop), there is no way by changing the code-behind to > obtain the three right expected records. It will be either 3 the same (with all the last value) or > 9 records (3x3)? > If you see the possibility in the code-behind to prevent that (leaving unchanged the proc), you > are welcome. > Thanks for your time anyway. > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> schreef in bericht > news:eC9vtS4VHHA.1432@TK2MSFTNGP02.phx.gbl... >> Again, you fist loop three times in the client app, but don't do anything in the loop (except >> assigning the variables). Then, *after* the loop, you execute the proc. Move the ExecuteNonQuery >> inside the loop (which will give you 9 rows if you also have a loop in the proc. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Francis" <fr@nospam.sd> wrote in message news:%23W$cUy3VHHA.1200@TK2MSFTNGP04.phx.gbl... >>> Thanks for replying (both). >>> Yoy are right, the stored procedure is executed once, but within that stored procedure, there is >>> a loop that inserts three times a record. So there are effectively three records (i can see them >>> in the table). i thought that the values in the loop in the stored procedure would be fed by the >>> values in the loop in the code-behind. But appearantly, it's not. >>> Any way to do that? >>> >>> "Roy Harvey" <roy_harvey@snet.net> schreef in bericht >>> news:j46ut25u33qouf31m9vate4gmn2j3l7bik@4ax.com... >>>> On Fri, 23 Feb 2007 16:57:16 +0100, "Francis" <fr@nospam.sd> wrote: >>>> >>>>>Consider this code-behind and below the stored procedure: everything works >>>>>fine, except that the three records have all the same data (the last value: >>>>>30). Any idea what to change in the code-behind or in the stored procedure >>>>>to fix that? >>>> >>>>> For x = 1 To 3 >>>>> comd.Parameters("@field1").Value = 10 * x >>>>> comd.Parameters("@field2").Value = 10 * x >>>>> Next >>>>> connection.Open() >>>>> comd.ExecuteNonQuery() >>>>> connection.close() >>>> >>>> The procedure is only called once, not three times, so I don't see how >>>> there can be three rows with all the same data. The one row will have >>>> the last value - 30 - since the procedure is called at the end. >>>> >>>> If the intention was to call the stored procedure threee times, with >>>> 10, 20 and 30, then the stored procedure would have to be IN the loop, >>>> not after it. >>>> >>>> Roy Harvey >>>> Beacon Falls, CT >>> >>> >> > >
Ok thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> schreef in bericht news:u5$U7w%23VHHA.1180@TK2MSFTNGP05.phx.gbl... [quoted text, click to view] > This of it this way: > > You have a subroutine (the stored procedure) which has a loop in it. The > subroutine accepts two parameters, loops three iterations and uses the > parameters in the loop to insert into a table. The subroutine use the same > parameter values for each iteration in the loop. This is what the > subroutine does. Period. > > Now, how would a caller of the subroutine change the logic inside the > subroutine? You can call the subroutine one time, two times etc. But that > won't change what the subroutine does for each time you invoke it. > > Whether you use code behind, a stored procedure etc is irrelevant. > > If you don't want the subroutine to insert the same value three times > (which it is now hard coded to do), you do have to change the subroutine > (stored procedure). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Francis" <fr@nospam.sd> wrote in message > news:Oegw0N6VHHA.480@TK2MSFTNGP02.phx.gbl... >> So, is this conclusion right: >> with this stored procedure (containing a loop), there is no way by >> changing the code-behind to obtain the three right expected records. It >> will be either 3 the same (with all the last value) or 9 records (3x3)? >> If you see the possibility in the code-behind to prevent that (leaving >> unchanged the proc), you are welcome. >> Thanks for your time anyway. >> >> >> >> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> >> schreef in bericht news:eC9vtS4VHHA.1432@TK2MSFTNGP02.phx.gbl... >>> Again, you fist loop three times in the client app, but don't do >>> anything in the loop (except assigning the variables). Then, *after* the >>> loop, you execute the proc. Move the ExecuteNonQuery inside the loop >>> (which will give you 9 rows if you also have a loop in the proc. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> "Francis" <fr@nospam.sd> wrote in message >>> news:%23W$cUy3VHHA.1200@TK2MSFTNGP04.phx.gbl... >>>> Thanks for replying (both). >>>> Yoy are right, the stored procedure is executed once, but within that >>>> stored procedure, there is a loop that inserts three times a record. So >>>> there are effectively three records (i can see them in the table). i >>>> thought that the values in the loop in the stored procedure would be >>>> fed by the values in the loop in the code-behind. But appearantly, it's >>>> not. >>>> Any way to do that? >>>> >>>> "Roy Harvey" <roy_harvey@snet.net> schreef in bericht >>>> news:j46ut25u33qouf31m9vate4gmn2j3l7bik@4ax.com... >>>>> On Fri, 23 Feb 2007 16:57:16 +0100, "Francis" <fr@nospam.sd> wrote: >>>>> >>>>>>Consider this code-behind and below the stored procedure: everything >>>>>>works >>>>>>fine, except that the three records have all the same data (the last >>>>>>value: >>>>>>30). Any idea what to change in the code-behind or in the stored >>>>>>procedure >>>>>>to fix that? >>>>> >>>>>> For x = 1 To 3 >>>>>> comd.Parameters("@field1").Value = 10 * x >>>>>> comd.Parameters("@field2").Value = 10 * x >>>>>> Next >>>>>> connection.Open() >>>>>> comd.ExecuteNonQuery() >>>>>> connection.close() >>>>> >>>>> The procedure is only called once, not three times, so I don't see how >>>>> there can be three rows with all the same data. The one row will have >>>>> the last value - 30 - since the procedure is called at the end. >>>>> >>>>> If the intention was to call the stored procedure threee times, with >>>>> 10, 20 and 30, then the stored procedure would have to be IN the loop, >>>>> not after it. >>>>> >>>>> Roy Harvey >>>>> Beacon Falls, CT >>>> >>>> >>> >> >> >
So, is this conclusion right:
with this stored procedure (containing a loop), there is no way by changing the code-behind to obtain the three right expected records. It will be either 3 the same (with all the last value) or 9 records (3x3)? If you see the possibility in the code-behind to prevent that (leaving unchanged the proc), you are welcome. Thanks for your time anyway. "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> schreef in bericht news:eC9vtS4VHHA.1432@TK2MSFTNGP02.phx.gbl... [quoted text, click to view] > Again, you fist loop three times in the client app, but don't do anything > in the loop (except assigning the variables). Then, *after* the loop, you > execute the proc. Move the ExecuteNonQuery inside the loop (which will > give you 9 rows if you also have a loop in the proc. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Francis" <fr@nospam.sd> wrote in message > news:%23W$cUy3VHHA.1200@TK2MSFTNGP04.phx.gbl... >> Thanks for replying (both). >> Yoy are right, the stored procedure is executed once, but within that >> stored procedure, there is a loop that inserts three times a record. So >> there are effectively three records (i can see them in the table). i >> thought that the values in the loop in the stored procedure would be fed >> by the values in the loop in the code-behind. But appearantly, it's not. >> Any way to do that? >> >> "Roy Harvey" <roy_harvey@snet.net> schreef in bericht >> news:j46ut25u33qouf31m9vate4gmn2j3l7bik@4ax.com... >>> On Fri, 23 Feb 2007 16:57:16 +0100, "Francis" <fr@nospam.sd> wrote: >>> >>>>Consider this code-behind and below the stored procedure: everything >>>>works >>>>fine, except that the three records have all the same data (the last >>>>value: >>>>30). Any idea what to change in the code-behind or in the stored >>>>procedure >>>>to fix that? >>> >>>> For x = 1 To 3 >>>> comd.Parameters("@field1").Value = 10 * x >>>> comd.Parameters("@field2").Value = 10 * x >>>> Next >>>> connection.Open() >>>> comd.ExecuteNonQuery() >>>> connection.close() >>> >>> The procedure is only called once, not three times, so I don't see how >>> there can be three rows with all the same data. The one row will have >>> the last value - 30 - since the procedure is called at the end. >>> >>> If the intention was to call the stored procedure threee times, with >>> 10, 20 and 30, then the stored procedure would have to be IN the loop, >>> not after it. >>> >>> Roy Harvey >>> Beacon Falls, CT >> >> >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |