field variables in sql string

J

John Sh

Active member
Local time
Tomorrow, 03:55
Joined
Feb 8, 2021
Messages
650
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.

Screenshot_23.jpg
Screenshot_24.jpg
 
John Sh said:
Code: 
"WHERE A.Main_Family ='" & sFamily & "'" & _
Click to expand...
What is your question?

Do you have an error?

It looks like you need an extra space at the end of the above:
Code: 
"WHERE A.Main_Family ='" & sFamily & "' " & _
                                       ^
                                       |
                                  space here
Also here:
Code: 
"AND B.Accession = " & nAccno & " " & _
                              ^^^^^
                              |||||
 
you can also Add records using Recordsets:

Code: 
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
 
cheekybuddha said:
It looks like you need an extra space at the end of the above:
Code: 
"WHERE A.Main_Family ='" & sFamily & "' " & _
                                       ^
                                       |
                                  space here
Also here:
Code: 
"AND B.Accession = " & nAccno & " " & _
                              ^^^^^
                              |||||
Click to expand...
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
 
If notes will be the same for every possible inserted record then you can try:
Code: 
    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, '" & Replace(sStr, "'", "''") & "' " & _
 
A few tips for troubleshooting/prevention:

* Use an extra variable for the concatenated SQL text, then you can output the content more easily.
Code: 
Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
 
   dim InsertSql as String

   InsertSql = "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 " & _

 
   debug.Print InsertSql
   stop ' check string in immediate window
 
   oDB.Execute InsertSql, dbfailonerror

* Avoid SQL injection: strings must remain strings.
... "WHERE A.Main_Family ='" & sFamily & "'" & ...

=> 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
(Note: simple function .. only show the concept)
 
John Sh said:
To achieve the results I need, I have to do a series of findfirst operations and it simply takes too long.
Using SQL is many times faster.
Click to expand...
i don;'t have Findfirst on my code. It just add a "filtered" records.
anyway, your choice.
 
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
 
RonPaii said:
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
Click to expand...
Josef P. said:
A few tips for troubleshooting/prevention:

* Use an extra variable for the concatenated SQL text, then you can output the content more easily.
Code: 
Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
 
   dim InsertSql as String

   InsertSql = "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 " & _

 
   debug.Print InsertSql
   stop ' check string in immediate window
 
   oDB.Execute InsertSql, dbfailonerror

* Avoid SQL injection: strings must remain strings.
... "WHERE A.Main_Family ='" & sFamily & "'" & ...

=> 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
(Note: simple function .. only show the concept)
Click to expand...
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
 
John Sh said:
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.
Click to expand...
This description, which I did not fully understand, is a bit of a 'code smell' that you have poor/sub-optimal table design.

The answers here are really just band-aids to work around what should be extremely simple with better designed tables.
 
John Sh said:
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
Click to expand...
You could make your life much easier by using a little different typography.
Most important is the leading space in each line, very easy to check if each line has (or needs) such a space.

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
 
cheekybuddha said:
This description, which I did not fully understand, is a bit of a 'code smell' that you have poor/sub-optimal table design.

The answers here are really just band-aids to work around what should be extremely simple with better designed tables.
Click to expand...
I know you professionals like every thing to be tickety boo, but!
I have inherited a "system" that was thrown together in the 70's and grew like topsy from there.
To say it is a poor design, implies that there was a design process in the first place.
That being said, it is what it is and I have to work with it. A re-design at this stage, while feasible, is impractical for what it would gain.
My current project is a single form that has reference to all of the various data errors in the system.
E.G. Different collectors have applied the wrong family/genus to a specimen.
There are data mismatches everywhere
We have raw images and no jpg.
We have jpg images and no raw.
Dates and numbers were stored as text, etc, etc etc.
The same accession number, id, has been used more than once. Note. The accession number, while unique, is not, and cannot be, an autonumber.
It is a computer generated double that has to comply with certain external factors.
Where more than one specimen is taken from the same plant at the same time the accession number for all such specimens will be the same integer base, 123 with a .1, .2 etc decimal content. So 123.1, 123.2 & etc.
Duplicates are now prohibited in the table design and in the number generation procedure.
Most, if not all, of these errors came from the data table consisting of nothing but text fields and the data being entered directly into the table.
I have since built forms for data entry and corrected the fields to accept only the relevant data.
Data entry, where required, is from combo boxes. Dates must be valid and the accession number cannot be duplicated.
Yes, there are multiple tables with similar data and no, the system has not been normalised.
My priority is to get a working system as quickly as possible without the niceties of an engineer designed system.
What I have is a working database that has no new errors but plenty of old errors that, without another form to highlight them, will never be seen, or corrected.
So, yes, there are a few little things you might say are not ideal but it works and that, people, is what really matters.

N.B. I did "Java OOP" 101 some twenty or so years ago. That is my entire resume re formal programming training.
John
 
Last edited:
Imb said:
You could make your life much easier by using a little different typography.
Most important is the leading space in each line, very easy to check if each line has (or needs) such a space.

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
Click to expand...
I like the concept.
You have in instant visualisation of you mistakes, or you don't make them in the first place.
Thank you.
John
 
One reason invalid SQL can happen is because the developer loses track of where they are with their spaces and value escape characters.
A solution to this is the use of StringFormatSQL function as found in Northwind 2 Developer Edition (NW2). After you import its modStrings module, you can write code as below. The benefit is that you can focus on the SQL itself and leave adding the escape characters to the function.
Dim sql As String
sql = "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 = {0} AND B.Accession = {1} AND A.Raw_Box = B.boxno ;"
sql = StringFormatSQL(sql, sFamily, nAccno)
oDB.Execute sql, dbFailOnError

A video about this and other NW2 string functions is here.
 
tvanstiphout said:
One reason invalid SQL can happen is because the developer loses track of where they are with their spaces and value escape characters.
A solution to this is the use of StringFormatSQL function as found in Northwind 2 Developer Edition (NW2). After you import its modStrings module, you can write code as below. The benefit is that you can focus on the SQL itself and leave adding the escape characters to the function.
Dim sql As String
sql = "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 = {0} AND B.Accession = {1} AND A.Raw_Box = B.boxno ;"
sql = StringFormatSQL(sql, sFamily, nAccno)
oDB.Execute sql, dbFailOnError

A video about this and other NW2 string functions is here.
Click to expand...
Thank you. I have implemented IMB's approach.
I have converted all of my sql strings to this format with a couple of search and replace passes.
 
You must log in or register to reply here.

Similar threads

J
Solved Variable not defined in sql string
Replies
11
Views
908
John Sh
J
J
Solved How to handle ' in sql string
Replies
15
Views
1,053
Ken Sheridan
Ken Sheridan
J
Solved Variable in HAVING clause
Replies
8
Views
823
John Sh
J
J
Solved Family not included in aggregate function
Replies
4
Views
638
Mark_
M
J
Using a variable in sql query
Replies
10
Views
1,419
ebs17
E

Users who are viewing this thread

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