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.Command

2130 With cmdADO8
2140 Set .ActiveConnection = ADOConn
2150 .CommandType = adCmdText
2160 .CommandText = strSQL8
2170 End With

'this runs in the loop
3150 cmdADO8.Execute adExecuteNoRecords, Array(EntryIDArray(i),
arr(i, 17), _
arr(i, 6), arr(i,
14), EncounterEntryIDArray(i), _
arr(i, 8), _
arr(i, 16),
arr(i, 1), arr(i, 7))

It falls over after running fine for a few loops on the above
statement.
It actually crashes Excel, so I have added a log to a text file and
when it falls over the
parameters seems fine:
6876398,16,"ja2S.","AHVLX",6876396,"FOR EXTERNAL USE
ONLY",5669941,1406,"CAPSAICIN 0.025% cream 45G"

There is an error handler, but as Excel just crashes on the above
statement that doesn't help me.
I suppose there has to be something wrong with:
Array(EntryIDArray(i), arr(i, 17), _
arr(i, 6), arr(i,
14), EncounterEntryIDArray(i), _
arr(i, 8), _
arr(i, 16),
arr(i, 1), arr(i, 7))

But I can't see it yet, particularly as it runs fine with parameters
that seem similar as the one above.
The variables in the query, lLoggedInUser and lCurrentDate are fine.

Without being able to test this myself, I have no chance of figuring
this out. Could you try running this code against an Access or SQL
Server database so we can confirm that it's not a problem with the
Intersolv driver? If it's reproducible against Access or SQL Server, let
us know and I will look at it again.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.