I've moved from VB & Access to VBNet & SQLServer. I'm trying to find out how to retrieve field names, data types and string lengths for a table.

With VB & Access it was easy to loop threw a table's fields and retrieve the field types, etc., with VBNet & SQLServer it is not as intuitive (an understatement).

Are the field names, data types & string lengths stored in a dataset (VBNet) along with the data? If so how does one access them?

I can loop threw a dataset to retrieve the data i.e.:

Thedata = dataset1.Tables(0).Rows(0).Item(0)

Any suggestions on how to get the rest of the information? (field name, data type, field length)

Thanks in advance for your help.
Jack

I don't know much about VB.Net yet, but you can always query for the information:

SELECT sysColumns.Name, sysTypes.Name 'DataType', sysColumns.Length, sysColumns.Prec, sysColumns.Scale, sysColumns.IsNullable
FROM sysObjects (NOLOCK)
    JOIN sysColumns (NOLOCK)
        ON sysObjects.Id = sysColumns.Id
    JOIN sysTypes (NOLOCK)
        ON sysColumns.xType = sysTypes.xType
WHERE sysObjects.Name = 'myTable' -- Name of your table.
 AND sysObjects.Type = 'U' -- U = User Defined Table.
ORDER BY sysColumns.ColOrder

Note: When you do this, you will need to understand when to look at Perc, and when to look at Length.



John R Lick
[email protected]

I was thinking along the lines of if a database's table's data is captured in a dataset(VBNet) then the structure of the tables fields might also be captured in the same dataset. If this is the case, with one VBnet dataset I would have all of the info on the captured table's fields' structure that I would need without having to requery the database. Otherwise...

Your SQL statement is greatly appriciated. It saved me a whole lot of time experimenting with a query(s) to get it to work right. I'm not sure what you mean by "Length" & "Perc", but I'm going to plug in a table name and see what results I get. Thanks again. Jack

Text based data types: (char, nchar, varchar, nvarchar, etc)
Length: Max number of characters for the column.
Precision: Not useful
Scale: Not useful
Numeric data types: (int, bigint, smallint, tinyint, numeric, decimal, money, etc)
    Length: Number of bytes used to store the number.
    Precision: Number of digits (not including the “-“ for negative numbers)
    Scale: Number of digits after the decimal point
Special Numeric: (real, float) (I have only used the numeric, decimal, or money data types to store decimal values)
    Length: ?
    Precision: ?
    Scale: ?


John R Lick
[email protected]

Hi, you might want to look into strongly typed datasets if you havent already.

Basically, an XML schema is created containing all the information about a the data structure the dataset is replicating then at design time you get intellisense and can use field names rather than ordinal values (dataset.tablename then . will get a list of fields that you can choose from and it knows if you try to reference a field that isnt there). Also, you get compilation errors if you try to set an invalid data type to a field.

You should be able to get more information about structures if you use strongly typed datasets.

HTH