How to update column between date

Question

1.00/5 (2 votes)

See more:

Hello

I need some help.
I want to update column between two dates.
Here is my code.

C#

public string UpdateValid(DateTime startDate, DateTime endDate)
 {
 OleDbConnection conn = new OleDbConnection("my own database setup");
 string status = null;
 
 conn.Open();
 OleDbCommand cmd = conn.CreateCommand();
 
 cmd.CommandText = "UPDATE Watchreceipt SET valid = @valid WHERE Dato BETWEEN startDate AND @endDate AND DriverIDFK = @DriverIDFK";
 
 cmd.Parameters.AddWithValue("@startDate", startDate);
 cmd.Parameters.AddWithValue("@endDate", endDate);
 cmd.Parameters.AddWithValue("@DriverIDFK", UserMapper.driverNumber);
 
 OleDbTransaction sqltrans = cmd.Connection.BeginTransaction();
 
 try
 { 
 OleDbCommand updateCmd = cmd.Connection.CreateCommand();
 updateCmd.CommandText = cmd.CommandText;
 updateCmd.Transaction = sqltrans;
 
 
 updateCmd.Parameters.Add("@valid", OleDbType.Boolean);
 updateCmd.Parameters["@valid"].Value = false;
 
 updateCmd.ExecuteNonQuery(); 
 sqltrans.Commit();
 
 
 status = "Ok";
 }
 catch (OleDbException odbe)
 {
 sqltrans.Rollback();
 System.Windows.Forms.MessageBox.Show("Rolled back\n" + odbe.Message);
 }
 catch (Exception ex)
 {
 System.Windows.Forms.MessageBox.Show("Systemfejl\n" + ex.Message);
 }
 finally
 {
 conn.Close();
 }
 
 return status;

 }

It says something like that, there is no value is specified for one or more parameters.

Please Help

Posted 27-Jul-12 2:15am

Sego Turan

Updated 27-Jul-12 2:16am

Prasad_Kulkarni

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sego Turan · Answer 1 · 2012-07-27T07:41:00

This is the solution.

C#

public string UpdateValid(DateTime startDate, DateTime endDate)
        {
            OleDbConnection conn = new OleDbConnection("my db path"));
            string status = null;
            
            string updateSql = "UPDATE Watchreceipt SET valid = false WHERE Dato BETWEEN @startDate AND @endDate AND DriverIDFK = @DriverIDFK";

            conn.Open();
            OleDbTransaction sqltrans = conn.BeginTransaction();
            OleDbCommand updateCmd = conn.CreateCommand();
            updateCmd.CommandText = updateSql;
            
            updateCmd.Parameters.AddWithValue("@startDate", startDate);
            updateCmd.Parameters.AddWithValue("@endDate", endDate);
            updateCmd.Parameters.AddWithValue("@DriverIDFK", UserMapper.driverNumber);

            updateCmd.Transaction = sqltrans;

            try
            {
                
                updateCmd.ExecuteNonQuery();
                sqltrans.Commit();
                
                status = "Ok";
            }
            catch (OleDbException odbe)
            {
                sqltrans.Rollback();
                System.Windows.Forms.MessageBox.Show("Rolled back\n" + odbe.Message);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Systemfejl\n" + ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return status;

        }

I changed the SQL statement and deleted some code inside the try block.
Thanks

OriginalGriff · Answer 2 · 2012-07-27T02:21:00

Solution 3

Add the '@' character to startDate

C#

cmd.CommandText = "UPDATE Watchreceipt SET valid = @valid WHERE Dato BETWEEN startDate AND @endDate AND DriverIDFK = @DriverIDFK";

Becomes

C#

cmd.CommandText = "UPDATE Watchreceipt SET valid = @valid WHERE Dato BETWEEN @startDate AND @endDate AND DriverIDFK = @DriverIDFK";

Posted 27-Jul-12 2:21am

OriginalGriff

Comments

Sego Turan 27-Jul-12 8:35am

Thanks for the comment.
I have add the character '@' to startDate but it give me the same problem.

Here is my code

public string UpdateValid(DateTime startDate, DateTime endDate)
{
OleDbConnection conn = new OleDbConnection("my own database setup");
string status = null;

conn.Open();
OleDbCommand cmd = conn.CreateCommand();

cmd.CommandText = "UPDATE Watchreceipt SET valid = @valid WHERE Dato BETWEEN @startDate AND @endDate AND DriverIDFK = @DriverIDFK";

cmd.Parameters.AddWithValue("@startDate", startDate);
cmd.Parameters.AddWithValue("@endDate", endDate);
cmd.Parameters.AddWithValue("@DriverIDFK", UserMapper.driverNumber);

OleDbTransaction sqltrans = cmd.Connection.BeginTransaction();

try
{
OleDbCommand updateCmd = cmd.Connection.CreateCommand();
updateCmd.CommandText = cmd.CommandText;
updateCmd.Transaction = sqltrans;

updateCmd.Parameters.Add("@valid", OleDbType.Boolean);
updateCmd.Parameters["@valid"].Value = false;

updateCmd.ExecuteNonQuery();
sqltrans.Commit();

status = "Ok";
}
catch (OleDbException odbe)
{
sqltrans.Rollback();
System.Windows.Forms.MessageBox.Show("Rolled back\n" + odbe.Message);
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Systemfejl\n" + ex.Message);
}
finally
{
conn.Close();
}

return status;

}

OriginalGriff 27-Jul-12 9:38am

Yes. That is what I would expect - you are creating a new command in the try block - use the one outside it instead, as the parameters do not "magically" transfer themselves! :laugh:
(I should have spotted that the first time, but I got caught up with teh more obvious missing '@' character).

Vani Kulkarni · Answer 3 · 2012-07-27T02:20:00

Solution 2

You have not mentioned the values for startDate and endDate. Also startDate should be accompanied by @

UPDATE Watchreceipt SET valid = @valid WHERE Dato BETWEEN startDate AND @endDate AND DriverIDFK = @DriverIDFK

Posted 27-Jul-12 2:20am

Vani Kulkarni

Comments

OriginalGriff 27-Jul-12 9:33am

Yes he has - see the Parameters.AddWithValue below that statement.

Santhosh Kumar Jayaraman · Answer 4 · 2012-07-27T02:19:00

Solution 1

Check this .. You used startdate instead of @startdate

SQL

cmd.CommandText = "UPDATE Watchreceipt SET valid = @valid WHERE Dato BETWEEN @startDate AND @endDate AND DriverIDFK = @DriverIDFK";

Posted 27-Jul-12 2:19am

Santhosh Kumar Jayaraman

Updated 27-Jul-12 2:20am

v2