Click here to Skip to main content
15,901,666 members
Please Sign up or sign in to vote.
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
Updated 24-Dec-12 0:45am
v2

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);
 
Share this answer
 
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 13th 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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900