an sql command that doest work in a page

Hello,

I have a sequense of sql commands in order to recursively update a table that has parents/childs

After I create a temporary table, I need to run an sql command that for some reason is not working. No errors, the command is actually excecuted, but I beieve the rowcount is 0 from the beggining

Here is the command:

Dim

InsertConnectionAs Data.SqlClient.SqlConnection =New System.Data.SqlClient.SqlConnection("Server=myServer;User ID=myUser;pwd=myPSW;Database=myDatabase")Dim SqlInsertCommandAs Data.SqlClient.SqlCommand =New Data.SqlClient.SqlCommand("while @.@.rowcount > 0 " _

&

"begin INSERT INTO submenu" _

& uid &

" (pageid,parentid) SELECT y.pageid , y.parentid FROM submenu" & uid _

&

" i INNER JOIN page y ON y.ParentId = i.pageID LEFT OUTER JOIN subMenu" _

& uid &

" i1 ON i1.pageId = y.pageId WHERE(i1.pageID Is NULL) " _

&

"end", InsertConnection)

InsertConnection.Open()

SqlInsertCommand.ExecuteNonQuery()

InsertConnection.Close()

SqlInsertCommand =

Nothing

If I insert any other SQLcommand there it is excecuted normally.

The command I have is excecuted fine using sql server manager.

Is there any way that a command is excecuted in the SQL manager but not in a page...??

Any ideas would be great...

Thank you

Hello my friend,

I would not use @.@.rowcount outside of Enterprise Manager. Could you describe your database structure and what you are trying to insert. No need to send vb code, just the SQL or some comments on the steps and I can send you the correct SQL that will work from wherever it is used.

Kind regards

Scotty

|||

You use @.@.RowCount in first line of your query but this returns number of rows affected by last select statement in current SQL thread, but your thread is starting so it returns always 0 so your loop is never executed.

You should populate your temporary table in the same select statement to work correctly. The best solution is to create SQL stored procedure which will do all your work at one shot if you can do it.

Thanks

JPazgier

|||

Hi,

The software is a sitebuilder. The particular table holds the page stucrure of each site.

The table is this one:

----

pageid int identify

siteid int

pagename nvarchar(200)

parentid int

----

I need to update / delete all of the tree when the user wants to update or delete a top element. The number of levels is not limited.

I managed to do it, using a variable. The "problem" is that I set it to 1000 times. So if someone has more than 1000 pages under the parent, if will fail. And it's not right in the first place.

The thing is, that this worked fine when I was on an other server that used MS SQL 2000. I didn;t find any differences searching the web from 2000 to 2005

jpazgier, why does it work then when I excecute it using SQL manager..? Isn't this weird? I mean, if the rowcount is 0 from the beggining in the application, should't it be 0 in the SQL manager too?

Thank you

|||

Hello my friend,

I realize now what you are trying to do and I have the answer for you and this will work no matter how many levels you have (no 1000 limit). Run the following SQL, but change tblTree to the name of your table (I did not know what you have called it): -

CREATE FUNCTION dbo.fnGetPages
(
@.PageID AS INT
)

RETURNS @.ChildPageIDs TABLE(PageID INT)

AS

BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM tblTree WHERE ParentID = @.PageID

DECLARE @.TempChildPageIDs TABLE(PageID INT)
INSERT INTO @.TempChildPageIDs (PageID)
SELECT PageID FROM @.ChildPageIDs ORDER BY PageID

DECLARE @.ChildPageID AS INT
SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)

WHILE (@.ChildPageID IS NOT NULL)
BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM dbo.fnGetPages(@.ChildPageID)
DELETE FROM @.TempChildPageIDs WHERE PageID = @.ChildPageID

SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)
END
RETURN
END

Now to get all child IDs of page 1 (either direct children of 1, and also children of ones that are children of 1, and so on) I run the following: -

select PageID from dbo.fnGetPages(1)

To delete the page and all of its children I run the following 2 commands: -

DELETE FROM tblTree WHERE PageID IN (SELECT PageID FROM dbo.fnGetPages(1))

DELETE FROM tblTree WHERE PageID = 1

Kind regards

Scotty

|||

Thanks Scotty,

The code to create the function is only run once right?

Then I just select update or do whatever I need to do using the function right?

Thanks

|||

Yes that is correct. You only run the function SQL once. You only need to run this again if you decide to use this functionality within a new database.

You just need to run the commands that use the function and you should be fine.

Kind regards

Scotty