Unable to insert DateTime format into database

Question

I'm unable to insert the DateTime into my database. Am i writing the statement wrongly?

Apparently without the DateTime, I am able to insert into the database

    string dateAndTime = date + " " + time;

    CultureInfo provider = CultureInfo.InvariantCulture;        
    DateTime theDateTime = DateTime.ParseExact(dateAndTime, "d MMMM yyyy hh:mm tt", provider);

//Create a connection, replace the data source name with the name of the SQL Anywhere Demo Database that you installed
            SAConnection myConnection = new SAConnection("UserID=dba;Password=sql;DatabaseName=emaDB;ServerName=emaDB");
            //open the connection 
            ; myConnection.Open();
            //Create a command object. 
            SACommand insertAccount = myConnection.CreateCommand();
            //Specify a query. 
            insertAccount.CommandText = ("INSERT INTO [meetingMinutes] (title,location,perioddate,periodtime,attenders,agenda,accountID,facilitator,datetime) VALUES ('"+title+"','" + location + "', '" + date + "','" + time + "', '" + attender + "','" + agenda + "', '" + accountID + "','" + facilitator + "','" +theDateTime+ "')");
try
    {
        insertAccount.ExecuteNonQuery();

        if (title == "" || agenda == "")
        {
            btnSubmit.Attributes.Add("onclick", "displayIfSuccessfulInsert();");
            //ScriptManager.RegisterStartupScript(this, GetType(), "error", "alert('Please ensure to have a title or agenda!');", true);
        }
        else
        {

            btnSubmit.Attributes.Add("onclick", "displayIfSuccessfulInsert();");
            Response.Redirect("HomePage.aspx");
            //ScriptManager.RegisterStartupScript(this, this.GetType(), "Redit", "alert('Minutes Created!'); window.location='" + Request.ApplicationPath + "/HomePage.aspx';", true);
        }
    }
    catch (Exception exception)
    {
        Console.WriteLine(exception);
    }

    finally 
    {            
        myConnection.Close();          
    }

It does not insert the SQL into my database.

PS: theDateTime for example, may a value which is 7/14/2012 1:35:00 AM. How to insert this into the database??

you try changing your date time `formatDateTime theDateTime = DateTime.ParseExact(dateAndTime, "d MMMM yyyy hh:mm tt", provider);` to `DateTime theDateTime = DateTime.ParseExact(dateAndTime, "dd MMMM yyyy hh:mm tt", provider);` — James, Jul 05 '12 at 09:16
It came out an error : String was not recognized as a valid DateTime. — melvg, Jul 05 '12 at 09:29

McGarnagle · Answer 1 · 2012-07-05T09:39:53.960

2

Yes, you should write the query with parameters {0}, {1}, etc, and then use Parameters.Add.

insertAccount.CommandText = ("INSERT INTO [meetingMinutes]  
   (title,location,perioddate,periodtime, ...) 
   VALUES (?,?,?,?,  ... )");
insertAccount.Parameters.Add( ... );

This will ensure that the SQL gets formed with correct syntax; and also prevent SQL injection attacks.

edited Jul 05 '12 at 09:39

answered Jul 05 '12 at 09:13

McGarnagle

101,349
31
229
260

There is no .CreateParam, is it possible for you to write a full example out for me? Sorry my C# knowledge is still at beginners – melvg Jul 05 '12 at 09:25
@melvg sorry, I'm confused as to what library you're using. Where do *SAConnection* and *SACommand* come from? – McGarnagle Jul 05 '12 at 09:34
Sybase iAnywhere 12 for the database. – melvg Jul 05 '12 at 09:35
using iAnywhere.Data.SQLAnywhere; – melvg Jul 05 '12 at 09:36
@melvg ah, ok. Please see my updated answer. I'm not sure if my syntax is right (I'm not familiar with Sybase iAnywhere), but it looks like the documentation is decent: http://dcx.sybase.com/1101/en/dbprogramming_en11/ianywhere-data-sqlanywhere-sacommand-parameters2-0.html – McGarnagle Jul 05 '12 at 09:40
Tried it, it came out this error : The SAParameterCollection only accepts non-null SAParameter type objects, not String objects. – melvg Jul 05 '12 at 09:44
Sybase is such a troublesome thing. :S – melvg Jul 05 '12 at 09:45
Btw dbaseman, does the insertAccount.Parameters.Add(...); have to come before the insertAccount.CommandText?? I dont think it matters right? – melvg Jul 05 '12 at 12:15

score 1 · Answer 2 · answered Jul 05 '12 at 09:33

1

First of all NEVER use string concatenation for SQL queries or commands. Use parameters. If you will use parameters then:

it is not possible to make sql-injection
query text and plan is cached, which increases performance
and what is important in your case - you do not have to think about formatting of the value, just pass the DateTime variable as the parameter

And also crosscheck that your DB column has datetime2 type, otherwise most likely you will not be able to store values less than 1 Jan 1758 (e.g. DateTime.MinValue).

answered Jul 05 '12 at 09:33

Alexey F

1,763
14
19

Nope, Sybase Anywhere DB do not have a datetime2 type. Only datetime and datetimeoffset – melvg Jul 05 '12 at 10:00
I am not familiar with Sybase Anywhere DB, but it would be wise to check what the DB driver will do if you specify a DateTime value less than 1 Jan 1758. Anyway, use sql command with parameters =) – Alexey F Jul 05 '12 at 10:29

score 0 · Answer 3 · answered Jul 05 '12 at 09:17

0

Dont use quotes for yr date, remove all quotes where you are using a date

change ,'" +theDateTime+ "') to ," +theDateTime+ ")

and also secure yr sql cause it unsave for SQL injections

answered Jul 05 '12 at 09:17

JohnnBlade

4,261
1
21
22

Did as you told but still it didnt redirect. :S – melvg Jul 05 '12 at 09:24

Unable to insert DateTime format into database

3 Answers3