1

Wondering if someone can help me with this please. I'm getting the following error message when adding data to an access database.I know it's a time issue function but I don't know how to fix it so I can add data to the database in the required format. One or more values are prohibited by the validation rule 'Time()' set for 'tblmph'. Enter a value that the expression for this field can accept. 

Public Sub Add_Data()

    con.Open()

    Dim rs As New OleDb.OleDbCommand("Insert into tblmph(ID,ThisDate,TimeStart,TimeFinish,Notes) " _
        & "values ('" & TextBox1.Text & "' , '" & TextBox2.Text & "' , '" & TextBox3.Text & "' , '" _
        & TextBox4.Text & "', '" & TextBox5.Text & "')", con)

    rs.ExecuteNonQuery()

    con.Close()
    Display_Data()

End Sub

The columns are formatted as follows

  • ID = Auto Number-Long Integer
  • ThisDate = Short Date
  • TimeStart = Medium Time, Default Value = Time()
  • TimeFinish = Medium Time, Default Value = Time()
  • Notes = Memo
Jason
  • 19
  • 8
  • Will you provide more specifics for the validation rule? Properties and Values? -- also, I shouldn't have used underscores for line continuation in my edit since this is VB.NET, not vba. – Don Jewett Jul 16 '15 at 00:02
  • None of the columns are string, yet that is what you are passing for each. You appear to be reusing your connection, and your code is also wide open to SQL injection attacks. [Here are some tips](http://stackoverflow.com/a/29187199/1070452) – Ňɏssa Pøngjǣrdenlarp Jul 16 '15 at 00:16
  • Sorry mate, that went over my head. I am a newbie at all this so im learning slowly and need a bit patience. – Jason Jul 16 '15 at 02:28
  • Inserting the ID is the only issue that I can see. – ThatGuy Jul 16 '15 at 03:11

1 Answers1

2

Do not insert the value of ID. If it is truly an auto-number, Access will automatically assign a value to the newly inserted row. Trying to insert a value into the field will cause an error and prevent the insert from working.

Change your Dim statement to:

Dim rs As New OleDb.OleDbCommand( "Insert into tblmph( ThisDate, TimeStart, TimeFinish, Notes ) values ( '" & TextBox2.Text & "' , '" & TextBox3.Text & "' , '" & TextBox4.Text & "', '" & TextBox5.Text & "' ) ", con)
StarPilot
  • 2,246
  • 1
  • 16
  • 18