Prevent Incorrect syntax near '*' errors using Row_Deleted

Tips submitted by ASP.NET Maker users

Prevent Incorrect syntax near '*' errors using Row_Deleted

Postby xgis » Sun Aug 11, 2013 1:34 pm

This example uses the new method to delete Master and Child Rows from a SQL 2008 database.
The database already has working Foreign Key constraints set to ensure child records are deleted
but the application needs to have it also when deletes are enabled as most users never see SQL

When using the original method;
ew_Execute("DELETE * FROM DMPDB.DMP_Sites_Contacts WHERE SiteID=" + rs["SiteID"]);

the following error may appear;
Incorrect syntax near '*'
return c.ExecuteNonQuery(Sql);

This error occurs with or without the DB Schema prefix

The alternate solution is to use the new Row_Deleted code to achieve the same result without error.

var DMP_Sites_Contacts = new cDMP_Sites_Contacts();
DMP_Sites_Contacts.Delete(null, "SiteID=" + rs["SiteID"]);

This example is 1 of four child tables that has the deletes enforced in this way.
Check ANM custom scripts for a generic sample of this code.
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Re: Prevent Incorrect syntax near '*' errors using Row_Delet

Postby xgis » Sun Aug 11, 2013 3:08 pm

ADVANCED: Related Delete Sample but NOT related to the Error listed in the post.

The example outlines considerations for the same method applied to a Self Referencing Hierarchy with SQL Foreign Key Constraints.

eg
var DMP_System_Domains = new cDMP_System_Domains();
DMP_System_Domains.Delete(null, "DomainParentID=" + rs["DomainID"]);

This method allows you to delete child values (ie virtual table values).
It will however throw a different type of error, than the one listed in this post, if you try and delete a Parent Key value.

eg
ID Name ParentID
1 Colours NULL
2 Red 1
3 Green 1

You can delete rows 2 and 3 using this code but not row 1 since the Self Referencing Foreign Key Constraint depends on this record to function.
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm


Return to User Submited Tips (ASP.NET Maker)