Dynamic SQL Query

[whythetorment]

Hi There,

I need to create a dynamic query based on input given by the user. I have a few IF statements in my vb app, and thats how the query gets build e.g.

Code: Select all

If txtInsurer.Text  "" Then
   Sel = Sel & Cnd & "{Insured.InsurerId} = " & txtInsurer.Text
   Cnd = " And "
End If

If TxtRepairer.Text  "" Then
   Sel = Sel & Cnd & "{Claim.RepairId} = " & TxtRepairer.Text
   Cnd = " And "
End If

I read somewhere on the forum that I need to do something like this in my code when generating the report, but it doesn't seem to filter the information.
Report.Dictionary.Variables.Add("Insured.InsurerId", GetType(Integer))
Report.Dictionary.Variables("Insured.InsurerId").Value = txtInsurer.Text

Even if I enter a insurer id, and i have 20 insurers in my table, it still brings up everything for all insurers instead of for just the one insurer I entered in the textbox.

Any help is appreciated.

[Jan]

Hello,

Sorry don't fully understand your requirements. If you need change sql query you can use variable in your query. For example:

Code: Select all

select * from customer where {myvariable}

myvariable is string variable. You need assign to this variable value, something like this:

Code: Select all

report.Compile();
report["myvariable"] = "customerid = 123";
report.Render();

Thank you.

[whythetorment]

Hi,

Yes that's what I ended up doing.

Had my query like

Code: Select all

SELECT * FROM Claim WHERE Number > 0
{Query}

And in each of my IF Statements, if i wanted to add an additional clause, I said something like

Code: Select all

IF txtLName.text  "" then
      sQuery = " AND LName = '" & txtLName.text & "'"
END IF

And then rendered it like

Code: Select all

Report.Item("Query") = sQuery2

Then it would just append my sql query every time.

Thanks.