Error while inserting the date data

Question

Hi all, i am inserting patient details to the database. but when i am submiting the details getting this error "Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query." Here is all my coding: PatientProperty.cs

public class PatientProperty
{   
    private string Pdisease;
    private string Pname;
    private string Pcategory;
    private string Paddr;
    private DateTime Dateofjoining;
    private int Page;
public PatientProperty(string PDisease, string PName, string PCategory, string PAddr, DateTime DateOfJoining, int PAge)
    {       
        this.Pdisease = PDisease;
        this.Pname = PName;
        this.Pcategory = PCategory;
        this.Paddr = PAddr;
        this.Dateofjoining = DateOfJoining;
        this.Page = PAge;
    }   
    public string PDISEASE
    {
        get
        {
            return Pdisease;
        }
        set
        {
            Pdisease=value;
        }
    }
    public string PNAME
    {
        get
        {
            return Pname;
        }
        set
        {
            Pname = value;
        }
    }
    public string PCATEGORY
    {
        get
        {
            return Pcategory;
        }
        set
        {
            Pcategory = value;
        }
    }
    public string PADDRESS
    {
        get
        {
            return Paddr;
        }
        set
        {
            Paddr = value;
        }
    }
    public DateTime DATEOFJOINING
    {
        get
        {
            return Dateofjoining;
        }
        set
        {
            Dateofjoining = value;
        }   
    }
    public int PAGE
    {
        get
        {
            return Page;
        }
        set
        {
            Page = value;
        }
    }
}
PatientRegistration.cs
public class PatientRegistration
{
    string str = ConfigurationManager.ConnectionStrings["HealthCare"].ConnectionString.ToString();    
    public void InsertPatient(PatientProperty obj)
    {
        using (var con = new SqlConnection(str))
        {
            using (var com = new SqlCommand("PatientRegister", con))
            {
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddWithValue("Pdisease", obj.PDISEASE);
                com.Parameters.AddWithValue("Pname", obj.PNAME);
                com.Parameters.AddWithValue("Pcategory", obj.PCATEGORY);
                com.Parameters.AddWithValue("Paddr", obj.PADDRESS);
                com.Parameters.AddWithValue("Dateofjoining", obj.DATEOFJOINING);
                com.Parameters.AddWithValue("Page", obj.PAGE);
                con.Open();
                com.ExecuteNonQuery();
                con.Close();          
            }
        }
    }
}
PatientRegistrationBussiness.cs
public class PatientRegistrationBussiness
{
    public void AddPatient(PatientProperty obj)
    {
        PatientRegistration PR = new PatientRegistration();
        PR.InsertPatient(obj);    
    }   
}
protected void Button1_Click(object sender, System.EventArgs e)
     {
            //int id = Convert.ToInt32(TextBox1.Text);
            string name = TextBox2.Text;
            string address = TextBox3.Text;
            string category = RadioButtonList1.Text;
            int age =Convert.ToInt32(TextBox4.Text);
            string disease = TextBox5.Text;
            DateTime date =Convert.ToDateTime(TextBox6.Text);           
      try
            {
            PatientRegistrationBussiness obj = new PatientRegistrationBussiness();
            PatientProperty PP = new PatientProperty(disease, name, category, address, date, age);           
            obj.AddPatient(PP);           
                Response.Write("Patient details have been successfully added");
                TextBox2.Text = string.Empty;
                TextBox3.Text = string.Empty;
                TextBox4.Text = string.Empty;
                TextBox5.Text = string.Empty;
                TextBox6.Text = string.Empty;
                RadioButtonList1.SelectedIndex = 0;
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
            finally
            {
                obj = null;
            }    
}

In database date field taken as DateTime. Pls somebody modify it.

Thanks, Sumit

Answer 1

Looks like your database is expecting an int but you are providing a DateTime. Why are you doing that?

The simplest thing to do, assuming you are developing this application is to use a Date data type in the DB.

There are several to choose from depending on your need and the version of SQL Server you are using. Have a look at the Date and time data types listed here- http://msdn.microsoft.com/en-us/library/ms187752.aspx.

If you are using SQL Server 2008, you might consider using some of the new Date data types.

Answer 2

As Richard pointed out it could be because the date field column is defined as int in table. In your question you mention that its correctly defined as DateTime.

I guess then the problem is in the Stored Procedure where it is trying to assign Date to some int variable or something like that.

Answer 3

I agreed with Richard and Brushn. Also, to prevent such errors I think it is better to use the method Add of SqlCommand.Parameters to impicit set corresponding DbType value of the parameter, for example:

            SqlParameter param = com.Parameters.Add("@Dateofjoining", SqlDbType.DateTime);
            param.Value = obj.DATEOFJOINING;

and so on for each parameter of command.