Geek asks and answers

Cannot perform INSERT INTO using DateTime to save timestamp in MS Access

I try to save the username and DateTime object in the MS-Access database, but I get a "Syntax error in the INSERT INTO statement". When I debug a program, I see that the date is correctly transmitted. In addition, I completely deleted the DateTime information and used the insert command only with the username and saved it correctly, so it has something to do with DateTime itself, but I don't know what. Here is the related code:

//event handler for Begin Program button
private void btnBeginProgram_Click(object sender, EventArgs e)
{
  //assign DateTime object to current computer date/time 
  busObject.DtDate = DateTime.Now;

  //assign input to busObject.UserName property
  busObject.UserName = txtEnterName.Text;   

  //call method to save input data
  busObject.SaveData();           

  this.Close();
}

//properties for variables and objects
public string UserName
{
  get { return userName; }
  set { userName = value; }
}

public DateTime DtDate
{
  get { return dtDate; }
  set { dtDate = value; }
} 

//method to call SaveData method in ProgramLoginDAL class
public void SaveData()
{
  ProgramLoginDAL.SaveData(this);
}

//method to save user input to database
public static void SaveData(ProgramLoginBOL busObject)
{
  try
  {
    OleDbCommand cmd = aConnection.CreateCommand();

    String sSQLCommand = "INSERT INTO ProgramLogin (UserName, DateTime) VALUES (?,?)";

    cmd.Parameters.AddWithValue("?", busObject.UserName);
    cmd.Parameters.AddWithValue("?", busObject.DtDate);

    if (aConnection.State == ConnectionState.Closed)
    {
      aConnection.Open();
    }

    cmd.CommandText = sSQLCommand;
    // Execute the SQL command
    cmd.ExecuteNonQuery();
    aConnection.Close();

    MessageBox.Show("Data Saved");                
  }
  catch (Exception ex)
  {
    Console.WriteLine(ex.ToString());
    MessageBox.Show("Error! Data was not saved.");
  }
}
+3
source share
5 answers

Try placing brackets around the name of your field. This is probably a keyword:

String sSQLCommand = "INSERT INTO ProgramLogin (UserName, [DateTime]) VALUES (?,?)";

For DateTime, you may need to specify a data type:

sSQLCommand .Parameters.Add(
  new OleDbParameter("?", OleDbType.Date) { Value = busObject.DtDate });
+4

... , , ...

+1

Try to assign different parameters to parameters?

String sSQLCommand = "INSERT INTO ProgramLogin (UserName, DateTime) VALUES (@Name,@Date)";           

cmd.Parameters.AddWithValue("@Name", busObject.UserName);     
    cmd.Parameters.AddWithValue("@Date", busObject.DtDate);
+1
source

Try as follows:

com.CommandText = "INSERT INTO ProgramLogin (UserName, [DateTime]) values (@userName, @date)";

cmd.Parameters.AddWithValue("@userName", busObject.UserName);
cmd.Parameters.AddWithValue("@date", busObject.DtDate);
+1
source

Although the OleDb provider uses positional parameters instead of named parameters, it is not very good to have two parameters with the same name inside the collection.

Also, if you use an access database, DateTime is a reserved word. Must be enclosed in square brackets

+1
source

More articles:


All Articles