I have a problem using UpsertData, show me an error

Question

I am trying to insert the responses of a form to a DE, but when the information is captured and I direct it to my cloude it sends me the following error

{"message":"The function expression is invalid. See inner exception for detail.\r\n Script: UpsertData("Audience_MG_Motor_Promo_2021",2, "EmailAddress", @email, "SubmissionDate", @submission, "FirstName", @firstname, "LastName", @lastname, "SecondLastName", @slastname, "MobilePhone", @mobilephone, "PostalCode", @cp, "Agencia", @agencia, "Modelo", @modelo, "Marca", @marca, "Placas", @placas, "VehicleYear", @vehicleyear, "Kilometres", @kilometraje, "Campaign", @campaign, "Submit", @registro, "Estado", @estado)\r\n Index: 5074\r\n ListID: 0\r\n","description":"ExactTarget.OMM.InvalidScriptException: The function expression is invalid. See inner exception for detail.\r\n Script: UpsertData("Audience_MG_Motor_Promo_2021",2, "EmailAddress", @email, "SubmissionDate", @submission, "FirstName", @firstname, "LastName", @lastname, "SecondLastName", @slastname, "MobilePhone", @mobilephone, "PostalCode", @cp, "Agencia", @agencia, "Modelo", @modelo, "Marca", @marca, "Placas", @placas, "VehicleYear", @vehicleyear, "Kilometres", @kilometraje, "Campaign", @campaign, "Submit", @registro, "Estado", @estado)\r\n Index: 5074\r\n ListID: 0\r\n Error Code: OMM_FUNC_EXPR_INVALID\r\n - from Jint --> \r\n\r\n

--- inner exception 1---\r\n\r\nExactTarget.OMM.FunctionExecutionException: An error occurred when attempting to execute an UpsertData function call. See inner exception for details.\r\n Error Code: OMM_FUNC_EXEC_ERROR\r\n - from OMMCommon --> \r\n\r\n

--- inner exception 2---\r\n\r\nExactTarget.OMM.OMMException: An error occurred when attempting to execute an update of the database. See inner exception for details.\r\n\r\n StackID: 11\r\n EnterpriseID: 110006029\r\n ClientID: 514000019\r\n Client Database ID: 11063\r\n Account Type: BUSINESS_UNIT\r\n JobID: 0\r\n MachineName: ATL1S11PGS156\r\n\r\nSql Text:\r\nUPDATE [C514000019].[Error Code: OMM_SQL_UPDATE_ERR\r\n - from OMMCommon --> \r\n\r\n

--- inner exception 3---\r\n\r\nSystem.FormatException: Failed to convert parameter value from a String to a DateTime. - from System.Data --> \r\n\r\n

--- inner exception 4---\r\n\r\nSystem.FormatException: String was not recognized as a valid DateTime. - from mscorlib\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n"}

my code is this

 <script runat="server">
          Platform.Load("Core","1");
          try{
          </script>

          %%[

          VAR @firstname, @lastname, @slastname, @email, @mobilephone, @cp, @agencia, @modelo, @placas, @vehicleyear, @kilometraje, @submission, @campaign, @marca, @registro, @estado
          
          SET @firstname = RequestParameter("FirstName")
          SET @lastname = RequestParameter("LastName")
          SET @slastname = RequestParameter("SecondLastName")
          SET @email = RequestParameter("EmailAddress")
          SET @mobilephone = RequestParameter("MobilePhone")
          SET @cp = RequestParameter("PostalCode")
          SET @agencia = RequestParameter("Agencia")
          SET @modelo = RequestParameter("Modelo")
          SET @placas = RequestParameter("Placas")
          SET @vehicleyear = RequestParameter("VehicleYear")
          SET @kilometraje = RequestParameter("Kilometres")
          SET @submission = formatdate(now(),"DD/MM/YYY","HH:MM:SS", "es-MX")
          SET @campaign = "campaña"
          SET @marca = "marca"
          SET @registro = "True"


          IF (Empty(@mobilephone) == 0) THEN
          SET @mobilephone = Concat('52', @mobilephone)
          ENDIF

          /*Colocar el estado relacionado a la agencia*/
          IF @agencia == 'MG LindaVista' or
            @agencia == 'MG Sendero' or
            @agencia == 'MG Las Torres' or
            @agencia == 'MG Fleteros' or
            @agencia == 'MG Calzada del Valle' THEN 
            SET @estado = 'Nuevo Leon'
          ELSEIF @agencia == 'MG Interlomas' or
            @agencia == 'MG Ecatepec' or
            @agencia == 'MG Metepec' or
            @agencia == 'MG Tlalnepantla' or
            @agencia == 'MG Cuautitlan' THEN
            SET @estado = 'Estado de Mexico'
          ELSEIF @agencia == 'MG Angelopolis' THEN
            SET @estado = 'Puebla'
          ELSEIF @agencia == 'MG Aeropuerto' or
            @agencia == 'MG Pedregal' or
            @agencia == 'MG Miramontes' or
            @agencia == 'MG Iztapalapa' THEN
            SET @estado = 'CDMX'
          ELSE
            SET @estado = 'Desconocido'
          ENDIF


          IF @registro == true THEN
          UpsertData("Audience",2, "EmailAddress", @email, "SubmissionDate", @submission, "FirstName", @firstname, "LastName", @lastname, "SecondLastName", @slastname, "MobilePhone", @mobilephone, "PostalCode", @cp, "Agencia", @agencia, "Modelo", @modelo, "Marca", @marca, "Placas", @placas, "VehicleYear", @vehicleyear, "Kilometres", @kilometraje, "Campaign", @campaign, "Submit", @registro, "Estado", @estado)
          ENDIF
           
          ]%%

          
          <h3>Gracias por participar<br>Tu registro ha sido exitoso</h3>


          <script runat="server">
          }catch(e){
           Write(Stringify(e));
          }
          </script>

but i can't find my mistake I appreciate your help

Answer 1

It looks like the error is due to the FormateDate function failing to convert a string to a date (inner exception 3), and then this in turn causes the UpsertData to fail because it's trying to insert a string into a date field (inner exception 4).

Your FormatDate function has a typo I think - you've only put 3 "Y" in there. Please make sure it's SET @submission = formatdate(now(),"DD/MM/YYYY","HH:MM:SS", "es-MX") and try again?

Otherwise, try using SET @submission = SystemDateToLocalDate(NOW()) - not sure if that works for your use-case?