SQL and Join and delete

softboy12 · Aug 6, 2004

Hi all

i use the follwing asp page to delete records from our customer database

<%@ codepage = 1250 LCID = 2055 %>
<% If Session("project6_status") <> "login" Then Response.Redirect "login.asp" %>
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>


<%
Response.Buffer = True

' Single delete record
key = Request.querystring("key")
If key = "" Or IsNull(key) Then
key = Request.Form("key")
End If
If key = "" Or IsNull(key) Then Response.Redirect "Kundenlist.asp"
sqlKey = sqlKey & "[ID]=" & "" & key & ""

' Get action
a = Request.Form("a")
If a = "" Or IsNull(a) Then
a = "I" ' Display with input box
End If

' Open Connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
Select Case a
Case "I": ' Display
strsql = "SELECT * FROM [Kunden] WHERE " & sqlKey
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn
If rs.Eof Then
Response.Clear
Response.Redirect "Kundenlist.asp"
Else
rs.MoveFirst
End If
Case "D": ' Delete
strsql = "SELECT * FROM [Kunden] WHERE " & sqlKey
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn, 1, 2
Do While Not rs.Eof
rs.Delete
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Response.Clear
Response.Redirect "Kundenlist.asp"
End Select
%>

Eintrag aus Kunden DB loeschen
WICHTIGER HINWEIS: aufgrund einer technischen Einschränkung ist es zur Zeit nicht möglich auch
verlinkte Pointings, Subdomains oder Synonyme zu löschen
Bitte mit Hilfe der ID überprüfen und ggf von Hand löschen! 
 
<a href="Kundenlist.asp">Zurueck zur Uebersicht</a>
<form action="Kundendelete.asp" method="post">

<input type="hidden" name="a" value="D">
<table border="0" cellspacing="1" cellpadding="4" bgcolor="#CCCCCC">
<tr bgcolor="#0099CC">
<td>ID </td>
<td>Domain </td>
<td>TLD </td>
<td>Login Name </td>
<td>Passwort </td>
<td>Service </td>
<td>Kundennummer </td>
<td>FK ID Server </td>
<td>Erfasst am </td>
<td>gesperrt </td>
<td>Reseller </td>
<td>Reseller nummer </td>
<td>Anrede best </td>
<td>Firma best </td>
<td>Nname best </td>
<td>Vorname best </td>
<td>Strasse best </td>
<td>Postfach best </td>
<td>PLZ best </td>
<td>Ort best </td>
<td>Telefon best </td>
<td>Fax best </td>
<td>Land best </td>
<td>Email best </td>
<td>Anrede re </td>
<td>Firma re </td>
<td>Nname re </td>
<td>Vorname re </td>
<td>Strasse re </td>
<td>Postfach re </td>
<td>PLZ re </td>
<td>Ort re </td>
<td>Telefon re </td>
<td>Fax re </td>
<td>Land re </td>
<td>Email re </td>
<td>Anrede tec </td>
<td>Firma tec </td>
<td>Nname tec </td>
<td>Vorname tec </td>
<td>Strasse tec </td>
<td>Postfach tec </td>
<td>PLZ tec </td>
<td>Ort tec </td>
<td>Telefon tec </td>
<td>Fax tec </td>
<td>Land tec </td>
<td>Email tec </td>
<td>Kontrolliert </td>
<td>erfasst durch </td>
<td>MXrecord </td>
<td>Survey </td>
<td>sperrdatum </td>
<td>Pointing </td>
</tr>
<%
recCount = 0
Do While Not rs.Eof
recCount = recCount + 1

' Set row color
bgcolor = "#FFFFFF"
%>
<%

' Display alternate color for rows
If recCount Mod 2 <> 0 Then
bgcolor = "#F5F5F5"
End If
%>
<%
x_ID = rs("ID")
x_Domain = rs("Domain")
x_TLD = rs("TLD")
x_LoginName = rs("LoginName")
x_Passwort = rs("Passwort")
x_Service = rs("Service")
x_Kundennummer = rs("Kundennummer")
x_FK_ID_Server = rs("FK_ID_Server")
x_Erfasst_am = rs("Erfasst_am")
x_gesperrt = rs("gesperrt")
x_Bemerkungen = rs("Bemerkungen")
x_alte_infos = rs("alte_infos")
x_alg_infos = rs("alg_infos")
x_best_anm = rs("best_anm")
x_Reseller = rs("Reseller")
x_Reseller_nummer = rs("Reseller_nummer")
x_Anrede_best = rs("Anrede_best")
x_Firma_best = rs("Firma_best")
x_Nname_best = rs("Nname_best")
x_Vorname_best = rs("Vorname_best")
x_Strasse_best = rs("Strasse_best")
x_Postfach_best = rs("Postfach_best")
x_PLZ_best = rs("PLZ_best")
x_Ort_best = rs("Ort_best")
x_Telefon_best = rs("Telefon_best")
x_Fax_best = rs("Fax_best")
x_Land_best = rs("Land_best")
x_Email_best = rs("Email_best")
x_Anrede_re = rs("Anrede_re")
x_Firma_re = rs("Firma_re")
x_Nname_re = rs("Nname_re")
x_Vorname_re = rs("Vorname_re")
x_Strasse_re = rs("Strasse_re")
x_Postfach_re = rs("Postfach_re")
x_PLZ_re = rs("PLZ_re")
x_Ort_re = rs("Ort_re")
x_Telefon_re = rs("Telefon_re")
x_Fax_re = rs("Fax_re")
x_Land_re = rs("Land_re")
x_Email_re = rs("Email_re")
x_Anrede_tec = rs("Anrede_tec")
x_Firma_tec = rs("Firma_tec")
x_Nname_tec = rs("Nname_tec")
x_Vorname_tec = rs("Vorname_tec")
x_Strasse_tec = rs("Strasse_tec")
x_Postfach_tec = rs("Postfach_tec")
x_PLZ_tec = rs("PLZ_tec")
x_Ort_tec = rs("Ort_tec")
x_Telefon_tec = rs("Telefon_tec")
x_Fax_tec = rs("Fax_tec")
x_Land_tec = rs("Land_tec")
x_Email_tec = rs("Email_tec")
x_Kontrolliert = rs("Kontrolliert")
x_erfasst_durch = rs("erfasst_durch")
x_MXrecord = rs("MXrecord")
x_Survey = rs("Survey")
x_sperrdatum = rs("sperrdatum")
x_Pointing = rs("ID_point")

%>
<tr bgcolor="<%= bgcolor %>">
<input type="hidden" name="key" value="<%= key %>">
<td>
<% Response.Write x_ID %> 
</td>
<td>
<% Response.Write x_Domain %> 
</td>
<td>
<% Response.Write x_TLD %> 
</td>
<td>
<% Response.Write x_LoginName %> 
</td>
<td>
<% Response.Write x_Passwort %> 
</td>
<td>
<% Response.Write x_Service %> 
</td>
<td>
<% Response.Write x_Kundennummer %> 
</td>
<td>
<% Response.Write x_FK_ID_Server %> 
</td>
<td>
<% Response.Write x_Erfasst_am %> 
</td>
<td>
<% If x_gesperrt = True Then %><%= "Yes" %><% Else %><%= "No" %><% End If %> 
</td>
<td>
<% If x_Reseller = True Then %><%= "Yes" %><% Else %><%= "No" %><% End If %> 
</td>
<td>
<% Response.Write x_Reseller_nummer %> 
</td>
<td>
<% Response.Write x_Anrede_best %> 
</td>
<td>
<% Response.Write x_Firma_best %> 
</td>
<td>
<% Response.Write x_Nname_best %> 
</td>
<td>
<% Response.Write x_Vorname_best %> 
</td>
<td>
<% Response.Write x_Strasse_best %> 
</td>
<td>
<% Response.Write x_Postfach_best %> 
</td>
<td>
<% Response.Write x_PLZ_best %> 
</td>
<td>
<% Response.Write x_Ort_best %> 
</td>
<td>
<% Response.Write x_Telefon_best %> 
</td>
<td>
<% Response.Write x_Fax_best %> 
</td>
<td>
<% Response.Write x_Land_best %> 
</td>
<td>
<% Response.Write x_Email_best %> 
</td>
<td>
<% Response.Write x_Anrede_re %> 
</td>
<td>
<% Response.Write x_Firma_re %> 
</td>
<td>
<% Response.Write x_Nname_re %> 
</td>
<td>
<% Response.Write x_Vorname_re %> 
</td>
<td>
<% Response.Write x_Strasse_re %> 
</td>
<td>
<% Response.Write x_Postfach_re %> 
</td>
<td>
<% Response.Write x_PLZ_re %> 
</td>
<td>
<% Response.Write x_Ort_re %> 
</td>
<td>
<% Response.Write x_Telefon_re %> 
</td>
<td>
<% Response.Write x_Fax_re %> 
</td>
<td>
<% Response.Write x_Land_re %> 
</td>
<td>
<% Response.Write x_Email_re %> 
</td>
<td>
<% Response.Write x_Anrede_tec %> 
</td>
<td>
<% Response.Write x_Firma_tec %> 
</td>
<td>
<% Response.Write x_Nname_tec %> 
</td>
<td>
<% Response.Write x_Vorname_tec %> 
</td>
<td>
<% Response.Write x_Strasse_tec %> 
</td>
<td>
<% Response.Write x_Postfach_tec %> 
</td>
<td>
<% Response.Write x_PLZ_tec %> 
</td>
<td>
<% Response.Write x_Ort_tec %> 
</td>
<td>
<% Response.Write x_Telefon_tec %> 
</td>
<td>
<% Response.Write x_Fax_tec %> 
</td>
<td>
<% Response.Write x_Land_tec %> 
</td>
<td>
<% Response.Write x_Email_tec %> 
</td>
<td>
<% If x_Kontrolliert = True Then %><%= "Yes" %><% Else %><%= "No" %><% End If %> 
</td>
<td>
<% Response.Write x_erfasst_durch %> 
</td>
<td>
<% Response.Write x_MXrecord %> 
</td>
<td>
<% Response.Write x_Survey %> 
</td>
<td>
<% Response.Write x_sperrdatum %> 
</td>
</tr>
<td>
<%Response.Write x_Pointing %> 
</td>
</tr>

<%
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
</table>

<input type="submit" name="Action" value="Loeschung bestaetigen">
</form>

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

there were also 3 other tables named "Pointings,Synonyme,Subdomains" this tables has no relations to the main table called "data"

now my problem is the following

if i delete (with the asp code above) the customer entry (Primary Key is the unique Field named "ID"), all associated entrys (if there any) in the other tables shoud then deleted automatically also

how can i implement this?

many thanks for your help

best regards

E.Altherr

vbkris · Aug 6, 2004

2 ways:
1. write the delete for the other tables as well in ASP
2. write a procedure that will do that for u...

Known is handfull, Unknown is worldfull

softboy12 · Aug 6, 2004

1.) what do you mean, separate SQL Commands to delete the recordsets in the other table

2.) i was study also on this solution, but how can access the stored procedures from within the asp pages?

vbkris · Aug 6, 2004

1. before u delete from the original table:
delete * from Pointings where column='Value to delete'
delete * from Synonyme where column='Value to delete'
delete * from Subdomains where column='Value to delete'
delete * from original_table where column='Value to delete'

2.
rs.open "exec ProcedureName(Arguements)"

Known is handfull, Unknown is worldfull

Tarwn · Aug 6, 2004

Um...isteadof doing it from the coee all you need to do is set up the relationships in the database to cascade updates and deletes. This way if you delete a record from one table, and it is relatd to another table with three related records, those three will be deleted automagically.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

softboy12 · Aug 6, 2004

thanks i tried the second solution (realtionships)

and then i made the following relations between the tables:

---------------------------------------------------------
ID Field (Primary Key) of the Table "Kunden"
is in relation to

ID_point (also Primary Key) of the table "Pointings

and is in relation to

ID_Sub (Primary Key), of the table "Subdomains"

and last but not least

its also related to

ID_Synonym (Primary Key) of the table "Synonyme"

but this does't work, which means, if i delete a recordset from the Table "Kunden" all other associated Fields in the other Tables won't be deleted

any ideas?

softboy12 · Aug 6, 2004

it tried also this (see code below)

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

' Single delete record
key = Request.querystring("key")
If key = "" Or IsNull(key) Then
key = Request.Form("key")
End If
If key = "" Or IsNull(key) Then Response.Redirect "Kundenlist.asp"
sqlKey = sqlKey & "[ID]=" & "" & key & ""

' Get action
a = Request.Form("a")
If a = "" Or IsNull(a) Then
a = "I" ' Display with input box
End If

' Open Connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
Select Case a
Case "I": ' Display
strsql = "SELECT * FROM [Kunden],[Pointings],[Subdomains],[Synonyme] WHERE " & sqlKey
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn
If rs.Eof Then
Response.Clear
Response.Redirect "Kundenlist.asp"
Else
rs.MoveFirst
End If
Case "D": ' Delete
strsql = "SELECT * FROM [Kunden],[Pointings],[Subdomains],[Synonyme] WHERE " & sqlKey
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql,conn, 1, 2
Do While Not rs.Eof
rs.Delete
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Response.Clear
Response.Redirect "Kundenlist.asp"
End Select
%>
------------------------------------------------------------

but then a ran into a timeout, if a want to delete a rs in all tables

Tarwn · Aug 6, 2004

Did you select the option to cascade DELETE's and UPDATE's?

I would need to know more about your table structure to advise you further, but generally I would assume that your relationships would not all be 1-to-1 relationships.

Example, say I have three tables: manufacturer, product, product_attributes

And they looked somehting like:
man_id - primary key
man_name - text field
man_phone - text field

prod_id - primary key
man_id - secondary key to manufacturer (one manufacturer can have many products)
prod_name - text

pa_id - primary key
prod_id - secondary key to product (one product can have many attributes)
pa_name - text
pa_desc - text

Now I would set the relationships up as follows:
Manufacturer.man_id -> Product.man_id, 1-to-many relationship, allow cascade DELETE and UPDATE
Product.prod_id -> Attributes.prod_id, 1-to-many relationship, allow cascade DELETE and UPDATE

This way if I noo longer deal with a manufacturer I can simply delete the manufacturer and the database will then:
1) Find all records in Product for that manufacturer
2) Delete those records from Product, causing another cascade to start
2a) Find all records in attributes for eahc product
2b) Delete found records

This should work the same with a one-to-one relationship as long as the correct options were chosen (cascade DELETE and UPDATE).

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

Joelo · Aug 6, 2004

Hey Tarwn,

PLease Could show me an example of what the select query (cascade DELETE and UPDATE)looks like as I trying to do same

Thanks

softboy12 · Aug 7, 2004

Hi Twan

yes i selected the option "casade delete/update" but it doesn't work

after i tried to save the relations the following error occurs

"Kunden'-Tabelle wurde gespeichert
'Pointings'-Tabelle
- Beziehung 'FK_Pointings_Kunden' kann nicht erstellt werden.
ODBC-Fehler: [Microsoft][ODBC SQL Server Driver][SQL Server]Die ALTER TABLE-Anweisung verstieß gegen die COLUMN FOREIGN KEY-Einschränkung 'FK_Pointings_Kunden'. Der Konflikt trat in der data-Datenbank, Tabelle 'Kunden', column 'ID' auf."

this error only occurs if i use the "Option" "Check Datas before insert" on the relation tab

Tarwn · Aug 8, 2004

Here is the google translation for everyone else:

ODBC error: [ Microsoft][ODBC SQL server Driver][SQL Server]Die AGE TABLE instruction offended against the COLUMN FOREIGN key restriction ' FK_Pointings_Kunden '. The conflict arose in the DATA data base, table ' customer ', column to 'ID'."

Again, I don't know what your data structure looks like, but the error sounds like you already have relatioonships set up and that the new one you were trying to add violated an existing one.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

softboy12 · Aug 8, 2004

hi all

well if a modified the sql commands in the area below:

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

' Open Connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
Select Case a
Case "I": ' Display
strsql = "SELECT * FROM [Kunden],[Pointings],[Subdomains],[Synonyme] WHERE " & sqlKey
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn
If rs.Eof Then
Response.Clear
Response.Redirect "Kundenlist.asp"
Else
rs.MoveFirst
End If
Case "D": ' Delete
strsql = "SELECT * FROM [Kunden],[Pointings],[Subdomains],[Synonyme] WHERE " & sqlKey
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql,conn, 1, 2
Do While Not rs.Eof
rs.Delete
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Response.Clear
Response.Redirect "Kundenlist.asp"
End Select
%>
-----------------------------------------------------------

to

' Open Connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
Select Case a
Case "I": ' Display
strsql = "SELECT * FROM [Kunden] LEFT OUTER JOIN [Pointings] ON [Kunden].[ID]= [Pointings].[FK_ID_Kunde] LEFT OUTER JOIN [Subdomains] ON [Kunden].[ID] =[Subdomains].[FK_ID_Kunde] LEFT OUTER JOIN [Synonyme] ON [Kunden].[ID] =[Synonyme].[FK_ID_Kunde] WHERE " & sqlKey
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn
If rs.Eof Then
Response.Clear
Response.Redirect "Kundenlist.asp"
Else
rs.MoveFirst
End If
Case "D": ' Delete
strsql = "SELECT * FROM [Kunden] LEFT OUTER JOIN [Pointings] ON [Kunden].[ID]=[Pointings].[FK_ID_Kunde] LEFT OUTER JOIN [Subdomains] ON [Kunden].[ID]=[Subdomains].[FK_ID_Kunde] LEFT OUTER JOIN [Synonyme] ON [Kunden].[ID]=[Synonyme].[FK_ID_Kunde] WHERE " & sqlKey
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql,conn, 1, 2
Do While Not rs.Eof
rs.Delete
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Response.Clear
Response.Redirect "Kundenlist.asp"
End Select
-----------------------------------------------------------

it works behalf, which means The display function " shows also the content of the joined tables but the the delete function won't be affected on the joined tables

any ideas?

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL and Join and delete

softboy12

ISP

vbkris

Programmer

softboy12

ISP

vbkris

Programmer

Tarwn

Programmer

softboy12

ISP

softboy12

ISP

Tarwn

Programmer

Joelo

MIS

softboy12

ISP

Tarwn

Programmer

softboy12

ISP

Similar threads

Part and Inventory Search

Sponsor