How to use SQL Query Parameter?

Comments

• xwolkyx

  March 2010 edited 7:27PM

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

  March 2010 edited 7:27PM

  You can change the report script code by yourself.
• xwolkyx

  March 2010 edited 7:27PM

  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

  March 2010 edited 7:27PM

  If you need single quotes, change the following code line:
  
  selectedItems += "'" + table["firma"].ToString() + "', ";
  
• xwolkyx

  March 2010 edited 7:27PM

  Yes now ist working Tahnk you for help AlexTZ.
• xwolkyx

  March 2010 edited 7:27PM

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

  March 2010 edited 7:27PM

  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

  March 2010 edited 7:27PM

  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

  March 2010 edited 7:27PM

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

  March 2010 edited 7:27PM

  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

  March 2010 edited 7:27PM

  It works well for me, all quotes are correct.
• xwolkyx

  March 2010 edited 7:27PM

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

  March 2010 edited 7:27PM

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

  March 2010 edited 7:27PM

  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

  March 2010 edited 7:27PM

  I see valid date 03.06.2010
• AlexTZ

  March 2010 edited 7:27PM

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