How to Dynamically Create Queries from User Input

Oct 5, 2012 at 2:58 AM

Great Piece of Software.

I want my user to select the fields they want in the query from a form. Also to select the where conditions they want.

How can this be done with the single long continuous call?

I was hoping I could do something like:

   DBQuery sel = DBQuery.Select()
   if(comoBoxSelField.SelectedValue == "Department")
     sel.Field("D", Schools.Departments.Name.Name).As("dept_name")

But this does not seem to work.

 

 

Coordinator
Oct 5, 2012 at 7:21 PM

Don't forget to set the table too.

sel.From("Departments").As("D")

Oct 10, 2012 at 8:22 AM

Thanks but that doesn't work.

Here is the exact code I'm using

      	Dim DB As DBDatabase = Perceiveit.Data.DBDatabase.Create(DAL.a2ConnStr, "MySql.Data.MySqlClient")
        Dim SEL As Query.DBQuery = Query.DBQuery.Select()
        SEL.Select.Field("D""SurveyInstance")
        SEL.Select.Field("D""SurveyInstance")
        SEL.Select.From("tblsurveyresponse").As("D")
  
        LabelSQL.Text = SEL.ToSQLString(DB)

When I get to the last line I get the run time error

"No Fields were specified to be selected"

Coordinator
Oct 10, 2012 at 9:52 AM

Problem here is VB is allowing you to call the shared Select() factory method from an instance reference. That will return a new instance of a DBSelectQuery which is then having the field added to it, rather than the SEL instance. 

Try this

        Dim db As DBDatabase = DBDatabase.Create("MyConnection", "MySql.Data.MySqlClient")

        Dim sel As DBSelectQuery = DBQuery.Select()
        sel.Field("D", "SurveyInstance")
        sel.Field("D", "SurveyInstance")
        sel.From("tblsurveyresponse").As("D")

        'SELECT `D`.`SurveyInstance`, `D`.`SurveyInstance`
        '   FROM `tblsurveyresponse` AS `D`;

'Append where and grouping as required 
        Return sel.ToSQLString(db)

The sel variable is of type DBSelectQuery and you can call the Field and From methods directly.

There are corresponding DBInsertQuery, DBDeleteQuery, DBUpdateQuery etc classes if you want to use this mechanism for modifications too.