• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

Passing Variables into SQL????

The following codes works fine when I set the parameters of the WHERE clus, but if I try to pass a varible collected from within the previous code it goes wrong.


cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=c:\MyDatabase\MyDatabase.mdb"
 
rs.CursorType = adOpenStatic
rs.Open "Select * From tblnewsletter Where [Newsletter] = np", cn

How do I correctly pass a variable into SQL and why is my way wrong?????
0
mercia
Asked:
mercia
1 Solution
 
HossyCommented:
can you show me the example where you try to pass a variable and it doesn't work?
0
 
merciaAuthor Commented:
The code I have sent shows that np is a boolean varible set to false ealier in the code, if I replace np with the word false all works fine I have changed the brackets slightly from WHERE (((tblNewsletter.NewsletterPlus)=False)) this works fine.  Any clues?
0
 
singasCommented:
I assume that np is a variable name and not the value of Newsletter that you are looking for. If this is the case then what you need to do is build the Select statement using the contents of np

e.g.

rs.Open "Select * From tblnewsletter Where [Newsletter] = '" & np & "'"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
singasCommented:
I assume that np is a variable name and not the value of Newsletter that you are looking for. If this is the case then what you need to do is build the Select statement using the contents of np

e.g.

rs.Open "Select * From tblnewsletter Where [Newsletter] = '" & np & "'"
0
 
PaulS_IIICommented:
If you want to pass the value of a variable to your SQL for it to act on it, try this.

rs.Open "SELECT * FROM tblnewslatter WHERE [Newsletter] = '" & <<Variable Name>> & "'"

By including the name of the variable within the SQL string it is interpreted as a string literal, so your SQL is really looking for the variable name as a value in the field. You have to concatenate the variable to the SQL to pass the value of to the query.

I hope that helps
0
 
merciaAuthor Commented:
Thanks for your helps works fine now.
0
 
JH0401Commented:
Hello,

Try one of the following statements ...

If np is a string:

rs.Open "Select * From tblnewsletter Where [Newsletter] = '" & np & "'"

If np is a number:
rs.Open "Select * From tblnewsletter Where [Newsletter] = " & np

Good luck!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now