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

RB Smissaert wrote:
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?

You don't need to worry about it. That's the beauty of using parameters.
BtW, that adExecuteNoRecords argument should be the third argument in the
call. Like this:

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

The first argument is supposed to contain the sql statement if you haven't
already assigned one to CommandText. The third argument takes the Command
and Execute options.

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.

It's probably because you have the arguments in the wrong order - which
should raise an error, not crash Excel ???

So, how do I handle a string with comma's in the parameter array?

Again: don't worry about them!!!


--
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"


.

Relevant Pages

  • Re: Trying to insert textboxdata into database
    ... typed with a @ prefix in SQL server in Access? ... also another remark a textbox is returning a string so why convert a string ... Dim Projname, ProjSpon As String ... 'Create SQL Select query command ...
    (microsoft.public.dotnet.languages.vb)
  • Re: OfficeCodeBehind?
    ... The WHERE clause needs to be inserted into the SQL string definition in this ... ' Set up the command text: ... I am not sure though whether that will work directly on an SQL Server ...
    (microsoft.public.word.vba.customization)
  • Re: What could be the problem with this INSERT with ADO parameters?
    ... that adExecuteNoRecords argument should be the third argument in the call. ... The third argument takes the Command and Execute options. ... The only way I have found sofar is to enclose the string in single ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: Insert Command
    ... Change your SQL Insert stmt to: ... then add three parameters to your command, one for each of the actual ... quoted string, and you won't have to worry about SQL Injection attacks from ... programmer helping programmers. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Including BLOB vaules in INSERT SQL
    ... Now, with this, you create your SQL. ... Say you have a string value ... And then you place it in a command, ... insert into MyTable values; drop database ...
    (microsoft.public.dotnet.languages.csharp)