Sign in to follow this  
Followers 0
jfisher

ADO Usage - How to add a record

5 posts in this topic

I am frantically trying to add a new record to an excel database via ado with no luck whatsoever. I have tried building an sql statement INSERT INTO $table VALUES .... and using $recordset.execute($sql) with no luck, currently what I have below which I believe should be working is:

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")
$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\AdvisorGUI\ExcelTestFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")


$objRecordSet.Open ($s_Tablename, $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)
        With $objRecordSet
            .AddNew
            .Fields("Agent") = "bob"
            .Fields("CallDate") = "3/24"
            .Fields("CallTime") = "6:28pm"
            .Update
        EndWith

Also as a side note, I get no error when this does not go in correctly. Now this is my first time ever messing with ADO...is there any way to be notified if an entry did not go in, and why that is the case? Thanks much.

Share this post


Link to post
Share on other sites



First, $adOpenStatic and $adLockOptimistic are constants taht are not available to AutoIt unless you define them. Look at my sig for a link t a post about harvesting Enum constants -- it includes a method of getting these values as well as a sample include file for Excel.

Second, unless you are really intent on using ODBC and treating Excel like a database, I'd suggest using LocoDarwin's ExcelCom UDF to work with getting your data in instead...

Dale


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

Also as a side note, I get no error when this does not go in correctly. Now this is my first time ever messing with ADO...is there any way to be notified if an entry did not go in, and why that is the case? Thanks much.

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

Func MyErrFunc()
    $hexnum=hex($objErr.number,8)

    Msgbox(0,"","We intercepted a COM Error!!"      & @CRLF                & @CRLF & _
                 "err.description is: " & $objErr.description   & @CRLF & _
                 "err.windescription is: " & $objErr.windescription & @CRLF & _
                 "err.lastdllerror is: "   & $objErr.lastdllerror   & @CRLF & _
                 "err.scriptline is: "   & $objErr.scriptline    & @CRLF & _
                 "err.number is: "       & $hexnum               & @CRLF & _
                 "err.source is: "       & $objErr.source        & @CRLF & _
                 "err.helpfile is: "       & $objErr.helpfile      & @CRLF & _
                 "err.helpcontext is: " & $objErr.helpcontext _
                )
    Exit
EndFunc

Share this post


Link to post
Share on other sites

I really am intent on using it. I tried the excelcom and what I have is a list of people on the left side of gui, and a bunch of fields that the data it dropped into. Using excelcom it took 8 seconds to save person 1 and load person 2. It takes a fraction of a second to read from the file with ado....if only I could get it to write to it. I have already had those defined....

I have also included the entire source if someone would like to give it a go. All you need to do is place the excel file into C:\ and plce adoconstants.au3 to include and it should run. The functions we are looking at are _SaveSingleEval() an _LoadSingleEval() which are noted below in the code. in particular savesingleeval will not save the data to excel which is the problem function.

In order to get to this point you must click the button below the listbox, wait a couple seconds and click it again...afterwards, change the selected entry in the listbox and when saving correctly, the msgbox should have data other than just | pipes.

heres some more code.

[autoit]#include-once

;---- CursorType Values ----

$adOpenForwardOnly = 0

$adOpenKeyset = 1

$adOpenDynamic = 2

$adOpenStatic = 3

;---- CursorOptionEnum Values ----

$adHoldRecords = 0x00000100

$adMovePrevious = 0x00000200

$adAddNew = 0x01000400

$adDelete = 0x01000800

$adUpdate = 0x01008000

$adBookmark = 0x00002000

$adApproxPosition = 0x00004000

$adUpdateBatch = 0x00010000

$adResync = 0x00020000

$adNotify = 0x00040000

;---- LockTypeEnum Values ----

$adLockReadOnly = 1

$adLockPessimistic = 2

$adLockOptimistic = 3

$adLockBatchOptimistic = 4

;---- ExecuteOptionEnum Values ----

$adRunAsync = 0x00000010

;---- ObjectStateEnum Values ----

$adStateClosed = 0x00000000

$adStateOpen = 0x00000001

$adStateConnecting = 0x00000002

$adStateExecuting = 0x00000004

;---- CursorLocationEnum Values ----

$adUseServer = 2

$adUseClient = 3

;---- DataTypeEnum Values ----

$adEmpty = 0

$adTinyInt = 16

$adSmallInt = 2

$adInteger = 3

$adBigInt = 20

$adUnsignedTinyInt = 17

$adUnsignedSmallInt = 18

$adUnsignedInt = 19

$adUnsignedBigInt = 21

$adSingle = 4

$adDouble = 5

$adCurrency = 6

$adDecimal = 14

$adNumeric = 131

$adBoolean = 11

$adError = 10

$adUserDefined = 132

$adVariant = 12

$adIDispatch = 9

$adIUnknown = 13

$adGUID = 72

$adDate = 7

$adDBDate = 133

$adDBTime = 134

$adDBTimeStamp = 135

$adBSTR = 8

$adChar = 129

$adVarChar = 200

$adLongVarChar = 201

$adWChar = 130

$adVarWChar = 202

$adLongVarWChar = 203

$adBinary = 128

$adVarBinary = 204

$adLongVarBinary = 205

;---- FieldAttributeEnum Values ----

$adFldMayDefer = 0x00000002

$adFldUpdatable = 0x00000004

$adFldUnknownUpdatable = 0x00000008

$adFldFixed = 0x00000010

$adFldIsNullable = 0x00000020

$adFldMayBeNull = 0x00000040

$adFldLong = 0x00000080

$adFldRowID = 0x00000100

$adFldRowVersion = 0x00000200

$adFldCacheDeferred = 0x00001000

;---- EditModeEnum Values ----

$adEditNone = 0x0000

$adEditInProgress = 0x0001

$adEditAdd = 0x0002

$adEditDelete = 0x0004

;---- RecordStatusEnum Values ----

$adRecOK = 0x0000000

$adRecNew = 0x0000001

$adRecModified = 0x0000002

$adRecDeleted = 0x0000004

$adRecUnmodified = 0x0000008

$adRecInvalid = 0x0000010

$adRecMultipleChanges = 0x0000040

$adRecPendingChanges = 0x0000080

$adRecCanceled = 0x0000100

$adRecCantRelease = 0x0000400

$adRecConcurrencyViolation = 0x0000800

$adRecIntegrityViolation = 0x0001000

$adRecMaxChangesExceeded = 0x0002000

$adRecObjectOpen = 0x0004000

$adRecOutOfMemory = 0x0008000

$adRecPermissionDenied = 0x0010000

$adRecSchemaViolation = 0x0020000

$adRecDBDeleted = 0x0040000

;---- GetRowsOptionEnum Values ----

$adGetRowsRest = -1

;---- PositionEnum Values ----

$adPosUnknown = -1

$adPosBOF = -2

$adPosEOF = -3

;---- enum Values ----

$adBookmarkCurrent = 0

$adBookmarkFirst = 1

$adBookmarkLast = 2

;---- MarshalOptionsEnum Values ----

$adMarshalAll = 0

$adMarshalModifiedOnly = 1

;---- AffectEnum Values ----

$adAffectCurrent = 1

$adAffectGroup = 2

$adAffectAll = 3

;---- FilterGroupEnum Values ----

$adFilterNone = 0

$adFilterPendingRecords = 1

$adFilterAffectedRecords = 2

$adFilterFetchedRecords = 3

$adFilterPredicate = 4

;---- SearchDirection Values ----

$adSearchForward = 1

$adSearchBackward = -1

;---- ConnectPromptEnum Values ----

$adPromptAlways = 1

$adPromptComplete = 2

$adPromptCompleteRequired = 3

$adPromptNever = 4

;---- ConnectModeEnum Values ----

$adModeUnknown = 0

$adModeRead = 1

$adModeWrite = 2

$adModeReadWrite = 3

$adModeShareDenyRead = 4

$adModeShareDenyWrite = 8

$adModeShareExclusive = 0xc

$adModeShareDenyNone = 0x10

;---- IsolationLevelEnum Values ----

$adXactUnspecified = 0xffffffff

$adXactChaos = 0x00000010

$adXactReadUncommitted = 0x00000100

$adXactBrowse = 0x00000100

$adXactCursorStability = 0x00001000

$adXactReadCommitted = 0x00001000

$adXactRepeatableRead = 0x00010000

$adXactSerializable = 0x00100000

$adXactIsolated = 0x00100000

;---- XactAttributeEnum Values ----

$adXactCommitRetaining = 0x00020000

$adXactAbortRetaining = 0x00040000

;---- PropertyAttributesEnum Values ----

$adPropNotSupported = 0x0000

$adPropRequired = 0x0001

$adPropOptional = 0x0002

$adPropRead = 0x0200

$adPropWrite = 0x0400

;---- ErrorValueEnum Values ----

$adErrInvalidArgument = 0xbb9

$adErrNoCurrentRecord = 0xbcd

$adErrIllegalOperation = 0xc93

$adErrInTransaction = 0xcae

$adErrFeatureNotAvailable = 0xcb3

$adErrItemNotFound = 0xcc1

$adErrObjectInCollection = 0xd27

$adErrObjectNotSet = 0xd5c

$adErrDataConversion = 0xd5d

$adErrObjectClosed = 0xe78

$adErrObjectOpen = 0xe79

$adErrProviderNotFound = 0xe7a

$adErrBoundToCommand = 0xe7b

$adErrInvalidParamInfo = 0xe7c

$adErrInvalidConnection = 0xe7d

$adErrStillExecuting = 0xe7f

$adErrStillConnecting = 0xe81

;---- ParameterAttributesEnum Values ----

$adParamSigned = 0x0010

$adParamNullable = 0x0040

$adParamLong = 0x0080

;---- ParameterDirectionEnum Values ----

$adParamUnknown = 0x0000

$adParamInput = 0x0001

$adParamOutput = 0x0002

$adParamInputOutput = 0x0003

$adParamReturnValue = 0x0004

;---- CommandTypeEnum Values ----

$adCmdUnknown = 0x0008

$adCmdText = 0x0001

$adCmdTable = 0x0002

$adCmdStoredProc = 0x0004

;---- SchemaEnum Values ----

$adSchemaProviderSpecific = -1

$adSchemaAsserts = 0

$adSchemaCatalogs = 1

$adSchemaCharacterSets = 2

$adSchemaCollations = 3

$adSchemaColumns = 4

$adSchemaCheckraints = 5

$adSchemaraintColumnUsage = 6

$adSchemaraintTableUsage = 7

$adSchemaKeyColumnUsage = 8

$adSchemaReferentialContraints = 9

$adSchemaTableraints = 10

$adSchemaColumnsDomainUsage = 11

$adSchemaIndexes = 12

$adSchemaColumnPrivileges = 13

$adSchemaTablePrivileges = 14

$adSchemaUsagePrivileges = 15

$adSchemaProcedures = 16

$adSchemaSchemata = 17

$adSchemaSQLLanguages = 18

$adSchemaStatistics = 19

$adSchemaTables = 20

$adSchemaTranslations = 21

$adSchemaProviderTypes = 22

$adSchemaViews = 23

$adSchemaViewColumnUsage = 24

$adSchemaViewTableUsage = 25

$adSchemaProcedureParameters = 26

$adSchemaForeignKeys = 27

$adSchemaPrimaryKeys = 28

$adSchemaProcedureColumns = 29

jfisher446AdvisorGUIAdo.zip

Share this post


Link to post
Share on other sites

Nevermind all, thanks for the help but I found the problem. It was all in my connection string:

$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\AdvisorGUI\ExcelTestFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

The problem was in the IMEX = 1...per connectionstrings.com if imex = 1 then it may cause problems with write access in excel! Thanks.

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
Sign in to follow this  
Followers 0