Solved

Datasource column size (max length)

Posted on 2009-04-09
11
794 Views
Last Modified: 2013-11-26
Hi,
Is it possible to retrieve the datasource columns max length, as specified in the db, through VB.Net code ? If so what would it be ? I don't need a loop, I need to be able to only work with the columns I want either with some sort of an index or with the column name. Thanks
0
Comment
Question by:GPSAddict
  • 4
  • 3
  • 3
11 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 24106762
Did you take a look at
size = dataTables(0).Columns(0).MaxLength
0
 
LVL 28

Expert Comment

by:sybe
ID: 24106788
Starting from a SqlDataReader, you need to get the Schema


oDR = oCmd.ExecuteReader()

oSchema = oDR.GetSchemaTable()
 

' get the columnsize for field by (numeric) index

iColumnSize = oSchema.Rows.Item(index)("ColumnSize")

Open in new window

0
 

Author Comment

by:GPSAddict
ID: 24106832
Dhaest: datables(0), 0 beeing ??? I have many sql datasources, how to reference the good one ?
sybe: anymore documentation on that ? I'm not using tableadapters or anything.
Thanks
0
 
LVL 28

Expert Comment

by:sybe
ID: 24106914
I could point you to some Googles links on "GetSchemaTable()", but I guess those are easy to find by yourself.

The few lines of code I posted are useful to me, in the sense that sometimes I need to auto-truncate submitted texts. For that I need to compare the length of the text with the column size of string-fields in the database (char, varchar etc). After some browsing around, I puzzled the code together bit by bit.

I rewrote that code as below, to make clear how to get the max length.



Dim oSchema As DataTable

Dim oCmd As New SqlCommand(sSQL, oSQLConnection)

Dim oDR As SqlDataReader

oDR = oCmd.ExecuteReader()

oSchema = oDR.GetSchemaTable()
 
 

' loop through the fields (not rows) in the SQLDataReader

Dim i As Integer

Dim sFieldName As String

Dim iColumnSize As Integer
 

For i = 0 To oDR.FieldCount - 1

    sFieldName = oDR.GetName(i)

    iColumnSize = oSchema.Rows.Item(i)("ColumnSize")

    If oSchema.Rows.Item(i)("DataType").ToString = "System.String" Then

        Response.write "field of name = " & sFieldName & " is of type String, and has a maximal length of " & iColumnSize & "<br>"

    End If

Next

Open in new window

0
 

Author Comment

by:GPSAddict
ID: 24107020
Thanks, my sqldatasource is named SubCatDataSource. Now I didn't write a single line of code and whent he page loads, the select query is returned. Since everything is embedded through the objects, I tried, in the form load even, the code below. It doesn't work...Any ideas why ? Thanks

    Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim myReader As SqlClient.SqlDataReader = SubCategoryDataSource.SelectCommand.ExecuteReader()
 

    End Sub

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 28

Expert Comment

by:sybe
ID: 24107105
I don't know about that. It seems a totally different question.
But in my code, the connection if created by a line like this, not from a datasource in the page. I don't know if it is possible to create a connection object from that.

New SqlConnection(sConnectionString)
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24115378
Can you try the following:

How To Retrieve Column Schema Using the DataReader GetSchemaTable Method and Visual Basic .NET
http://support.microsoft.com/kb/310108

http://vb.net-informations.com/ado.net-dataproviders/ado.net-schema-information-sqldatareader.htm
Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim myReader As SqlClient.SqlDataReader = SubCategoryDataSource.SelectCommand.ExecuteReader()

 'Retrieve column schema into a DataTable. 

schemaTable = myReader.GetSchemaTable() 
 

'For each field in the table... 

For Each myField As DataRow In schemaTable.Rows 

    'For each property of the field... 

    For Each myProperty As DataColumn In schemaTable.Columns 

        'Display the field name and value. 

        Console.WriteLine((myProperty.ColumnName & " = ") + myField(myProperty).ToString()) 

    Next 

    Console.WriteLine() 

    

    'Pause. 

    Console.ReadLine() 

Next 
 

    End Sub

Open in new window

0
 

Author Comment

by:GPSAddict
ID: 24151162
Thanks daest, I've read those links but I am unable to work it through the datasource object. Using your code, it fails on the below line. VS.NET won't let me write .ExecuteReader at all. All the rest seems ok. Anymore ideas ?
Dim myReader As SqlClient.SqlDataReader = SubCategoryDataSource.SelectCommand.ExecuteReader()

Open in new window

0
 

Author Comment

by:GPSAddict
ID: 24294331
Hello ?
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 24295237
Dim cmd As New SqlCommand("select * from yourtable", cn)
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
...
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now