Re: Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

In SQLite type guid does not exist. Look at this documentation:
https://www.sqlite.org/datatype3.html

Affinity for guid would be BLOB but in trigger you store TEXT.
I do not use .NET but I think you should use different parameter
type. I would use TEXT type.

Regards R.A.


On 14.02.2020 15:09, Andy KU7T wrote:
> Hi,
>
> I am having trouble searching for a Guid. I think some conversion is missing.  I wonder if someone can point me to the error…
>
> Column:
> [ID] GUID(16)
> Index:
> CREATE UNIQUE INDEX [ID_INDEX] ON [DXLOG]([ID] COLLATE [BINARY] ASC);
> Trigger:
> CREATE TRIGGER [AUTOGENERATE_ID] AFTER INSERT ON [DXLOG] FOR EACH ROW WHEN ([NEW].[ID] IS NULL)
> BEGIN
>    UPDATE
>      [DXLOG]
>    SET
>      [ID] = HEX (RANDOMBLOB (16))
>    WHERE
>      [NEW].[ID] IS NULL AND [DXLOG].[ROWID] = [NEW].[ROWID];
> END;
>
> Inserting a row simply will create a new guid for me. Good. Reading it like this:
>
> SELECT ID FROM [Dxlog] WHERE RowId = @RowID
>
> Where @RowId is the LastInsertedRowId.
> And getting it in code:
>
> Dim rdr2 As SQLiteDataReader = Nothing
> rdr2 = theDB.ExecuteQuery("SELECT ID FROM [Dxlog] WHERE RowId = @RowID", sqlParameters)
> rdr2.Read()
> Me.mvarId = rdr2.Item("ID")
>
> The returned ID is indeed a Guid. In SQLite it looks like this:
>
> 40FD6722384053ED3BA45CD1C5FDB30D
> And in .NET: {40fd6722-3840-53ed-3ba4-5cd1c5fdb30d}
>
> Now, when I am trying to search for it like this, I get nothing:
>
>                      sqlParameters = New List(Of SQLiteParameter)()
>                      AddSqlParam(sqlParameters, "@ID", DbType.Guid, Me.Id)
>
>                      rdr2 = theDB.ExecuteQuery("SELECT * FROM [Dxlog] WHERE ID = @ID", sqlParameters)
>                      rdr2.Read()
>
>
> Do I have to converte the Guid to binary somehow?
>
> Thanks
> Andy
>
> Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users