SQL statement

[peekay]

I have a table named Jobs and a field in that table named CatName. If I wish to open a recordset which selects all the records which has a CatName equal to a variable named SStr I set the following query :<br>Set rsCatItems = dbWorkshop.OpenRecordset(&quot;Select * from&nbsp;&nbsp;jobs where catname&nbsp;&nbsp;= SStr&quot;, dbOpenDynaset)<br>I then get an error no 3061 : Too few parameters : expected 1<br>Can someone please help me to get the correct syntax <p>PK Odendaal<br><a href=mailto: pko@mweb.co.za> pko@mweb.co.za</a><br><a href= > </a><br>

 

[steveblum]

The text of the error message is a little perplexing, but the problem with your syntax is that the variable SStr is inside the quotes.&nbsp;&nbsp;That means the NAME of the variable is being sent to the database, not the VALUE.&nbsp;&nbsp;Change it to:<br><br>Set rsCatItems = dbWorkshop.OpenRecordset(&quot;Select * from&nbsp;&nbsp;jobs where catname&nbsp;&nbsp;= '&quot; & SStr & &quot;'&quot;, dbOpenDynaset)<br><br>This will pass the value of the variable, and put it in single quotes, which is what any database requires for string values.<br><br>A tip on debugging SQL problems:<br><br>1. Build your sql into a string (say sSQL), and then use the string in the OpenRecordset command.<br>2. Put in a breakpoint where the sql string is built, and copy it's value from the immediate window (?sSQL).<br>3. Paste the sql into you database query tool and run it.<br><br>This will give you an idea whether the string you built will work and if not what the actual database error message is.

 

[NoHandle]

Is CatName alphanumeric? If it is then you should provide quotes around your SStr as follows:<br>&quot;Select * from&nbsp;&nbsp;jobs where catname&nbsp;&nbsp;= '&quot; & SStr & &quot;'&quot;<br>Note that the variable SStr needs to be appended to the string rather than contained within it, otherwise your sql is always only going to find jobs with a catname of 'SStr' (if there are any!)

 

[peekay]

Steve<br>Thanks for help. I have pasted your query into my program, but get the same error. Have you not put in the single and double quotation marks in the incorrect order. I you can spare me another hint please.<br> <p>PK Odendaal<br><a href=mailto: pko@mweb.co.za> pko@mweb.co.za</a><br><a href= > </a><br>

 

[peekay]

NoHandle<br>Thanks to you as well. I get the same error after pasting your query too. CatName is an alphanumeric variable. <p>PK Odendaal<br><a href=mailto: pko@mweb.co.za> pko@mweb.co.za</a><br><a href= > </a><br>

 

[NoHandle]

I would try Steveblum's 'A tip on debugging SQL problems'; I program against Access databases and often find I need to get Access to help me out when debugging my SQL strings.

 

[Amiel]

try this<br><br>...against jet Databases use.<br><br>Select * from&nbsp;&nbsp;jobs where catname&nbsp;&nbsp;= &quot;&quot;&quot; & SStr & &quot;&quot;&quot;&quot;, dbOpenRecordset <br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>