I found out something interesting about the VFP OleDb driver yesterday. I’m using a DataReader to return records from a FoxPro table, and one thing about DataReaders in .NET 1.1 is that you can’t get a record count. (There’s a .RowsAffected property but that’s different, as I understand it.)<\/p>\n\n\n\n
The usual thing to do is to perform a pre-select to get the record count, and use ExecuteScalar() to return the single count value:<\/p>\n\n\n\n
StringBuilder sb = new StringBuilder();\nsb.Append(\"SELECT COUNT(*) FROM customers WHERE name = '\");\nsb.Append( cCustomer ) ;\nsb.Append( \"'\") ;\n\noCmd = new System.Data.OleDb.OleDbCommand( sb.ToString(), oVfpConn ) ;\n\nint resultCount = (int) oCmd.ExecuteScalar(); \n<\/pre>\n\n\n\nThis code works fine if you’re using a SqlClient.SqlDataReader against SQL Server, but here, we get an exception: “specified cast is not valid”.<\/p>\n\n\n\n
It turns out that, for the VFP OleDb source, oCmd.ExecuteScalar() returns a System.Decimal object instead of an int. After some experiments, I found this seemed to work best:<\/p>\n\n\n\n
\t\nint resultCount = System.Decimal.ToInt32( (Decimal) oCmd.ExecuteScalar() ); \n\nif ( resultCount == 0 )\n{\n\t...\n<\/pre>\n\n\n\nThis is fun!<\/p>\n\n\n\n