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
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.
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
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+"' ")
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.
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