C# application with mysql database with date saved as string

Question

I have an application where I store a value with date-type "as string"

The problem is that the results of the query aren't being shown correctly in C# when I execute the program, but when I apply the same query in mysql workbench the results are correct.

The date value is stored as a string in mysql; can we use comparison this way as shown or is it wrong?

string s = "select date  from guest,program where guestid=guest.id AND *date >= " + date + "* "' ";

possible duplicate of using c# datetime in mysql update query — mjw
– mjw, Commented Aug 26, 2015 at 14:57
Is "date" in your MySQL database a datetype or a string type? And is your "date" variable in C# a string? Also, setting up your query like that can lead to SQL injections (very bad). It would be better to use a parameterized query, instead. — Russ
– Russ, Commented Aug 26, 2015 at 14:59
Providing your code that retrieves data from database and a sample string of your date variable would help us understanding the issue. — Ham3d
– Ham3d, Commented Aug 26, 2015 at 15:01
mjw no actually it is different because the string issue i checked and didn't find anything to help me . i don't know if storing the date in the database could be the problem — Ramaz Loi
– Ramaz Loi, Commented Aug 26, 2015 at 15:17
Russ Yes they are both string. i'll look up the SQL injection but now i needto solve this ASAP . i am trying to produce working code not an optimal code for now . — Ramaz Loi
– Ramaz Loi, Commented Aug 26, 2015 at 15:19

Felipe Oriani · Accepted Answer · 2015-08-26 14:58:36Z

2

Since your column in the database is a date column and the value you want to pass is a DateTime strucure, as a good pratice, try to use parameters in your command. Using Parameters in your command, you avoid a lot of problems, like sql injection, type definition, etc. For sample:

string sql = "select date  from guest,program where guestid=guest.id AND date >= @date";

using (MySqlConnection con = new MySqlConnection("your connectionstring"))
{
    MySqlCommand cmd = new MySqlCommand(sql, con); 

    try 
    { 
        con.Open(); 
        cmd.Parameters.AddWithValue("date", date); 

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var dateField = (DateTime)reader["date"];
                // some task
            }
        }
    } 
    finally 
    { 
        con.Close(); 
    }
}

Read more here: http://www.csharp-station.com/Tutorial/AdoDotNet/Lesson06

answered Aug 26, 2015 at 14:58

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dmitrii Bychenko Over a year ago

con.Close(); as well as try finally is redundant in your code: using will do the work

Ramaz Loi Over a year ago

it is a string not a Date . i meant the value is date- type but is stored as a string . sorry if it wasn't obvious :(

Collectives™ on Stack Overflow

C# application with mysql database with date saved as string

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related