A Data Insertion Difficulty

Question

0.00/5 (No votes)

See more:

I am designing a client/server application where users connect to a database on the server computer. The point is that the application allows the setting of different user levels and based on the user's level, the date of the user or client computer should be used for the insertion otherwise the date on the server machine should be used for the insertion.
I am stuck with how to code the part that does the insertion to cater for this.

Example:
Assuming I have a table [table1] with the columns Amount and DatePaid. This is the code I used:

C#

query = "INSERT INTO table1" +
        "(Amount , DatePaid) " +
        "VALUES " +
        "(@Amount , @DatePaid)";

        // Declares CmdString as an object of the SqlCommand object
        // Assuming that con is a valid mysql connections string
        MySqlCommand CmdString = new MySqlCommand(query, con);

        try
        {
            // Specifies the query stored in the CmdString variable as the query  to be executed. Assuming MySqlDataAdap is validly declared and initialized.
            MySqlDataAdap.InsertCommand = CmdString;

            // Associates the named parameter variables with the fields in the table
            // Assuming amt and payDate are valid variables
            MySqlDataAdap.InsertCommand.Parameters.Add(new MySqlParameter("@Amount", amt));
            MySqlDataAdap.InsertCommand.Parameters[0].Value = amt;

            MySqlDataAdap.InsertCommand.Parameters.Add(new MySqlParameter("@DatePaid", payDate));
            MySqlDataAdap.InsertCommand.Parameters[1].Value = 
                (getUserLevel() == "Lower Level" ? "SYSDATE()" : 
                depositDate.ToString());
            //Where SYSDATE() is a MySql function for returning the current server's date and assuming getUserLevel() is a valid function for retrieving the current user level

            // Executes the query
            rowsAffected = MySqlDataAdap.InsertCommand.ExecuteNonQuery();
        }
        catch (MySqlException MyException)
        {
            MessageBox.Show("Message: " + MyException.Message + "\n" +
                            "Source: " + MyException.Source + "\n" +
                            "Number: " + MyException.Number.ToString(),
                            "Inserting Pay",
                            MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

When I execute the above above I get the error message:
The string was not recognize as a valid DateTime. There is a unknown word starting at index 0.

Please folks help me to get round this puzzle. Thanks

Posted 24-Dec-12 0:29am

BBAzar

Updated 24-Dec-12 0:45am

v2

Add a Solution

2 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)

phil.o · Answer 1 · 2012-12-24T00:37:00

Solution 1

Since the DatePaid field is a Datetime one, you should use a DateTime when you assign the corresponding parameter :

C#

MySqlDataAdap.InsertCommand.Parameters[1].Value =
     (getUserLevel() == "Lower Level" ? SYSDATE() : depositDate);

Posted 24-Dec-12 0:37am

phil.o

Keith Barrow · Answer 2 · 2012-12-24T01:04:00

You've got redundant code:

C#

MySqlDataAdap.InsertCommand.Parameters.Add(new MySqlParameter("@Amount", amt));
MySqlDataAdap.InsertCommand.Parameters[0].Value = amt;

Similarly, where you add the second parameter:

C#

MySqlDataAdap.InsertCommand.Parameters.Add(new MySqlParameter("@DatePaid", payDate));
MySqlDataAdap.InsertCommand.Parameters[1].Value =  (getUserLevel() == "Lower Level" ? SYSDATE() :  depositDate.ToString());

You set the property to payDate, then set it again to SYSTDATE() or depositDate.ToString() so you should decide which of these is correct. Additionally you don't need the .ToString() on the final term as the value is an object not a string. As far as I'm aware SYSTDATE() isn't a c# method so that is suspicious too, my guess is you need to remove that line of code from your description of what it does but I'm not certain.

If, after you've changed you code you are still getting the same error message you need examine the value you are passing in to the date paid parameter. What is currently happening is that the currently date is being passed as a string rather than a datetime and the framework can't figure out how to transform the string into a .net datetime (this change of string to a different object type is called parsing). You need to work out why the parse is failing. There are lots of reasons this can happen e.g. the date is just invalid "32-Dec-2012", culture plays a part for example when an american formatted date is supplied date where a UK date is expected (e.g. 12/13/2012 for the 13^th December) or even the date being passed is null. In your case I'd google for "DateTime.Parse" and actually manually parse the string into a datetime object and pass that as the value not its string.