Search code examples
sqlvb.netdatasetjet

Jet Access Query not working wildcard not select

I am using a dataset sql query and trying to populate a reportviewer field however I am not having much luck. It keeps populating everything in the field. all of this stuff is new to me and so I do not even think I am approaching this the right way.

I am trying to select data from a wildcard that is represented by a string variable in vb.net. If I run it in query builder with Class_Schedule.(blah) in the where column it will work. however if I use the query builder and run it no matter what I put in the parameter it populates the entire field.

SELECT        MemberBase.ID, MemberBase.EntireName, Class_Schedule.MemberID
FROM            (MemberBase INNER JOIN
                         Class_Schedule ON MemberBase.ID = Class_Schedule.MemberID)
WHERE        (true = ?)

any help is appreciated and sorry for the poor explanation, I have googled and tried various things for 2 hours and I have gotten no where.

EDIT:

Here is ideally what I want but it doesnt work:

SELECT        MemberBase.ID, MemberBase.EntireName, Class_Schedule.MemberID
FROM            (MemberBase INNER JOIN
                     Class_Schedule ON MemberBase.ID = 
Class_Schedule.MemberID)
WHERE        (Class_Schedule.? = true)
Solution

  • The query parameter placeholder ? can only accept the values to be compared. If cannot contain column names. If you want to compare different columns, you must use string concatenation to build your query.

    sql = "SELECT ... WHERE [" & column_name & "] = true"

    or with the new string interpolation (where column_name is a VB string variable):

    sql = $"SELECT ... WHERE [{column_name}] = true"

    If you compare against values other than true, you should still use parameters for them for safety reasons. See: SQL injection.

    You cannot pass column names as parameters, but you could use a trick:

    SELECT ... WHERE (col1 = true OR 0 = ?) AND (col2 = true OR 0 = ?) AND (col3 = true OR 0 = ?)

    and then you pass the parameter 1 for the column you want to test and 0 for the others.