A character is cut off when data from a textfield in a form containing two dots has to be inserted into a table

Question

When I want to insert data from a form into a table in MS Access 2010 using the following code I receive a runtime error '3075'. It says: Syntax error in the query experession '123.11.1' even though the text field "AbsErst" contained '123.11.11'. When I enter something without dots or with only one dot into "AbsErst" the code runs perfectly and inserts the data into the table. I looked for other questions with the same error code but did not find the same issue there. Looking forward for your answers or ideas Henrik

Private Sub cmdStore_Click()
  CurrentDb.Execute "INSERT INTO tblAbschnitt(INST_F,GDE_F,ABT_F,RW_F,Erst,Stand) " & " VALUES(" & _
                            Me.cboInst & "," & Me.cboGem & "," & Me.cboAbt & "," & Me.cboRW & "," & Me.AbsErst & "," & Me.absStan & ")"       
End Sub

And watch out for SQL injection. Read http://stackoverflow.com/questions/512174/non-web-sql-injection and http://stackoverflow.com/a/36454773/3820271 — Andre, May 06 '16 at 11:04

Leviathan · Accepted Answer · 2016-05-06T11:04:15.867

If you want to insert text into a table (and '123.11.1' is text), then you have to enclose it with single quotes in the SQL statement.

CurrentDb.Execute "INSERT INTO tblAbschnitt" & _
  "(INST_F,GDE_F,ABT_F,RW_F,Erst,Stand) " & _
  " VALUES(" & Me.cboInst & _
         "," & Me.cboGem & _
         "," & Me.cboAbt & _
         "," & Me.cboRW & _
         ",'" & Me.AbsErst & "'" & _
         "," & Me.absStan & _
         ")"

Do this not only with Me.AbsErst but with all text columns. You have to make sure for all those columns that the value to be inserted does not contain any single quotes themselves. They'd need to be escaped by another single quote. (Cue: SQL Injection)

All this could be probably done much easier and safer if you do not use an INSERT statement but something like this:

With CurrentDb.OpenRecordset("tblAbschnitt")
    .AddNew
    .Fields("INST_F") = Me.cboInst
    .Fields("GDE_F") = Me.cboGem
    .Fields("ABT_F") = Me.cboAbt
    .Fields("RW_F") = Me.cboRW
    .Fields("Erst") = Me.AbsErst
    .Fields("Stand") = Me.absStan
    .Update
End With

This way all the escaping and single quotes are handled automatically.

A character is cut off when data from a textfield in a form containing two dots has to be inserted into a table

1 Answers1