Insert date to database in c#

Question

I found lot of questions regarding this matter and I tried everything but couldn't succeed yet.

I need to pass date to my SQL Server database. my code is as follows:

public bool CreatePatient()
{
    IDbConnection connection = database.CreateOpenConnection();
    IDbTransaction transaction = database.BeginTransaction(connection);

    try
    {
        GenerateRegNo();

        SQL_STATEMENT = "INSERT INTO TblPatientRegistration(RegistrationNo, HospitalID, NIC, AdmitDate, BHTNo, WardNo) 
                         VALUES(@RegNo, @HospiatlNo, @NIC, @Admitdate, @BHTNo, @WardNo)";

        IDbCommand com = database.CreateCommandTransaction(SQL_STATEMENT, connection, transaction);

        com.Parameters.Add(database.CreateParameter("@RegNo", PatientRegistration.RegistrationNo));
        com.Parameters.Add(database.CreateParameter("@HospiatlNo", PatientRegistration.HospitalID));
        com.Parameters.Add(database.CreateParameter("@NIC", PatientRegistration.NIC));

        com.Parameters.Add(database.CreateParameter("@Admitdate", PatientRegistration.AdmitDate));
        com.Parameters.Add(database.CreateParameter("@BHTNo", PatientRegistration.BHTNo));
        com.Parameters.Add(database.CreateParameter("@WardNo", PatientRegistration.WardNo));

        if (com.ExecuteNonQuery() > 0)
        {
            string updStatement = "Update TblControl set RegNo=RegNo+1";
            IDbCommand com2 = database.CreateCommandTransaction(updStatement, connection, transaction);
            com2.ExecuteNonQuery();
            transaction.Commit();
        return true;
        }
        else
        {
            transaction.Rollback();
            return false;
        }
     }
     catch (Exception)
     {
        transaction.Rollback();
        return false;
     }
}

What I have tried:

As mentioned above Link

  com.Parameters.Add(database.CreateParameter("@Admitdate", PatientRegistration.AdmitDate.ToString("dd/MM/yyyy")));

and this link

com.Parameters.Add(database.CreateParameter("@Admitdate", "'"+PatientRegistration.AdmitDate+"'"));

then I tried with this link

 String UrDate = PatientRegistration.AdmitDate.ToString("dd/MM/yyyy");
 System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();
 dateInfo.ShortDatePattern = "dd/MM/yyyy";
 DateTime validDate = Convert.ToDateTime(UrDate, dateInfo);

 com.Parameters.Add(database.CreateParameter("@Admitdate", validDate));

These methods throw this exception :

Operand type clash: int is incompatible with date

Value I received PatientRegistration.AdmitDate from is : {2/25/2013 12:00:00 AM}

I tried with these all methods but couldn't able to save it. please help me to figure out the problem in here.

Thanks...

EDIT :

My database schema :

CREATE TABLE [dbo].[TblPatientRegistration](
    [RegistrationNo] [char](10) NOT NULL,
    [HospitalID] [char](10) NOT NULL,
    [NIC] [char](10) NOT NULL,
    [AdmitDate] [date] NOT NULL,
    [BHTNo] [varchar](10) NOT NULL,
    [WardNo] [varchar](10) NOT NULL,
    [ReleaseDate] [date] NOT NULL,
 CONSTRAINT [PK_TblPatientRegistration_1] PRIMARY KEY CLUSTERED 
(
    [RegistrationNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

This question may already have an answer here (close vote): -->i tried with the answer that have suggested in there (see option 2), but it seems not working with my problem

Answer: after several investigation i found this problem occurs due to my mistake in database.

i have not passed value to ReleaseDate field and it set to default value in database. that default value is the one says incompatible with date. So that is the cause for this problem. any how I'm able to pass my value as mentioned by Ashok kumar

@SimonWhitehead- i tried AdmitDate as Date and DateTime both types. but it give same exception — DevT, Feb 25 '13 at 05:13
possible duplicate of [Operand type clash: int is incompatible with date/The INSERT statement conflicted with the FOREIGN KEY constraint](http://stackoverflow.com/questions/7176596/operand-type-clash-int-is-incompatible-with-date-the-insert-statement-conflicte) — mbeckish, Feb 25 '13 at 05:32
Please provide your class for your `database` object. I think most people are assuming you're using the standard ADO framework method named CreateParameter, but I can see that it is not. — Sybeus, Feb 25 '13 at 05:47
@Sybeus i tried to introduce transaction lock in my code. do you think that can be the cause for this problem? — DevT, Feb 25 '13 at 05:57

score 2 · Accepted Answer · edited Feb 25 '13 at 05:52

I have modified your code at @Admitdate line only.

public bool CreatePatient()
{
    IDbConnection connection = database.CreateOpenConnection();
    IDbTransaction transaction = database.BeginTransaction(connection);

    try
    {
    GenerateRegNo();

    SQL_STATEMENT = "INSERT INTO TblPatientRegistration(RegistrationNo, HospitalID, NIC, AdmitDate, BHTNo, WardNo) 
                     VALUES(@RegNo, @HospiatlNo, @NIC, @Admitdate, @BHTNo, @WardNo)";

    IDbCommand com = database.CreateCommandTransaction(SQL_STATEMENT, connection, transaction);

    com.Parameters.Add(database.CreateParameter("@RegNo", PatientRegistration.RegistrationNo));
    com.Parameters.Add(database.CreateParameter("@HospiatlNo", PatientRegistration.HospitalID));
    com.Parameters.Add(database.CreateParameter("@NIC", PatientRegistration.NIC));

        String admitDate = txtAdmitDate.Text;
        DateTime parsedAdmitDate;
        if (DateTime.TryParseExact(admitDate, "d/M/y", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedAdmitDate))
            PatientRegistration.AdmitDate = parsedAdmitDate;

    //com.Parameters.Add(database.CreateParameter("@Admitdate", PatientRegistration.AdmitDate));
    com.Parameters.Add(database.CreateParameter("@BHTNo", PatientRegistration.BHTNo));
    com.Parameters.Add(database.CreateParameter("@WardNo", PatientRegistration.WardNo));

    if (com.ExecuteNonQuery() > 0)
    {
        string updStatement = "Update TblControl set RegNo=RegNo+1";
        IDbCommand com2 = database.CreateCommandTransaction(updStatement, connection, transaction);
        com2.ExecuteNonQuery();
        transaction.Commit();
    return true;
    }
    else
    {
        transaction.Rollback();
        return false;
    }
 }
 catch (Exception)
 {
    transaction.Rollback();
    return false;
 }
}

Please let me know if you need more help.

even though it still gives me same error. i tried to introduce transaction lock in my customize database class code. do you think that can be the cause for this problem? pdated my question with my database class coding — DevT, Feb 25 '13 at 05:59
I understood from your code and message that you are facing the problem with datatype mismatch. (Am I right?) As far my knowledge concernsed I won't think that the introduction of transaction lock on customized database class causes this problem, but I am not sure. Is it possible for you to check by removing trasaction lock on your customize database and try again? — Ashok kumar, Feb 25 '13 at 06:07
i have another class called person and in there also have date field called DOB. so i introduce my transaction lock to that class. :) it works properly on there. but not in here. :( — DevT, Feb 25 '13 at 06:25
thanx ur help. this is happen due to my stupid work. i have not passed value to ReleaseDate field and it set to default value. so that is the cause for this problem — DevT, Feb 25 '13 at 07:07

score 1 · Answer 2 · answered Feb 25 '13 at 05:12

1

The data type of your Database table column is Date, can you try DateTime or a datetime2.

And dont enclose the Date variable in quotes:

com.Parameters.Add(database.CreateParameter("@Admitdate",PatientRegistration.AdmitDate))

answered Feb 25 '13 at 05:12

Jeremy Thompson

61,933
36
195
321

i tried with DateTime previously and it also did not allow me to save. so thats why i changged to date - [AdmitDate] [date] NOT NULL, – DevT Feb 25 '13 at 05:14
I found the same error here: http://stackoverflow.com/a/7176687/495455, I cant be sure but am quietly confident that answer is the solution. – Jeremy Thompson Feb 25 '13 at 05:17
yah from there i got that solution. but i didn't work for me. i'll change my db and try it. – DevT Feb 25 '13 at 05:18
i tried that one and even though it throws same exception - `Operand type clash: int is incompatible with date` – DevT Feb 25 '13 at 05:22

score 1 · Answer 3 · answered Feb 25 '13 at 05:55

1

command.Parameters.AddWithValue("@yourDateTimeVariable", dateTimeObject); ????

answered Feb 25 '13 at 05:55

Aniket Inge

25,375
5
50
78

tried not worked ... i tried to introduce transaction lock in my customize database class code. do you think that can be the cause for this problem? updated my question with my database class coding – DevT Feb 25 '13 at 06:00

score 0 · Answer 4 · edited Nov 26 '13 at 15:21

0

cmd.Parameters.AddWithValue("@DATE", DateTime.Now);

edited Nov 26 '13 at 15:21

Bucket

7,415
9
35
45

answered Nov 26 '13 at 15:03

Beep

2,737
7
36
85

Insert date to database in c#

4 Answers4