In the SQL string below, the "Main_Family" field needs to be controlled by the variable "dFamily".
I need to run the same basic SQL with the "Family" data going into different fields..
This occurs n the "INSERT INTO" and "WHERE" clauses.
I have tried fields(dFamily) to no avail AI has been no help.
Code:
Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
oDB.Execute "INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection) " & _
"SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection " & _
"FROM Boxes2 as B LEFT JOIN Discrepancies as A ON A.Accession = B.Accession " & _
"WHERE A.Main_Family ='" & sFamily & "'" & _
"AND B.Accession = " & nAccno & _
"AND A.Raw_Box = B.boxno ;", dbFailOnError
End Sub
I would also like to insert sStr into a "Notes" field
These are the relevant fields of the two tables.
Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
Dim rstMain As DAO.Recordset
Dim rstDisc As DAO.Recordset
Set rstMain = oDB.OpenRecordset("Select * From Discrepancies Where (1=0);")
Set rstDisc = oDB.OpenRecordset( _
"SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection" & _
"FROM Boxes2 as B LEFT JOIN Discrepancies as A ON A.Accession = B.Accession " & _
"WHERE A.Main_Family ='" & sFamily & "'" & _
"AND B.Accession = " & nAccno & _
"AND A.Raw_Box = " & B.boxno & ";")
With rstDisc
If Not (.BOF And .EOF) Then
.MoveFirst
End If
Do Until .EOF
rstMain.AddNew
rstMain!Accession = !Acccession
rstMain!Main_Family = !Family
rstMain!Genus = !Genus
rstMain!Infra = !Infra
rstMain!Raw_Box = !boxno
rstMain!Collection = !Collection
rstMain!Notes = sStr
rstMain.Update
.MoveNext
Loop
.Close
End With
rstMain.Close
Set rstMain = Nothing: Set rstDisc = Nothing
End Sub
Thank you. I should have realised those spaces were missing but the harder you look, the easier it is to not see.
The second part of my query was how to include the "Notes" field using the sStr variable.
E.g.
Code:
Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
oDB.Execute "INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection, Notes) " & _
"SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection, sStr " & _
This code gives error 3061: too few parameters. Expected 1. Which I assume is referring to the placement of sStr
=> try with sFamily = "abc' or 1=1 or 'a' = '"
=> "WHERE A.Main_Family = " & SqlText("abc' or 1=1 or 'a' = '")
Code:
public function SqlText(byval Text2Convert as Variant) as string
if isnull(Text2Convert) then
SqlText = "Null"
else
SqlText = "'" & replace(Text2Convert, "'", "''") & "'"
end if
end function
Using Parameters eliminates the need to cleanup the text and the possibility of injection.
Code:
Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
' I don't know where oDB is, so I used CurrentDb
' See the new ,Nones in insert and [sStr] as NewNote
With CurrentDb.CreateQueryDef(vbNullString, _
"PARAMETERS " & _
"dFamily Text (255), " & _
"sFamily Text (255), " & _
"nAccno IEEEDouble, " & _
"sStr Text (255); " & _
"INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection, Notes) " & _
"SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection, [sStr] as NewNote " & _
"FROM Boxes2 as B LEFT JOIN Discrepancies as A ON A.Accession = B.Accession " & _
"WHERE A.Main_Family =[sFamily] " & _
"AND B.Accession = [nAccno] " & _
"AND A.Raw_Box = B.boxno " & _
"AND B.Main_Family = [dFamily];")
' I Add "AND B.Main_Family = [dFamily]" for
' "Main_Family" field needs to be controlled by the variable "dFamily"
.Parameters("dFamily") = dFamily
.Parameters("sFamily") = sFamily
.Parameters("nAccno") = nAccno
.Parameters("sStr") = sStr
.Execute dbFailOnError
End With
End Sub
Using Parameters eliminates the need to cleanup the text and the possibility of injection.
Code:
Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
' I don't know where oDB is, so I used CurrentDb
' See the new ,Nones in insert and [sStr] as NewNote
With CurrentDb.CreateQueryDef(vbNullString, _
"PARAMETERS " & _
"dFamily Text (255), " & _
"sFamily Text (255), " & _
"nAccno IEEEDouble, " & _
"sStr Text (255); " & _
"INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection, Notes) " & _
"SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection, [sStr] as NewNote " & _
"FROM Boxes2 as B LEFT JOIN Discrepancies as A ON A.Accession = B.Accession " & _
"WHERE A.Main_Family =[sFamily] " & _
"AND B.Accession = [nAccno] " & _
"AND A.Raw_Box = B.boxno " & _
"AND B.Main_Family = [dFamily];")
' I Add "AND B.Main_Family = [dFamily]" for
' "Main_Family" field needs to be controlled by the variable "dFamily"
.Parameters("dFamily") = dFamily
.Parameters("sFamily") = sFamily
.Parameters("nAccno") = nAccno
.Parameters("sStr") = sStr
.Execute dbFailOnError
End With
End Sub
=> try with sFamily = "abc' or 1=1 or 'a' = '"
=> "WHERE A.Main_Family = " & SqlText("abc' or 1=1 or 'a' = '")
Code:
public function SqlText(byval Text2Convert as Variant) as string
if isnull(Text2Convert) then
SqlText = "Null"
else
SqlText = "'" & replace(Text2Convert, "'", "''") & "'"
end if
end function
I do usually use a similar format. Vis variable "sQry" is declared private in a module that holds many SQL strings
sQry = "xyz"
odb.execute sQry, dbfailonerror
Once the string is working correctly I will transfer it to this module in that format.
I prefer to get things working in a separate space before committing them to the overall code bank.
I think there is a misconception here, and it is all my fault.
The field "Main_Family", second in the INSERT INTO clause does not change it's value according to "sFamily".
Rather, sFamily changes the field name.
There are four passes of the SQL string with "Main_Family", RAW_Family, Ref_Family and Spec_Family occupying that position on each consecutive pass. sFamily is the new field name, while B.Family supplies the data to the various family fields.
The addition of the two missing spaces has corrected the 3061 error. I just need to fix the "Notes" and "sStr" error and I'm good to go.
I have said "Thank you" to many people, and to the same people many times, on this forum
and I find myself, once again, having to offer my sincere thanks to all who have contributed to the solution.
Being the novice that I am, and some 86 years young, I do not necessarily fully understand all that is offered although I am usually able to glean enough information to keep me on the right track.
Once again, many thanks to you and this forum for keeping my head just above the water level.
John
