[blue_aro]

Hello,

I've got the following code:

sql = "UPDATE TaskWorkHistory set MinutesWorked = MinutesWorked + @MinutesWorked " +
"WHERE DateWorked = @DateWorked and [email protected] and [email protected]";

...code here

OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.Parameters.AddWithValue("@TaskID", (int)r.Cells["ID"].Value);
                    cmd.Parameters.AddWithValue("@MinutesWorked", double.Parse(r.Cells["Minutes_Worked"].Value.ToString()));
                    cmd.Parameters.AddWithValue("@DateWorked", now.ToShortDateString());
                    cmd.Parameters.AddWithValue("@UserID", myRefs.GetUserID(r.Cells["Owner"].Value.ToString()));

It only works if I only use the TaskID condition.

To debug it, I also removed the parameters and entered the actual values and it worked.

I guess it has something to do with how it resolves the date but I cant figure out what would be wrong with using the UserID.

I'm using MS Access.

anyone know why the date and the userId would not work pls?

thanks

[FlashM]

What are you database field data types?
Do you get any exception thrown?

[blue_aro]

FlashM, on 19 March 2010 - 03:04 AM, said:

What are you database field data types?
Do you get any exception thrown?

Database field type for DateWorked is Date/Time and no I dont get any exception. I guess that the where condtion is not returning any matches but it should since I have one instance in the table where the date is 18/03/2010 and what I'm passing in the variable is the same.

[EtherealMonkey]

blue_aro, on 19 March 2010 - 03:35 AM, said:

... the date is 18/03/2010

I am waaayyyy better with MySQL than MSSQL or Access, but - I am pretty sure that ToShortDateString would return 03/10/2010 (or maybe 3/10/2010) rather than 18/03/2010.

[blue_aro]

EtherealMonkey, on 19 March 2010 - 03:56 AM, said:

blue_aro, on 19 March 2010 - 03:35 AM, said:

... the date is 18/03/2010

I am waaayyyy better with MySQL than MSSQL or Access, but - I am pretty sure that ToShortDateString would return 03/10/2010 (or maybe 3/10/2010) rather than 18/03/2010.

I did think about that so I stepped through the code and added a watch on the field and it is returning it as '18/03/2010'.

I also tried to use enclose it in # but that didnt work.

I tried to run the following SQL in Access

UPDATE TaskWorkHistory set MinutesWorked =1333.22
WHERE DateWorked = '18/03/2010';

and got data mismatch error

so I tried

UPDATE TaskWorkHistory set MinutesWorked =1333.22
WHERE DateWorked = datevalue('18/03/2010');

this worked in Access but when I tried it in c# eg.

UPDATE TaskWorkHistory set MinutesWorked =1333.22
WHERE DateWorked = datevalue(@DateWorked);

i get a data type mismatch!

[FlashM]

I believe your problem is because today it is the 19th March 2010 and not 18th...

This solution works perfectly with me:

using System;
using System.Data.OleDb;

namespace DatabaseApp
{
    class Program
    {
        static void Main(string[] args)
        {
            InitCustomComponents();
            DoSomeStuff();
        }

        static void InitCustomComponents()
        {
            m_conn = new OleDbConnection
                (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\testdb.mdb;User Id=admin;Password=;");
        }

        static void DoSomeStuff()
        {
            string query = "UPDATE TaskWorkHistory SET MinutesWorked = MinutesWorked + @MinutesWorked " +
                           "WHERE DateWorked = @DateWorked and [email protected] and [email protected]";

            using (OleDbCommand cmd = new OleDbCommand(query, m_conn))
            {
                m_conn.Open();

                cmd.Parameters.AddWithValue("MinutesWorked", 25);
                cmd.Parameters.AddWithValue("DateWorked", DateTime.Now.ToShortDateString());
                cmd.Parameters.AddWithValue("UserID", 1);
                cmd.Parameters.AddWithValue("TaskID", 1);

                cmd.ExecuteNonQuery();

                m_conn.Close();
            }
        }

        private static OleDbConnection m_conn = null;
    }
}

[EtherealMonkey]

blue_aro, on 19 March 2010 - 04:10 AM, said:

i get a data type mismatch!

Well, I think you are on the right path (stepping through the debugger).

I'm sorry that I can't be of more help here. I have not installed MS Office or Access on this machine.

I had an issue with MySQL once like this using their .Net adapter but didn't have the same error using ODBC IIRC.

Other than that... Good Luck?

[blue_aro]

FlashM, on 19 March 2010 - 04:41 AM, said:

I believe your problem is because today it is the 19th March 2010 and not 18th...

This solution works perfectly with me:

using System;
using System.Data.OleDb;

namespace DatabaseApp
{
    class Program
    {
        static void Main(string[] args)
        {
            InitCustomComponents();
            DoSomeStuff();
        }

        static void InitCustomComponents()
        {
            m_conn = new OleDbConnection
                (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\testdb.mdb;User Id=admin;Password=;");
        }

        static void DoSomeStuff()
        {
            string query = "UPDATE TaskWorkHistory SET MinutesWorked = MinutesWorked + @MinutesWorked " +
                           "WHERE DateWorked = @DateWorked and [email protected] and [email protected]";

            using (OleDbCommand cmd = new OleDbCommand(query, m_conn))
            {
                m_conn.Open();

                cmd.Parameters.AddWithValue("MinutesWorked", 25);
                cmd.Parameters.AddWithValue("DateWorked", DateTime.Now.ToShortDateString());
                cmd.Parameters.AddWithValue("UserID", 1);
                cmd.Parameters.AddWithValue("TaskID", 1);

                cmd.ExecuteNonQuery();

                m_conn.Close();
            }
        }

        private static OleDbConnection m_conn = null;
    }
}

thanks FlashM. The date thing was just a typo - it was using 19/03/2010.

The problem from comparing your code with mine was that I was using @ when adding my parameters! When I removed it, it worked!

I'm sure that was how I read it in a book but never mind.

Many thanks and the others for the help.

[blue_aro]

blue_aro, on 19 March 2010 - 05:05 AM, said:

FlashM, on 19 March 2010 - 04:41 AM, said:

I believe your problem is because today it is the 19th March 2010 and not 18th...

This solution works perfectly with me:

using System;
using System.Data.OleDb;

namespace DatabaseApp
{
    class Program
    {
        static void Main(string[] args)
        {
            InitCustomComponents();
            DoSomeStuff();
        }

        static void InitCustomComponents()
        {
            m_conn = new OleDbConnection
                (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\testdb.mdb;User Id=admin;Password=;");
        }

        static void DoSomeStuff()
        {
            string query = "UPDATE TaskWorkHistory SET MinutesWorked = MinutesWorked + @MinutesWorked " +
                           "WHERE DateWorked = @DateWorked and [email protected] and [email protected]";

            using (OleDbCommand cmd = new OleDbCommand(query, m_conn))
            {
                m_conn.Open();

                cmd.Parameters.AddWithValue("MinutesWorked", 25);
                cmd.Parameters.AddWithValue("DateWorked", DateTime.Now.ToShortDateString());
                cmd.Parameters.AddWithValue("UserID", 1);
                cmd.Parameters.AddWithValue("TaskID", 1);

                cmd.ExecuteNonQuery();

                m_conn.Close();
            }
        }

        private static OleDbConnection m_conn = null;
    }
}

thanks FlashM. The date thing was just a typo - it was using 19/03/2010.

The problem from comparing your code with mine was that I was using @ when adding my parameters! When I removed it, it worked!

I'm sure that was how I read it in a book but never mind.

Many thanks and the others for the help.

just a correction on the above. It was not after all the @ sign that was causing the problem - it was bugging me that that was the problem. I put them back and then realised that when I added the parameters, it wasnt in the same order as in the query so I re-arranged the order and hey it worked!

thanks again

[FlashM]

Order of parameters in you SQL query matters, but an order of your command parameters when you are adding them with value, does not matter.