?
Solved

Passing Variables into SQL????

Posted on 2003-03-04
7
Medium Priority
?
138 Views
Last Modified: 2010-05-01
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
Comment
Question by:mercia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Expert Comment

by:Hossy
ID: 8063821
can you show me the example where you try to pass a variable and it doesn't work?
0
 

Author Comment

by:mercia
ID: 8063872
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
 

Expert Comment

by:singas
ID: 8063878
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
Technology Partners: 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!

 

Accepted Solution

by:
singas earned 800 total points
ID: 8063885
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
 
LVL 2

Expert Comment

by:PaulS_III
ID: 8063892
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
 

Author Comment

by:mercia
ID: 8063898
Thanks for your helps works fine now.
0
 
LVL 1

Expert Comment

by:JH0401
ID: 8063951
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month10 days, 7 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question