Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Unanswered: Stored Procedure to Delete from multiple tables?

    Language: ASP 3.0 with VB Script
    Platform: Windows 2000
    SQL Vers: MS SQL 2000

    Just when I'm beginning to build up a little confidence with MS SQL, I hit another brick wall. I have written an ASP Application for a tutoring service that tracks personal information for Customers and Tutors. So, I created a field in all the various tables to uniquely identify each user: CustomerTutorID.

    The database is slowly getting bloated, however, so I need to delete customers or tutors when they stop using the service. I need to be able to pick a single CustomerTutorID and delete EVERY Record that has is associated with that unique ID. Now, this ID is NOT a primary or foreign key, and I can't make it one (the reasons why are complicated, but valid: the ID is sometimes brought in from QuickBooks or generated as a random number by the ASP app).

    I think I need a Stored Procedure to do this, but I'm not sure. I've read things about Cascade Deletes and am confused about how to go about this.

    I would like the admin to be able to:
    1) Select the Customer or Tutor name from a dropdown menu (this will provide us with the CustomerTutorID)
    2) Click a submit button on the bottom of the form
    3) Have the system search ALL tables in the database and delete EVERY record where the CustomerTutorID equals the number provided.
    4) Return a "Operation Successful" response (or redirect us to a page with that message.

    Am I on the right track with trying to do this as a stored procedure? Is it possible to search through all the tables, or do I need to specify each table that it looks in? This might be a problem because some of the tables (such as CreditMemos) may have info for one client, but not another.

    Any and all help will be GREATLY appreciated!

  2. #2
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    ** First part is the form and population of the dropdown box **

    <form name=frmName method=get action=CustomerTutorSearch.asp>
    <%
    response.write("<select name=CustomerTutorID>")
    sql = "Select CustomerTutorName,CustomerTutorID from CustomerTutorTable"
    rs.open sql,connectionstring
    do while not rs.eof
    response.write("<option value=" & CustomerTutorID & ">" & CustomerTutorName & "</option>"
    rs.movenext
    loop
    response.write("</select>")
    %>
    <input type="submit" value="Go")
    </form>

    ** after submit it goes to CustomerTutorSearch.asp?CustomerTutorID=WhatEverWa sSelected **

    <%
    ctid = request.querystring("CustomerTutorID")

    strSQL = "SELECT sysobjects.name FROM sysobjects"
    strSQL = strSQL & " JOIN syscolumns ON sysobjects.ID = syscolumns.ID"
    strSQL = strSQL & " where syscolumnsname = ''CustomerTutorID')"
    rs.Open strSQL, conn

    do while not rs.eof
    rs2.open "Delete from " & rs(0) & " where CustomerTutorID = '" & ctid & "'"
    rs2.close
    rs.movenext
    loop
    rs.close
    set rs=nothing
    conn.close
    set conn = nothing
    %>
    Beyond Limitation

  3. #3
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85
    vexout: Thanks for a very clear reply.

    It looks very straightforward. I'll try it this evening and let you know if I get it to work.

    Thanks!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I like vextout's creative approach, but I can't recommend this method for an application. It is too easy for future developers/debuggers to create objects that reference the same IDs by a slightly different name, (CustomerTutorManagerID, CustomerTutorIDOld, CustTutID, etc...) or to create tables where it is undesirable to delete records that reference contain a CustomerTutorID column (History tables, for example).

    It is good programming practice to explicitly delete from each appropriate table.

    blindman

  5. #5
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    So what would you recommend?

    Blindman,

    I see your point (no pun intended). It's unrealistic to expect the Admin to go through and delete each reference from about 12 tables (to be blunt, she's barely smart enough to log on and handle the bookkeeping tasks).

    What do you sugest instead?

  6. #6
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85
    Vextout,

    I tried running the script tonight and couldn't get it to work. I have a few questions about things I should edit in the code:

    1.
    I replaced "conn" with my connection string, and am wondering if that's where it's breaking. Here's my connection string:

    Dim MM_myConnection_STRING
    MM_myConnection_STRING = "dsn=dbname;uid=user;pwd=pass;"


    2.
    Am I opening a recordset? Is that what the "rs" is for? I'm used to opening them with this code (where I notice that you didn't Set the Server Object before opening it):

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.ActiveConnection = MM_myConnection_STRING
    rs.Open()

    ------------

    Thanks in advance for your help!

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should have a stored procedure which explicitly deletes from the appropriate tables, and then your admin can execute the procedure.

    blindman

  8. #8
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    when i tested it, my convention is importing the connection string and declaration for rs and conn in another file
    example
    <!--#include file="connection.asp"-->

    and within that file i have

    dim conn
    dim rs
    set conn = server.CreateObject("ADODB.connection")
    conn.open "DSN=dnsname;uid=Login;pwd=Pass"
    set rs = server.CreateObject("ADODB.recordset")

    also at the end of each file i do a deconn
    example
    <!--#include file="deconnection.asp"-->

    which contains

    if rs.state <> 0 then
    rs.close
    end if
    set rs = nothing
    if conn.state <> 0 then
    conn.close
    end if
    set conn = nothing
    Beyond Limitation

  9. #9
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Finally got code to work...

    Whew! I finally got the code to work. There were two typos in the code which I had to fix:

    orig (with errors)
    strSQL = strSQL & " WHERE syscolumnsname = ''CustomerTutorID')"

    fix:
    strSQL = strSQL & " WHERE syscolumns.name = 'CustomerTutorID'"



    <%
    dim conn
    dim rs
    dim rs2
    set conn = server.CreateObject("ADODB.connection")
    conn.open "dsn=dbname;uid=user;pwd=pass"
    set rs = server.CreateObject("ADODB.recordset")

    ctid = request.querystring("CustomerTutorID")

    strSQL = "SELECT sysobjects.name FROM sysobjects"
    strSQL = strSQL & " JOIN syscolumns ON sysobjects.ID = syscolumns.ID"
    strSQL = strSQL & " WHERE syscolumns.name = 'CustomerTutorID'"
    rs.Open strSQL, conn

    do while not rs.eof
    Set rs2 = Server.CreateObject("ADODB.Command")
    rs2.ActiveConnection = conn
    rs2.CommandText = "Delete from " & rs(0) & " where CustomerTutorID = '" & ctid & "'"
    rs2.Execute
    ' rs2.ActiveConnection.Close
    ' rs2.close
    rs.movenext
    loop

    %>

    ---------------------------

    <%
    if rs.state <> 0 then
    rs.close
    end if
    set rs = nothing
    if conn.state <> 0 then
    conn.close
    end if
    set conn = nothing
    %>

    I suspect I'm not closing everything correctly, but it works! Finally. Thanks vextout!

  10. #10
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    I'm just curious if there is a special reason you're using a recordset for a non-row-returning query such as the delete statement.

    Why not just use the Connection.Execute?

  11. #11
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    The honest answer is...

    Because I'm not very good at this. I'm getting better, but at this point I'm still winging it. ;-)

    I'm just using the code Vextout sent me, and that's how he did it. If you have a better suggestion, I'm all ears.

  12. #12
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45

    Re: The honest answer is...

    My question wasn't specifically aimed at you, rather vextout or whomever would have an idea as to why.

    I would use the connection's execute method. In your code that would mean:

    conn.Execute strSQL

    But I'm more used to ado from traditional programming than scripted programming. There might be a difference I don't know about.

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •