Read and write a Constraint or Default Value

Okay, maybe I'm getting ahead of myself.

Using SQL Server Express, VWD and .net 2.0 I've figured out how to drop a Table Column Constraint or Default Value/Binding and then Create it again using a stored procedure. What I can't figure out is how to retrieve that column's constraint value and write it to, say a label, in an aspx page, simply for reference. Is it possible? In this case the Data Type of the column is money.

I'm using it to perform a calculation to a column with a value that the user inserts into another column. (Column1(user input) minus Column2(with Default Value) = Column3(Difference). I just want to read Column2's Default Value for reference so I know whether to change it or not.

Tables have Check Constraints, Columns do not.

Open up the Master database and take a look inside.

You are looking for the INFORMATION_SCHEMA views.

In particular, you want this one: INFORMATION_SCHEMA.CHECK_CONSTRAINTS and INFORMATION_SCHEMA.COLUMNS

|||

If I open dbo.tbl1 in my database and right click to modify, I can put a Default Value or Binding of 1000000.00(or whatever) in a column I've named "Gen_ourlim". When I do that, there is, in the folder "Constraints" under dbo.tbl1, something created called DF_tbl1_Gen_ourlim. When I drop that Default Value from column "Gen_ourlim" the DF_tbl1_Gen_ourlim goes away in the Constraint folder. That's why I called the column having a constraint I suppose. In any case, how can I read that Default Value and write it to a web page?

|||

Did you query INFORMATION_SCHEMA.COLUMNS?

The default value is a column in that view, as is the catalog name, the schema name, the table name and the column name. Plus lots of other goodies about a column.

Querying data and placing it on a web page is an entirely different discussion and belongs in one of the web forums.

|||

Thanks David,

Got it. At least I was able to query it:

USE <path to database>

SELECT Column_Name, Column_Default

frominformation_schema.columnswhere table_name='tbl1'and Column_name='Gen_ourlim'

|||

Then you need to mark this thread as resolved, mark answers as appropriate, and start a new thread in a nore appropriate forum on how to put data on a web page.

(But the internet is chock full of how-to articles on that, and any beginner asp.net book will tell you how also. Best to start doing it and ask specific questions when you get stuck.)

|||

Will do David. Thanks again. Using the query in VWD is the easy part. Put the Select in a stored procedure in your Database Explorer as such:

ALTER PROCEDURE dbo.GetDefaultValue

AS
SELECT COLUMN_NAME, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME ='tbl1') AND (COLUMN_NAME = 'Gen_ourlim')
RETURN

Put a SqlDataSource on your page along with a DataView and then configure it to use the stored procedure as the Select in the SqlDataSource. There's a nice little video tutorial on doing this at

http://www.asp.net/learn/sql-videos/video-114.aspx

sql