none
Date query expression

    Question

  • Hi all,

    Whats the correct query expression for date datatype field in order to select and update in MS Access SQL using OleDBCommand, realy i am get tired of trying

    Thanks


    • Edited by Esmat_Halfa Thursday, April 13, 2017 9:48 AM
    Thursday, April 13, 2017 9:47 AM

All replies

  • If you are putting the date directly into the SQL statement then the variable (or value) must be enclosed between hash characters (e.g. #4/13/2017#). To avoid this type of syntactical issue it is preferable to use the Parameters collection of the OleDbCommand Class:

    System.Data.OleDb.OleDbConnection AccessConn = new System.Data.OleDb.OleDbConnection(("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=C:\\Test Files\\db1 XP.mdb;"));
    AccessConn.Open();
    System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand("INSERT INTO [TableName] (ID, CreateDate) VALUES (@ID, @DateVal)", AccessConn);
    System.DateTime DateValue = 9/30/2011 12:00:00 AM;
    AccessCommand.Parameters.AddWithValue("@ID", 12345);
    AccessCommand.Parameters.AddWithValue("@DateVal", DateValue);
    AccessCommand.ExecuteNonQuery();
    AccessConn.Close();


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, April 13, 2017 12:21 PM
  • Hi Esmat_Halfa,

    Thank you for posting here.

    For your question, here is a similar thread in SO for your reference.

    Based on my search, the MSDN article would be helpful. INFO: OleDbType Enumeration vs. Microsoft Access Data Types

    I hope this would be helpful.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 14, 2017 6:19 AM
    Moderator
  • Hi Paul and all,

    Thanks for your response but really what bad access than other DBMSs

                        cmd.CommandText = "Update tb_Fuel_Daily_update set Spend_Quantity = " + Convert.ToInt32(TXT_TodayTotal.Text) + " Where ServiceMain_ID =@MainService and ServiceDetail_ID =@DetailService and Update_date =@Update_date ";
                        cmd.Parameters.AddWithValue("@MainService",serviceMain_IDComboBox.SelectedIndex);
                        cmd.Parameters.AddWithValue("@DetailService",serviceDetail_IDComboBox.SelectedIndex);
                        cmd.Parameters.AddWithValue("@Update_Date", trans_DateDateTimePicker.Value.Date);
    
    // Execute
    cmd.ExecuteNonQuery();

    I already tryed selecting through appending parameters to my command  using  the same table ,when i used only Date field as filtering parameters it was working well but when appending more parameters selection no selection there ,an other observe that when using DataTimePicker.Value.Date it get back selection result but when using  DataTimePicker.Value only the was Null value

    Access Wonderful world!!!!!!!!!!

    Thanks





    Friday, April 14, 2017 7:50 AM
  • First, why don't you use a parameter for Spend_Quantity? Second, you probably want SelectedItem and not SelectedIndex for the ComboBox:

    cmd.CommandText = "Update tb_Fuel_Daily_update set Spend_Quantity = @SpendQuantity Where ServiceMain_ID=@MainService and ServiceDetail_ID=@DetailService and Update_date=@Update_date ";
    cmd.Parameters.AddWithValue("@SpendQuantity", Convert.ToInt32(TXT_TodayTotal.Text));
    cmd.Parameters.AddWithValue("@MainService",serviceMain_IDComboBox.SelectedItem.ToString);
    cmd.Parameters.AddWithValue("@DetailService",serviceDetail_IDComboBox.SelectedItem.ToString);
    cmd.Parameters.AddWithValue("@Update_Date", trans_DateDateTimePicker.Value.Date);


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, April 14, 2017 7:38 PM
  • Hi Paul,

    thank for your reply , it is still not work , even with SelectItem.ToString() ,i am also  tryed with Convert.ToInt32(SelectValue) ,when i used fixed value in an other table selection it is work

                /* Using these code no way
                 * ================================
                cmd.Parameters.AddWithValue("@MainService", serviceMain_IDComboBox.SelectedIndex);
                cmd.Parameters.AddWithValue("@DetailService", serviceDetail_IDComboBox.SelectedIndex);
                cmd.Parameters.AddWithValue("@MainService", Convert.ToInt32(serviceMain_IDComboBox.SelectedValue));
                cmd.Parameters.AddWithValue("@DetailService", Convert.ToInt32(serviceDetail_IDComboBox.SelectedValue));
                cmd.Parameters.AddWithValue("@MainService", serviceMain_IDComboBox.SelectedItem.ToString());
                cmd.Parameters.AddWithValue("@DetailService", serviceDetail_IDComboBox.SelectedItem.ToString());
                */
    
                // --- But Using fixed value it was working well
                cmd.Parameters.AddWithValue("@MainService",0);
                cmd.Parameters.AddWithValue("@DetailService",1);
                da = new OleDbDataAdapter();
                da.SelectCommand = cmd;
                dt = new DataTable();
                da.Fill(dt);
                dataGridView1.DataSource = dt;
                con.Close();
            }

    Why? i don't know!!!

    Thanks



    Monday, April 17, 2017 6:59 AM
  • What is the current exception (error)?

    OK, wait a minute. Your parameters appear to be incorrect. It should look like:

    cmd.Parameters.AddWithValue("@SpendQuantity", Convert.ToInt32(TXT_TodayTotal.Text));
    cmd.Parameters.AddWithValue("@MainService", Convert.ToInt32(serviceMain_IDComboBox.SelectedItem.ToString));
    cmd.Parameters.AddWithValue("@DetailService", Convert.ToInt32(serviceDetail_IDComboBox.SelectedItem.ToString));
    cmd.Parameters.AddWithValue("@Update_Date", trans_DateDateTimePicker.Value.Date);

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, April 17, 2017 12:21 PM
  • Exception says:

    Input string was not in a correct format

    Monday, April 17, 2017 1:02 PM
  • Exception says:

    Input string was not in a correct format


    Are all of the values you are converting (using Convert.ToInt32) numeric values? None of the values from the ComboBoxes or from TXT_TodayTotal can contain non-numeric characters.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 17, 2017 6:16 PM
  • Hi Paul,

    yes

    Tuesday, April 18, 2017 4:42 AM
  • Hi Paul,

    yes


    So did you step through the code in debug and check each of the parameter values? If so what are they? Perhaps you could identify which line of code is generating the exception?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 18, 2017 12:36 PM
  • Hi all ,

    Please keep interacting with thread,look at this  i block all but date parameter selection is work but with parameters not work

                cmd = new OleDbCommand();//Format (#17/04/2004#, "Short Date")
                cmd.Connection = con;
                cmd.CommandText = @"Select SUM(Begining_Counter)-SUM(Last_Counter) from tb_DailyTrans_By_Counters 
                                    where Trans_Date =@Trans_Date and ServiceMain_ID =@MainService and ServiceDetail_ID =@DetailService";
    
                cmd.Parameters.AddWithValue("@Trans_Date", dateTimePicker1.Value.Date);
    
                /* Using these code no way
                 * ================================
                 * using SelectedIndex
                cmd.Parameters.AddWithValue("@MainService",Convert.ToInt32(tb_Sevice_typesComboBox.SelectedIndex));
                cmd.Parameters.AddWithValue("@DetailService",Convert.ToInt32(tb_ServicesComboBox.SelectedIndex));
                 * 
                 * using SelectedValue
                cmd.Parameters.AddWithValue("@MainService", Convert.ToInt32(tb_Sevice_typesComboBox.SelectedValue));
                cmd.Parameters.AddWithValue("@DetailService", Convert.ToInt32(tb_ServicesComboBox.SelectedValue));
                 */
    
                cmd.Parameters.AddWithValue("@MainService", Convert.ToInt32(tb_Sevice_typesComboBox.SelectedItem.ToString()));
                cmd.Parameters.AddWithValue("@DetailService", Convert.ToInt32(tb_ServicesComboBox.SelectedItem.ToString()));

    Really bcaome depressed or may turn to sqlserver...


    • Edited by Esmat_Halfa Tuesday, April 18, 2017 3:29 PM
    Tuesday, April 18, 2017 3:16 PM
  • Well I can't help you further if you don't answer my questions.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 19, 2017 1:48 PM