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



Let me explain the last problem a bit clearer:

given this SQL:

strSQL = "INSERT INTO TABLE1(FIELD1, FIELD2, FIELD3) " & _
"VALUES(?, ?, ?)"

and this ADO command:

cmdADO.Execute adExecuteNoRecords, Array(arr(i, 1), arr(i, 2), arr(i, 3)

How do I handle the situation where arr(i, 1) is a string with a comma in it?
arr is a variant array.
The only way I have found sofar is to enclose the string in single quotes, but that is
no good really as that full string, including the quotes will then be written to the database.

I have tried doing this:

Dim str As String
str = CStr(arr(i, 1)
cmdADO.Execute adExecuteNoRecords, Array(str, arr(i, 2), arr(i, 3)

But that gives the same problem, it actually crashes Excel.

So, how do I handle a string with comma's in the parameter array?
I could for this particular query go back to not using parameters, but I am sure there must be a
solution to this.


RBS


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message news:eRoLADrEIHA.1168@xxxxxxxxxxxxxxxxxxxxxxx
I think I have this mostly fixed now.
Needed to replace the constants with variables.
Also I needed to enclose fields that had comma's with single quotes.
This surprised me and it is not that good as these single quotes do appear in the database.
Any solution for this last problem?

RBS


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:%23A2DxooEIHA.4752@xxxxxxxxxxxxxxxxxxxxxxx
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.




.