Type mismatch error

Databases
1

Does anyone know what this error means:

ClientsWindow.UpdateRow, line 4
Type mismatch error. Expected class MySQLPreparedStatement, but got interface PreparedSQLStatement

dim ps as MySQLPreparedStatement = peopledb.Prepare("SELECT LastName,FirstName,MiddleInitial,DOB,SocialSecurity,Phone,Email,Address,Address2,City,State,Zip,MedicareNumber,PartAEffDate,PartBEffDate,Company,PlanType,PlanName,EffDate,Premium from people where LastName = ? and FirstName = ? and MiddleInitial = ? and DOB = ? and SocialSecurity = ? and Phone = ? and Email = ? and Address = ? and Address2 = ? and City = ? and State = ? and Zip = ? and MedicareNumber = ? and PartAEffDate = ? and PartBEffDate = ? and Company = ? and PlanType = ? and PlanName = ? and EffDate = ? and Premium = ?")
2

To the best that I can see there is nothing wrong with THAT line of code…
I suggest that error is actually caused by another line, but not that one

OR… peopleDB is not a mySQL database reference (something that cannot be discerned from this one line)

3

Switched it to SQLite and now getting the following

dim ps as SQLitePreparedStatement ps = peopledb.Prepare(sql)

ClientsWindow.UpdateRow, line 8
Type mismatch error. Expected class SQLitePreparedStatement, but got interface PreparedSQLStatement
ps = peopledb.Prepare(sql)

4

What type is peopledb?

I think your dim ps should be MySqlPreparedStatement or SQLitePreparedStatement if peopledb is defined as MySQL or SQLite database.

Edit: I just did a test and I was able to reproduce the error. I defined peopledb as database, then used dim ps as mysql or sqlite and got the error

5

Change to

dim ps as PreparedSQLStatement

See my reply to your thread
https://forum.xojo.com/46033-nilobjectexception-was-not-handled

6

here is the exact code

[code]Dim dr As New DatabaseRecord
dr = new DatabaseRecord

Dim sql As String
sql = “SELECT LastName,FirstName,MiddleInitial,DOB,SocialSecurity,Phone,Email,Address,Address2,City,State,Zip,MedicareNumber,PartAEffDate,PartBEffDate,Company,PlanType,PlanName,EffDate,Premium from people where LastName = ? and FirstName = ? and MiddleInitial = ? and DOB = ? and SocialSecurity = ? and Phone = ? and Email = ? and Address = ? and Address2 = ? and City = ? and State = ? and Zip = ? and MedicareNumber = ? and PartAEffDate = ? and PartBEffDate = ? and Company = ? and PlanType = ? and PlanName = ? and EffDate = ? and Premium = ?;”)

dim ps as MySQLPreparedStatement
ps = peopledb.Prepare(sql)

ps.BindType(0,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(1,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(2,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(3,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(4,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(5,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(6,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(7,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(8,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(9,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(10,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(11,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(12,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(13,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(14,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(15,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(16,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(17,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(18,MySQLPreparedStatement.MySQL_Type_String)
ps.BindType(19,MySQLPreparedStatement.MySQL_Type_String)

ps.Bind(0,lstPeople.Cell(lstPeople.ListIndex,0))
ps.Bind(1,lstPeople.Cell(lstPeople.ListIndex,1))
ps.Bind(2,lstPeople.Cell(lstPeople.ListIndex,2))
ps.Bind(3,lstPeople.Cell(lstPeople.ListIndex,3))
ps.Bind(4,lstPeople.Cell(lstPeople.ListIndex,4))
ps.Bind(5,lstPeople.Cell(lstPeople.ListIndex,5))
ps.Bind(6,lstPeople.Cell(lstPeople.ListIndex,6))
ps.Bind(7,lstPeople.Cell(lstPeople.ListIndex,7))
ps.Bind(8,lstPeople.Cell(lstPeople.ListIndex,8))
ps.Bind(9,lstPeople.Cell(lstPeople.ListIndex,9))
ps.Bind(10,lstPeople.Cell(lstPeople.ListIndex,10))
ps.Bind(11,lstPeople.Cell(lstPeople.ListIndex,11))
ps.Bind(12,lstPeople.Cell(lstPeople.ListIndex,12))
ps.Bind(13,lstPeople.Cell(lstPeople.ListIndex,13))
ps.Bind(14,lstPeople.Cell(lstPeople.ListIndex,14))
ps.Bind(15,lstPeople.Cell(lstPeople.ListIndex,15))
ps.Bind(16,lstPeople.Cell(lstPeople.ListIndex,16))
ps.Bind(17,lstPeople.Cell(lstPeople.ListIndex,17))
ps.Bind(18,lstPeople.Cell(lstPeople.ListIndex,18))
ps.Bind(19,lstPeople.Cell(lstPeople.ListIndex,19))

dim rs as RecordSet = ps.SQLSelect

if peopledb.Error then
MsgBox "Database error: " + peopledb.ErrorMessage
end

if rs <> nil then
// Do whatever you need to do with your RecordSet
end[/code]

7

The entire source code is here: http://anderson-technology.com/xojo/MyClients.rar

8

Is peopledb type = MySQLCommunityServer, SQLiteDatabase or just Database?

9

He has TWO topics going about this same issue…
and has been asked that same question multiple times.

10

http://bestgacontracts.com/xojo/MyClients.rar just tested it and works now.

11

peopledb is a SQLiteDatabase

12

peopledb is defined as a database like Alberto said. You need to define it as a SQLiteDatabase. I’m guessing it got defined weird because you dragged it into the project.

I can get the mismatch error to go away by defining peopledb as a database type that matches the prepared statement.

You might want to look into ActiveRecord from BKeeney Software. It connects to a database and treats data like objects. You’d be able to select your person from the people table, and that person becomes an object. The object then takes care of all of the database interactions for you, you just update properties and save.

To make things even easier there’s an accompanying app called ARGen which will generate all of the classes and properties for you. Provide the database connection, make a few decisions for how things should be handled, and out pops a project with the code and classes you’ll need.

https://bkeeney.com/allproducts/argen/

13

Maybe this webinar will help you: https://youtu.be/ut9bMJWXb_M

Paul use the New SQLite Database, create the schema, then delete that from the project and call the db by code. I bet if you remove the database from your project and create peopledb property as SQLiteDatabase, open the database file with code and change the prepared statements from MySQL to SQLitePreparedStatements you will make it work.

Good luck.

14

How do you export a SQLite database I am trying to use ARGen like @Tim Parnell said and it is not allowing me to export.

15

You already have the database file, clients.sqlite in your graphics folder. There’s no need to export anything from Xojo. I am writing a response to your private inquiry now.