Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I want to reading a datetime value from a Sqlite database and assign it to datepicker control. Here is my code that i am trying:

C#
try
            {
                sqlitecon.Open();

                string Query = "Select * from Customer_New where Cust_Id='" + val + "'  ";

                SQLiteCommand createCommand = new SQLiteCommand(Query, sqlitecon);
              //  createCommand.ExecuteNonQuery();
                SQLiteDataReader dr = createCommand.ExecuteReader();
                while(dr.Read()){
                if(!dr.IsDBNull(1))
                    date_open.DisplayDate = dr.GetDateTime(1);
                if(!dr.IsDBNull(2))    
                    Date_Joining.DisplayDate = dr.GetDateTime(2);
                if(!dr.IsDBNull(3))
                    txt_Title.Text = dr.GetString(3);
                if(!dr.IsDBNull(4))
                    txt_cardnum.Text =dr.GetString(4);
                }
                sqlitecon.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

The above code is executed on form load event .so when form loads it should load the date value from db table into date picker .but form is opening with Message box that shows exception "specified cast is not valid". Then it fails to load any value from db and if i remove date fields from assigning in while loop then rest of text fields are assigned correctly to form . My db foramt of date is :MM/DD/YYYY and is of type datetime . On debugging by putting break point at "date_open.DisplayDate = dr.GetDateTime(1);" it shows system date and time .My db tables are following sequence: Cust_id | date_open | Date_Joining |..... .Please Help

What I have tried:

nil....................................................................
Posted
Updated 29-Jan-20 4:33am
v2
Comments
Pheonyx 10-Oct-13 3:43am    
if you step through your code and before either of the dr.GetDateTime lines execute, view the dr.GetDateTime(1) or dr.GetDateTime(2) in the watch window, what values do you get?
ZurdoDev 29-Jan-20 9:24am    
All you need to do is debug this and you'll quickly find what's wrong. You're trying to load something into date that is not in a date format.

It's very simple but we can't run your code so only you can do it.

1 solution

First things first; your code is vulnerable to SQL Injection!
Never, ever, ever create an SQL query by stringing together a command and user input. The proper way to add user input into a query is via the Command's parameter collection
C#
try
{
        sqlitecon.Open();

// BAD: string Query = "Select * from Customer_New where Cust_Id='" + val + "' ";
        string Query = "Select * from Customer_New where Cust_Id= ? ";

        SQLiteCommand createCommand = new SQLiteCommand(Query, sqlitecon);
        createCommand.Parameters.AddWithValue("val", val);

// continue on with existing code

Next thing to talk about is "My db foramt of date is :MM/DD/YYYY and is of type datetime"; DateTimes are actually numerical values; and what you are seeing is based on either your local system settings or the settings of the displaying application. You may want to actually verify what type this is as SQLite doesn't have a "true" DateTime type.
SQLite - Data Type - Tutorialspoint[^]

The way that your query looks, it appears that only one record should be returned. If this is the case then you do not need a WHILE loop; as only a maximum of 1 row should be returned. Instead you should be using the HasRows property (which should be used even if multiple records are returned, wrapping the loop).

I would also be tempted to grab the values from your DataReader into local variables first to make sure you are getting what you think you are getting, and then populate the form controls from those local values. It just may help identify what exactly you got going on as you can see the types/values of those local variables when you hit a breakpoint.

The above 2 paragraphs would give me code looking something like this
C#
if (!dr.HasRows) { throw new Exception("No Rows Returned"); }
else {
  var DisplayDateOpen = dr.GetDateTime(1);
  var DisplayDateJoin = dr.GetDateTime(2);
  var Title = dr.GetString(3);
  var CardNum = dr.GetString(4);

// insert breakpoint here

// check/validate values

  date_open.DisplayDate = DisplayDateOpen;
  Date_Joining.DisplayDate = DisplayDateJoin;
  txt_Title.Text = Title;
  txt_cardnum.Text = CardNum;
}

Once you get comfortable with this and know what exactly is going on; you could adjust as needed.
 
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