Insert Into Not Working

J

JithuAccess

Member
Local time
Today, 09:48
Joined
Mar 3, 2020
Messages
325
Hello Guys,

This is my Code


Code: 
db.Execute "Insert Into tblFileRequestData (File_Number, File_Name, File_Title, File_Requested_By, File_Requested_Date, File_Data_Entry_Date, File_Status) Values ('" & FlNum & "','" & FlNam & "','" & FlTitle & "','" & UserN & "','" & FlReqDate & "','" & FLEntryDate & "','" & FlStatus & "');"

This code is working perfect. But if there is some thing like this "Employee's File" in File Name field this code is not working. I guess the query is not fetching the data if there is a Single Quote.

Could you guys please let me know how to fix this?

Thanks
 
TBH I cannot believe it works when you use ' as date delimiters also for what appear to be numbers?
That said, I believe you double up on the single quotes in the value.

Again, build your sql onto a string, then you can debug.print it until you get it correct.
Only then do you execute it.
 
JithuAccess said:
Hello Guys,

This is my Code


Code: 
db.Execute "Insert Into tblFileRequestData (File_Number, File_Name, File_Title, File_Requested_By, File_Requested_Date, File_Data_Entry_Date, File_Status) Values ('" & FlNum & "','" & FlNam & "','" & FlTitle & "','" & UserN & "','" & FlReqDate & "','" & FLEntryDate & "','" & FlStatus & "');"

This code is working perfect. But if there is some thing like this "Employee's File" in File Name field this code is not working. I guess the query is not fetching the data if there is a Single Quote.

Could you guys please let me know how to fix this?

Thanks
Click to expand...
Take the ' (apostrophe) out of Employee's File. Employees File should work.
 
A simpler and more robust solution is to use a temp QueryDef...
Code: 
    Const SQL_INSERT As String = _
        "Insert Into tblFileRequestData " & _
            "(File_Number, File_Name, File_Title, File_Requested_By, File_Requested_Date, File_Data_Entry_Date, File_Status) " & _
        "Values " & _
            "( p0, p1, p2, p3, p4, p5, p6);"
    
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = flnum
        .Parameters(1) = flnam
        .Parameters(2) = fltitle
        .Parameters(3) = usern
        .Parameters(4) = flreqdate
        .Parameters(5) = flentrydate
        .Parameters(6) = flstatus
        .Execute dbFailOnError
    End With
The QueryDef knows the types of all the fields in the table, so it will seamlessly convert, delimit, coerce, or do whatever it has to for the insert to succeed, and it doesn't care if your string data has embedded string delimiters, like O'Malley, O'Reardan, or 12 x 8' - 2" x 4".
hth
 
MarkK said:
A simpler and more robust solution is to use a temp QueryDef...
Code: 
    Const SQL_INSERT As String = _
        "Insert Into tblFileRequestData " & _
            "(File_Number, File_Name, File_Title, File_Requested_By, File_Requested_Date, File_Data_Entry_Date, File_Status) " & _
        "Values " & _
            "( p0, p1, p2, p3, p4, p5, p6);"
   
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = flnum
        .Parameters(1) = flnam
        .Parameters(2) = fltitle
        .Parameters(3) = usern
        .Parameters(4) = flreqdate
        .Parameters(5) = flentrydate
        .Parameters(6) = flstatus
        .Execute dbFailOnError
    End With
The QueryDef knows the types of all the fields in the table, so it will seamlessly convert, delimit, coerce, or do whatever it has to for the insert to succeed, and it doesn't care if your string data has embedded string delimiters, like O'Malley, O'Reardan, or 12 x 8' - 2" x 4".
hth
Click to expand...
Thank you so much.

It works perfect
 
Here is a little extension of that.
You just need to pass the fields, the table name, and the values.

Code: 
'**************************************************************************************************************
'----------------------------------------------Parameter Insert-----------------------------------------------
'**************************************************************************************************************
Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
  Dim qdf As QueryDef
  Dim i As Integer
  Dim MyParams As New Collection
  Dim strMyParams As String
  Dim strSql As String
  If Left(TheFields, 1) <> "(" Then TheFields = "(" & TheFields & ")"
 
  For i = 0 To UBound(TheValues)
    MyParams.Add "Param" & i, "Param" & i
    If strMyParams = "" Then
      strMyParams = "[" & MyParams(i + 1) & "]"
    Else
      strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
    End If
  Next i
  strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
  ParamInsert = strSql
  Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
   For i = 0 To UBound(TheValues)
    qdf.Parameters(i) = TheValues(i)
  Next i
  qdf.Execute
  CurrentDb.QueryDefs.Delete ("tempquery")
End Function
Code: 
Public Sub TestParamInsert()
  Dim TheFields As String
  Dim FirstName As Variant
  Dim LastName As Variant
  FirstName = Null
  LastName = "Smith"
  TheFields = "(FirstName, LastName, OrderID, OrderDate)"
  ParamInsert "MyTable", TheFields, FirstName, LastName, 1, Date
End Sub

The test shows it handles Nulls, Strings, numerics, and Dates.
 
Last edited:
MarkK said:
A simpler and more robust solution is to use a temp QueryDef...
Code: 
    Const SQL_INSERT As String = _
        "Insert Into tblFileRequestData " & _
            "(File_Number, File_Name, File_Title, File_Requested_By, File_Requested_Date, File_Data_Entry_Date, File_Status) " & _
        "Values " & _
            "( p0, p1, p2, p3, p4, p5, p6);"
  
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = flnum
        .Parameters(1) = flnam
        .Parameters(2) = fltitle
        .Parameters(3) = usern
        .Parameters(4) = flreqdate
        .Parameters(5) = flentrydate
        .Parameters(6) = flstatus
        .Execute dbFailOnError
    End With
The QueryDef knows the types of all the fields in the table, so it will seamlessly convert, delimit, coerce, or do whatever it has to for the insert to succeed, and it doesn't care if your string data has embedded string delimiters, like O'Malley, O'Reardan, or 12 x 8' - 2" x 4".
hth
Click to expand...
Does not work for me either in Chrome or Firefox :(
 
When I use temp QueryDefs with parameters I prefer to name the parameters according their value for readability:
Example:
Code: 
    Const SQL_INSERT As String = _
        "Insert Into tblFileRequestData " & _
            "(File_Number, File_Name, File_Title, File_Requested_By, File_Requested_Date, File_Data_Entry_Date, File_Status) " & _
        "Values " & _
            "( p_File_Number, p_File_Name, p_File_Title, p_File_Requested_By, p_File_Requested_Date, p_File_Data_Entry_Date, p_File_Status);"
 
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters("p_File_Number") =  flnum
        .Parameters("p_File_Name") = flname
        .Parameters("p_File_Title") = fltitle
        .Parameters("p_File_Requested_By") = usern
        .Parameters("p_File_Requested_Date") = flreqdate
        .Parameters("p_File_Data_Entry_Date") =  flentrydate
        .Parameters("p_File_Status") = flstatus
        .Execute dbFailOnError
    End With
 
Last edited:
I have been watching the video on the Northwind 2 developer edition and at this time index there is another way of building SQL queries that might be worth investigating:-

AEU16: Northwind 2.0 Developer Edition: Inventory and String Functions​

 
Uncle Gizmo said:
I have been watching the video on the Northwind 2 developer edition
Click to expand...

Having watched a bit more of the YouTube, I think it's basically the same idea as MajP's in post 8 in this thread here:-

MajP

Post in thread 'Insert Into Not Working'

Here is a little extension of that.
You just need to pass the fields, the table name, and the values.

Code: 
'**************************************************************************************************************
'----------------------------------------------Parameter Insert-----------------------------------------------
'**************************************************************************************************************
Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
  Dim qdf As QueryDef
  Dim i As Integer...
  • Like
 
You must log in or register to reply here.

Similar threads

J
Solved Single Quotes in Criteria
Replies
4
Views
249
plog
P
C
Change the Sub-Form View by using VBA
2
Replies
21
Views
591
Uncle Gizmo
Uncle Gizmo
K
Solved Insert Into - Select
Replies
18
Views
304
arnelgp
arnelgp
brucemc777
Solved Date field, INSERT INTO
2
Replies
20
Views
225
brucemc777
brucemc777
S
How to insert a new row into a dynaset after normalising underlying tables
Replies
11
Views
547
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom