date is not compare to database where combobo selected item

Question

I am creating a project that user have to select the date in Combobox but where I have select that date in Combobox that is displaying the error date

String was not recognized as a valid DateTime.' I am trying to fetch date from database in slq. Below is my code

            System.Data.DataTable dt;
       SqlConnection con=new SqlConnection(@"Data Source=krupal;Initial Catalog=LOANNOTICE;Integrated Security=True");
        con.Open();
        this.cmbmonth.FormatString = "yyyy/ MM/dd";
        // DateTime dat = Convert.ToDateTime(cmbmonth.SelectedItem.ToString("yyyy/MM/dd"));
        //var date = cmbmonth.Text;
       //var d1 = Convert.ToDateTime(cmbmonth.Text);
       // MessageBox.Show("" + d1);
        string Querie=("SELECT HCODE ,(select top 1  rtrim(Name) From RNSB_TEST.dbo.FC014076 p with(nolock) where  convert(varchar,PrdCd) = convert(varchar,HCODE)) as PrdName,count(*) as NoOfAcc,round(Sum(BAL2802),2) as PrintAmt ,round(SUM(IntAmt),2) as TotalInt,0 as TDSAmount, round(SUM(IntAmt),2) as NetIntAmt, round((Sum(BAL2802) + SUM(IntAmt)),2)as TotalAmt from LOANNOTICE.dbo.UNCLAIM_DEPO with(nolock) where EffeDate='"+Convert.ToDateTime(cmbmonth.SelectedText).ToString("yyyy/MM/dd") + "' group by HCODE union SELECT HCODE, (select top 1  rtrim(Name) From RNSB_TEST.dbo.FC014076 p with(nolock) where  convert(varchar, PrdCd) = convert(varchar, HCODE)) as PrdName,count(*) as NoOfAcc,round(Sum(MatAmt), 2) as PrintAmt ,round(SUM(IntAmt), 2) as TotalInt,round(sum(TDS_Amount), 2) as TDSAmount, round(SUM(Net_Int), 2) as NetIntAmt, round((Sum(MatAmt) + SUM(Net_Int)), 2) as TotalAmt from LOANNOTICE.dbo.UNCLAIM_DEPO_TDR with(nolock) where EffeDate = '"+Convert.ToDateTime(cmbmonth.SelectedText).ToString("yyyy/MM/dd") + "' group by HCODE");
        SqlCommand cmd = new SqlCommand(Querie, con);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        dt = new System.Data.DataTable();
        sda.Fill(dt);

Anyone have a solution please let me know fast. Thanks.

score 0 · Answer 1 · answered Jan 10 '20 at 10:59

0

You need to parse string into valid date time.

Your conversion was wrong, please convert string to date time and then pass to appropriate date fields.

please refer this link .

answered Jan 10 '20 at 10:59

Alpesh Vaghela

107
4

score 0 · Answer 2 · answered Jan 10 '20 at 12:11

It draws my attention that you have this blank space here:

this.cmbmonth.FormatString = "yyyy/ MM/dd"

Anyway, you should always check dates passed as string, and throw an exception if you can't cast them:

if (!DateTime.TryParse(_date, out DateTime date))
{
    throw new InvalidCastException("Not a valid date.");
}

And last, try to set you SqlCommand parameters this way, is more readable and you don't have to deal with string interpolation (checkout for:

using (DbConnection connection = new SqlConnection("MyConnectionString"))
{
    using (DbCommand command = new SqlCommand(connection))
    {
        command.CommandType = System.Data.CommandType.Text;
        command.CommandText = "SELECT * FROM MyTable WHERE Date = @Date";
        command.Parameters.Add("@Date", SqlDbType.Date);
        command.Parameters["@Date"].Value = date;
    }
}

Give it a try! :)

Regards!

date is not compare to database where combobo selected item

2 Answers2