Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > VB.NET 2002/2003 Basics
|
VB.NET 2002/2003 Basics For coders who are new to Visual Basic, working in .NET versions 2002 or 2003 (1.0 and 1.1).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET 2002/2003 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 29th, 2003, 07:07 PM
Registered User
 
Join Date: Jun 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Finding Field Type & string length From dataset?

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
 
Old June 30th, 2003, 09:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 101
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jlick
Default

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
JohnRLick@hotmail.com
 
Old July 7th, 2003, 10:45 PM
Registered User
 
Join Date: Jun 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 8th, 2003, 09:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 101
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jlick
Default

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
JohnRLick@hotmail.com
 
Old July 9th, 2003, 04:53 PM
Authorized User
 
Join Date: Jun 2003
Posts: 33
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Quote:
quote:Originally posted by Jackie
 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








Similar Threads
Thread Thread Starter Forum Replies Last Post
finding max string length if more than 2 elements srini XSLT 4 April 4th, 2006 01:08 PM
Chap 3, "Length of String" richajos BOOK: Beginning Visual Basic 2005 ISBN: 978-0-7645-7401-6 1 February 25th, 2006 08:38 PM
String Type Statement to convert field CORiverRat Access 5 December 15th, 2005 02:13 AM
Determining the length of a database field john_reeve41 BOOK: Beginning ASP 3.0 2 February 26th, 2004 12:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.