How do I add a static column to a matrix?

Folks,
I'm getting data back from the database in the following format:
row_id | col_Name | col_Value
0 | ID | 10
0 | Name | First
0 | Jan | 1
0 | Feb | 2
1 | ID | 20
1 | Name | Seconds
1 | Jan | 10
1 | Feb | 20
...
We are grouping based on the row_id db column, thus creating dynamic columns
for ID, Name, Jan, Feb, etc.
However, I need to customize the ID and Name column which is, to my
understanding, not something that can be done if they are dynamic columns in
the matrix.
So, what I was thinking of doing was to make two static columns, ID and
Name, and then filter those rows out of the dynamic columns. However, when I
make a second ColumnGrouping in the RDL file for these, and define the two
static columns, what happens is that they appear set up such that these
columns would be in a separate row, rather than off to the left of the rows
of dynamic columns (hopefully I'm explaining this well enough).
What would be the best way to accomplish this, presuming that we can't
simply have the returned data changed to a better format?
Thanks!
randyAssuming you want to transform your table to:
ID | Name | Jan | Feb
10 | First | 1 | 2
20 | Seconds | 10 | 20
etc.
The easiest is to tweak your SQL, but I am assuming this is the result of a
SP that you can't since you said:
> What would be the best way to accomplish this, presuming that we can't
> simply have the returned data changed to a better format?
I'm not sure if you can, but you might have to do this with a custom code
module to transform your data and then bind the report to the dataset from
code. Maybe someone else knows way to modify the underlying dataset
structure.
"Randy Chapman" <RandyChapman@.discussions.microsoft.com> wrote in message
news:5F87A271-7591-4453-975F-06394CC8E2A8@.microsoft.com...
> Folks,
> I'm getting data back from the database in the following format:
> row_id | col_Name | col_Value
> 0 | ID | 10
> 0 | Name | First
> 0 | Jan | 1
> 0 | Feb | 2
> 1 | ID | 20
> 1 | Name | Seconds
> 1 | Jan | 10
> 1 | Feb | 20
> ...
> We are grouping based on the row_id db column, thus creating dynamic
> columns
> for ID, Name, Jan, Feb, etc.
> However, I need to customize the ID and Name column which is, to my
> understanding, not something that can be done if they are dynamic columns
> in
> the matrix.
> So, what I was thinking of doing was to make two static columns, ID and
> Name, and then filter those rows out of the dynamic columns. However,
> when I
> make a second ColumnGrouping in the RDL file for these, and define the two
> static columns, what happens is that they appear set up such that these
> columns would be in a separate row, rather than off to the left of the
> rows
> of dynamic columns (hopefully I'm explaining this well enough).
> What would be the best way to accomplish this, presuming that we can't
> simply have the returned data changed to a better format?
> Thanks!
> randy
>

How do I .. use a dataview to break up sqldatareader ? Please Help!

Hello,

I am using a sqldatareader to get back a whole set of data.

The columns are id, data_id and data_desc

for example, the collection is
id data_id data_desc
0 100 this is name for id 0
0 101 this is address for id 0
0 102 this is gender for id 0
1 100 this is name for id 1
1 101 this is address for id 1
1 102 this is gender for id 1
2 100 this is name for id 2
2 101 this is address for id 2
2 102 this is gender for id 2
3 100 this is name for id 3
3 101 this is address for id 3
3 102 this is gender for id 3

I want to be able to go thru' this list and break up for each id and create a txt file. So, i will have four text files.

txt0.txt will contain
100, this is name for id 0
101, this is address for id 0
102, this is gender for id 0

I would like to know, how do i use a dataview to break up the sqldatareader so i can then repeat the loop for each id and generate the text file.

I don't have a problem generating the text file.

Please let me know, how should i go about it.

Thanks a bunch,
-Sean

If you are just looking to loop thru a SqlDataReader. Try this.

SqlCommand cmd = new SqlCommand("<Your query here>", con);
cmd.Connection.Open();
SqlDataReader dtrText = cmd.ExecuteReaderCommandBehavior.CloseConnection);
while(dtrText.Read())
{
//Write to text file here.
}
dtrText.Close();
con.Close();

Hope this helps.

How do i "Join" back to the same table and fields ? Please help !

Hi and thanx for reading my post..

Underneat is the current SQL i'm trying to get to work (in Access 2000)

Cant get it to work, cause i'm getting Join errors..

----CODE-----
strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname" & _
" FROM Person p, Person p1" & _
" INNER JOIN (PersonPerson pp ON pp.PID = p.PID OR pp.PID2 = p.PID)" & _
" WHERE (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
----END CODE----

----DESCRIPTION-------
1) Got a table "Person" where i'm inserting PID and Lastname.
2) Registering person 1-> PID = 1, Lastname = Doe
3) Registering person 2-> PID = 2, Lastname = John
4) Using table PersonPerson (manually) to connect these two together.
PID1 = 1, PID2 = 2
5) By using this i can use SQL to get the data from both persons taht are
connected
6) So basically, i want to go through all records in the personperson table, to find PID and PID2, and then use _both_ the fields to check against the Person-table. First getting Person.PID and Person.Lastname (For PERSON 1) THEN... do the exact same operation for PID2.

----END DESCRIPTION------

Result should look like this :

Person1 ID, Person1 Lastname, Person2 ID, Person2 Lastname
------------------
1 Doe 2 John

Will be enormously happy if anyone could help me out with this code...

/(_Mirador:(I'm not 100% on what you want. Is this your table structure?

Person
PID Firstname etc
-- ---
1 Joel
2 Bell
3 Bruce
4 Harry

PersonPerson
PID1 PID2
-- --
1 2
4 3

If so - this should help:

strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname
FROM Person p, Person p1, PersonPerson pp
WHERE (p.PID = pp.PID1 AND p1.PID = pp.PID2)
AND (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"

It will generate the following output:

PID Lastname PID Lastname
---- ---- ---- ----
1 Dixon 2 Crawford
4 Potter 3 Shark

(Yes, Person 4's name is Harry Potter - but no - I haven't read the books :) )|||Great ! :)

I will try the code right away..

I'll send you the results i got :)

Mirador.|||Make sure you're using the latest code - I've edited the post a few times since I first posted it (stupid syntax bug)|||Super-great !!!!! :)

It worked like a dream..

You have no clue how much you've helped me !

Owe u a digital-beer.

Mirador.|||lol - no worries mate.

But I've gotta drive tonight - so how about a digital soda?|||/Me hands over a digital master-soda :)

hehe..

btw : since you're so master'ish at SQL.. maybe u could try and help me with this query too ?

----CODE-----
strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _

" WHERE Person.Etternavn LIKE '%" & Me.txtSearch & "%' OR Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
" OR Person.Alias LIKE '%" & Me.txtSearch & "%' OR Person.Alias LIKE '%" & Me.txtSearch2 & "%' OR Person.Yrke LIKE '%" & Me.txtSearch & "%' OR Person.Yrke LIKE '%" & Me.txtSearch2 & "%'" & _ etc.etc.etc...
---- END CODE -----

---- DESCRIPTION-------
As you can see it's 2 x fields where i want to search..
Basically... i'm running 2 different queries depending on (and checking) if only one, or two fields are filled in for the search... AND...

If both the fields are filled in, it should ex : if Me.txtsearch is "June" and Me.txtSearch2 is "2004" make sure it's only getting a record if both "June" and "2004" is included in _ANY_ of the fields in the record..

I have realized that i cannot only use AND alone, because then all the fields have to match, and i cannot use OR alone either.. because then it grabs if it only matches one of them..

Got any clues on this one ?

Thanx for your help mate..

Mirador.|||So basically - you are trying to select records in which BOTH of the criteria is in ONE of the fields. If this is correct (and if it's not I'm lost :) ) - then this should help (take note of the brackets):

strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _

" WHERE (Person.Etternavn LIKE '%" & Me.txtSearch & "%' AND Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
") OR (Person.Alias LIKE '%" & Me.txtSearch & "%' AND Person.Alias LIKE '%" & Me.txtSearch2 & "%') OR Person.Yrke LIKE '%" & Me.txtSearch & "%' AND Person.Yrke LIKE '%" & Me.txtSearch2 & "%')" & _ etc.etc.etc...

Basically you are saying (i'll use a generic example of a Product record. No offence - but I really can't understand the table you have with the column names ;) ):

If txtSearch1 is "big" and txtSearch2 is "round"

SELECT blah
FROM Product
WHERE (Product.Name LIKE '%big%' AND Product.Name LIKE '%round%')
OR (Product.Description LIKE '%big%' AND Product.Description LIKE '%round%')
OR (Product.Comment LIKE '%big%' AND Product.Comment LIKE '%round%')

So you will get products with big AND round in either the Name, Description or Comment column.

Is this what you were after - or have I just spoken pure gibberish?|||Hay mate :) thanx for your reply :)

did u enjoy your digital soda ? hehe.. :)

Well... gotta admit that it's a bit confusion for myself too :)

I'll try to explain a bit better :

---------
Q :
Lets say u want to search for every record that includes both "Green" and "Yellow" in _any_ of the fields in the record, and u want to see _only_ those records.

A:
You find a record where field "lastname" has "Green" in it and field "Comment" has "Yellow" in it -> MATCH!!
----------

If i understood it right, the one you posted has to have both "Green" and "Yellow" in one field right ?
So.. u would get a match if ex. "Comment" field had the text :
"All green people are infact Yellow because they bla-bla.-bla..."

I have to lets say.. use the second searchfield (txtsearch2) to... "narrow" down the search.

Example :
-------
Lets say u want to find a person named "Mike" and u press search. U would maybe get something like 1000 matches if u got a huuge database.
But then u altso know that "Mike" is from "Uganda". So... therefore i type "Mike" in txtsearch and "Uganda" in txtsearch2 to make sure that u get all the "Mike" records which got "Uganda" in any of the other fields..
-------

Yea.. that's about it :) dunno if it's even possible but i surely hope so : )heeh..

Hope that made things a littlebit more clear :)

Thanx for your help btw !!!.. most appreciated..

want another digital soda ? or.. maybe a digital beer this time :)

Best regards
Mirador.|||Ahh - I see what you mean. It's also possible - but the code is quite long, depending on the columns in your table. It's basically the same as my other example above - but switch the ANDs and ORs.

For each search criteria you have to check if it's in any of the columns. Again - using my Product table:

SELECT blah
FROM Product
WHERE (Product.Name LIKE <SearchCriteria1> OR Product.Description LIKE <SearchCriteria1> OR Product.Comment LIKE <SearchCriteria1>)
AND (Product.Name LIKE <SearchCriteria2> OR Product.Description LIKE <SearchCriteria2> OR Product.Comment LIKE <SearchCriteria2>)

Doing this for each column should get what you're after. Basically you're saying SearchCriteria1 needs to be in any of the columns (using the OR) - AND SearchCriteria2 needs to be in any of the columns.

Is that what you're after?

And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :D|||Tjohooo !!..

yea.. that's JUST what i was after..

I had thoughts in this track, but wasn't certain because it would be so extremely long:) hehe.. Didn't know quite how to write it either..

but.. THANK YOU AGAIN !! :)

Double scotch coming up.. or.. maybe it's back to coffee ? heheh !!:

Mirador..|||And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :DThese last few weeks (since mid-April) have been awful for me too. Do you suppose that the universe has taken some kind of unusually perverse twist against us "denizens of databases" lately?

-PatP

how did guest get back into all of my databases?

i clearly remember deleting the guest user from all of my sql2000
databases a couple of years ago.
the guest user is now back in all of my databases on all of my servers
and i can't tell when or where it came from.
at first, i thought sp4 might have done it, but i've still got one
server running sp3 that also has the guest user in all databases.
i guess the next logical guess might be the hotfix that got you to
version 818.
anybody know if there was actually a patch or service pack (either db or
operating system) that re-added the guest user to each database?
i feel sort of silly because it's apparently been there for months and
i'm just now noticing it.
Is the 'guest' user actually enabled? You should always have a row for
'guest' user in sysusers in all databases but the 'guest' user is enabled
only if it is listed when you execute sp_helpuser.
Hope this helps.
Dan Guzman
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:43A16C3E.CB03F014@.dontemailme.com...
>i clearly remember deleting the guest user from all of my sql2000
> databases a couple of years ago.
> the guest user is now back in all of my databases on all of my servers
> and i can't tell when or where it came from.
> at first, i thought sp4 might have done it, but i've still got one
> server running sp3 that also has the guest user in all databases.
> i guess the next logical guess might be the hotfix that got you to
> version 818.
> anybody know if there was actually a patch or service pack (either db or
> operating system) that re-added the guest user to each database?
> i feel sort of silly because it's apparently been there for months and
> i'm just now noticing it.

how did guest get back into all of my databases?

i clearly remember deleting the guest user from all of my sql2000
databases a couple of years ago.
the guest user is now back in all of my databases on all of my servers
and i can't tell when or where it came from.
at first, i thought sp4 might have done it, but i've still got one
server running sp3 that also has the guest user in all databases.
i guess the next logical guess might be the hotfix that got you to
version 818.
anybody know if there was actually a patch or service pack (either db or
operating system) that re-added the guest user to each database?
i feel sort of silly because it's apparently been there for months and
i'm just now noticing it.Is the 'guest' user actually enabled? You should always have a row for
'guest' user in sysusers in all databases but the 'guest' user is enabled
only if it is listed when you execute sp_helpuser.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:43A16C3E.CB03F014@.dontemailme.com...
>i clearly remember deleting the guest user from all of my sql2000
> databases a couple of years ago.
> the guest user is now back in all of my databases on all of my servers
> and i can't tell when or where it came from.
> at first, i thought sp4 might have done it, but i've still got one
> server running sp3 that also has the guest user in all databases.
> i guess the next logical guess might be the hotfix that got you to
> version 818.
> anybody know if there was actually a patch or service pack (either db or
> operating system) that re-added the guest user to each database?
> i feel sort of silly because it's apparently been there for months and
> i'm just now noticing it.sql

how did guest get back into all of my databases?

i clearly remember deleting the guest user from all of my sql2000
databases a couple of years ago.
the guest user is now back in all of my databases on all of my servers
and i can't tell when or where it came from.
at first, i thought sp4 might have done it, but i've still got one
server running sp3 that also has the guest user in all databases.
i guess the next logical guess might be the hotfix that got you to
version 818.
anybody know if there was actually a patch or service pack (either db or
operating system) that re-added the guest user to each database?
i feel sort of silly because it's apparently been there for months and
i'm just now noticing it.Is the 'guest' user actually enabled? You should always have a row for
'guest' user in sysusers in all databases but the 'guest' user is enabled
only if it is listed when you execute sp_helpuser.
Hope this helps.
Dan Guzman
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:43A16C3E.CB03F014@.dontemailme.com...
>i clearly remember deleting the guest user from all of my sql2000
> databases a couple of years ago.
> the guest user is now back in all of my databases on all of my servers
> and i can't tell when or where it came from.
> at first, i thought sp4 might have done it, but i've still got one
> server running sp3 that also has the guest user in all databases.
> i guess the next logical guess might be the hotfix that got you to
> version 818.
> anybody know if there was actually a patch or service pack (either db or
> operating system) that re-added the guest user to each database?
> i feel sort of silly because it's apparently been there for months and
> i'm just now noticing it.

how check if Transaction object already rollback or not?

Hi,
i would like to know how to determine a transaction is already rollback so that we dont have to reroll back again and get an exception like below:

This SqlTransaction has completed;
it is no longer usable. at System.Data.SqlClient.SqlTransaction.Rollback()

Please help because when i tried to do this: objTrans.rollback() and i got the above exception.

Regards,If the SqlTransaction's Connection property is nothing, then the transaction is no longer valid (and you should not call Rollback() ). Here's the docs for a code sample:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqltransactionclasstopic.asp|||Thanks alot..
actually i have another issue that is somehow related to this thread, i m using dataAdater to populate my TreeView COntrol but if i refresh 2 browser at the same time it will give me 2 exception. The first exception is

1)There is already an open DataReader associated with this Connection which must be closed first.
2)The SqlCommand is currently busy Open, Fetching.

I m not so sure why can this be? i m doing recursive loop and below is my code

private void LoadNode()
{
//clear the tree Component
TreeView1.Nodes.Clear();
string Tsql;
TreeNode Node;
Tsql="sp_framework_get_imis_function";
DataSet dsTree;
dsTree=ExecuteDbConnection.fillDataset(Tsql,imis.framework.net.ImisUser .getDetails.userID);
int i=0;
TreeNode tempNode=null;
if (dsTree.Tables.Count <= 0) return;
while (i<= dsTree.Tables[0].Rows.Count -1)
{
//create a new node for parent
//get the parent node
Node=new TreeNode();
if (dsTree.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty|| dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString() !=string.Empty)
{
//desc_chinese is not null
if (dsTree.Tables[0].Rows[i]["function_desc"].ToString() !=string.Empty && dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty ) Node.Text=dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()==null?dsTree.Tables[0].Rows[i]["function_desc"].ToString() :dsTree.Tables[0].Rows[i]["function_desc"].ToString() + "<br>"+dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsTree.Tables[0].Rows[i]["function_desc"].ToString()==string.Empty && dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty )Node.Text=dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsTree.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty && dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()==string.Empty ) Node.Text=dsTree.Tables[0].Rows[i]["function_desc"].ToString();
}
else
{
//throw exception because both desc and desc_chinese is not null.
return;
}
Node.SelectedImageUrl=dsTree.Tables[0].Rows[i]["img_selected"].ToString();
Node.ImageUrl =dsTree.Tables[0].Rows[i]["img"].ToString();
if (dsTree.Tables[0].Rows[i]["program"].ToString() !="")
{
Node.NavigateUrl ="index.aspx?pageID=" +dsTree.Tables[0].Rows[i]["function_id"];
Node.Target="WorkFrame";
}
tempNode=Node;
TreeView1.Nodes.Add(Node);
ChildNode(tempNode,Convert.ToInt32(dsTree.Tables[0].Rows[i]["function_id"]));
i=i+1;
}
}//end sub
private void ChildNode(TreeNode node,int functionID)
{
try
{
TreeNode nodeX;
string userID=imis.framework.net.ImisUser.getDetails.userID;
string tSql;
string TempSQL;
if (imis.framework.net.ImisUser.getDetails.userID ==null)
return;
TempSQL="sp_framework_get_imis_function_child_id";
DataSet dsChild;
dsChild=ExecuteDbConnection.fillDataset(TempSQL,functionID,userID);
int i=0;
if (dsChild.Tables[0].Rows.Count >0)
{
while (i <=dsChild.Tables[0].Rows.Count-1)
{
//get all the child of the belong the the first level nodes
nodeX=new TreeNode();
if (dsChild.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty || dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString() !=string.Empty)
{
//desc_chinese is not null
if (dsChild.Tables[0].Rows[i]["function_desc"].ToString() !=string.Empty && dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty) nodeX.Text=dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()==null?dsChild.Tables[0].Rows[i]["function_desc"].ToString() :dsChild.Tables[0].Rows[i]["function_desc"].ToString() + "<br>"+dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsChild.Tables[0].Rows[i]["function_desc"].ToString()==string.Empty && dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty)nodeX.Text=dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsChild.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty && dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()==string.Empty) nodeX.Text=dsChild.Tables[0].Rows[i]["function_desc"].ToString();
}
else
{
//throw exception because both desc and desc_chinese is not null.
}
//nodeX.Text= dsChild.Tables[0].Rows[i]["function_desc"].ToString();
nodeX.SelectedImageUrl=dsChild.Tables[0].Rows[i]["img_selected"].ToString();;
nodeX.ImageUrl =dsChild.Tables[0].Rows[i]["img"].ToString();
if (dsChild.Tables[0].Rows[i]["program"].ToString() != "")
{
nodeX.NavigateUrl="index.aspx?pageID=" +dsChild.Tables[0].Rows[i]["function_id"];
nodeX.Target="WorkFrame";
}
node.Nodes.Add(nodeX);
tSql="sp_framework_get_imis_function_child_id";
DataSet ds2=ExecuteDbConnection.fillDataset(tSql,Convert.ToInt32(dsChild.Tables[0].Rows[i]["function_id"]),userID);
if (Convert.ToBoolean(ds2.Tables[0].Rows.Count > 0))
{
int j=0;
while (j <=ds2.Tables[0].Rows.Count -1)
{
//recursive way of getting all the child in the first level
TreeNode nodeY=new TreeNode();
if (ds2.Tables[0].Rows[j]["function_desc"].ToString()!=string.Empty || ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString() !=string.Empty)
{
//desc_chinese is not null
if (ds2.Tables[0].Rows[j]["function_desc"].ToString() !=string.Empty && ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()!=string.Empty) nodeY.Text=ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()==null?ds2.Tables[0].Rows[j]["function_desc"].ToString() :ds2.Tables[0].Rows[j]["function_desc"].ToString() + "<br>"+ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString();
if(ds2.Tables[0].Rows[j]["function_desc"].ToString()==string.Empty && ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()!=string.Empty)nodeY.Text=ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString();
if(ds2.Tables[0].Rows[j]["function_desc"].ToString()!=string.Empty && ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()==string.Empty) nodeY.Text=ds2.Tables[0].Rows[j]["function_desc"].ToString();
}
else
{
//throw exception because both desc and desc_chinese is not null.
}
//nodeY.Text=ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()==null?ds2.Tables[0].Rows[j]["function_desc"].ToString() :ds2.Tables[0].Rows[j]["function_desc"].ToString() + "<br>"+dsChild.Tables[0].Rows[j]["function_desc_chinese"].ToString() ;
nodeY.SelectedImageUrl=ds2.Tables[0].Rows[j]["img_selected"].ToString();
nodeY.ImageUrl =ds2.Tables[0].Rows[j]["img"].ToString();
if (ds2.Tables[0].Rows[j]["program"].ToString() != "")
{
nodeY.NavigateUrl="index.aspx?pageID=" +ds2.Tables[0].Rows[j]["function_id"];
nodeY.Target="WorkFrame";
}
nodeX.Nodes.Add(nodeY);
ChildNode(nodeY,Convert.ToInt32(ds2.Tables[0].Rows[j]["function_id"]));
++j;
}//end while
}//end if
++i;
}//end while
}//end if
}
catch(Exception err)
{
imis.framework.net.ApplicationLog.writeError(err,"TreeView");
}
}//end sub

Please advise, i m using dataAdater purely.|||I'm not sure. I'd advise you to repost the previous message in a new thread so that more people will take a look at it.sql

How can wrap a case statement around us

I would like to maybe wrap a case statement around the following so that I
can set any results that come back as NULL to 0 (the performance of updating
afterwards is horrible)
update STAGE_PHX_FACT_POLICY
set STAGE_PHX_FACT_POLICY.fire_fee_if = whdata1.dbo.premium_detail.fee,
STAGE_PHX_FACT_POLICY.fire_fee_written =
whdata1.dbo.premium_detail.billed_premium
from whdata1.dbo.premium_detail
inner join whdata1.dbo.lob_xref
on whdata1.dbo.premium_detail.premium_lob = whdata1.dbo.lob_xref.premium_lob
where whdata1.dbo.lob_xref.a_lob = '6'
and whdata1.dbo.premium_detail.policy_number =
STAGE_PHX_FACT_POLICY.policy_number
and whdata1.dbo.premium_detail.policy_date_time =
STAGE_PHX_FACT_POLICY.policy_date_time
Thanks you!>> ...around the following so that I can set any results that come back as
Lookup COALESCE or ISNULL function in SQL Server Books Online
Anith|||"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:3B52A763-5C4B-4030-ACDF-7661406D5149@.microsoft.com...
>I would like to maybe wrap a case statement around the following so that I
> can set any results that come back as NULL to 0 (the performance of
> updating
> afterwards is horrible)
>
> update STAGE_PHX_FACT_POLICY
> set STAGE_PHX_FACT_POLICY.fire_fee_if = whdata1.dbo.premium_detail.fee,
> STAGE_PHX_FACT_POLICY.fire_fee_written =
> whdata1.dbo.premium_detail.billed_premium
> from whdata1.dbo.premium_detail
> inner join whdata1.dbo.lob_xref
> on whdata1.dbo.premium_detail.premium_lob =
> whdata1.dbo.lob_xref.premium_lob
> where whdata1.dbo.lob_xref.a_lob = '6'
> and whdata1.dbo.premium_detail.policy_number =
> STAGE_PHX_FACT_POLICY.policy_number
> and whdata1.dbo.premium_detail.policy_date_time =
> STAGE_PHX_FACT_POLICY.policy_date_time
You can use COALESCE or ISNULL to do this in a much simpler way. As you
didn't specify what needed updating, I'll take a guess that it's both
values:
update STAGE_PHX_FACT_POLICY
set STAGE_PHX_FACT_POLICY.fire_fee_if =
COALESCE(whdata1.dbo.premium_detail.fee,0) ,
STAGE_PHX_FACT_POLICY.fire_fee_written =
COALESCE(whdata1.dbo.premium_detail.billed_premium,0)
from whdata1.dbo.premium_detail
inner join whdata1.dbo.lob_xref
on whdata1.dbo.premium_detail.premium_lob = whdata1.dbo.lob_xref.premium_lob
where whdata1.dbo.lob_xref.a_lob = '6'
and whdata1.dbo.premium_detail.policy_number =
STAGE_PHX_FACT_POLICY.policy_number
and whdata1.dbo.premium_detail.policy_date_time =
STAGE_PHX_FACT_POLICY.policy_date_time
Dan

How can revert a SQL2K DB back to SQL7?

Hello. I have a SQL2K DB that needs to go on an SQL 7.0 Server. How can I
make it restore to the 7.0 Server?
Any help appreciated!
Thanks,
Tom
You could use DTS and copy Database. Unfortunately you cannot just do a
restore or an attach.
Jeff Duncan
MCDBA, MCSE+I
"Tom" <none@.none.com> wrote in message
news:OiUcI%23VWEHA.2716@.tk2msftngp13.phx.gbl...
> Hello. I have a SQL2K DB that needs to go on an SQL 7.0 Server. How can
> I
> make it restore to the 7.0 Server?
> Any help appreciated!
> Thanks,
>
> Tom
>
|||: (
Okay.. Thanks Jeff!
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:%23C7ZAHWWEHA.3012@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> You could use DTS and copy Database. Unfortunately you cannot just do a
> restore or an attach.
> --
> Jeff Duncan
> MCDBA, MCSE+I
> "Tom" <none@.none.com> wrote in message
> news:OiUcI%23VWEHA.2716@.tk2msftngp13.phx.gbl...
can
>