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();

}

}