How do I get numericScale, precision and size from a DataColumn

[Mark Neilson]

I want to be able to populate all of the properties of a SqlParameter for
updating via stored procedure by passing in a datatable object.

I was able to do this easily under ADO as the Fields collection of an ADO
recordset would give me all of this.

The closest I can find is a DataColumn object but I am missing NumericScale,
Precision and Size.

I expect the stored procedure will fail if it does not get this info for say
a Decimal parameer.

Any way of being able to gather this information this way?

 

[Miha Markic]

Hi Mark,

Yes, DataColumn doesn't hold such info.
You might query the database (OleDbConnection.GetOleDbSchemaTable or
something else) instead or create parameters at design time.

 

[Kevin Yu [MSFT]]

Thanks for Miha's quick response.

Hi Mark,

Thank you for using MSDN Newsgroup! My name is Kevin, and I will be
assisting you on this issue.

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get the precision and scale
information about the decimal.

If you're working on a SQL Server as the database, please try to call the
stored procedure named "sp_columns" to get a table with information about
the columns in the database. Here's a simple example:

EXEC sp_columns @table_name = 'Table1', @column_name = 'Column1'

This SQL statement will get the information about "Column1" in the "Table1"
table. The precision and scale property you need to get is in the Precision
and Scale column. For more information, please check the SQL Book Online.

Also OleDbConnection.GetOleDbSchemaTable as Miha mentioned is a good idea
to achieve this. The following code will get the same result as the SQL
statement mentioned above. The Precision and Scale information will be put
in the "NUMERIC_PRCISION" and "NUMERIC_SCALE" columns.

Dim t As DataTable =
Me.OleDbConnection1.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "Table1", "Column1"})

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."