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

I hope you fixed your Execute statements ...

Yes, all fixed now.
Surprised that I haven't noticed this earlier and that
it hasn't caused problems. I suppose it was just ignored.

Yes, I probably didn't need the full .CreateParameter method in the end, but there
might be some advantage if only that I learned something new there.

RBS

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:O27M%23twEIHA.1316@xxxxxxxxxxxxxxxxxxxxxxx
You should not have had to. There is nothing wrong with doing it that way, but there is no necessity to do it that way either, especially with a Jet backend (sing Jet does not support output and return parameters)

I hope you fixed your Execute statements ...

RB Smissaert wrote:
Think I have this all fixed now.
I needed to create the paramaters with .CreateParameter
and set the datatype and size (for strings) of all the parameters.

RBS


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
news:%23hl$reuEIHA.280@xxxxxxxxxxxxxxxxxxxxxxx
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.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.