Re: Error using " in .open with ADODB.Recordset

Piper wrote:
I'm using VB coding inside an Access Database. I'm trying to open a
recordset but I'm getting hung up on the SQL statement. I'm using a
query that has a string as a condition. My code is as follows:

Sub setRcdSetQry(rcdSet As ADODB.Recordset, ByVal empID As Long, num
As String)
Dim mrk As String

mrk = Chr$(34)

rcdSet.Open "SELECT Phone_Tbl.Phone_ID, Phone_Tbl.Phone_EmpID,
Phone_Tbl.Phone_Type, Phone_Tbl.Phone_Num, Phone_Tbl.Phone_Active FROM
Phone_Tbl WHERE (((Phone_Tbl.Phone_EmpID)=" & empID & ") AND
((Phone_Tbl.Phone_Num)=" & mrk & num & mrk & "));", setCnxn,
adOpenDynamic, adLockOptimistic, adCmdText

End Sub

The error I get is in the .open statement and is as follows:

Run-time error '-2147217904 (80040e10)':
[Microsoft][ODBC Microsoft Access Driver] Too Few Parameters.
Expected 1.

So I know my SQL statement is failing. I have tried everything, the
first thing I tried was to pass a variable (with the statement) into
the .open statement. But I realized after reading the 'help files',
which should be named the 'will confuse you more files', I was
passing a string within a string to the jet database engine and it
didn't know which string to use.

Thats when it said I needed to use the Chr$(34) in a variable and use
it in the .open statement.

That's one way. Another is to "escape" the literal quote character by
doubling it. However, the best way to deal with this stupid delimiter issue
is to use parameters instead of dynamic sql. I will provide some links below

So that's what I did, and low and behold I
get the same error. I used a quick watch to see what the statement
evaluated to:

You really should have shown us what you changed your code to, but I think I
can guess.

"SELECT Phone_Tbl.Phone_ID, Phone_Tbl.Phone_EmpID,
Phone_Tbl.Phone_Type, Phone_Tbl.Phone_Num, Phone_Tbl.Phone_Active
FROM Phone_Tbl WHERE (((Phone_Tbl.Phone_EmpID)=" & empID & ") AND
((Phone_Tbl.Phone_Num)=" & mrk & num & mrk & "));",

I've tried everything I can think of and everything the help files
can think of. I've spent so many hours on this bug that I'm almost to
the point where I throw my computer away. Please Help!

I'm sure it's an easy fix, I just need a lot of help getting it.

Ok, the idea behind using dynamic sql (not that I'm in favor of using
dynamic sql, mind you) is to create a sql statement that can be run without
modification by the query engine. In the case of Access, that means using
the query builder to test the result. obviously, the above statement would
not run if you copied and pasted it into the SQL View of a query builder
window, would it? Part of the problem, of course is that you've concatenated
the names of the variables into the string instead of the values of the
variables. The Phone_EmpID field will never have an value consisting of " &
empID & ", will it? and the same problem exists for mrk and num.

However, that is not the entire problem. The "too few parameters" error
occurs when your sql statement contains a table or field name that does not
correspond to an actual query or field name in the database. When that
occurs, the query engine assumes you are using a parameter marker in the
query and needs a value for that parameter marker. if you were running the
query in the Access Query Builder (which should be the first thing you do
when you have a problem executing a query via ADO), Access would prompt for
the value of the parameter marker, making it easy for you to see which word
in the query string is causing the problem.

Best Practice Tip #1:
Assign your sql string to a variable, making it easier to troubleshoot:
dim sSQL as String
sSQL= "SELECT Phone_Tbl.Phone_ID, Phone_Tbl.Phone_EmpID,
Phone_Tbl.Phone_Type, Phone_Tbl.Phone_Num, Phone_Tbl.Phone_Active FROM
Phone_Tbl WHERE (((Phone_Tbl.Phone_EmpID)=" & empID & ") AND
((Phone_Tbl.Phone_Num)=" & mrk & num & mrk & "));"

debug.write sSQL

rs.Open sSQL, ...

Based on your second result, I suspect you changed the string concatenation
to:
sSQL= "SELECT Phone_Tbl.Phone_ID, Phone_Tbl.Phone_EmpID,
Phone_Tbl.Phone_Type, Phone_Tbl.Phone_Num, Phone_Tbl.Phone_Active FROM
Phone_Tbl WHERE (((Phone_Tbl.Phone_EmpID)=" & _
Chr(34) & " & empID & " & Chr(34) & _
") AND ((Phone_Tbl.Phone_Num)=" & Chr(34) & _
" & mrk & num & mrk & " & Chr(34) &"));",

You see? You are concatenating a string containing " & empID & " . It should
be:

sSQL= "SELECT Phone_Tbl.Phone_ID, Phone_Tbl.Phone_EmpID,
Phone_Tbl.Phone_Type, Phone_Tbl.Phone_Num, Phone_Tbl.Phone_Active FROM
Phone_Tbl WHERE (((Phone_Tbl.Phone_EmpID)=" & _
Chr(34) & empID & Chr(34) & _
") AND ((Phone_Tbl.Phone_Num)=" & Chr(34) & _
mrk & num & mrk & Chr(34) &"));",

Best Practice Tip #2: use parameters
Isn't this a PITA? Wouldn't this be simpler?

sSQL= "SELECT Phone_Tbl.Phone_ID, Phone_Tbl.Phone_EmpID,
Phone_Tbl.Phone_Type, Phone_Tbl.Phone_Num, Phone_Tbl.Phone_Active FROM
Phone_Tbl WHERE (((Phone_Tbl.Phone_EmpID)= pEmpID & _
") AND ((Phone_Tbl.Phone_Num)= pNumber));"

See? No delimiters. pEmp and pNumber are parameter markers. You can use
parameters to pass any type of data without worrying about using quotes for
strings or hash marks (#) for dates. So much simpler.
If you run the sql statement in Access Query Builder, Access will prompt for
the values. Supply values, just for testing.Run it and make sure it returns
the intended results. You should always do this step anyways just to be sure
you've built the sql statement correctly.

Take note of the order in which Access prompts for the values: you will need
to supply the values in your code in the same order. The simplest way to do
this is to create a variant array containing the values in the correct
order, and use the Execute method of a Command object to retrieve a
recordset containing the values:

dim cmd as adodb.command
set cmd=new adodb.command
with cmd
.commandtext=sSQL
.commandtype=adCmdText
set .activeconnection = setCnxn
end with
dim arParms
arParms = Array(empID, mrk & num & mrk)
set rcdSet=cmd.Execute(,arParms)

Simplest yet is to use saved parameter queries. See these links:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


--
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: Same date range in main and sub form
    ... You need to concatenate together the parts to make up the SQL string ... it will look like the SQL statement you original ... Allen Browne - Microsoft MVP. ... well as the name of the query on which the form is based. ...
    (microsoft.public.access.forms)
  • Re: Multi-Select ListBox
    ... the selected values are then used as query criteria. ... > ' Remove the leading comma from the string ... > ' Build the new SQL statement incorporating the string ...
    (microsoft.public.access.forms)
  • Re: Multi-Select ListBox
    ... ' Remove the leading comma from the string ... ' Apply the new SQL statement to the query ... You have not closed the qdf so the Query has not been updated with the ... >> ' Remove the leading comma from the string ...
    (microsoft.public.access.forms)
  • Re: list box sort
    ... If you are already modifying the query, then building the entire SQL statement as a string in code might be even easier. ... > Dim strSQL as string ...
    (microsoft.public.access.formscoding)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)