Cascading delete in a self-joined table - Database Discussion Boards

Luca Leonardo Scorcia11-Oct-02 6:55

11-Oct-02 6:55

What's this Query Analyser? Is it included in SQL Server? Because I'm using SQL on an Access DB via ADO... but if it's a standalone tool it could be very useful!!!

Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)

Re: Fast SELECT Count

Daniel Turini14-Oct-02 0:55

Daniel Turini

14-Oct-02 0:55

Oh, only in SQL Server, and it uses some nice SQL Server features, like showing the query plan, and so on...
If you go serious about database development, you should really consider using SQL Server. There's even a free version of SQL Server, named MSDE or SQL Desktop Engine. Try it, is nice...

My latest articles:
Desktop Bob - Instant CP notifications
XOR tricks for RAID data protection

Only 255 char?

Rickard Andersson²⁰10-Oct-02 21:30

Rickard Andersson²⁰

10-Oct-02 21:30

I have tried to store more than 255 chars in an Access 2000 database, but it isn't allowed!
How can I stor more than 255 chars in Access?
I beleive it's not possible!?

Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!

Re: Only 255 char?

Mazdak10-Oct-02 22:01

Mazdak

10-Oct-02 22:01

Hi Rickard.

Rickard Andersson wrote:
I beleive it's not possible!?

No.Use Memo type.

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Re: Only 255 char?

Rickard Andersson²⁰10-Oct-02 22:40

Rickard Andersson²⁰

10-Oct-02 22:40

Mazdak wrote:
Use Memo type

Okay, but how do I do that?
I opened my table in Access 200 in Design mode and checked if Memo type could be used for my field, but no.... Frown | :(

Can you explain more?

Thank you very much!

Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!

Re: Only 255 char?

Nick Parker11-Oct-02 1:31

Nick Parker

11-Oct-02 1:31

Rickard Andersson wrote:
I opened my table in Access 200 in Design mode and checked if Memo type could be used for my field, but no....

The Memo field is listed under the data type section of your database.

Nick Parker

The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill

Re: Only 255 char?

Rickard Andersson²⁰11-Oct-02 2:05

Rickard Andersson²⁰

11-Oct-02 2:05

Nick Parker wrote:
The Memo field is listed under the data type section of your database.

Found it!
In the Swedish edition Memo = PM (which is the same anyway Smile | :)

)

Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!

Cascading delete in a self-joined table

Wjousts10-Oct-02 10:14

Wjousts

10-Oct-02 10:14

I have a treeview that is populated from a self joined table in a way analogus to that detailed by Gevik in his article:
http://www.codeproject.com/useritems/2dtreeview.asp#xx281692xx[^]

Now I've been trying to work out the most efficent way to delete a from the treeview and have the corresponding node in the database, plus any children, be deleted. I tried this:

private void CascadeDelete(int iParent)
{
    DataView dvView = new DataView(dsData.Tables[0]);
    dvView.RowFilter = "ParentID = '" + iParent + "'";		

    while (dvView.Count>0)
    {
        DataRowView thisRow = dvView[0];
        int thisID = Int32.Parse(thisRow["TypeID"].ToString());
        CascadeDelete(thisID);
        //dvView = new DataView(dsData.Tables[0]);
        //dvView.RowFilter = "ParentID = '" + iParent + "'";
        MessageBox.Show("DELETING: " + thisRow["strType"].ToString());
        thisRow.Delete();
	//dsData.AcceptChanges();
    }
}

The problem, I think, is that as the recursion unwinds the DataRowView of the caller is out of date because the callee has changed it, but trying to update it again (by uncommenting the above lines) doesn't seem to work. The end result is that when I try to call dsData.GetChanges() it returns null.

Here's a example of the messed up recursion, give the nodes:

1
|-3
|-2
  |-6
  |-5
  | |-7
  4

I get this series of "deletion": 3,6,7,7,5,4,6,5,4,2,3,2
Any ideas on an efficient (or even just working!) way to do this? I could just issue a bunch of individual SQL statements to delete each row, but that seems horribly inefficient.

Thanks

Re: Cascading delete in a self-joined table

Daniel Turini10-Oct-02 10:45

Daniel Turini

10-Oct-02 10:45

A trigger on the table?

My latest articles:
XOR tricks for RAID data protection
Win32 process suspend/resume tool

Re: Cascading delete in a self-joined table

Wjousts11-Oct-02 3:16

Wjousts

11-Oct-02 3:16

Sorry, can you explain what you mean?

Bulk Insert (ADO/Access2000)

ssirisha10-Oct-02 9:56

ssirisha

10-Oct-02 9:56

I would have to insert around 500 records every 10 seconds into a table in Access 2000. I am using ADO. What is the most efficient way to do this with good performance.

Is it possible to pass all of the 500 records to a query using some kind of SafeArray parameters and Insert them all at once?

Any tips how this can be accomplished ?
Thanks

Re: Bulk Insert (ADO/Access2000)

Mazdak10-Oct-02 22:02

Mazdak

10-Oct-02 22:02

I think stored procedure is the best way.

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Re: Bulk Insert (ADO/Access2000)

ssirisha11-Oct-02 2:01

ssirisha

11-Oct-02 2:01

Could you please help me in with a small sample code.

Do I have to send the records as an array and loop the INSERT statement for all records? I am not quite sure if all this is possible in ACCESS.

I guess my question would be can I pass an array to Stored proceedure, if Yes how?

Thanks for your time.

Display Row Number

Jason Weibel10-Oct-02 9:30

Jason Weibel

10-Oct-02 9:30

I am trying to reset the PK on a SQL Server table. The key is auto populated and automatically increments by 1. I have cleared all the data and want to repopulate. Is there a way to have the first Id be equal to 1? Currently numbering starts where the last data ended.

Second question is there a way to print the row number in a select statement?

Jason W.

Re: Display Row Number

Michael P Butler10-Oct-02 9:58

Michael P Butler

10-Oct-02 9:58

If I remember correctly, to reset the row number field you need to TRUNCATE TABLE.

Michael

"I've died for a living in the movies and tv.
But the hardest thing I'll ever do is watch my leading ladies,
Kiss some other guy while I'm bandaging my knee."
-- The Unknown Stuntman

Re: Display Row Number

Jason Weibel10-Oct-02 10:03

Jason Weibel

10-Oct-02 10:03

Exactly what I was looking for thanks. Smile | :)

Jason W.

Re: Display Row Number

Robby10-Oct-02 10:35

Robby

10-Oct-02 10:35

you can also do this by changing your identity to "NO" in the design view, saving the table and then re-instating the identity to YES

Re: Display Row Number

Carlos Antollini10-Oct-02 10:44

Carlos Antollini

10-Oct-02 10:44

The only mode that I know, is removing the PK state, repopulating the fileld and setting like PK again...

Carlos Antollini.
Sonork ID 100.10529 cantollini

Re: Display Row Number

Rein Hillmann14-Oct-02 22:30

Rein Hillmann

14-Oct-02 22:30

To reseed your identity column you can use the following dbcc command. Not too sure if it's supported though:

dbcc checkident ( <tablename>, RESEED, <newvalue> )<br />

example:

<br />
dbcc checkident(authors, RESEED, 100000)<br />

Re: Display Row Number

Rein Hillmann14-Oct-02 22:50

Rein Hillmann

14-Oct-02 22:50

Oh, and for ther row number you might want to check out this link:
http://www.sqlteam.com/item.asp?ItemID=1491

Can I SELECT information the same time i INSERT

Robby10-Oct-02 9:04

Robby

10-Oct-02 9:04

I want to know if there is a way to get the PK from a row the minute I insert information. Here's the scenario, what's the most efficient way of doing this?

1. I have 50 tables that represent 50 HTML forms.
2. I am inserting results from OnlineAccess.asp into dbo.OnlineAccess.
3. I am keeping all dateTime stamps along with the status of the request (OPEN, BEING PROCESSED, COMPLETE) in another table called dateTime.
4. I want to get the PK of the information entered into dbo.OnlineAccess so that I can insert this into dbo.DateTime for relation to dbo.OnlineAccess's information.

Is there any "bi-di" capabilities that can be used to return the PK without having to run another line of code to SELECT the PK WHERE <and then="" pass="" results="" of="" the="" form="" back="" again="">. For instance, I know I can do it this way, but it seems like too much leg work:

sqlInsert = "INSERT into OnlineAccess(userID,lastName,firstName,...) values('"&userID&"','"&lastName&"','"&firstName&"',...)"

sqlSelect = "SELECT requestID FROM OnlineAccess WHERE userID='"&userID&"' AND lastName='"&lastName&"' AND ..."
RS = MyConn.Execute(sqlSelect)
requestID = RS("requestID")

sqlInsert = "INSERT into DateTime (requestID,status) values('"&requestID&"','OPEN')"

(I have a trigger that takes care of the dateTime stamps based on the status)

Can anyone help me on this one? Thanks in advance!
Robby

Re: Can I SELECT information the same time i INSERT

David Salter10-Oct-02 9:56

David Salter

10-Oct-02 9:56

Probably the best way would be to write a stored procedure that can then insert your data and then return the PK to the caller.

I know this works with SQL Server, but I've had trouble returning values from SPs in Oracle.

Dave.

Re: Can I SELECT information the same time i INSERT

Jon Hulatt10-Oct-02 22:05

Jon Hulatt

10-Oct-02 22:05

Indeed, using a stored procedure would by far and away be the best solution.

after an insert, sql server special variable @@IDENTITY contains the new pk of what you just inserted. so this will probably work:-

sqlInsert = "INSERT into OnlineAccess(userID,lastName,firstName,...) values('"&userID&"','"&lastName&"','"&firstName&"',...) ; SELECT @@IDENTITY AS [newkeyname]"

RS = MyConn.Execute(sqlInsert)
requestID = RS("newkeyname")

But really thats a bag of sh!t of an implementation. Stored procedures are the way to go. Why?

1. stored procedures run much quicker, because they are precompiled.
2. They are like functions in proper languages; you can change the internal code without changing the interface.

You could most probably do the whole job in one SP, like this:

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [p_YourProc]
 @UserId int,
 @Firstname varchar(100),
 @Lastname varchar(100),
 @NewPKout integer output

AS

-- define a sql variable
declare @NewPK int;

-- do the main insert first.
INSERT into OnlineAccess(userID,lastName,firstName) values
( @UserId, @LastName, @Firstname);

-- retrieve the new key
select @NewPK=@@IDENTITY;

-- do the other inserts
INSERT into DateTime (requestID,status) 
values(@NewPK,'OPEN')

-- might as well send the pk back to asp
set @NewPKout = @NewPK

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

There. Bob's your uncle.

Signature space for rent. Apply by email to....

ADO Command->Execute throws exception

ssirisha10-Oct-02 5:55

ssirisha

10-Oct-02 5:55

Using Access 2000 and ADO VC++6.0

I am executing the following statement..
VARIANT Params[2];
Params[0].vt = VT_I2; Params[0].iVal = 1;
Params[1].vt = VT_R4; Params[1].fltVal = 8.99;
m_pCommand->adCmdText = "Query4";
m_pCommand->ActiveConnection = m_pConnection;
m_pCommand->Execute(0, Params, adCmdStoredProc);
Generates an exception {DB_E_PARAMNOTOPTIONAL}

Query4 in Access is as follows:
PARAMETERS vID Short, Rad IEEESingle;
INSERT INTO TABLE1 ( ODID, MeasRadius )
VALUES (vID, Rad);

Thanks for your help in advance.

Re: ADO Command->Execute throws exception

Carlos Antollini10-Oct-02 6:22

Carlos Antollini

10-Oct-02 6:22

You need to inform the type of Param, if input or output, for example...
But I see that you are passing a float param,, in that case you MUST to inform the presicion and the scale

Regards

Carlos Antollini.
Sonork ID 100.10529 cantollini

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.