Jump to content
Blois

[SOLVED] MDB - Error to INSERT

Recommended Posts

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.

 

#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!

 

Share this post


Link to post
Share on other sites

@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? :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
5 hours ago, Earthshine said:

does your query work in access?

@Earthshine

Yes, work!

$querySelect = 'Select * From TABLE'

 

Edited by Blois

Share this post


Link to post
Share on other sites

@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? :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

@Blois
Happy to have helped :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

@Blois

What is your Connection String on AutoIt? :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
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:

image.png.39637bc68658184a06710deff9fb9765.png

 

line error: 

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


   

 

Edited by Blois

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By KF5WGB
      Hi database and SQL masters,
      This is my first time dealing with .mdb files and Arrays and I have a few questions. I just need to change one field in a databases of unknow # of records. The script
      runs on different PC's. Each PC has a database with the same structure just more or less records.
      Let me explain what I want to do:
      1: Open .mdb
      2: Get the number of records
      3: Start with 1st record
      4: Check DBfield "fldQSLConfByS" if Letter "Q" is in the field
      5: If Letter exists goto next record
      6: If not, add Letter Q to existing entry and write it back to "fldQSLConfBYs' field in DB record
         Put complete record (all fields) into a workable string (I think ArraytoString does
         that) for further processing or even better into $fieldsxy[xy] [xy]
      7: next record until all records are processed.
      I found ADO.au3 UDF, which is over my head and an overkill to what I need to do, I think.
      Then I stumbled across Access.au3.I modified the example.au3 and can read the DB, display the fields etc. but I can not write back to the DB. Always get: "Error in writting Data"
      See Section:
      ; *******************************************************
      ; 10 - Edit/change Record
      ; *******************************************************
      No idea why I can not write the data back. Playing with it for a week now and can not figure it out.
      Is there an easier way to just edit/change one DBfield?
      Oh.. and how do I display or read an array value? ( $datafieldxy = $array[xy][xy] )
      Is _ArrayToString($avArray_Record, "", 48,48) the only way? (48, number of field)
      Attached are 3 files:
      Access.au3 - the UDF ~~ HAMLog_AccessDB.au3 - Scripy ~~ testdb.mdb - Database
      Thanks for any help
       
      HAMLog_AccessDB.au3 testdb.mdb Access.au3
    • By TheSaint
      Only early days at this point, but I have been pondering such a program for a while.
      As good as calibre is (thank you Kovid Goyal), which is a great and wonderful ebook suite of tools and a fair database, it does have its limitations. One of which, is how it deals with multiple libraries, another is the views you get. CalibBrowser will seek to address those.
      What CalibBrowser is not going to be, is an editor for existing calibre libraries. That will be left up to calibre, which is very much needed still, and covers many aspects I will never look at. Unlike calibre, which is quite a complex program, CalibBrowser also seeks to be simple. It is mainly a viewer, at this point, but will later be able to create its own libraries. However, it does not and will not export them to calibre, especially as calibre employs a far different method and structure to what CalibBrowser will employ.
      When CalibBrowser starts, it looks for calibre executables and the main Calibre Library. Whatever isn't found, you get prompted for with a browse option.
      A calibre library, is a set of ebook folders (Author\Ebooks) and a database file, always named metadata.db, and which causes an issue when it comes to multiple libraries, but makes life a bit easier when reconstructing any corrupted libraries. However, there are better ways to deal with that, as my program will show.
      The metadata.db file is an SQL database, so I am having a learning curve right now, as I have only ever dealt with an SQL database previously, codewise, when I created my INItoSQL program some time last year, as an exercise to prove a point.
      At the moment, things are pretty basic, and not everything works 100%. Here is a screenshot, to give an idea of it, but keep in mind, I intend to expand the current GUI for other stuff I will be adding.

      Older Screenshots
      Gawd, I just noticed the '3|7' in the Book Input field (original screenshot). I was using that during troubleshooting for the multiple images scrolling and forgot to disable it ... not that it impacts anything. When it comes to maths, I struggle a bit, especially when tired. Right scrolling was easy, with a continuous loop, was easy to implement. Left scrolling was significantly harder for my poor brain ... until I realized I needed to see them as Min and Max.
      As you can see the program is usable, and all the buttons, aside from the Program Information one, work. You can even load different calibre libraries, and even reload after making changes to one with calibre. The calibre program does not need to be running, even to view an ebook in the Calibre Reader. The combo selector for a library and the ADD button are only temporarily placed where they are, until I expand the GUI.
      My intention at this point, is to add another five thumbnail images, directly below existing. Currently they aren't clickable, but I may add that.
      Here is another screenshot, of what you see when you click the larger Cover image.

      If you want to have a play with the program as is, then you will need to also get the 'sqlite3.dll' file from some online source. When CalibBrowser starts successfully with the selected calibre library, it copies its metadata.db file to a sub-folder of the program called 'Backups'. It also creates a sub-folder in that, based on the library name, to house it. That copied file, is the one the program uses, though it does not even edit that, and file modification is checked every time the program starts with a particular library, or when you Reload or select a library. If the original source file has been modified, then the program copy is overwritten. The Reload Database button does nothing, if there is no change detected, and reports such.
      Place the required 'sqlite3.dll' file in the CalibBrowser root folder. Download includes source files (sqlite3.dll excepted).
      Also required of course, is an install of calibre, plus some ebooks in a created library - Calibre Library is the default when you first add ebooks to calibre. The Mobile Read Forums, is a great source for all things ebook, and calibre can be found there in the E-Book Software section.
      CalibBrowser.zip  - Upload 4  (previous downloads: 1 + 12 + 5)
      CalibBrowser (new).zip
      My apologies for the program being created in AutoIt v3.3.0.0. It is the first one I have done in a while, with an older AutoIt version. Basically my Win 7 Netbook, which has a current version of AutoIt, was busy and is always busy doing something, and not suitable for doing big projects for several reasons. My older but more powerful Win XP Laptop, has a better programming environment, better computer chair (most important for my knees etc), better external monitor (wider and newish), full size external keyboard & mouse, and a great suite of setup tools to assist me. I run several older versions of AutoIt already on my Laptop, but haven't yet determined what I need to adjust to add a current version of AutoIt to the mix. This also applies to my hugely beneficial Toolbar For Any program (one of those tools), which I constantly use with SciTE. At some point, when finished, I may update the program to the current AutoIt version ... especially as I believe I am now proficient enough to do so, having become quite familiar with it in the last year or so, making many programs with it.
      Enjoy!
       
    • By robertocm
      In the code below i can write the excel sheet with an array from an ADODB GetRows command
      But not using _Excel_RangeWrite function
      Thanks for your comments,
      #include <Array.au3> #include <Excel.au3> ;Help: COM Error Handling Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB") Local $sFilePath = @ScriptDir & "\db.mdb" Local Const $iCursorType = 0 ; adOpenForwardOnly, 3 adOpenStatic Local Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic Local Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable Global $cn = ObjCreate("ADODB.Connection") ; Create a connection object Global $rst = ObjCreate("ADODB.Recordset") ; Create a recordset object ;Global $sADOConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $sFilePath ;Global $sADOConnectionString = 'DRIVER={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath & ';uid=;pwd=MyPassword;' Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Jet OLEDB:Database Password=123" $cn.CursorLocation = 3 ; adUseClient ;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode ;https://www.w3schools.com/asp/prop_rec_mode.asp $cn.Mode = 1 ;Read-only ;$cn.CommandTimeout = 0 $cn.Open($sADOConnectionString) ; Open the connection Local $sSQL = "SELECT * FROM TABLE1" $rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query If Not $rst.EOF = True Then Local $rstArray = $rst.GetRows() _ArrayDisplay ($rstArray) $rst.Close ;Create application object Local $oAppl = _Excel_Open() If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; Create a new workbook with only 1 worksheet Local $oWorkbook = _Excel_BookNew($oAppl, 1) If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.") Local $sSheet = $oWorkbook.ActiveSheet Local $aArray1D[3] = ["Field1", "Field2", "Field3"] $sSheet.Range("A1:C1").Font.Bold = True $sSheet.Range("A1:C1").value = $aArray1D Local $RecCount = UBound($rstArray) Local $TrstArray = $rstArray _ArrayTranspose($TrstArray) ;$sSheet.Range("A2:C" & $RecCount + 1).value = $TrstArray ;<<<<<<<<<<<<<< This writes the data OK ;This fails _Excel_RangeWrite($oWorkbook, Default, $TrstArray, "A2:C" & $RecCount + 1) If @error Then MsgBox(0, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $rst = 0 ;Release the recordset object $cn.Close ;Close the connection $cn = 0 ;Release the connection object Else $rst.Close $rst = 0 ; Release the recordset object $cn.Close ; Close the connection $cn = 0 ; Release the connection object MsgBox(262144, "", "Empty" & @CRLF & "Empty recordset" , 5) EndIf Func _ErrADODB() Msgbox(0,"ADODB COM Error","We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $errADODB.description & @CRLF & _ "err.windescription:" & @TAB & $errADODB.windescription & @CRLF & _ "err.number is: " & @TAB & hex($errADODB.number,8) & @CRLF & _ "err.lastdllerror is: " & @TAB & $errADODB.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $errADODB.scriptline & @CRLF & _ "err.source is: " & @TAB & $errADODB.source & @CRLF & _ "err.helpfile is: " & @TAB & $errADODB.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $errADODB.helpcontext, 5) Local $err = $errADODB.number If $err = 0 Then $err = -1 $rst = 0 $cmd = 0 $cn.Close $cn = 0 Exit EndFunc  
    • By 9252Survive
      Hello guys! 

      I am having some difficulty in achieving a very simple task here. I have gone through the forums and tried some examples and UDFs but I can't seem to work it out. I would really appreciate if someone could help me out. 

      Problem:

      Currently, I am logging the required feedback from the script into a log file in a simple way ... get the info in the var >> write it in the file 

      But now I am in need to perform some analysis and need some of the values to go into an MSSQL table  in the attached format
      Also, I need to be able to use Integrated Security" or "Trusted_Connection set as true or use the logged in windows credentials to connect to the server/database




      any help will be much appreciated!!!  

      Thanks! 
    • By dangr82
      I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything.
      Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc  
×
×
  • Create New...