how do I delete whole records from db?

[taval]

I want to delete all fields of a record by specfying an id:<br>the current way I do this is by :<br><br>----------------------------------------------------<br><br>Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot<br>conn.open &quot;DSN=Architron;uid=sa;pwd=&quot;<br><br>SQLstmt = &quot;Delete Customer SET &quot;<br>SQLStmt = SQLStmt & &quot; WHERE CustID=&quot; & frm_CustID <br><br>Set RS = Conn.Execute(SQLStmt)<br>-----------------------------------------------------<br><br>But I get the following error:<br><br>-------------------------------<br><br>Database Errors Occured<br>Delete Customer SET WHERE CustID = 28<br><br>Error #-2147217900<br><br>Error desc. -&gt; [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.<br><br>--------------------------------<br><br>I know CustID is a entity of the db and is correctly spelt and exists. How do I overcome this problem.<br><br>Grateful for any help , thanks.<br><br><br>

 

[Guest_imported]

Taval, don't use the &quot;set&quot; keyword in your SQL statement:<br><br><b>SQLstmt = &quot;Delete Customer&quot;<br>SQLStmt = SQLStmt & &quot; WHERE CustID=&quot; & frm_CustID</b> <br><br>

 

[hallm]

I'm not sure why yours doesn't work (new to ASP).&nbsp;&nbsp;But this is the script that I have been using to delete records from the database by id.&nbsp;&nbsp;It's worked fine so far.<br><br>set db = server.createObject(&quot;ADODB.connection&quot<br>db.open &quot;news&quot;<br>set sleuth = db.execute(&quot;DELETE * FROM cybersleuth WHERE SleuthID=&quot;&request(&quot;strID&quot)<br><br>db.close<br>set sleuth = nothing<br>set db = nothing<br>

 

[palbano]

&gt; [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.<br><br>Ok... ONE MORE TIME!!! WHEN YOU GET A SQL ERROR GO RUN THE SQL STATEMENT IN YOUR DATABASE TOOL, THAT IS WHERE THE ERROR IS OCCURING !!!<br><br>&quot;But, that's just my opinion... I could be wrong&quot;.<br>-pete

 

[taval]

Ok people I tried running the query as :<br><br>Delete From Customer WHERE Customer.CustID=6<br><br>and now I get this error message:<br><br>------------------------------------------------<br><br>Database Errors Occured<br><br>Delete From Customer WHERE Customer.CustID=6<br><br>Error #-2147217900<br><br>Error desc. -&gt; [Microsoft][ODBC SQL Server Driver][SQL Server]DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_FileDescription_Customer'. The conflict occurred in database 'Architron', table 'FileDescription', column 'CustId'.<br><br>Error #-2147217900<br><br>Error desc. -&gt; [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.<br><br>------------------------------------------------<br><br>I know there is a relation connecting the primary key of Customer table &quot;CustID&quot; and with the foreign key of the FileDescription table &quot;CustId&quot;. I think this error is telling me I have to delete all references to that customer.<br>Can anyone help me out to whats happening and tell me how to write the valid query.<br><br>Thanks.<br>-Taha<br>

 

[Snaggs]

Yes, that is exactly what's happening.&nbsp;&nbsp;It's part of the referential integrity of the database.&nbsp;&nbsp;It means you can't delete a parent record without deleting all it's childeren first.&nbsp;&nbsp;If you were able to delete the parent you would orphan all the child records.&nbsp;&nbsp;Delete the childeren first, then the parent.&nbsp;&nbsp;If you were using MS-Access, you could turn on 'Cascading Deletes' and it would automatically delete all childeren records.&nbsp;&nbsp;However, this is a DANGEROUS option to turn on. <p>Steve<br><a href=mailto:tribesaddict@swbell.net>tribesaddict@swbell.net</a><br><a href= > </a><br>

 

[Guest_imported]

Please try this one.<br>set db = server.createObject(&quot;ADODB.connection&quot<br>db.open &quot;your database&quot;<br>set Your-RecordSet = db.execute(&quot;DELETE * FROM Your-TableName WHERE Your-FieldName=&quot;&request(&quot;Form-InputName&quot)<br><br>db.close<br>set Your-RecordSet = nothing<br>set db = nothing<br>

 

[taval]

Steve,<br><br>I've tried deleting all the children first by doing :<br><br>Delete From FileDescription where CustId=28<br><br>but I still get the referential integrity error.<br>I guess I have to delete the children and parent records at the same time. How would I setup the query?<br>

 

[taval]

I still can't slove this problem.<br>Grateful for any help.<br>Thanks.

 

[Guest_imported]

Taval, I suggest you try to create a trigger in SQL Server, which enables cascading delete:<br><br><br><b>create trigger del_trig<br>on parent_table for delete<br>as<br>if @@rowcount = 0<br>&nbsp;&nbsp;&nbsp;print &quot;No rows affected!&quot;<br>&nbsp;&nbsp;&nbsp;return<br>delete child_table<br>from deleted d, child_table cht<br>where d.uid = cht.uid<br>if @@error != 0<br>begin<br>&nbsp;&nbsp;&nbsp;raiserror (&quot;error encountered in trigger processing&quot;, 16, 1)<br>&nbsp;&nbsp;&nbsp;rollback tran<br>&nbsp;&nbsp;&nbsp;return<br>end<br>return</b><br><br>then, to fire it you'll have to issue a delete command in your ASP page:<br><br><b>strSQL = &quot;Delete from parent_table where primary_key = somevalue&quot;</b><br><br>This will fire the trigger in SQL Server.

 

[Snaggs]

One thing I forgot to mention is that your primary table could have more than one relation with other tables.&nbsp;&nbsp;You have to delete all childeren in all related tables before you can delete the parent. <p>Steve<br><a href=mailto:tribesaddict@swbell.net>tribesaddict@swbell.net</a><br><a href= > </a><br>