Error using sql Insert(SQL Server) command, via classic ASP

Question

Error using sql Insert(SQL Server) command, via classic ASP

Asked 9 years, 11 months ago

Viewed 374 times

0

Good evening, I have a problem running an sql command through an ASP application.

This is the command:

strSql =    " INSERT INTO movimento_tef_nsu (           "&_
            "   identificador                           "&_
            "   ,nsu_sitef                              "&_
            "   ,valor                                  "&_
            "   ,ordem_cartao                           "&_
            "   ,data_hora                              "&_
            "   ,texto_comprovante                      "&_
            "   )                                       "&_
            " VALUES (                                  "&_
            "   '" & objJSON.data("identificador") & "' "&_
            "   ," & this.item("nsu")                   &_
            "   ," & this.item("valor")                 &_
            "   ," & this.item("ordem_cartao")          &_
            "   ,'" & this.item("data_hora") & "' "     &_           
            "   ,LEFT('" & this.item("texto_comprovante") & "', 255) "&_
            "   )                                       "

I have already checked that the values (which are coming via JSON) are correct because I write to a Log before executing the command.

The error that occurs is as follows:

"An unspecified error occurred! Microsoft OLE DB Provider for SQL Server, error '80040e14'. Syntax error or access Violation"

I also checked that the values are coming according to the type and size of the fields in the database, so much so that if I take the query that is in the log and run in sql management, it works normally.

Does anyone have a hint of what this mistake might be?

2

Do you happen to have a simple quotation mark on texto_comprovante? You can post the final SQL string generated by ASP?

– bfavaretto

2014/07/25 at 12:40
should not close quotes after "& this.item("nsu")... ?

– Dante

2014/07/25 at 12:42
Try to get the generated SQL and try to run directly on the Database. But I would look at this "255" it looks "loose"

– Motta

2014/07/25 at 17:53
@Dante, not that line is correct. If the OP can run this query in the SMS then the problem is in the assembly of the "string" or the connection. Make sure you are connected to the correct base. Try, instead of "buildar" the string, a simple Insert/update query

– jean

2014/07/29 at 12:16
Guys, I followed what @jean said and I checked that the Connection object was not set at the correct base (somehow lost the setting midway).

– Maturano

2014/07/30 at 19:25

1 answer

Browser other questions tagged sql sql-server asp

You are not signed in. Login or sign up in order to post.

by John Calistro • 81 points · Answer 1 · 2014-08-07T13:57:55+00:00

The problem is here

",LEFT('" & this.item("texto_comprovante") & "', 255) "

left comma makes mysql understand that there is one more parameter, to solve, use the function before calling the query, save it in a variable and use it in INSERT.

 textoComprovante = LEFT('" & this.item("texto_comprovante") & "', 255)

strSql = " INSERT INTO movimento_tef_nsu 
   (identificador, nsu_sitef, valor, ordem_cartao,data_hora, texto_comprovante) 
    VALUES
    ('" & objJSON.data("identificador") & "', " & this.item("nsu") & ", " & this.item("valor")
    &", " & this.item("ordem_cartao") &", '" & this.item("data_hora") & "', '" & textoComprovante & "'")