retrive all records within the Case statment

i want to filter a table by a view using a defind function
for example:
select * from person
where dbo.person.company like case CmpName()
when 'all' then '%'
else CmpName()
end
prblem is that '%' doesn't show the records with NULL value
how can i
thanks
samTry using IS NULL clause
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]
"Sam" <focus10@.zahav.net.il> wrote in message
news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> i want to filter a table by a view using a defind function
> for example:
> select * from person
> where dbo.person.company like case CmpName()
> when 'all' then '%'
> else CmpName()
> end
> prblem is that '%' doesn't show the records with NULL value
> how can i
> thanks
> sam
>|||"IS NULL" is not working with "=" , or "Like" operators
so i can not use it with the "CASE" statment
"Uwa Agbonile [MSFT]" <uwaag@.online.microsoft.com> wrote in message
news:ez3NxhwYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Try using IS NULL clause
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Regards,
> Uwa Agbonile[MSFT]
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
>

retrive all records within the Case statment

i want to filter a table by a view using a defind function
for example:
select * from person
where dbo.person.company like case CmpName()
when 'all' then '%'
else CmpName()
end
prblem is that '%' doesn't show the records with NULL value
how can i
thanks
sam
Try using IS NULL clause
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]
"Sam" <focus10@.zahav.net.il> wrote in message
news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> i want to filter a table by a view using a defind function
> for example:
> select * from person
> where dbo.person.company like case CmpName()
> when 'all' then '%'
> else CmpName()
> end
> prblem is that '%' doesn't show the records with NULL value
> how can i
> thanks
> sam
>
|||"IS NULL" is not working with "=" , or "Like" operators
so i can not use it with the "CASE" statment
"Uwa Agbonile [MSFT]" <uwaag@.online.microsoft.com> wrote in message
news:ez3NxhwYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Try using IS NULL clause
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Regards,
> Uwa Agbonile[MSFT]
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
>
sql

Retrieving ID after INSERT Behind the scenes - Not using a grid view

Hi there. I looked through many other posts describing scope_identity but I am trying to achieve the same thing from the code behind. i.e. I need to some how call a method to execute the insert command and then return the ID so I can update other tables with this value.

I was going down the road of something like:

addnew as sqldatasource = new sqldatasource

addnew.insertcommand = "Insert into...; def @.NewID as scope_identity"

addnew.insert()

The problem is I don't know how to add a output parameter using VB or how to retrieve it.

Any help would be much appreciated, this is doing my head in...

Doug.

Hi, you can just new a SqlParameter and set the Direction to output like this:

Dim parameterdat2AsNew SqlParameter("@.accountnum", SqlDbType.NVarChar, 20, ParameterDirection.Output)

retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
****************************************
*/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427 210504 NULL
1953 210514 NULL
646 210486 NULL
650 210487 NULL
749 210491 NULL
****************************************
*/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
****************************************
*/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/I didn't go through your script, but the old outer join syntax has some unex
pected behaviors, this is why it
will be removed in some future release. Did you consider using the modern ou
ter join syntax?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.goog
le.com...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> ****************************************
*/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> ****************************************
*/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> ****************************************
*/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
*****************************************/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427 210504 NULL
1953 210514 NULL
646 210486 NULL
650 210487 NULL
749 210491 NULL
*****************************************/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
*****************************************/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/I didn't go through your script, but the old outer join syntax has some unexpected behaviors, this is why it
will be removed in some future release. Did you consider using the modern outer join syntax?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.google.com...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427,210504,1)
insert into tbl1(oldID, newID, refID) values(1953,210514,0)
insert into tbl1(oldID, newID, refID) values(646,210486,3)
insert into tbl1(oldID, newID, refID) values(650,210487,4)
insert into tbl1(oldID, newID, refID) values(749,210491,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
142721050445280
1953210514NULL
64621048644701
65021048744701
74921049145827
*****************************************/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427210504NULL
1953210514NULL
646210486NULL
650210487NULL
749210491NULL
*****************************************/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
142721050445280
1953210514NULL
64621048644701
65021048744701
74921049145827
*****************************************/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/
I didn't go through your script, but the old outer join syntax has some unexpected behaviors, this is why it
will be removed in some future release. Did you consider using the modern outer join syntax?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.google.co m...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

Retrieving data from SQL server table to display on button on datagrid table.

I have nine type of buttons,

EnrollAmtBTM

PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button

this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION

THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITION

protectedvoid CollectAgencyInformation()

{

WebLibraryClass ConnectionFinanceDB;

ConnectionFinanceDB =new WebLibraryClass();

string SQLCONDITION ="";

string RUN_SQLCONDITION ="";

SessionValues ValueSelected =null;

int CollectionCount = 0;if (Session[Session_UserSPersonalData] ==null)

{

ValueSelected =new SessionValues();

Session.Add(Session_UserSPersonalData, ValueSelected);

}

else

{

ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]);

}

ProcPaymBTM.Visible =false;PaymenLstBTN.Visible =false;

Dataviewlisting.ActiveViewIndex = 0;

TreeNode SelectedNode =new TreeNode();

SelectedNode = AgencyTree.SelectedNode;

SelectedAgency = SelectedNode.Value.ToString();

Agencytxt.Text = SelectedAgency;

Agencytxt2.Text = SelectedAgency;

Agencytxt3.Text = SelectedAgency;

DbDataReader CollectingDataSelected =null;

try

{

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency +"'");

}

catch

{

}

DataTable TableSet =new DataTable();

TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);

int IndexingValues = 0;foreach (DataRow DataCollectedRowin TableSet.Rows)

{

if (IndexingValues == 0)

{

SQLCONDITION ="where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() +"'";

}

else

{

SQLCONDITION = SQLCONDITION +" OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() +"'";

}

IndexingValues += 1;

}

SQLCONDITION = SQLCONDITION +")";

ConnectionFinanceDB.DisconnectToDatabase();

if (Dataviewlisting.ActiveViewIndex == 0)

{

Dataviewlisting.ActiveViewIndex += 1;

}

else

{

Dataviewlisting.ActiveViewIndex = 0;

}

SelectedAgency = SQLCONDITION;

ValueSelected.CONDITION = SelectedAgency;

?? this is where i use to get count where in other buttons and are displayed... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the view

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

try

{

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

catch

{

}

////this is myCollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg.

"SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List"

here is the function

publicint CollectedFinaceDataCount(String SQLStatement)

{

int DataCollection;

DataCollection = 0;

try

{

SQLCommandExe = FinanceConnection.CreateCommand();

SQLCommandExe.CommandType = CommandType.Text;

SQLCommandExe.CommandText = SQLStatement;

ConnectToDatabase();

DataCollection = (int) SQLCommandExe.ExecuteScalar();

DisconnectToDatabase();

}

catch (Exception ex)

{

Console.WriteLine("Exception Occurred :{0},{1}",

ex.Message, ex.StackTrace.ToString());

}

return DataCollection;

}

So here mu requirement request is to display only the value fronm the view i have against the agency selected

Please help ASAP

Thanks

Santosh

I am getting to display the values

But the problem is that the table has 9 type of payments,

enrollment, placement, WPR, retention 1 month, retention 3 month ,retention 6 month, replacement bonus, satis complete,

Now there are different amouht agains the agency and type of payment above,

So i need do write a for loop can anyone help me

My statement is below ans SQL condition as mentioned in earlier code above posted fetches the value agains each agency, but not against each payment.

my buttons are

EnrollAmtBTM.Text

WPRAmtBTM.Text

PlacAmtBTM.Text

SatisCompAmtBTM.Text

Reten1AmtBTM.Text

Reten3AmtBTM.Text

Reten6AmtBTM.Text

EnrollBonusAmtBTM.Text

and finally

RePlacBonusAmtBTM.Text

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

try /////this is where i need the C# for loop

{

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

catch

{

}

|||

I am trying to something like this using switch case but still being a newbie i have no idea please help.

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

switch(Dataviewlisting.GetType(Payment_Description).ToString() )

{

case ("Enrollment(5 Days)"):

EnrollAmtBTM.Text = CollectionCount.ToString();

break;case ("Placement"):

PlacAmtBTM.Text = CollectionCount.ToString();

break;

case ("Work Participation"):

PlacAmtBTM.Text = CollectionCount.ToString();

break;case ("Satisfactory Complete"):

PlacAmtBTM.Text = CollectionCount.ToString();

break;

default:

case ("Enrollment(5 Days)"):break;

}

|||

Can any body help me with FOR LOOP for this wolode objective of fetching data the whole view has 81 records....9 type agains each type of payment..so i need for loop

Like

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

Foreach........

If statement

then EnrollAmtBTM.Text= value blah blah ...

something like thsi for the first and second posts i hav made here

|||

I am using something like this but still no luck can anybody help...ASAP

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

foreach (V_Payment_Amount_List Rowsin DetailDataList.Rows)

{

if (Rows.EnrollAmtBTM.Text =="EnrollAmtBTM".ToString())

{

Rows["PaymentDescription"] = EnrollAmtBTM.Text;

}

}

// try

// {

// CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

// EnrollAmtBTM.Text = CollectionCount.ToString();

// }

// catch

// {

// }

Thanks,

George

|||

I am trying something like this too but still there is error.My syntax itself is wrong or I am not sure please help,

As you may know from the very first post what i am trying to do...or please ask me if any doubt??

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

foreach(DataRow Paymentin TABLE1.Rows)

{

if (Rows.EnrollAmtBTM.Text == Payment["Enrollment(5 Days)"].ToString())

{

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

}

|||

I am trying to do like this

collecting the data in datatable then displaying then against the payment description but

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT Payment_Amount_Budgeted,Payment_Description,Project_ID FROM V_Payment_Amount_List") + SQLCONDITION;

DataTable Payment =new DataTable();

int CollectionCount = 0;

Payment.Load(CollectingDataSelected, LoadOption.Upsert);

foreach (DataRow DataCollectedRowin Payment.Rows)

{

if (e.Row.Cells[2].Text == Payment["Payment_Description"].ToString()) //here i need help for bringing in the type of payment ie. "enrollment" placement etc to display on EnrollAmtBTM.Text and PlacNotPaidBTM.Text respectively

{

EnrollAmtBTM.Text = CollectionCount.ToString();

}

}

|||

still error is comming

when i use the following code

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData(("SELECT Payment_Amount_Budgeted,Payment_Description,Project_ID FROM V_Payment_Amount_List") + SQLCONDITION);

DataTable Payment =new DataTable();

int CollectionCount = 0;///pointing here

Payment.Load(CollectingDataSelected, LoadOption.Upsert);

foreach (DataRow DataCollectedRowin Payment.Rows)

{

if (DataCollectedRow.ToString() == Payment["Payment_Description"].ToString())

{

PaymentData.ToString() = DataCollectedRow["Enrollment(5 Days)"].ToString();

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(CollectingDataSelected);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

}

|||

I resolved this by the following code fetching the data to a table payment and then using for loop against all 9 type of payments.

ConnectionFinanceDB.DisconnectToDatabase();

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT Payment_Amount_Budgeted, Payment_Description FROM dbo.V_Payment_Amount_List " + SQLCONDITION);DataTable Payment =new DataTable();

Payment.Load(CollectingDataSelected, LoadOption.Upsert);

foreach (DataRow DataCollectedRowin Payment.Rows)

{

if (DataCollectedRow["Payment_Description"].ToString() =="Enrollment(5 Days)")

{

EnrollAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Placement")

{

PlacAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Work Participation")

{

WPRAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="30 days Retention")

{

Reten1AmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="3 Months Retention")

{

Reten3AmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="6 Months Retention")

{

Reten6AmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Enrollment Bonus")

{

EnrollBonusAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Re-Placement Bonus")

{

RePlacBonusAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Satisfactory Complete")

{

SatisCompAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

}

Retrieve ONLY the Report Items that a user has permissions for

Using ListChildren(@."\",true) returns all items in the report server that a
user has access to, however if the user does not have access to view the home
folder, the call fails.
How do I retrieve ALL the folders/reports that a user has permission for
from an application running with the users default credentials?You would have to write or run the program with an admin account & then once
you get a list of all the children,
you'll have to loop through all of them to determine which one belong to the
X-User.
Edgar,
"DaveH" wrote:
> Using ListChildren(@."\",true) returns all items in the report server that a
> user has access to, however if the user does not have access to view the home
> folder, the call fails.
> How do I retrieve ALL the folders/reports that a user has permission for
> from an application running with the users default credentials?

Retrieve new values of a database

Hello, I have the view from a table that keeps incrementing with new
values, I have no control of this database, but I need to retrieve the
new values to insert it on my table (sql server) and send some
parameters of the database to another application, I'm using C# to get
access to both databases, so does anybody know of an efficient way to
retrieve only the new values of the database?

Thanks a lot

Luis Saavedra

Luis,
I don't think there is any general solution to your problem. I usually use triggers, but since you said you have no control on the db that won't work.

The only solution I can think of is to see it your table contains some identity column. If that is the case (and it often is), then you may just select data from the table with an identity column greater than the last you found. This will only work to get new additions to the db. It cannot detect deletions.

HTH
--mc

|||

the problem is that the identity column appears to be autoincremental, but is not in all of the cases, for example I have something like

1001

1002

1003

1005

1007

1006

1004

what I have considered is using a counter (of the inserted registers) in a table of my database, then use a count of all the registers of the informix table and get a top (of the difference between these two tables), the problem is that if in the lapse between the count and the selection of the registers they insert something in the table of informix, I will be missing some values, any ideas to solve this or use another solution...

Thanks

Luis Saavedra

|||

I think you're on the right track here. You might want to store the same values in that field from the source system in your own column in a table on your system. You can then compare based on a join. That might not work for you if there are a lot of values or it is updated a lot. In that case, is there a "Row Number" in your source database you can get at pprogramatically? If so, you could store the last value of that column, which will increment, in one row of your table and update based on it. Does that make sense? Something like this:

Source DB:

(Hidden RowNumber feature) IdentityColumn

(1) 1001

(2) 1002

(3) 1003

(4) 1005

(5) 1007

(6) 1006

Your DB:

Tracking Number IdentityColumn

(1) 1001

(2) 1002

(3) 1003

(4) 1005

Buck Woody

http://www.buckwoody.com