Datatable Data Types

[Shaggy Hiker]

This thread is related to this other thread:

https://www.vbforums.com/showthread....nge&highlight=

In that other thread, my issue was that WriteXML was writing a bunch of type information for GUID fields, and that type information appeared to be specific to the .NET version...to some extent, such that a GUID exported from .NET 8 via XML was not the same as a GUID imported by Framework.

The solution was in the final post of that thread, but it turns out to have been a bit stranger than I realized.

The dataset being exported has half a dozen tables, all of which have at least one GUID fields and some have up to six or seven. For all of the tables, the PK is a GUID, but there are usually non-PK fields that are GUIDs, as well.

What I have found is that some GUID fields will import, but others will not, and it isn't consistent. Each export is a survey, and all the different items that hang off that survey. All surveys have the same schema, just different data, and different numbers of rows in child tables. What I am seeing is that some surveys import fine, and for others, some GUID fields come across as Nulls. If that happens for a field that is a PK, or any other non-nullable field, that's a disaster.

So, the solution in the other thread was that I created the target dataset, which I did with a series of statements like this:

 da.SelectCommand.CommandText = " SELECT * FROM STRM_Survey_Waypoints WHERE 1=0"
 da.Fill(existingDS, "STRM_Survey_Waypoints")

Basically, I was just getting an empty table. Looking at the columns in this table, they were properly identified. The PK was type GUID, and so forth for all other columns in the table. Of course, this table was one of the ones where I got nulls for the PK.

My first thought was to create the table directly, rather than through a query, such that I could control the types. This is tedious, but possible:

Dim dt As New DataTable("STRM_Survey_Waypoints")
Dim col = New DataColumn("WaypointID", GetType(Guid))
col.AllowDBNull = False
dt.Columns.Add(col)

dt.Columns.Add(New DataColumn("SurveyID", GetType(Guid)))
'.etc, several more columns

At first, I made the WaypointID a string, which worked...except that the field was a string, when it needed to be a GUID.

I then changed to the code shown, where it IS a GUID, and it still worked.

So, if I created the table by querying the DB where WaypointID is a GUID, the datatable has the type for the WaypointID column as a GUID, but all incoming Waypoints come in as Null. If I create the table directly, as shown, while setting the column type as GUID and not nullable, then it works fine.

That means that I can solve this with the tedious process of creating every table by hand, but I'm trying to understand WHY creating it by hand as a GUID works, while creating it via querying the underlying table does not always work (most fields work, and all fields work for some surveys, but not all).

Any suggestions?

[digitalShaman]

Is the issue replicable? I.e. if you do the same twice, are the same records and fields showing up as NULL?
Have you verified that the GUIDs actually ARE there in table STRM_Survey_Waypoints prior to writing?
Have you verified that the GUIDs actually are in the XML file, i.e. have been written by WriteXML?

[Shaggy Hiker]

Yes to all questions. The GUIDs are there on export, they are there on import (because they come in correctly when I create the table fields manually), and it does appear to be repeatable. If a survey has a problem one time, it will have that problem every time. If a survey has no problem...well, I guess I can't say that it won't later have a problem. There is no difference in how good and bad GUIDs are created, nor is there any obvious difference between them.

However, as I was writing that, I did think of some other things to check, though they are unlikely to matter. XML is fundamentally a string. Perhaps there's a difference between the GUIDs that work and those that do not work, such as, perhaps one starts with a digit, and the other starts with a letter.? I'll have to look for differences like that.

[Shaggy Hiker]

The situation is weirder than I initially realized.

With minimal testing, I found that there are two tables where the issue is occurring on. Whether or not it is really happening there, or happening everywhere else, but those are just the first two, I don't know and it doesn't really matter. The first table can be called W, the second can be called O. If W isn't right, the code fails before O is reached. If W is right, then the code fails on O. If O were right, perhaps it would fail on T, or R, or some other table, it doesn't really matter, because it never gets past W or O.

My first thought was that there was some pattern to the GUIDs that fail, such as that they all begin with letters (since to be passed in XML they are all strings), but that's not the case. The failures are on GUIDS that start with numbers.

I then thought that I could simplify things, by filling the datatable with SELECT *, then dropping the columns that were GUIDs and re-adding them. That worked fine for W, and the data came through. I then tried the same for O, but...it did something different. While the GUID fields now DID come through, all the other fields were Null, which was not right. Those other fields were mostly integers with a couple strings (which were, in fact, Null, so that was fine).

So, for W, dropping the GUID columns and adding them back in was sufficient, but for O, I had to build the entire table or else I would get ONLY the GUID columns. However, if I did build the table, rather than creating it with a SELECT statement, then all the data came through just fine.

Therefore, the solution is clear: Build all the tables and all will be well. What I don't understand is WHY?