Re: ADO Error - ODBC Microsft Access Driver Toofew parameters. Expecte

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Feb 15, 11:01 am, RoyVidar <roy_vidarNOS...@xxxxxxxx> wrote:
I think the concept of "doublequoting" is more a DAO phenomena thanADO.

Try

strSQL = "SELECT * " & _
"FROM tblNames " & _
"WHERE tblNames.Name = '" & Replace(strName, "'", "''") & "'"

That handles single quotes but what about escaping double quotes plus
any other 'illegal' characters? Is it practical/safe to try to escape
all these characters yourself?

I think the better approach is to let the system (engine, parser,
provider, driver, whatever) do the work and the best way I know of
achieving this is to use Parameter objects; ADO is my preference but
perhaps DAO can do the same. This approach has the added advantage of
making one think about converting dynamic/ad hoc SQL into engine-level
PROCEDUREs ;-)

Quick example:

Sub paramtest()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")

Dim value As String
value = "Double "" quote Single ' quote"

With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test (" & _
" col1 VARCHAR(30));"

' Try the 'manual escape' approach
On Error Resume Next
.Execute _
"INSERT INTO Test (col1) VALUES (" & _
Replace(value, "'", "''") & ");"
If Err.Number <> 0 Then
MsgBox _
Err.Description, , _
"Manual escape approach"
End If
On Error GoTo 0

End With

'Try the 'parameters' approach
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = .ActiveConnection
With cmd
.CommandText = _
"INSERT INTO Test (col1) VALUES (?);"

.Parameters _
.Append .CreateParameter(, 200, 1, 30, value)

Dim rowsAffected As Long
.Execute rowsAffected

MsgBox _
"Rows affected: " & CStr(rowsAffected), _
, "Parameters approach"
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--


.

Relevant Pages

  • Please proof script - Re: IsMember and IfMember
    ... Dim HourNow, Greeting ... ' Escape any forward slash characters, "/", with the backslash ... All other characters that should be escaped are. ... Set objGroup = Nothing ...
    (microsoft.public.scripting.vbscript)
  • Re: tab & other character in ssh
    ... i m firing grep command through ssh on remote machine. ... You need to escape those characters. ... Single quotes should do it: ...
    (comp.security.ssh)
  • Re: missing first chareacter after "
    ... I tried everything, escape, no escape... ... For some reason always two characters after = are missing. ... > the string is in between single quotes '' and not in between double ...
    (alt.php)
  • Re: PHP, mysql, and escaping characters
    ... MySQL requires only that backslash and the quote ... In addition to the qutoes, backslashes, and nulls, real escape string ... "Inserting a large value into a BLOB column is no different than ... a few characters that have special meaning in SQL or to the MySQL ...
    (comp.lang.php)
  • Re: Determine actual number of groups a user belongs to
    ... Dim intSecurity, intDistribution ... ' Escape any forward slash characters, "/", with the backslash ... All other characters that should be escaped are. ...
    (microsoft.public.windows.server.active_directory)