Re: What could be the problem with this INSERT with ADO parameters?



RB Smissaert wrote:
Trying to debug a procedure that writes data to an Interbase database.
I am using an Intersolve ODBC driver and ADO in Excel VBA.
This is done in a loop and I used to do this without parametrized
queries and I am now doing this
with parameters, but sofar no success yet.

This is the simplified code where arr is a variant array made by
assigning an Excel *** range to that array.
EntryIDArray and EncounterEntryIDArray are 1-D Long arrays.

2100 strSQL8 = "INSERT INTO " & _
"ENTRY(ENTRY_ID, ADDED_BY, UPDATED_BY, " & _
"READ_CODE, TERM_ID, ENCOUNTER_ID, " & _
"SECURE_TYPE, ADDED_DATE, UPDATED_DATE, " & _
"FREE_TEXT, DORMANT_FLAG, ENTRY_TYPE, " & _
"START_DATE, END_DATE, PROBLEM_ID, " & _
"ENTRY_FLAGS, PATIENT_ID, " & _
"SUBJECT_TYPE, TERM_TEXT) " & _
"VALUES(?, ?, " & lLoggedInUser & ", " & _
"?, ?, ?, 1, " & lCurrentDate & ", " & lCurrentDate

Why aren't you using parameters for the logged in user and added and
updated dates? The point of using parameters is to avoid concatenating
data that is not known at design time into your sql statements.

& ", " & _
"?, 0, 13, " & _
lCurrentDate & ", 0, ?, '', ?, 'M', ?)"

Again: why the concatenation here?


2120 Set cmdADO8 = New ADODB.Comma