none
INSERT query for Date and Time values... RRS feed

  • Question

  • Hi...

    I want to insert the Date and Time values in the MS ACCESS 2007 database.

    I have two seperate columns in the database for Date and Time. So i want  to insert two values in two different columns.
    If i use this command text its throwing exception.

    string commandText = "INSERT INTO Events(ID, Date, Time) VALUES(1,#01/03/2000#,#01:33:11 PM#)";

    I Have specidifed the date column DateTime fomrat in ACCESS Database as 'Medium Date'.
    and Time column DateTime format as 'Long Time'.

    I am using C# and .Net 2.0.
    How i can do it...?

    Thanks in advance,
    IamHuM
    Saturday, February 6, 2010 12:52 PM

Answers

  • Hi,

    Is the query working when you do it directly in the ACCESS database rather than from code? Also, can you please let us know what exactly is the exception you are getting? It also could be due to a number of other reasons like for example you columns named date and Time. Normally they have to be enclosed in square braces to ensure ACCESS treats them as column names. 


    • Marked as answer by IamHuM Thursday, February 11, 2010 11:37 AM
    Sunday, February 7, 2010 2:14 PM
  • hi,

    The problem was very simple here i think.
    I used 'Date' as a column name in the database.
    So on the query database is not expecting 'Date' since its a reserved type.

    I changed the name and got it working.

    Thank all for the replies,
    IamHuM
    • Marked as answer by IamHuM Thursday, February 11, 2010 11:37 AM
    Thursday, February 11, 2010 11:37 AM

All replies

  • Hi,

    Is the query working when you do it directly in the ACCESS database rather than from code? Also, can you please let us know what exactly is the exception you are getting? It also could be due to a number of other reasons like for example you columns named date and Time. Normally they have to be enclosed in square braces to ensure ACCESS treats them as column names. 


    • Marked as answer by IamHuM Thursday, February 11, 2010 11:37 AM
    Sunday, February 7, 2010 2:14 PM
  • create an updateQuery in Access db. Call that query from OleDBAdapter . You should use, command type as 'stored Procedure'.You need to map table names when you call select command for the adapter. For executing update query, adapter need to call select call to understand the state of the record in dataTable and Actual table.
    Thanks Mike --------Please mark as answer if it is useful----------
    Sunday, February 7, 2010 5:03 PM
  • Hi IamHum,

    Thank you for your post.

    What kind of exception did you get ? Could you provide the error message? This will be much helpful for solving your problem. Thank you.

    Best regards,
    Alex Liang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, February 8, 2010 9:56 AM
    Moderator
  • Use the parameter classes, do not hardcode the values directly into the SQL string.

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, February 10, 2010 5:58 PM
  • hi,

    The problem was very simple here i think.
    I used 'Date' as a column name in the database.
    So on the query database is not expecting 'Date' since its a reserved type.

    I changed the name and got it working.

    Thank all for the replies,
    IamHuM
    • Marked as answer by IamHuM Thursday, February 11, 2010 11:37 AM
    Thursday, February 11, 2010 11:37 AM
  • Hi,

    I have database table of ms access 2007 which has 4 date/time field. I had connected this database with vb.net using table adapter. when i try to run the query it throws me error that error into insert query. in xsd file the the query written is

     INSERT INTO `ItemDescriptionTable` (`PartyName`, `PartyAddress`, InvoiceDate, `ChallanNo`, ChallanDate, `OrderNo`, OrderDate, `LRNo`, LRDate, `GoodsDispatched`, `ItemDescription`, `Quantity`, `Rate`, `Per`, `Amount`, Total, Discount_Percent, Discount_Amount, NetAmount, Packagin&Forwarding, TransprotCharges, Vat_Percent, VatAmt, RoundOff, GgrandTotal, `Ruppes`, PaymentWithinDays) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and the query written in vb,net is 

    Dim z = Me.TableAdapterManager1.ItemDescriptionTableTableAdapter.InsertQuery1(PartyNameTextBox.Text, PartyAddressTextBox.Text, InvoiceDate.Value.Date, ChallanNoTextBox.Text, ChallanDate.Value.Date, OrderNoTextBox.Text, OrderDate.Value.Date, LRTextBox.Text, LRDate.Value.Date, GoodsDispatchTextBox.Text, item, qty, rate, per, amt, Val(TotalTextBox.Text), Val(Dis_PercentTextBox.Text), Val(Dis_AmountTextBox.Text), Val(NetAmountTextBox.Text), Val(PFTextBox.Text), Val(Tran_ChargeTextBox.Text), Val(Vat_PercentTextBox.Text), Val(Vat_AmountTextBox.Text), Val(RoundOffTextBox.Text), Val(GrandTotalTextBox.Text), RsWordsTextBox.Text, Val(DaysTextBox.Text))

    please please help me in this matter. 

    Sunday, June 26, 2011 3:00 PM