Member Avatar for RickyG

Last date value (Next Training ) gets properly inserted but not the rest...

any clue?

<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/App_Data/JABILMCALLEN.mdb" 
            DeleteCommand="DELETE FROM [Certifications] WHERE [CertificationID] = ?" 
            InsertCommand="INSERT INTO [Certifications] ([EmployeeID], [TrainingDocID], [StartedTraining], [EndedTraining], [EvalutationType], [TrainedBy], [NextTraining], [Approved]) VALUES (?, ?, ?,?,?, ?, ?, ?)" 
            SelectCommand="SELECT * FROM [Certifications]" 
            UpdateCommand="UPDATE [Certifications] SET [EmployeeID] = ?, [TrainingDocID] = ?, [Started Training] = ?, [Ended Training] = ?, [EvalutationType] = ?, [TrainedBy] = ?, [NextTraining] = ?, [Approved] = ? WHERE [CertificationID] = ?">
            <DeleteParameters>
                <asp:Parameter Name="CertificationID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="EmployeeID" Type="Int32" />
                <asp:Parameter Name="TrainingDocID" Type="Int32" />
                <asp:Parameter Name="Started_Training" Type="DateTime" />
                <asp:Parameter Name="Ended_Training" Type="DateTime" />
                <asp:Parameter Name="EvalutationType" Type="String" />
                <asp:Parameter Name="TrainedBy" Type="String" />
                <asp:Parameter Name="NextTraining" Type="DateTime" />
                <asp:Parameter Name="Approved" Type="Boolean" />
                <asp:Parameter Name="CertificationID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="EmployeeID" Type="Int32" />
                <asp:Parameter Name="TrainingDocID" Type="Int32" />
                <asp:Parameter Name="StartedTraining" Type="DateTime" />
                <asp:Parameter Name="EndedTraining" Type="DateTime" />
                <asp:Parameter Name="EvalutationType" Type="String" />
                <asp:Parameter Name="TrainedBy" Type="String" />
                <asp:Parameter Name="NextTraining" Type="DateTime" />
                <asp:Parameter Name="Approved" Type="Boolean" />
            </InsertParameters>
        </asp:AccessDataSource>
  • 2 Contributors
  • 3 Replies
  • 134 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by dnanetwork

Dani AI

Generated

This is a classic Access + ASP.NET gotcha: mismatched identifiers and parameter mapping are usually the culprit. As found, the symptoms — one date saving while the others do not, with no exception thrown — often come from how field names are quoted or how parameters are supplied to the Jet/ACE provider. Square brackets are for delimiting identifiers that contain spaces or reserved words; using them inconsistently (or around the wrong token) can change what the provider sees and lead to silent failures.

Quick checklist to reproduce and fix the issue:

  • Confirm the actual column names in the Access table exactly match what the SQL uses. Prefer names without spaces (StartedTraining vs Started Training).
  • When using Access (Jet/ACE) with parameter placeholders (?) parameters are positional. Add parameters in the exact same order the ? placeholders appear.
  • Ensure date parameters are sent as DateTime (not as unparsed strings). For declarative AccessDataSource, set Type="DateTime" on the parameter or bind a control that yields a DateTime.
  • Avoid reserved names (Date, Time, etc.) or always bracket them consistently on every command.
  • For debugging, run a minimal INSERT in Access or use a small ADO.NET test to inspect parameter values before ExecuteNonQuery.

Example (ADO.NET) showing explicit Date parameter types and ordered parameters:

using (var conn = new OleDbConnection(connStr))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO Certifications (EmployeeID, TrainingDocID, StartedTraining, EndedTraining, EvalutationType, TrainedBy, NextTraining, Approved) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        cmd.Parameters.Add(new OleDbParameter("EmployeeID", OleDbType.Integer)).Value = employeeId;
        // add other parameters in the same order...
        cmd.Parameters.Add(new OleDbParameter("NextTraining", OleDbType.Date)).Value = nextTrainingDateTime;
        cmd.ExecuteNonQuery();
    }
}

If staying with AccessDataSource declarative markup, tie parameters to controls with ControlParameter, test with simple inserts, and handle empty strings explicitly so the provider does not coerce them to invalid values.

Recommended Answers

All 3 Replies

Member Avatar for dnanetwork

did u get any exception..?

Member Avatar for RickyG

No, it just the data were not inserted already solved by removing [] from date fields

Member Avatar for dnanetwork

cool... :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.