[SOLVED] MDB - Error to INSERT

[Blois]

Hi Guys,
Fine?

I have this code and I use it to perform the query, however when I change the query to INSERT it is not working return error.

 

expandcollapse popup

#include <GUIConstants.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>

Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

Example()

Func Example()
    Local $dbname = FileOpenDialog("Choose Access Database", @ScriptDir, "Access files (*.accdb)", 1)
    If @error then Return SetError(@error, @extended, 0)


    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & $dbname & ";Uid=;Pwd=;")
    $adoRs = ObjCreate("ADODB.Recordset")

    GUICreate("listview items", 550, 250, 100, 200, -1, $WS_EX_ACCEPTFILES)
    Local $idListview = GUICtrlCreateListView("Codigo    |Nome         |Valor  ", 10, 10, 520, 150) ;,$LVS_SORTDESCENDING
    $queryInsert = INSERT INTO TABLENAME VALUES (''aaaaa'', ''bbbbbb'', ''cccccc'')
    
    Local $aResult
    With $adoRs
        .CursorType = 2
        .LockType = 3
        .Open($queryInsert, $adoCon)
        If @error Then
            ; deal with Probable SQL error
            Return SetError(1)
        EndIf
        If Not .EOF Then $aResult = .GetRows()
        .Close()
    EndWith
    $adoRs = 0
    _ArrayDisplay($aResult, 'UBound($aResult)=' & UBound($aResult))

    For $iRow_idx = 0 To UBound($aResult) - 1
        GUICtrlCreateListViewItem($aResult[$iRow_idx][0], $idListview)
    Next
    $adoCon.Close
    GUISetState()

    ; Loop until the user exits.
    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop

;~             Case $idButton
;~                 MsgBox($MB_SYSTEMMODAL, "listview item", GUICtrlRead(GUICtrlRead($idListview)), 2)

            Case $idListview
                MsgBox($MB_SYSTEMMODAL, "listview", "clicked=" & GUICtrlGetState($idListview), 2)

        EndSwitch
    WEnd
EndFunc   ;==>Example

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

Help, Please!

 

[Earthshine]

does your query work in access?

[FrancescoDiMuro]

@Blois

Seems ( at least from the code you posted ), that you are missing some double quotes in your query definition.

$queryInsert = INSERT INTO TABLENAME VALUES (''aaaaa'', ''bbbbbb'', ''cccccc'')

Should be: 

$queryInsert = "INSERT INTO TABLENAME VALUES ('aaaaa', 'bbbbbb', 'cccccc');"

What kind of error does AutoIt return?

[Blois]

5 hours ago, Earthshine said:

does your query work in access?

@Earthshine

Yes, work!

$querySelect = 'Select * From TABLE'

 

Edited August 24, 2018 by Blois

[Blois]

@FrancescoDiMuro

 

 

 

[FrancescoDiMuro]

@Blois

The script is telling you that the number of fields you are inserting in the table is not the same of the number of fields you do have in your table.

What's your table schema, and which real values are you trying to insert into it?

[Blois]

7 hours ago, FrancescoDiMuro said:

@Blois

The script is telling you that the number of fields you are inserting in the table is not the same of the number of fields you do have in your table.

What's your table schema, and which real values are you trying to insert into it?

@FrancescoDiMuro Thank you!
I had forgotten that I put a self-numbered Column.

[FrancescoDiMuro]

@Blois
Happy to have helped

[Blois]

@FrancescoDiMuro

In the test file where only the insert code works, but when I paste the code in .au3 the GUI gives an error that does not find the database.

 

 

 

[FrancescoDiMuro]

@Blois

What is your Connection String on AutoIt?

[Blois]

1 hour ago, FrancescoDiMuro said:

@Blois

What is your Connection String on AutoIt?

 

These errors stopped when I used the only

$adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname & ";Uid=;Pwd=;")

Now I have this error:

 

line error: 

If Not .EOF Then $aResult = .GetRows()

   

 

Edited August 27, 2018 by Blois

[Blois]

@FrancescoDiMuro

I'm sorry, I was closing the connection before capturing the result.

Machines without Access can not connect to the MDB, is there anything that can be done?