Problem with GUID and ms Access usin DAAB

Topics: Data Access Application Block
Oct 30, 2007 at 10:04 PM
Hi,
This is driving me crazy. Any help would be appreciated:
I am trying to develop the app in Access before porting it to a larger DB.
I can connect find, execute a LoadDataSet and get most of the results back. However, it seem to bomb when I try to access the PK field, which is a GUID:
Dim sqlcommand As String = ....
Dim cmd As DbCommand = myDb.GetSqlStringCommand(sqlCommand)
myDb.AddInParameter(cmd, "?", DbType.Guid, New Guid("{325D729A-5C4C-4AA2-B0ED-2A2304A5CEF7}"))

Dim dsTest As New t15DataSet
Dim drTest As t15DataSet.FLASHCARDRow
myDb.LoadDataSet(cmd, dsTest, "FLASHCARD") <----Works
drTest = dsTest.FLASHCARD.Rows(0) <-----Works

Console.WriteLine(drTest.FlaschardName & " " & " " & drTest.Explanation) <----Works
Console.Write(drTest.FlashcardID.ToString) <----THOWS EXCEPTION

The exception is "Specified Cast Is Not Valid." It occurs in the Get for FlashcardId, which is the PK:
Public Property FlashcardID() As System.Guid
Get
Return CType(Me(Me.tableFLASHCARD.FlashcardIDColumn), System.Guid) <----THOWS EXCEPTION
End Get

Everything works fine in SQL Server with an "Upsized" version of the same db. (I just used the built in access->sql Server tool).
Oct 31, 2007 at 12:36 PM
Hi Chris,

If the type cast is failing, what is the actual type for the FlashcardIDColumn after you load the DataSet?

In any case, have you considered using SQL Server Express for your entry level version instead of MS Access? You can redistribute it with your app (http://www.microsoft.com/sql/editions/express/default.mspx).

Regards,
Fernando
Oct 31, 2007 at 7:58 PM
The type appears to be GUID.
Here is the getter/setter with a few debug statments:

<System.Diagnostics.DebuggerNonUserCodeAttribute()>
Public Property FlashcardID() As System.Guid
Get
If Not Me.tableFLASHCARD.FlashcardIDColumn Is Nothing Then 'THIS BLOCK ADDED BY ME
Console.WriteLine(Me.tableFLASHCARD.FlashcardIDColumn.GetType.ToString)
Console.WriteLine(Me.tableFLASHCARD.FlashcardIDColumn.ToString)
Console.WriteLine(Me.tableFLASHCARD.FlashcardIDColumn.DataType.ToString)
End If 'END BLOCK ADDED BY ME
Return CType(Me(Me.tableFLASHCARD.FlashcardIDColumn), System.Guid)
End Get
Set
Me(Me.tableFLASHCARD.FlashcardIDColumn) = value
End Set
End Property

The outputs are:
System.Data.DataColumn
FlashcardID
System.Guid

Which looks normal to me. Remember, all other fields are returned fine.

Could there be a difference in the way GUIDs are handled by the SQL provider vs the Access Jet provider?

The command paramater is :
myDb.AddInParameter(cmd, "?", DbType.Guid, New Guid("{325D729A-5C4C-4AA2-B0ED-2A2304A5CEF7}"))

If I try changing it to :
myDb.AddInParameter(cmd, "?", Data.OleDb.OleDbType.Guid, New Guid("{325D729A-5C4C-4AA2-B0ED-2A2304A5CEF7}"))

I receive "No mapping exists from DbType 72 to a known OleDbType." when it tries to execute that line


Any thoughts are appreciated.

As to SQL Server Express, we are considering it. It has a lot of advantages, but the size may be a problem. We are also looking at VistaDb.

Thanks
Chris
Oct 31, 2007 at 10:01 PM
Hi,

The type you need to know to understand why the cast is failing is shown by Console.WriteLine(Me(Me.tableFLASHCARD.FlashcardIDColumn).GetType.ToString).

Fernando

Nov 2, 2007 at 5:51 PM
Thanks,

hmmm...
When it connects to the access version, it returns System.DBNull

But when it connects to the SQL Express version, with an identical db and code, it returns System.guid.

I know access is understanding the query fine because 1) I used the access deigner to create it nd 2) it runs fine in access.

I don't get it.
Nov 2, 2007 at 6:00 PM
BTW -
It seems to work fine in VistaDb. I think we will probably just switch to vista during development.
But I am still curious, and would like to understand this issue for projects, or incase VistaDb ends up having problems of its own.