constructing varchar computed column , using bit fields

Hi,
Due to a certain circumstance i have to add a field to my table, which would be a varchar type. this varchar contructs istfel using 4 fields of type bit

so if the table def goes as
isA(bit), isB(bit), isC(bit), isD(bit), ABCD(varchar(4))

the value of the field ABCD should be constructued from the 4 bit fields
if isA,isC is checked then ABCD should be AC
if isA,isB,isD is checked then ABCD should be ABD
is there a way i could achieve it using computed columns or would i have to make a trigger..(which i am also not sure on how to go about it)

Sorry for the trouble, but i couldnt get my head around with starting it either !

thankyouHi,
Updating on what i have done
Created a trigger on insert,update
and with a series of IF statements i contructed the varchar field based on if the bit fields were set to true.

constructing strings from table

Hi friends,
please help me in selecting values from the table
the record is as follows:
Id HomePhone WorkPhone Mobile Email
20 2323223 323232232 test@.test.com
i have to select values as follows.
Id DeviceType DeviceInfo
20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
the one solution is:
select
'HomePhone, Mobile, Email' AS DeviceType
HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
DeviceInfo
from table where Id = 20
but here the work phone number is not available so that information
has to be truncated...
Thanks in Advance
Arunkumar.D
Use the same thinking as was suggested in my earlier reply:
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NOT NULL THEN 'WorkPhone' + ', ' ELSE '' END + 'Mobile' AS
DeviceType
,HomePhone + ', ' + COALESCE(WorkPhone + ', ', '') + Mobile
FROM
(
SELECT 20 AS No, '23232' AS HomePhone, NULL AS WorkPhone, '98327' AS Mobile
) AS x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1179848438.242226.4580@.n15g2000prd.googlegrou ps.com...
> Hi friends,
> please help me in selecting values from the table
> the record is as follows:
> Id HomePhone WorkPhone Mobile Email
> 20 2323223 323232232 test@.test.com
> i have to select values as follows.
> Id DeviceType DeviceInfo
> 20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
>
> the one solution is:
> select
> 'HomePhone, Mobile, Email' AS DeviceType
> HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
> DeviceInfo
> from table where Id = 20
> but here the work phone number is not available so that information
> has to be truncated...
> Thanks in Advance
> Arunkumar.D
>

constructing strings from table

Hi friends,
i have to select values from the table consider with the following
record:
No HomePhone WorkPhone Mobile
20 23232 98327
the selection have to be done as:
No DeviceType DeviceInfo
20 HomePhone, Mobile 23232, 98327
since there is no value for workphone it have to be removed in both
devicetype and deviceinfo column.
Thanks
Arunkumar.D
No DDL = not tested...
Something like (assuming NULL when you say "no value"):
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NULL THEN 'WorkPhone' + ', ' ELSE '' END + 'Mobile' AS
DeviceType
,HomePhone + ', ' + COALESCE(WorkPhone + ', ', '') + Mobile
FROM ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1179849918.549063.61900@.n15g2000prd.googlegro ups.com...
> Hi friends,
> i have to select values from the table consider with the following
> record:
> No HomePhone WorkPhone Mobile
> 20 23232 98327
> the selection have to be done as:
> No DeviceType DeviceInfo
> 20 HomePhone, Mobile 23232, 98327
> since there is no value for workphone it have to be removed in both
> devicetype and deviceinfo column.
> Thanks
> Arunkumar.D
>
|||> i have to select values from the table consider with the following
> record:
> No HomePhone WorkPhone Mobile
> 20 23232 98327
> the selection have to be done as:
> No DeviceType DeviceInfo
> 20 HomePhone, Mobile 23232, 98327
What about something like:
SELECT No,
CASE WHEN HomePhone IS NULL THEN '' ELSE 'HomePhone' END +
CASE WHEN WorkPhone IS NULL THEN '' ELSE 'WorkPhone' END +
CASE WHEN Mobile IS NULL THEN '' ELSE 'Mobile' END
AS DeviceType,
CASE WHEN HomePhone IS NULL THEN '' ELSE HomePhone END +
CASE WHEN WorkPhone IS NULL THEN '' ELSE WorkPhone END +
CASE WHEN Mobile IS NULL THEN '' ELSE Mobile END
AS DeviceInfo
FROM TableName
(You will need to add a bit of logic to work out where to put the commas)
Rgds,
Gavin

constructing strings from table

Hi friends,
please help me in selecting values from the table
the record is as follows:
Id HomePhone WorkPhone Mobile Email
20 2323223 323232232 test@.test.com
i have to select values as follows.
Id DeviceType DeviceInfo
20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
the one solution is:
select
'HomePhone, Mobile, Email' AS DeviceType
HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
DeviceInfo
from table where Id = 20
but here the work phone number is not available so that information
has to be truncated...
Thanks in Advance
Arunkumar.DUse the same thinking as was suggested in my earlier reply:
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NOT NULL THEN 'WorkPhone' + ', ' ELSE '' END + 'Mobile' AS
DeviceType
,HomePhone + ', ' + COALESCE(WorkPhone + ', ', '') + Mobile
FROM
(
SELECT 20 AS No, '23232' AS HomePhone, NULL AS WorkPhone, '98327' AS Mobile
) AS x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1179848438.242226.4580@.n15g2000prd.googlegroups.com...
> Hi friends,
> please help me in selecting values from the table
> the record is as follows:
> Id HomePhone WorkPhone Mobile Email
> 20 2323223 323232232 test@.test.com
> i have to select values as follows.
> Id DeviceType DeviceInfo
> 20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
>
> the one solution is:
> select
> 'HomePhone, Mobile, Email' AS DeviceType
> HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
> DeviceInfo
> from table where Id = 20
> but here the work phone number is not available so that information
> has to be truncated...
> Thanks in Advance
> Arunkumar.D
>

constructing strings from table

Hi friends,
i have to select values from the table consider with the following
record:
No HomePhone WorkPhone Mobile
20 23232 98327
the selection have to be done as:
No DeviceType DeviceInfo
20 HomePhone, Mobile 23232, 98327
since there is no value for workphone it have to be removed in both
devicetype and deviceinfo column.
Thanks
Arunkumar.DNo DDL = not tested...
Something like (assuming NULL when you say "no value"):
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NULL THEN 'WorkPhone' + ', ' ELSE '' END + 'Mobile' AS
DeviceType
,HomePhone + ', ' + COALESCE(WorkPhone + ', ', '') + Mobile
FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1179849918.549063.61900@.n15g2000prd.googlegroups.com...
> Hi friends,
> i have to select values from the table consider with the following
> record:
> No HomePhone WorkPhone Mobile
> 20 23232 98327
> the selection have to be done as:
> No DeviceType DeviceInfo
> 20 HomePhone, Mobile 23232, 98327
> since there is no value for workphone it have to be removed in both
> devicetype and deviceinfo column.
> Thanks
> Arunkumar.D
>|||> i have to select values from the table consider with the following
> record:
> No HomePhone WorkPhone Mobile
> 20 23232 98327
> the selection have to be done as:
> No DeviceType DeviceInfo
> 20 HomePhone, Mobile 23232, 98327
What about something like:
SELECT No,
CASE WHEN HomePhone IS NULL THEN '' ELSE 'HomePhone' END +
CASE WHEN WorkPhone IS NULL THEN '' ELSE 'WorkPhone' END +
CASE WHEN Mobile IS NULL THEN '' ELSE 'Mobile' END
AS DeviceType,
CASE WHEN HomePhone IS NULL THEN '' ELSE HomePhone END +
CASE WHEN WorkPhone IS NULL THEN '' ELSE WorkPhone END +
CASE WHEN Mobile IS NULL THEN '' ELSE Mobile END
AS DeviceInfo
FROM TableName
(You will need to add a bit of logic to work out where to put the commas)
Rgds,
Gavin

constructing strings from table

Hi friends,
please help me in selecting values from the table
the record is as follows:
Id HomePhone WorkPhone Mobile Email
20 2323223 323232232 test@.test.com
i have to select values as follows.
Id DeviceType DeviceInfo
20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
the one solution is:
select
'HomePhone, Mobile, Email' AS DeviceType
HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
DeviceInfo
from table where Id = 20
but here the work phone number is not available so that information
has to be truncated...
Thanks in Advance
Arunkumar.DUse the same thinking as was suggested in my earlier reply:
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NOT NULL THEN 'WorkPhone' + ', ' ELS
E '' END + 'Mobile' AS
DeviceType
,HomePhone + ', ' + COALESCE(WorkPhone + ', ', '') + Mobile
FROM
(
SELECT 20 AS No, '23232' AS HomePhone, NULL AS WorkPhone, '98327' AS Mobile
) AS x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1179848438.242226.4580@.n15g2000prd.googlegroups.com...
> Hi friends,
> please help me in selecting values from the table
> the record is as follows:
> Id HomePhone WorkPhone Mobile Email
> 20 2323223 323232232 test@.test.com
> i have to select values as follows.
> Id DeviceType DeviceInfo
> 20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
>
> the one solution is:
> select
> 'HomePhone, Mobile, Email' AS DeviceType
> HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
> DeviceInfo
> from table where Id = 20
> but here the work phone number is not available so that information
> has to be truncated...
> Thanks in Advance
> Arunkumar.D
>

constructing strings from table

Hi friends,
i have to select values from the table consider with the following
record:
No HomePhone WorkPhone Mobile
20 23232 98327
the selection have to be done as:
No DeviceType DeviceInfo
20 HomePhone, Mobile 23232, 98327
since there is no value for workphone it have to be removed in both
devicetype and deviceinfo column.
Thanks
Arunkumar.DNo DDL = not tested...
Something like (assuming NULL when you say "no value"):
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NULL THEN 'WorkPhone' + ', ' ELSE ''
END + 'Mobile' AS
DeviceType
,HomePhone + ', ' + COALESCE(WorkPhone + ', ', '') + Mobile
FROM ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1179849918.549063.61900@.n15g2000prd.googlegroups.com...
> Hi friends,
> i have to select values from the table consider with the following
> record:
> No HomePhone WorkPhone Mobile
> 20 23232 98327
> the selection have to be done as:
> No DeviceType DeviceInfo
> 20 HomePhone, Mobile 23232, 98327
> since there is no value for workphone it have to be removed in both
> devicetype and deviceinfo column.
> Thanks
> Arunkumar.D
>|||> i have to select values from the table consider with the following
> record:
> No HomePhone WorkPhone Mobile
> 20 23232 98327
> the selection have to be done as:
> No DeviceType DeviceInfo
> 20 HomePhone, Mobile 23232, 98327
What about something like:
SELECT No,
CASE WHEN HomePhone IS NULL THEN '' ELSE 'HomePhone' END +
CASE WHEN WorkPhone IS NULL THEN '' ELSE 'WorkPhone' END +
CASE WHEN Mobile IS NULL THEN '' ELSE 'Mobile' END
AS DeviceType,
CASE WHEN HomePhone IS NULL THEN '' ELSE HomePhone END +
CASE WHEN WorkPhone IS NULL THEN '' ELSE WorkPhone END +
CASE WHEN Mobile IS NULL THEN '' ELSE Mobile END
AS DeviceInfo
FROM TableName
(You will need to add a bit of logic to work out where to put the commas)
Rgds,
Gavin

constructing strings from table

Hi friends,
please help me in selecting values from the table

the record is as follows:
Id HomePhone WorkPhone Mobile Email
20 2323223 323232232 test@.test.com
i have to select values as follows.

Id DeviceType DeviceInfo
20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
the one solution is:
select
'HomePhone, Mobile, Email' AS DeviceType
HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
DeviceInfo
from table where Id = 20

but here the work phone number is not available so that information
has to be truncated...

Thanks in Advance
Arunkumar.DOonz (arundhaj@.gmail.com) writes:

Quote:

Originally Posted by

Hi friends,
please help me in selecting values from the table
>
the record is as follows:
Id HomePhone WorkPhone Mobile Email
20 2323223 323232232 test@.test.com
>
i have to select values as follows.
>
Id DeviceType DeviceInfo
20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
>
>
the one solution is:
select
'HomePhone, Mobile, Email' AS DeviceType
HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
DeviceInfo
from table where Id = 20
>
but here the work phone number is not available so that information
has to be truncated...

Provided that WorkPhone is blank, the query above make sense to me.
The output would be "2323223,,323232232,test@.test.com". But if the
double comma wasn't there, how would you know which number that is
missing?

If WorkPhone is NULL, the entire expression will be NULL. In this
case you must use coalesce(WorkPhone, '') and similar for the other
columns.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On May 23, 2:48 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Oonz (arund...@.gmail.com) writes:

Quote:

Originally Posted by

Hi friends,
please help me in selecting values from the table

>

Quote:

Originally Posted by

the record is as follows:
Id HomePhone WorkPhone Mobile Email
20 2323223 323232232 t...@.test.com

>

Quote:

Originally Posted by

i have to select values as follows.

>

Quote:

Originally Posted by

Id DeviceType DeviceInfo
20 HomePhone, Mobile, Email 2323223, 323232232, t...@.test.com

>

Quote:

Originally Posted by

the one solution is:
select
'HomePhone, Mobile, Email' AS DeviceType
HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
DeviceInfo
from table where Id = 20

>

Quote:

Originally Posted by

but here the work phone number is not available so that information
has to be truncated...

>
Provided that WorkPhone is blank, the query above make sense to me.
The output would be "2323223,,323232232,t...@.test.com". But if the
double comma wasn't there, how would you know which number that is
missing?
>
If WorkPhone is NULL, the entire expression will be NULL. In this
case you must use coalesce(WorkPhone, '') and similar for the other
columns.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

The solution i have given is a static one.
The above solution holds good when all the column have value in it.
but when one column, say "HomePhone" is not available the resulting
table should build as

Id DeviceType DeviceInfo
20 Mobile, Email 323232232, t...@.test.com

please help me in this regards.|||Oonz (arundhaj@.gmail.com) writes:

Quote:

Originally Posted by

The solution i have given is a static one.
The above solution holds good when all the column have value in it.
but when one column, say "HomePhone" is not available the resulting
table should build as
>
>
Id DeviceType DeviceInfo
20 Mobile, Email 323232232, t...@.test.com
>
please help me in this regards.

Use the CASE exprssion:

SELECT DeviceType = CASE WHEN HomePhone IS NOT NULL
THEN HomePhone + ','
ELSE ''
END +
CASE WHEN WorkPhone IS NOT NULL
THEN WorkdPhone + ','
ELSE ''
END ...

It gets a little devilish if the email is missing as you may end with a
trailing comma, but maybe you can live wity that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Constructing Email message

Hi,

I am constructing a Message (Body) for sending our Emails. It is around
3000 characters long. But for whatever reason, the last line seems to
be broken with a "!" exclamatory mark in it, which results in
displaying the constructed image path as a broken one.

How to resolve this ?. Thanks.

Regards,
KarthickNo idea - what version of SQL Server, how are you building the message
body, which data type are you using for the message body, how are you
going to send the mail, what does "image path" refer to, can you post a
simplified SQL script to show the problem etc.

As a complete guess, you've declared the message body as varchar but
the path has Unicode characters in it, so you need to use nvarchar. But
without more information, that's probably wrong.

Simon|||Simon, thanks for your reply.

I am using SQL Server 2000. The Message body is NTEXT datatype in the
database. And I am constructing the message body like the following:

SELECT @.MessageBody = '<!DOCTYPE HTML PUBLIC "-//IETF//DTD
HTML//EN">'
SELECT @.MessageBody = @.MessageBody + '<html><head><meta
http-equiv="Content-Type" '
SELECT @.MessageBody = @.MessageBody + 'content="text/html;charset=' +
@.CharSet + '">'
SELECT @.MessageBody = @.MessageBody + '<title>Title goes here:
Ticket</title>'
SELECT @.MessageBody = @.MessageBody + '<link rel="stylesheet"
type="text/css" '

-- I have 67 lines of the @.MessageBody construction and at the end of
the stored procedure I am doing a EXEC to insert this @.MessageBody onto
another table and our third party vendor picks up the Emails from the
table and send them out. So I don't send out the emails manually, all I
do is insert the email contents onto a table and the rest is taken care
of.

Please help. Thanks.

Karthick|||It's still a little unclear (at least to me) - @.MessageBody can't be
ntext, because you can't declare an ntext variable. And you don't say
where the invalid data appears - if you SELECT @.MessageBody before
INSERTing it, is the data correct? Or is it only wrong after INSERTing?
And what does "doing an EXEC" mean? Are you using dynamic SQL, or a
stored procedure to do the INSERT?

Rather than describing your problem, I suggest that you try to produce
a (simplified) script to illustrate your problem - code is always
clearer than a description, and if other people can quickly copy and
paste into Query Analyzer, you're more likely to get a useful answer.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

Constructing a View into time dependant data

1. I have a table with data like this:

PersonID
DateTime
Temperature
Pressure

2. I want to build a view into this table so that it shows up as follows:

PersonID DateTime1 DateTime2 DateTime3 .....
1 Pressure1 Pressure2 Pressure3 ......
1 Temperature1 Temperature2 Tempearture3 .....
2 :
:

how would I do this?

Hello,

Your resultset seems to contain and thus represent two disparate data sets (one of Pressure and one of Temperature). Why would you want to do this when there would be no way to determine what is pressure and what is temperature?

Regardless, you would use the new PIVOT operator, and if you did want to include two data sets, you would need to union the results of two separate pivots in your view. BOL has some good examples of using PIVOT.

Cheers,

Rob

|||Thanks for responding!

I will construct 2 separate views - one for temperarure and one for pressure.
But I am confused as to how to do it for even just temperature.

Is there a way of doing this without Pivot? I am using SQL 2000|||

Hello,

Have a look at http://dotnetjunkies.com/WebLog/thomasswilliams/archive/2005/10/23/133383.aspx for starters. The actual solution will depend upon your data.

Cheers,

Rob

Constructing a Query

Hello all,

I am fairly new to using SQL in a production environment. I've gotten the management studio pretty much figured out and now I am finding the need for some more advanced knowledge. I would like to adjust the database autogrowth settings for each of my databases; however I have no prior statistics to examine to tell me how much the past years worth of being in production has caused the databases to grow.

I have found that I can look at each database in turn and select a basic report of disk usage that happens to include records of the last few autogrowth periods and particulars about each growth. What I would like to do is create a report (The same disk usage report) that includes all of my databases (I have about 30 active databases for MS Dynamics Great Plains.) I figured that I can create a maintenance plan consisting of a transact-SQL function that will run the same query, or a similar query that will report to me: Autogrowth statistics for each database in turn since it's inception (less than a year ago).

Perhaps there is an easier way to achieve the same results. If there is a way to create / run the same disk usage report for multiple databases, and have the results returned in a consolidated form, I have not yet figured out how.

I hope that someone out there has a solution for this; what i'm sure is not a new issue but mearly a newbie issue.

Thank you all in advance for any advice you may have.

Normally, you don't want to use 'Autogrow' on production servers. Autogrow causes excessive file fragmentation, and often occurs at the most inopportune moments (Autogrow is a performance hit.)

You would be best served to determine how much new space will be required over a period of time (week, month, year, etc.) by examining the historic records you have. Then exand the database files sufficiently to allow for all activity for the next period of time.

|||Ok, I can accept that. I've got a maximum of about 8 users working on a very beefy sql box. I have no concern about overall preformance just yet. However I WILL take your advice and remove the autogrowth. I still, however need to collect some pervious growth statistics. My question still remains, is there a way to script a report that will just pull the autogrowth statistics for all of my databases from the time of their creation?|||No, as far as I am aware, that historical information is not available.|||

Ok, Then lets take one step further back.

Since there is already a builtin 'disk usage' report that can be run on a database. Can I use a Transact Sql query to pull that same report (which will contain autogrowth history) for all (or a select number) of databases and filter through it at my leasure?

If so, I'll probably need some help with scripting of said query.

Thank you for your time and advice,

Constructing a field name

Can a field name be constructed, and if so, how?
For example;
Declare @.Date SmallDateTime
Set @.Date = GetDate()
I would like to create a field called 2005_Sales in a temp table
called 2005_Sales by constructing it from:
Convert( char(4), year( @.Date) ) + '_Sales'
If a field can not be created can a field be aliased by the same
construction?
Any help would be appreciated.>> Any help would be appreciated. <<
You are supposed to know to know what a thing is before you do it.
Even more basic, a field is NOT ANYTHING WHATSOEVER like a column.
You are trying to mimic a 1950's file system report in SQL.
Please, please, please learn the basics of data modeling before you
kill people or bankrupt companies. This is the kidn of thing which you
woudl learn in the first two days of a course.

Constructing a datatable

Hi,

I am experimenting to make a datatable in C# code in a page. This table should be a disconected table with Only valid for the present session.

I try the following code:

publicpartialclassDefault2 : System.Web.UI.Page

{

DataTable DT =newDataTable("TEST");

protectedvoid Page_Load(object sender,EventArgs e)

{

if (!IsPostBack)

{

DataColumn Col1 =newDataColumn("Col1");

Col1.DataType =typeof(Int32);

Col1.AllowDBNull =false;

DT.Columns.Add(Col1);

DataColumn Col2 =newDataColumn("Col2");

Col2.DataType =typeof(string);

Col2.AllowDBNull =true;

DT.Columns.Add(Col2);

DataColumn Col3 =newDataColumn("Col3");

Col3.DataType =typeof(DateTime);

Col3.AllowDBNull =true;

DT.Columns.Add(Col3);

GridView1.DataSource = DT;

}

}

protectedvoid Button1_Click(object sender,EventArgs e)

{

for (int i = 1; i < 20; i++)

{

DataRow MyRow = DT.NewRow();

MyRow["Col1"] = i;

DT.Rows.Add(MyRow);

}

}

}

For one reason or the other. if I click the button I get the message that Col1 dus not make part of the table TEST. It turns out that there are no columns added to the table. altroug the code in the page load part has been run. I suppose I have to do something with the session state to make my DataTable persistent, but I have no idea what. Can somebody help me out?

Thanks!

Rob

You've got the right idea. If you want to save your DataTable within the Session, just change your 'DT' reference to something like this:

private DataTable DT{get{if (this.Session["DT"] ==null){this.Session["DT"] =new DataTable("Test");}return this.Session["DT"]as DataTable;}set {this.Session["DT"] =value; }}

|||

Great! It works. Only one more question. The Datagrid is not updating the records. Do you maybe have also a solution for that?

many thanks

rob

|||

What do you mean by not updating the records?

|||I have put a gridview on the form, and thougt that with the statement

GridView1.DataSource = DT;

I would automaticly see the rows from the data table in de gridview.

But ASP always works the unexpected ways. . .

|||

Add this to the bottom of your Button1_Click event:

GridView1.DataSource =this.DT;GridView1.DataBind();

|||

I thank you very mutch. It works great.

regards Rob

Constraint question

I'm constructing a menu in a SQL Server database.
Each menu can have sub menus. So my table looks like this:

CREATE TABLE menu
(
idINT NOT NULL IDENTITY PRIMARY KEY,
nameVARCHAR(30)NOT NULL,
parentID INTNOT NULL /*ID Of Parent Menu -1 If Root*/
)

IS there a way of placing a constraint on it so if one menu is deleted
all its sub menus get deleted automatically. A normal foreign key
causes a cicrcular problem. Any ideas?Hi

I guess you could have a loop in a trigger

WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE FROM menu
WHERE parentID not in ( SELECT ID FROM menu)
AND ParentID <> 1
END

John

<wackyphill@.yahoo.com> wrote in message
news:1103232008.918252.175160@.f14g2000cwb.googlegr oups.com...
> I'm constructing a menu in a SQL Server database.
> Each menu can have sub menus. So my table looks like this:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NOT NULL /*ID Of Parent Menu -1 If Root*/
> )
>
> IS there a way of placing a constraint on it so if one menu is deleted
> all its sub menus get deleted automatically. A normal foreign key
> causes a cicrcular problem. Any ideas?|||(wackyphill@.yahoo.com) writes:
> I'm constructing a menu in a SQL Server database.
> Each menu can have sub menus. So my table looks like this:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NOT NULL /*ID Of Parent Menu -1 If Root*/
> )

Better to let parentID be NULL if root. If you go for -1 you cannot
have an fkey constraint anyway.

> IS there a way of placing a constraint on it so if one menu is deleted
> all its sub menus get deleted automatically. A normal foreign key
> causes a cicrcular problem. Any ideas?

You would have to write a trigger, and skip the constraint. Or simply
do the cascading in the stored procedure that removes a menu.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info guys.

> Better to let parentID be NULL if root. If you go for -1 you cannot
> have an fkey constraint anyway.
Yeah, good point. Although I was concidering allowing multiple root
menus which is why I did it. Each menu w/ -1 would begin another major
High Level Menu System/Section. And to get a list of all the sections
simply search for the menus w/ a -1 parentID. I thought if things
swelled I'd cut down on the amount of menus that need to be returned in
a query that way. (They will end up being displayed in a tree control
that shows all menu's in the current section).

> You would have to write a trigger, and skip the constraint. Or simply
> do the cascading in the stored procedure that removes a menu.

OK, I'm just learning SQL Server and didn't want to skip over a feature
that would do it for me if there was one. I'll probably go w/ the
stored procedure method. How best to set it up so a database can only
be accessed through its stored procedures, and stop adhoc SQL commands
that would not inforce the cascading?|||Although, now that I think about it I certainly could do the same thing
w/ NULLS as w/ -1s :) Sorry, Wasn't thinking that one thru far enough.|||(wackyphill@.yahoo.com) writes:
> OK, I'm just learning SQL Server and didn't want to skip over a feature
> that would do it for me if there was one. I'll probably go w/ the
> stored procedure method. How best to set it up so a database can only
> be accessed through its stored procedures, and stop adhoc SQL commands
> that would not inforce the cascading?

It is of course not possible to lock out ad-hoc statements completely
from Query Analyzer completely for people with admin privileges.
.. But with judicial use of constraints you can prevent bad things from
happening, at least by mistake.

But for application design, yes, it is a good idea make all access with
through stored procedures, and only grant users access to the stored
procedures, but not directly to the tables.

The advantage of doing the cascading in the stored procedure, is that
you can keep a table constraint that prohibits deletion.

Overall, while cascading referential integrity is available in SQL Server,
there are several situations where it is not possible to use it, the
usefulness of the feature is limited.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp