locked
OleDbDataReader getting field type and max length RRS feed

  • Question

  • User983388305 posted

    HI all, below i have code where im trying to get a fields type and size. I am pretty sure gettype is going to give me the db type. But i cant figure out the max length in the database. Now i dont want the length of the data inside i want the designed max length info, i wanna get from varchar(100) that 100 max limit. So i can set it in the coding to limit that. and if field length gets changed by db admin later on i dont have to change the max length limitation in coding. please help [;)][:(][:P]

    Dim Conn As OleDbConnection

    Dim sSQL As String

    Dim DBRdr As OleDbDataReader

    Dim cmd As New OleDbCommand

     sSQL = "select * from table"

    cmd = New OleDbCommand(sSQL, Conn)

    DBRdr = cmd.ExecuteReader()

     dbrdr.read()

    dbrdr("field"). get the size and length here but how

    Wednesday, February 6, 2008 7:22 PM

Answers

  • User187056398 posted

    That's a noble endeavor but sometimes the cure is worse than the illness.

    This link shows how to get Access schema information:   http://aspalliance.com/542

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2008 10:08 PM
  • User983388305 posted

    HI thanks for the response,

     I was so focus on getting the info while i was doing the selection i didnt look for other methods. After reading the schema coding,

    I found sp_colums SP which gave all i need. Here is the code i used

    ssql = "exec sp_columns @table_name = N'tablename', @column_name = '" & xxxx & "'"

    cmd = New OleDbCommand(ssql, conn)

    DBRdr = cmd.ExecuteReader()

    While DBRdr.Read

    If DBRdr("TYPE_NAME") = "varchar" Then

    xObj.MaxLength = DBRdr("LENGTH")

    ElseIf DBRdr("TYPE_NAME") = "nvarchar" Then

    xObj.MaxLength = DBRdr("Precision")

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 7, 2008 1:04 PM

All replies

  • User187056398 posted

    That's a noble endeavor but sometimes the cure is worse than the illness.

    This link shows how to get Access schema information:   http://aspalliance.com/542

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2008 10:08 PM
  • User983388305 posted

    HI thanks for the response,

     I was so focus on getting the info while i was doing the selection i didnt look for other methods. After reading the schema coding,

    I found sp_colums SP which gave all i need. Here is the code i used

    ssql = "exec sp_columns @table_name = N'tablename', @column_name = '" & xxxx & "'"

    cmd = New OleDbCommand(ssql, conn)

    DBRdr = cmd.ExecuteReader()

    While DBRdr.Read

    If DBRdr("TYPE_NAME") = "varchar" Then

    xObj.MaxLength = DBRdr("LENGTH")

    ElseIf DBRdr("TYPE_NAME") = "nvarchar" Then

    xObj.MaxLength = DBRdr("Precision")

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 7, 2008 1:04 PM