GPSAddict
asked on
Datasource column size (max length)
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
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
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")
ASKER
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
sybe: anymore documentation on that ? I'm not using tableadapters or anything.
Thanks
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.
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
ASKER
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
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(sConnectionS tring)
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(sConnectionS
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
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
ASKER
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()
ASKER
Hello ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
size = dataTables(0).Columns(0).M