how do i send date/time to @fromdate parameter in SSRS

Question

Hello Experts,

I want to know how to pass values from a date picker control to a ssrs parameter named @fromdate of type date/time

rdlPreview1.MyReportPath = "GrnRegister.RDL";
rdlPreview1.myConnectString =Appvaribales.ConnectionString;
System.Collections.ArrayList arlNames = new System.Collections.ArrayList();
System.Collections.ArrayList arlValues = new System.Collections.ArrayList();
doc_gls = doc_gls.Remove(doc_gls.Length-1);
arlNames.Add("plantid");
arlNames.Add("doc_gl");
arlNames.Add("fromdate");
arlNames.Add("todate");
arlValues.Add(ErpMdi.ID);
arlValues.Add(doc_gls);
arlValues.Add(dtpFrom.Value);
arlValues.Add(dtpto.Value);   
this.rdlPreview1.RdlParameterNames = arlNames;
this.rdlPreview1.RdlParameterValuse = arlValues;
rdlPreview1.exeReport();

i am getting error 

the conversion of nvarchar datatype to a date/time data type resulted in out of range value

what is the correct way to send values to date/time ssrs parameters 

Answer 1

Try formatting the dtpFrom.Value in "yyyy/MM/dd HH:mm:ss" format so that it is unambiguous.

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 2

Thanks Jones,

Actually i forgot to tell that i am using RDL reports to run in  winform application.

my friend Arjun Bagojikop modified  http://gotreportviewer.com/RDLViewer  sample and made a dll which we are using to run RDL  reports as local reports 

in rdlviewer solution  of http://gotreportviewer.com 

if some one modifies okButton_Click of  ParameterDialog

 private void okButton_Click(object sender, EventArgs e)
        {
            DataGridViewElementStates states = DataGridViewElementStates.Visible;
            int count = this.dataGridView1.Rows.GetRowCount(states);
            ReportParameter[] parameters = new ReportParameter[count];
            for (int i = 0; i < count; i++)
            {
                parameters[i] = new ReportParameter();
                ReportParameterInfo pi = (ReportParameterInfo)dataGridView1.Rows[i].Tag;
                parameters[i].Name = pi.Name;
                Object val = dataGridView1.Rows[i].Cells[2].Value;
                if (val != null)
                {
                    if (dataGridView1.Rows[i].Cells[1].Value.ToString() == "DateTime")
                    {
                        string newDate = Convert.ToDateTime(DateTime.ParseExact(val.ToString(), "yyyyMMdd", CultureInfo.InvariantCulture).ToString()).ToString("yyyy/MM/dd");
                        parameters[i].Values.Add(newDate);
                    }
                    else
                        parameters[i].Values.Add(val.ToString());
                }
            }
            this.reportViewer.LocalReport.SetParameters(parameters);
        }

and in reportinfo.cs replace the method getdata 

public DataTable GetData(string dataSetName, ReportParameterInfoCollection reportParams)
        {            
            DataSetInfo dataSetInfo = m_dataSetDictionary[dataSetName];
            if (dataSetInfo.dataTable == null)
            {
                DataSourceInfo dataSourceInfo = m_dataSourceDictionary[dataSetInfo.dataSourceName];
                if (dataSourceInfo.connection == null)
                {
                    ConnectionDialog dialog =
                        new ConnectionDialog(dataSetInfo.dataSourceName, dataSourceInfo.connectString);
                    if (dialog.ShowDialog() == DialogResult.Cancel)
                    {
                        return null;
                    }
                    else
                    {
                        dataSourceInfo.connection = dialog.connection;
                    }
                }
                SqlCommand command = dataSourceInfo.connection.CreateCommand();
                command.CommandText = dataSetInfo.query;
                string reportParameterValue =null;
                if (dataSetInfo.queryParameters != null)
                {
                    foreach (QueryParameterInfo queryParameterInfo in dataSetInfo.queryParameters)
                    {
                        string valueExpression = queryParameterInfo.valueExpression;
                        const string starting = "=Parameters!";
                        const string ending = ".Value";
                        if (!valueExpression.StartsWith(starting) ||
                            !valueExpression.EndsWith(ending))
                        {
                            throw new Exception("Can't parse query parameter expression: " + valueExpression);
                        }
                        int parameterNameLen = valueExpression.Length - starting.Length - ending.Length;
                        string parameterName = valueExpression.Substring(starting.Length, parameterNameLen);
                        if (getParamType(parameterName) == "DateTime")
                            reportParameterValue = Convert.ToDateTime(reportParams[parameterName].Values[0]).ToString("yyyyMMdd") ;
                        else
                            reportParameterValue = reportParams[parameterName].Values[0];

                        if (reportParameterValue == null)
                            throw new Exception("Report parameter " + parameterName + " has no value set");

                        command.Parameters.AddWithValue(queryParameterInfo.parameterName, reportParameterValue);
                    }
                }

                SqlDataReader reader = command.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(reader);
                reader.Close();

                dataSetInfo.dataTable = dataTable;
            }
            return dataSetInfo.dataTable;
        }

        private string getParamType(string parameterName)
        {
            string type = null;
            foreach (ReportParameterInfo repParameter in MainForm.reportParamInfoCollection)
            {
                if (repParameter.Name == parameterName)
                {
                    type = repParameter.DataType.ToString();
                    break;
                }
            }
           return type;
        }

the problem of sending date type parameters will vanish.