x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 4 replies
  • Subscribers 76 subscribers
  • Views 791 views
  • Users 0 members are here
Browse Forums
Announcements
105
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

  • 0

    The lines:

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

    You are appending the puor_PurchaseOrdersId to the end which I assume in this case is 30.

  • 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");

  • 0 in reply to John Kingsbury

    John:  Yes, thank you! Whew, I was afraid there was something weird going on in the system...but it is just me!

    The weird thing is that the page worked previously as it has been in place for at least six months.  When I read that line, it does not even make sense!  Not sure why it is even there.  I removed it and it is back to working as expected.  

    Thank you!

  • 0 in reply to Vega

    Vega:  Thank you for your assistance.  I really do want all the records with the status of exported to be updated rather than just one record.  The ASP page is behind a button on a list page.

    I appreciate your assistance.

*Community Hub is the new name for Sage City