I’m using the 32 bit version of ODBC Data Source Adminstrator set to Microsoft Access Driver (*.mbd, *.accdb) Version 12.006650.5000 File ACEODBC.DLL Date 31/08/2011.
As previously mentioned, a convensional Select statement does work (as shown above).
What’s the advantage of using your method of connecting?
As already mentioned, you have to connect to the database before you start sending any commands to the database your code from two days ago shows that you call Connect() after executing db.SQLSelect. You need to connect even before you start with the Prepare-statement!
The connection is made in the Open Event of the Application
Orders is a Public Property of the Application
BTW when I said it no longer works in a previous post, I meant since it was converted to ODBC. The original application works fine in SQLite.
There is no connection made here. And in your code from three days ago, the Connect() statement came after you prepared and queried the database.
If you have a RecordSet of Nil as a result of SQLSelect, you either have not initialized the database instance, not connected, or Database.Error will be True. Remember that Database.Error is an error returned from the server. If you are not connected to the server, Database.Error will be False.
A last remark: instead of uploading images, please copy the source code it’s far easier to help, when one can copy the source than when you have to re-type it. The icon with the angle brackets is for source code.
It is hard to figure out. Dave mentioned if he substitutes non prepared code then it does return a recordset so the connection is being made somewhere before the select. I think the “if self connect” “break” is his attempt at debugging the issue.
Eli’s point is that in your screenshot above you are calling if self.connect then break to check the recordset. Just put Break there and then check for an error in the debugger or use your message box approach without the connect.
I’m not sure but by “reconnecting” you are probably clearing any error code that may exist because the connection is being made - so no error. Only the last command made (connect) is being error reported on.
Also do a search for odbc and Access on the forum. I know there are some issues that people have used a different connection approach to solve.
Didn’t think of that possibility very good catch!
I was going to copy the source code, but when I discovered that it was possible to upload images, I used that facility to show much more detail.
The If self.Connect Then break, is purely testing to see if self is connected, which it is, as shown at the bottom of that image.
As far as I know, Im only connecting once and thats at the start of the application.
If you try running Eddies Electronics, you will find that it works with SQLite, but what I cant understand is why it wont work with ODBC.
I thought EE was provided as an example of good Xojo programming techniques, but perhaps its over sophisticated.
I tried searching everywhere I could think of before starting this thread, but most of the stuff I found was Mac related.
Try a query with “=” rather than “like” and use a valid test. At least that will determine if the problem is with the “Like” condition and Access or something else.
As I mentioned above I have no problems with odbc prepared statements and “like” but I do not have access to Access to test.
[quote=178578:@David Martindale]The If self.Connect Then break, is purely testing to see if self is connected, which it is, as shown at the bottom of that image.[/quote] This does not test if the connection existed before If self.Connect Then break. In such a case it would just quietly connect.
I will repeat again: you are probably not connected when you start with Self.Prepare(…). A returned RecordSet of Nil and no error on the database usually (not always but in most cases) means you are not connected.
I have simplified the code by putting everthing into the Open event of CustomerList listbox.
Orders is a property of Window1 of type ODBCDatabase.
From this, it doesn’t look like there is anything wrong with the design of EE after all.
This code works
[code] Orders = New ODBCDatabase
Orders.DataSource = “Conversion”
If not Orders.Connect then
MsgBox("Error: " + Orders.ErrorMessage)
end if
dim searchName As String = “”
Dim sql As String
sql = “SELECT * FROM Customers WHERE lastname LIKE '” +searchName+"%"+"’ OR firstname LIKE ‘" _
+searchName+"%"+"’ ORDER BY lastname, firstname"
Dim rs As RecordSet = Orders.SQLSelect(sql)
'Dim stmt As ODBCPreparedStatement
'stmt = ODBCPreparedStatement(Orders.Prepare(“SELECT * FROM Customers WHERE lastname LIKE ? OR firstname LIKE ? ORDER BY lastname, firstname”))
'stmt.BindType(0, ODBCPreparedStatement.ODBC_TYPE_STRING)
'stmt.BindType(1, ODBCPreparedStatement.ODBC_TYPE_STRING)
’
'stmt.Bind(0, searchName+"%")
'stmt.Bind(1, searchName+"%")
’
'Dim rs As RecordSet = stmt.SQLSelect
If rs <> Nil Then
CustomerList.DeleteAllRows
//For i As Integer = 1 To rs.RecordCount
While Not rs.EOF
CustomerList.AddRow(rs.field("lastname").StringValue + ", " + rs.Field("firstname").StringValue)
CustomerList.RowTag(CustomerList.LastIndex) = rs.Field("ID").IntegerValue
rs.MoveNext
Wend
//Next
CustomerList.ListIndex = 0
rs.Close
End If
[/code]
This code does not work
[code] Orders = New ODBCDatabase
Orders.DataSource = “Conversion”
If not Orders.Connect then
MsgBox("Error: " + Orders.ErrorMessage)
end if
dim searchName As String = “”
'Dim sql As String
‘sql = “SELECT * FROM Customers WHERE lastname LIKE '” +searchName+"%"+"’ OR firstname LIKE '" _
‘+searchName+"%"+"’ ORDER BY lastname, firstname"
’
'Dim rs As RecordSet = Orders.SQLSelect(sql)
Dim stmt As ODBCPreparedStatement
stmt = ODBCPreparedStatement(Orders.Prepare(“SELECT * FROM Customers WHERE lastname LIKE ? OR firstname LIKE ? ORDER BY lastname, firstname”))
stmt.BindType(0, ODBCPreparedStatement.ODBC_TYPE_STRING)
stmt.BindType(1, ODBCPreparedStatement.ODBC_TYPE_STRING)
stmt.Bind(0, searchName+"%")
stmt.Bind(1, searchName+"%")
Dim rs As RecordSet = stmt.SQLSelect
If rs <> Nil Then
CustomerList.DeleteAllRows
//For i As Integer = 1 To rs.RecordCount
While Not rs.EOF
CustomerList.AddRow(rs.field("lastname").StringValue + ", " + rs.Field("firstname").StringValue)
CustomerList.RowTag(CustomerList.LastIndex) = rs.Field("ID").IntegerValue
rs.MoveNext
Wend
//Next
CustomerList.ListIndex = 0
rs.Close
End If
[/code]
Thanks, but having just tried it, it’s made no difference.
Do you get a valid RecordSet, when you drop the WHERE clause and comment out the four lines following the Prepare statement?
[code]Dim stmt As ODBCPreparedStatement = ODBCPreparedStatement(Orders.Prepare(“SELECT * FROM Customers”))
// stmt.BindType(0, ODBCPreparedStatement.ODBC_TYPE_STRING)
// stmt.BindType(1, ODBCPreparedStatement.ODBC_TYPE_STRING)
// stmt.Bind(0, searchName+"%")
// stmt.Bind(1, searchName+"%")[/code]
[quote]
Try a query with "=" rather than "like" and use a valid test. At least that will determine if the problem is with the "Like" condition and Access or something else.
Thanks, but having just tried it, it’s made no difference.[/quote]
UPDATE
What you suggest does work if I change the BindType to ODBC_TYPE_INTEGER and search for an integer.
Must be something concerning the use of ODBC_TYPE_STRING.
[code] Orders = New ODBCDatabase
Orders.DataSource = “Conversion”
If not Orders.Connect then
MsgBox("Error: " + Orders.ErrorMessage)
end if
dim searchName As integer = 10000
'Dim sql As String
‘sql = “SELECT * FROM Customers WHERE lastname LIKE '” +searchName+"%"+"’ OR firstname LIKE '" _
‘+searchName+"%"+"’ ORDER BY lastname, firstname"
’
'Dim rs As RecordSet = Orders.SQLSelect(sql)
Dim stmt As ODBCPreparedStatement
stmt = ODBCPreparedStatement(Orders.Prepare(“SELECT * FROM Customers WHERE ID = ? OR ID = ? ORDER BY lastname, firstname”))
stmt.BindType(0, ODBCPreparedStatement.ODBC_TYPE_INTEGER)
stmt.BindType(1, ODBCPreparedStatement.ODBC_TYPE_INTEGER)
stmt.Bind(0, searchName)
stmt.Bind(1, searchName)
Dim rs As RecordSet = stmt.SQLSelect
If rs <> Nil Then
CustomerList.DeleteAllRows
//For i As Integer = 1 To rs.RecordCount
While Not rs.EOF
CustomerList.AddRow(rs.field("lastname").StringValue + ", " + rs.Field("firstname").StringValue)
CustomerList.RowTag(CustomerList.LastIndex) = rs.Field("ID").IntegerValue
rs.MoveNext
Wend
//Next
CustomerList.ListIndex = 0
rs.Close
End If
[/code]
Yes
Then my best guess is that either the % is stripped out by the Bind statement maybe it needs to be escaped?
stmt.Bind(0, searchName+"\\%")Or you must double or triple quote it doesn’t make sense to me, but…
stmt.Bind(0, searchName+""%"")
stmt.Bind(0, searchName+"""%""")
And then retry with * instead of % (maybe when you tried that a few days ago it was due to not being connected):
stmt.Bind(0, searchName+"*")Thanks, but I’ve just tried all your suggestions and it still doesn’t work.
I would try either renaming this thread or starting another and reference this thread. Have “MS Access” in the title. I know we have a bunch of people here who use Access that may be able to shed some light on this.
Also be sure to try your tests with the MS Access 2007 Like operators. I doubt that the generic ODBC driver is going to do any remapping of characters for you.
My understanding is that the ODBC driver uses ANSI-92 wildcards (%,_), but Access uses (*,?). To get Access to use the ANSI-92 standard wildcards use ALIKE (apparently the ANSI version of LIKE) rather than LIKE.