using my.settings in sql

nkotbox

Active member
Joined
May 28, 2011
Messages
31
Programming Experience
Beginner
I am trying to include my.settings.dept in my sql like:

VB.NET:
SELECT        CustID, CustType, Address1FROM            Profile
WHERE        (UCASE(CustType) = "' & my.settings.dept & '")
ORDER BY SiteName

This will not work. Is there a correct way to perform this using my.settings?
I copied this code from the tableadapter configuration wizard.
 

Attachments

  • Untitled.png
    Untitled.png
    20.9 KB · Views: 29
SQL code is executed on the database and the database knows nothing about My.Settings. This has really got nothing to do with My.Settings. How do you usually create a SQL statement with a variable that gets set later on? You do so using a parameter. Just like a VB method, you write your SQL code with a parameter name in the place(s) where you want to use that value at run time and then, when you execute the code, you provide a value for the parameter. If that value comes from My.Settings then so be it but that is irrelevant to writing the code that will use the value.

So, insert a parameter in your SQL code. For Access you must use a ? symbol for that, i.e.
VB.NET:
WHERE        (UCASE(CustType) = ?)
The wizard will then generate methods to execute that query, just as the Fill and GetData methods execute the default query. You can then call one of those methods at run time and pass you value as an argument.

By the way, your UCASE is pointless because text comparisons in that context are case-insensitive anyway.
 
Back
Top