Default value of a Table in Sql server To default value in DataSet scheme

  • Thread starter Thread starter itzik
  • Start date Start date
I

itzik

hello

I wanted to ask how to get the default value defined in the DataBase,
to be defined as the default value in the DS scheme?

and if there isn't a way, how else can i do that?

thanks.
 
itzik:

I'm not sure I follow you. You may or may not even have a dataset that
matches the whichever table you are defaulting to. An adapter is going to
move data from the database to the dataset or datatable - that'll will be
defined in the SelectCommand - so you can point it to any dataset you want.
 
Hi,

One way to accomplish this if you are using Sql Server is by querying the
INFORMATION_SCHEMA.COLUMNS. I have provided a sample query below.

I hope this helps.
----------------------------

SELECT c.COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.COLUMN_DEFAULT IS NOT NULL
and c.TABLE_NAME = '<tablenamegoeshere>'
AND C.COLUMN_NAME = '<columnnamegoeshere>'
 
After another look at your post I am not sure of what you are asking. If you
are wanting to place default values on your datatable you will have to code
around what you find in the information schema to accomplish this. If this
does not answer your question please reply to one of the postings to clarify
what you are asking. Then the group may help you more.

Good Luck.
 
I think I have the same problem.
I have a table created in sql server with fields which are defined as Not
Null and have default values:

CREATE TABLE [table1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[str] [char] (10) default 'erkan',
[x] [char] (10)
) ON [PRIMARY]

when I try to drop this table from Server Explorer into dataset the system
generates the xml code but the definition of the field [str] does not
contain defult atttribute.

How can this issue be solved?
 
Hi Erkan,

You can manually edit the .xsd file to return a default value. I have
posted a link to instructions on how to do this from the MSDN.

I hope this helps.
 
Thank you for your attention.
But this is not the solution of my problem. This is the solution of (next
step) code generation process.
I mean if I do the things that is mentioned in your link with correct
definition in xsd file then the code generation process creates lines for
"default" attribute and/or nullValue well.
But in my case I have about 100 fields in table and all of them were created
as Not Null & with a default value. When I drag & drop this table from
Server Explorer into DataSet, the system (I think Visual Studio .NET or an
add-in) generates xml definition of the table and the fields with all
attributes except default attiribute.
I want to know how can I create the xml file with the default values of the
fields.

I hope I could explain more clearly.
 
Well, thank you all for your posts.
Mr. Erkan clarified my problam exactly.

i need to do this automaticaly, i can't edit the xsd everytime i need
to re-create the schemes (once in a while the db may change).

so what i finally did, is to query the sysobjects table in my sql
server.
this is the query:

select dbo.syscomments.text, sysobjects.Name as TableName,
dbo.syscolumns.* from dbo.syscolumns
inner join dbo.syscomments on cdefault = dbo.syscomments.ID
inner join sysobjects on sysobjects.id = syscolumns.id
where sysobjects.name = 'ANY_TABLE_NAME'

so i ran in a loop through all my tables, query the systable to get
any default columns, and then i can do set the default (object) to the
column in the dataset.

i knew in the first place that this is an option, but i thought maybe
there is way to do that "from the box" in .NET (so, i guess there
isn't?)

thank you all.
 
Hi Erkan,

AFAIK there is no way to do this using the designer/wizard. You will have
to write your own functionality to derrive the default data from the database
and populate your .xsd file with it. This shouldn't be much of a problem
though if you use the query that I put in an earlier posting and look at the
link that was provided to set the defaults. The dataset can be created in
many ways (e.g. XSD.exe, Drag and Drop designer, custom written, etc...).
Once you have this under your belt then you can decide if the tool that you
write will add the defaults after the datasets have been created (this would
help you for all previous datasets that have been generated) or on the fly.

I hope this helps.
--------------------------------------
 
Back
Top