How to use SQL Query Parameter?

xwolkyx

The sended query "select * from fason where firma in ("EUROTEX", "GAPSAN", "RNC")" with (") doubles can you change code to (').

AlexTZ

You can change the report script code by yourself.

xwolkyx

I tryed but no succes can you tell me what must i do.

private void btnOk_Click(object sender, EventArgs e)
{
DataSelector1.FilterData();
TableDataSource table = Report.GetDataSource("Table1") as TableDataSource;
table.Init();

// selectedItems will contain a list of selected items, like "Item1", "Item2", "Item3"
string selectedItems = "";
table.First();

while (table.HasMoreRows)
{
selectedItems += "\"" + table["firma"].ToString() + "\", ";
table.Next();
}
if (selectedItems.EndsWith(", "))
selectedItems = selectedItems.Remove(selectedItems.Length - 2);

// now change the sql text of the Table datasource
table = Report.GetDataSource("Table") as TableDataSource;
table.SelectCommand = "select * from fason where firma in (" + selectedItems + ")";

AlexTZ

If you need single quotes, change the following code line:

selectedItems += "'" + table["firma"].ToString() + "', ";

xwolkyx

Yes now ist working Tahnk you for help AlexTZ.

xwolkyx

Can you teach me how can i pass DateTimepicker picked date to sql query i tryed changing your code but dont work.

AlexTZ

You don't need to use any code with DateTimePicker. Write in your sql:

select * from table where field = @param1

Set up parameter:
Name = param1
DataType = DateTime
Expression = DateTimePicker1.Value
DefaultValue = 1/1/2000

xwolkyx

But i have a coded report for dynamic sql query i have one paramater to Depatmant i wont to add one more to time.

AlexTZ

Something like
table.SelectCommand = "select * from table where field = '" + DateTimePicker1.Value.ToString("MM/dd/yyyy") + "'";

xwolkyx

Ok its working but selected date going to query with single quote on the last of the word " 02/02/2010' " how can i convert to " '02/02/2010' "

AlexTZ

It works well for me, all quotes are correct.

xwolkyx

I solved quotes problem but now when i select today from datepicker going to sql '06.55.2010' like this.

xwolkyx

and i am selecting 03.03.2010 nothing changing still same date '06.55.2010'

AlexTZ

Sorry I can't reproduce this.
Try to write the following code in your OK button handler:

MessageBox.Show(DateTimePicker1.Value.ToString("MM/dd/yyyy"));

what do you see when clicking OK button?

xwolkyx

I see valid date 03.06.2010

AlexTZ

In this case, the error is somewhere in your code, when you form a dynamic sql text.