x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 4 replies
  • Subscribers 76 subscribers
  • Views 797 views
  • Users 0 members are here
Browse Forums
Announcements
106
topics
32
replies
General Discussion
6k
topics
18k
replies
Michele Gaw

Unexplained characters causing syntax error

Posted By

I am experiencing an error when running an update to sql from an ASP page.  The page has worked in the past and I am not sure what is causing it to fail now.  I can see the error in the log.  However, I do not know what is causing it.

Aug 28 2020 3:12:39.272 7552 7780 1 execsql,time,sql,errormsg 15 Update PurchaseOrders SET puor_ExportStatus='Archived', puor_Deleted = '1' WHERE puor_ExportStatus = 'Exported'30
Incorrect syntax near '30'

The problem is the number 30 after Exported.  I don't know where this is coming from.  Any ideas??

This is what I have in the asp page...

var strQuery = "SELECT * FROM PurchaseOrders WHERE puor_ExportStatus = 'Exported'";
var myQuery = CRM.CreateQueryObj(strQuery,"");
myQuery.SelectSQL();

while (!myQuery.eof)
{
//Update PurchaseOrders record to Archived. ============
sql = "Update PurchaseOrders SET puor_ExportStatus='Archived', puor_Deleted = '1' WHERE puor_ExportStatus = 'Exported'";
sql+= myQuery.FieldValue("puor_PurchaseOrdersID");
UpdateQuery = CRM.CreateQueryObj(sql);
UpdateQuery.ExecSql();
//End Update PurchaseOrders record to Imported==============================================

myQuery.NextRecord();
}

Any idea where the 30 is coming from?

Any assistance would be greatly appreciated.  

Thank you!

Top Replies

Parents
  • 0

    You're appending the ID with this line 

    Michele Gaw said:
    sql+= myQuery.FieldValue("puor_PurchaseOrdersID");

    If you're doing this kind of update query, be careful because the update will only ever get a larger data set and you're updating records that have previously been updated because your update statement says update all records where the status is Exported and set the status to Archived and the Deleted flag to 1. You probably need your query to be:

    Update PurchaseOrders SET puor_ExportStatus='Archived', puor_Deleted = '1' WHERE puor_ExportStatus = 'Exported' and puor_PurchaseOrdersID = " + myQuery.FieldValue("puor_PurchaseOrdersID");

Reply
  • 0

    You're appending the ID with this line 

    Michele Gaw said:
    sql+= myQuery.FieldValue("puor_PurchaseOrdersID");

    If you're doing this kind of update query, be careful because the update will only ever get a larger data set and you're updating records that have previously been updated because your update statement says update all records where the status is Exported and set the status to Archived and the Deleted flag to 1. You probably need your query to be:

    Update PurchaseOrders SET puor_ExportStatus='Archived', puor_Deleted = '1' WHERE puor_ExportStatus = 'Exported' and puor_PurchaseOrdersID = " + myQuery.FieldValue("puor_PurchaseOrdersID");

Children

*Community Hub is the new name for Sage City