Member Avatar for hfuehf

Hi I've searched all over the internet for this but can't find anything useful. I have form which a user fills out and then inserts in access database. But I also have a field DateTime in my table which I want to automatically save the current date and time in the database. How do I do this? Thank you. I keep getting an error saying "an oledbparameter with parameter name '@datetime' is not contained by this oledb"

        Try
            con.Open()

            With cmd
                .Connection = con
                .CommandType = CommandType.Text
                .CommandText = "INSERT INTO tJ (S, T, DateTime) VALUES (@S, @T, @DateTime)"

                .Parameters.Add(New System.Data.OleDb.OleDbParameter("@S", System.Data.OleDb.OleDbType.VarChar, 255, Me.cmbN.Text))
                .Parameters.Add(New System.Data.OleDb.OleDbParameter("@T", System.Data.OleDb.OleDbType.VarChar, 255, Me.cmbT.Text))
                .Parameters.Add(New System.Data.OleDb.OleDbParameter("DateTime", System.Data.OleDb.OleDbType.Date))

                cmd.Parameters("@S").Value = Me.cmbN.Text
                cmd.Parameters("@T").Value = Me.cmbT.Text
                cmd.Parameters("@DateTime").Value = Date.Now

                cmd.ExecuteNonQuery()
                MsgBox("saved.", MsgBoxStyle.Information)
  • 2 Contributors
  • 1 Reply
  • 3K Views
  • 21 Minutes Discussion Span
  • Latest Post Latest Post by Santanu.Das

Dani AI

Generated

Good catch by on the parameter name. Your exception happens because you add a parameter named "DateTime" but later look it up as "@DateTime". With OleDb against Access, parameter names in the SQL are ignored and values are bound by position, so mixing and matching names often leads to confusion. Two practical fixes:

  • Let Access stamp the time for you. Either set the column's Default Value to Now() in the table design and omit it from the INSERT, or call Now() in the SQL. Also bracket the column name because DateTime is a type name in .NET and a risky identifier in SQL.
cmd.CommandText = "INSERT INTO tJ ([S],[T],[DateTime]) VALUES (?, ?, Now())"
cmd.Parameters.Clear()
cmd.Parameters.Add("?", OleDbType.VarWChar, 255).Value = cmbN.Text
cmd.Parameters.Add("?", OleDbType.VarWChar, 255).Value = cmbT.Text
cmd.ExecuteNonQuery()
  • If you want to pass the timestamp from VB, use positional placeholders and add parameters in the same order. Either OleDbType.Date or OleDbType.DBTimeStamp works with Access; I generally use Date.
cmd.CommandText = "INSERT INTO tJ ([S],[T],[DateTime]) VALUES (?, ?, ?)"
cmd.Parameters.Clear()
cmd.Parameters.Add("?", OleDbType.VarWChar, 255).Value = cmbN.Text
cmd.Parameters.Add("?", OleDbType.VarWChar, 255).Value = cmbT.Text
cmd.Parameters.Add("?", OleDbType.Date).Value = DateTime.Now
cmd.ExecuteNonQuery()

Tips:

  • Keep identifiers bracketed: [DateTime], or better, rename to [CreatedAt].
  • Avoid AddWithValue; specify the OleDbType and size for text columns.
  • Prefer Using blocks for the connection/command so resources are disposed automatically.
Member Avatar for Santanu.Das

New System.Data.OleDb.OleDbParameter("DateTime",

This is the cause for your exception. Write Parameters properly.
You can use the datatype as OleDb.OleDbType.DBTimeStamp.
And also you can declare the parameters more consizely. Like

.Parameters.Add("@S", System.Data.OleDb.OleDbType.VarChar, 255).Value = Me.cmbN.Text
.Parameters.Add("@T", System.Data.OleDb.OleDbType.VarChar, 255).Value = Me.cmbT.Text
.Parameters.Add("@DateTime", System.Data.OleDb.OleDbType.DBTimeStamp).Value = Date.Now()
Edited by Santanu.Das
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.