SQL query with variables error

[gspot]

Hi

Im strugling with a strange error (for me), i have a SQL query that gives me error and doesnt update the SQL table when it as some variable parameters:

If $Con_SQL = "on" Then ; 
            $sqlinsert = "update dbo.GSO_PORTIN set [ESTADO] = 'Sucesso', [ESTADO_PORTAB] = '" & $estado  & "', [PARA] = '" & $receptor & "' where LR = '" & $LR & "' and DataInicio = '" & $datadia & "'"
            MsgBox(0, "", $sqlinsert)
            $conn.Execute($sqlinsert)
                If @error Then
                    MsgBox(0, "Error", "Error updating DB" & @error, 1 )
                
                EndIf
    EndIf

I have tried the query $sqlinsert in the SQL server management and it OK. 

I have also tried to put the query without variables in autoit and its also ok. Something like the below works:

If $Con_SQL = "on" Then ; 
            $sqlinsert ="update dbo.GSO_PORTIN set [ESTADO] = 'Sucesso', [ESTADO_PORTAB] = 'Não Portado', [PARA] = 'NULL' where LR = '253887502' and DataInicio = '2015-01-12'"
                MsgBox(0, "", $sqlinsert)
                $conn.Execute($sqlinsert)
                If @error Then
                    MsgBox(0, "Erro", "Error updating DB" & @error )
                EndIf
    EndIf

Any ideas?

Thanks

[mLipok]

Hi

Im strugling with a strange error (for me), i have a SQL query that gives me error 

 

Post here error description from event handler.

[gspot]

Post here error description from event handler.

 

I put:

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Install a custom error handler

            If $Con_SQL = "on" Then ; Se não fez conexão à BD SQL nem vale a pena tentar isto
            $sqlinsert = "update dbo.GSO_PORTIN set [ESTADO] = 'Sucesso', [ESTADO_PORTAB] = '" & $estado  & "', [PARA] = '" & $receptor & "' where LR = '" & $LR & "' and DataInicio = '" & $datadia & "'"
            MsgBox(0, "", $sqlinsert)
            $conn.Execute($sqlinsert)
                If @error Then
                    MsgBox(0, "Erro", "Error updating DB" & @error, 1 )
                    ;   CloseFile()
                EndIf
            EndIf


Func MyErrFunc()
   $HexNumber=hex($oMyError.number,8)
   Msgbox(0,"","We intercepted a COM Error !" & @CRLF & _
                "Number is: " & $HexNumber & @CRLF & _
                "Windescription is: " & $oMyError.windescription )

   SetError(1) ; something to check for when this function returns
Endfunc

Returns error 80020009

[gspot]

Also adding some trace lines:

@@ Trace(223) :    $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Install a custom error handler
>Error code: 0

@@ Trace(226) :             If $Con_SQL = "on" Then ;
>Error code: 0

@@ Trace(228) :             $sqlinsert = "update dbo.GSO_PORTIN set [ESTADO] = 'Sucesso', [ESTADO_PORTAB] = '" & $estado  & "', [PARA] = '" & $receptor & "' where LR = '" & $LR & "' and DataInicio = '" & $datadia & "'"
>Error code: 0

@@ Trace(230) :             MsgBox(0, "", $sqlinsert)
>Error code: 0

@@ Trace(232) :             $conn.Execute($sqlinsert)
>Error code: 0

@@ Trace(237) :                 EndIf
>Error code: 0

@@ Trace(239) :             EndIf
>Error code: 0

[mLipok]

I see you are using event handler from here., but can you try this ?

[gspot]

I see you are using event handler from here., but can you try this ?

 

Of corse i can, thanks for the interest.

err.number is: 0x80020009

err.windescription: Exception occurred.

 

err.description is: Operation is not allowed when the object is closed.

err.source is: ADODB.Connection

err.helpfile is: C:WINDOWSHELPADO270.CHM

err.helpcontext is: 1240653

err.lastdllerror is: 0

err.scriptline is: 229

err.retcode is: 0x800A0E78

[gspot]

I have also find this

'?do=embed' frameborder='0' data-embedContent>>

But in my query im not finding a solution with the "SET NOCOUNT ON"

$sqlinsert = "SET NOCOUNT ON; update dbo.GSO_PORTIN set [ESTADO] = 'Sucesso', [ESTADO_PORTAB] = '" & $estado  & "', [PARA] = '" & $receptor & "' where LR = '" & $LR & "' and DataInicio = '" & $datadia & "'" 

...

Edited January 13, 2015 by gspot

[gspot]

Found the solution,

adding:

 

  $conn.Open.szConnString

$conn.CursorLocation.adUseClient

 

 

the explanation is here http://www.dreamincode.net/forums/topic/282972-operation-not-allowed-when-object-is-closed/

I dont fully understand it, but its working and life goes on

 

Thanks for the help mLipok that put me in the right track.. 

[mLipok]

btw.

are you using _sql.au3 UDF ?

[mLipok]

 

Found the solution,

adding:

 

  $conn.Open.szConnString

$conn.CursorLocation.adUseClient

 

 

the explanation is here http://www.dreamincode.net/forums/topic/282972-operation-not-allowed-when-object-is-closed/

I dont fully understand it, but its working and life goes on

 

Thanks for the help mLipok that put me in the right track.. 

 

 

And this is best way to learn:

Search, think, ask, search, analyze ....(Repeat the previous steps) ... (Repeat the previous steps) ...(Repeat the previous steps) ...

I'm glad I could help.

[gspot]

btw.

are you using _sql.au3 UDF ?

 

In this particular case, no. I use COM but not the functions of the SQL.au3...

[maniootek]

I also have a problem with the variables in query when using ADO connection. The connection is closed when query contains "DECLARE" statement.

On 1/12/2015 at 5:13 PM, gspot said:

 

I put:

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Install a custom error handler

            If $Con_SQL = "on" Then ; Se não fez conexão à BD SQL nem vale a pena tentar isto
            $sqlinsert = "update dbo.GSO_PORTIN set [ESTADO] = 'Sucesso', [ESTADO_PORTAB] = '" & $estado  & "', [PARA] = '" & $receptor & "' where LR = '" & $LR & "' and DataInicio = '" & $datadia & "'"
            MsgBox(0, "", $sqlinsert)
            $conn.Execute($sqlinsert)
                If @error Then
                    MsgBox(0, "Erro", "Error updating DB" & @error, 1 )
                    ;   CloseFile()
                EndIf
            EndIf


Func MyErrFunc()
   $HexNumber=hex($oMyError.number,8)
   Msgbox(0,"","We intercepted a COM Error !" & @CRLF & _
                "Number is: " & $HexNumber & @CRLF & _
                "Windescription is: " & $oMyError.windescription )

   SetError(1) ; something to check for when this function returns
Endfunc

Returns error 80020009

You can check the state property of ADO connection

$conn.Execute($sqlinsert)
if $conn.State = 0 then MsgBox(0, "", "Connection is closed")

 

In my case, changing CursorLocation property for connection to value "3" (adUseClient) didn't fixed the variable problem but adding "SET NOCOUNT ON" on the beginning of the script did the job.

[mLipok]

Did you look at ADO.au3 UDF ?