SQL Select with variables

  1. last week

    Hi,

    with the code below I receive the field elevation of the german airport "Berlin Schoenefeld" (ICAO-Code = EDDB) from a sqlite database.

    Dim dbFile As FolderItem
    Dim db As New SQLiteDatabase
    dbFile = GetFolderItem("airports_database.sqlite")
    db.DatabaseFile = dbFile
    If db.Connect Then
    Dim rs As RecordSet
    rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = 'EDDB' ")
    If rs <> Nil Then
    txtInputFieldElevation.text = str(rs.Field("intElevation_ft").Value)
    rs.close
    Else
    MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage)
    End If
    end if

    With the code below I assign the ICAO-Code EDDB to the string inputDepartureAirport and ensure that the input has 4 uppercase letters.

    Dim inputDepartureAirport as String
    txtDepartureAirport.LimitText=4
    inputDepartureAirport = txtDepartureAirport.text.Uppercase
    TextField1.text = inputDepartureAirport
    (<--- this is just to check that the input has 4 uppercase letters) ...

    But if I combine my SQLSelect statement with the String inputDepartureAirport I dont receive the field elevation from the database.

    Dim dbFile As FolderItem
    Dim db As New SQLiteDatabase
    dbFile = GetFolderItem("airports_database.sqlite")
    db.DatabaseFile = dbFile
    If db.Connect Then
    Dim rs As RecordSet
    rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = "+inputDepartureAirport+" ")
    If rs <> Nil Then
    txtInputFieldElevation.text = str(rs.Field("intElevation_ft").Value)
    rs.close
    Else
    MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage)
    End If
    end if

    Where is my mistake?

    Thanks in advance and best regards,
    Gerson Nerger

  2. Kem T

    Jul 10 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    You didn't quote the data supplied by your variable, but you really should be using a Prepared Statement for this type of query.

    dim ps as PreparedSQLStatement = _
        db.Prepare( "SELECT intElevation_ft FROM tblAirports WHERE txtAirport = ?" )
    ps.BindType 0, SQLitePreparedStatement.SQLITE_TEXT
    dim rs as RecordSet = ps.SQLSelect( inputDepartureAirport )

    Also, please use the code tags rather than italics to post code. It makes it easier for us.

  3. Hi Kem,

    thank you for your reply. Unfortunately I get an error message:

    wdwMain.ComputeFieldElevation, line 8
    This method doesn't return a value
    Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport)

    I changed my code to this:

    Dim dbFile As FolderItem
    Dim db As New SQLiteDatabase
    dbFile = GetFolderItem("airports_database.sqlite")
    db.DatabaseFile = dbFile
    If db.Connect Then
      dim ps as PreparedSQLStatement = db.Prepare("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = ?")
      ps.BindType 0, SQLitePreparedStatement.SQLITE_TEXT
      Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport)
      If rs <> Nil Then
        txtInputFieldElevation.text = str(rs.Field("intElevation_ft").Value)
        rs.close
      Else
        MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage)
      End If
    end if
  4. Alberto D

    Jul 10 Pre-Release Testers, Xojo Pro

    I don't know if this is your problem, but the LR for PreparedSQLStatement indicates that "instead use the appropriate PreparedStatement class for the database you are using: in this case SQLitePreparedStatement so

    dim ps as SQLitePreparedStatement 

    What happens if you change

    Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport)

    to

    Dim rs As RecordSet = ps.SQLSelect("EDDB")

    From your original post, I think this:

    rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = "+inputDepartureAirport+" ")

    should be:

    rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = '"+inputDepartureAirport+"' ")
  5. Edited last week

    Hi Alberto,

    thank you for your reply.

    Changing

    Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport)

    to

    Dim rs As RecordSet = ps.SQLSelect("EDDB")

    works but it does not help: I want that the SQL Statement gets the ICAO-Code from the String-variable "inputDepartureAirport".

    I checked different codes with direct inputs of the string to search:

    rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = 'EDDB' ") worked

    rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport like 'eddb' ") worked

    But if I want to use the variable instead it does not work anymore. I wonder if it has to do with case sensitivity ...

    The code

    rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = '"+inputDepartureAirport+"' ")

    does not work. It brings the same error message:

    "This method doesn't return a value"

  6. Emile S

    Jul 10 Europe (France, Strasbourg)

    Add a breakpoint at the beginning of the code you want to follow, run and follow step by step (line by line) your code: you may found from where the error lies.

  7. OK ... finally I found a solution that works. Instead of using the variable I use the textfield - for the input - directly ...

    Dim dbFile As FolderItem
    Dim db As New SQLiteDatabase
    dbFile = GetFolderItem("airports_database.sqlite")
    db.DatabaseFile = dbFile
    If db.Connect Then
      dim ps as PreparedSQLStatement
      ps = SQLitePreparedStatement(db.Prepare( "SELECT intElevation_ft FROM tblAirports WHERE txtAirport LIKE ?" ))
      ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
      ps.Bind(0, txtDepartureAirport.text)
      dim rs as RecordSet = ps.SQLSelect
      If rs <> Nil Then
        txtInputFieldElevation.text = str(rs.Field("intElevation_ft").Value)
        rs.close
      Else
        MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage)
      End If
    end if
  8. Emile S

    Jul 11 Europe (France, Strasbourg)

    @Gerson N txtInputFieldElevation.text = str(rs.Field("intElevation_ft").Value)

    Isn’t that better:

       txtInputFieldElevation.text = rs.Field("intElevation_ft").StringValue
  9. Greg O

    Jul 11 Xojo Inc Somewhere near Raleigh, NC

    @Emile S Isn’t that better:

    txtInputFieldElevation.text = rs.Field("intElevation_ft").StringValue

    Not necessarily. If you want control over how that number is formatted he should definitely use str or format.

  10. Alberto D

    Jul 11 Pre-Release Testers, Xojo Pro
    Edited last week

    Gerson, my guess is that your code have a problem with inputDepartureAirport, do you think it could be Nil when you trying to use it?

    I usually put a Break point or use MsgBox to see if I have the value I want, so you may use MsgBox inputDepartureAirport just before

    Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport)

    Edit: Thank you Emile, I have 'lots' to learn

  11. Emile S

    Jul 11 Europe (France, Strasbourg)

    @Alberto D;Poo use MsgBox

    No, use System.DebugLog(String) instead.

    http://docs.xojo.com/index.php/System.DebugLog

or Sign Up to reply!