CODEWITHSUNDEEP
c#sql-server
Naveen
Naveen

Reputation: 93

Conversion failed when converting date and/or time from character string

 private void btnCheck_Click(object sender, EventArgs e)
        {
            string costring = connection();
            string MyQuery = "select expense from dbo.KmtAccounts where date between '"+ txtFromDate +"' and '" +txtToDate +"' and    registernumber='" + txtRegNo.Text + "'";
            SqlConnection conn = new SqlConnection(costring);
            SqlCommand cmd = new SqlCommand(MyQuery, conn);
            conn.Open();
            txtResult.Text = Convert.ToString(cmd.ExecuteScalar());
            conn.Close();
        }

Upvotes: 0

Views: 394

Answers (2)

Jon Hanna
Jon Hanna

Reputation: 113352

The issue causing the exception is that the value in txtFromDate and/or txtToDate isn't understood as representing a date.

The bigger issue is that there seems to be little or nothing to prevent one of those values containing a valid value followed by ';delete from dbo.KmtAccounts;---.

Both problems will be solved by making the query select expense from dbo.KmtAccounts where date between @from and @to and registernumber=@reg and then using parameters to add the dates (as dates, not as strings) and reg-number into the query.

Upvotes: 2

Pilgerstorfer Franz
Pilgerstorfer Franz

Reputation: 8359

There are several point to improve within your code.

  1. It seems that txtFromDate and txtToDate are controls?! I think you'd like to access txtFromDate.Text
  2. You should really make use of SqlParameters to prevent SQL injection (good example at dotnetperls.com)
  3. You may check if your UI values are correct with some kind of validation or even better, choose a control that only allows valid values (DateTimePicker, Calendar, ...)

Upvotes: 3

Related Questions