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

}

}

Retrieving Data from database

Hi,
I have relatively less experience to SQL. I had a question. Say I have to
display certain records on a datagrid. this datagrid is dependent on these
parameters.
suppose a user enters a partial value in a text box. eg: "12"
I make use of "like" feature(...partID like '12%') in the query and it
retrieved "3" records from the database.
using the resultset I have to retrieve 5 records prior and 5 records after
the "original" set of results(3)... and display the total records (5+3+5 = 13) on the datagrid.
whats the best way of doing this... I have no clue of how to do this... hope
I have conveyed my idea properly...
Place advice,
Stephen> using the resultset I have to retrieve 5 records prior and 5 records after
> the "original" set of results(3)... and display the total records (5+3+5 => 13) on the datagrid.
You need to define what "prior" and "after" mean. Perhaps you are used to
Excel or Access, but in SQL Server, a table is an unordered set of rows. To
obtain the 5 rows "before" and "after" a certain row, you need to tell us
how you determine which rows come before and after...
--
http://www.aspfaq.com/
(Reverse address to reply.)

Retrieving Data from database

Hi,
I have relatively less experience to SQL. I had a question. Say I have to
display certain records on a datagrid. this datagrid is dependent on these
parameters.
suppose a user enters a partial value in a text box. eg: "12"
I make use of "like" feature(...partID like '12%') in the query and it
retrieved "3" records from the database.
using the resultset I have to retrieve 5 records prior and 5 records after
the "original" set of results(3)... and display the total records (5+3+5 =
13) on the datagrid.
whats the best way of doing this... I have no clue of how to do this... hope
I have conveyed my idea properly...
Place advice,
Stephen
> using the resultset I have to retrieve 5 records prior and 5 records after
> the "original" set of results(3)... and display the total records (5+3+5 =
> 13) on the datagrid.
You need to define what "prior" and "after" mean. Perhaps you are used to
Excel or Access, but in SQL Server, a table is an unordered set of rows. To
obtain the 5 rows "before" and "after" a certain row, you need to tell us
how you determine which rows come before and after...
http://www.aspfaq.com/
(Reverse address to reply.)

Retrieve Count from stored procedure and display in datagrid.

Hi Guys,

I have a sql procedure that returns the following result when I execute it in query builder:

CountE ProjStatus

6 In Progress

3 Complete

4 On Hold

The stored procedure is as follow:

SELECT COUNT(*) AS countE, ProjStatus
FROM PROJ_Projects
GROUP BY ProjStatus

This is the result I want but when I try to output the result on my asp.net page I get the following error:

DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.

Source Error:

Line 271: </asp:TemplateColumn>Line 272: <asp:TemplateColumn>Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>Line 274: </asp:TemplateColumn>Line 275: </columns>

My asp.net page is as follows:

<script runat="server">

Dim myCommandPSAsNew SqlCommand("PROJ_GetProjStatus")

' Mark the Command as a SPROC

myCommandPS.CommandType = CommandType.StoredProcedure

Dim numasinteger

num =CInt(myCommand.ExecuteScalar)

'Set the datagrid's datasource to the DataSet and databind

Dim myAdapterPSAsNew SqlDataAdapter(myCommandPS)

Dim dsPSAsNew DataSet()

myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS

dgProjSumm.DataBind()

myConnection.Close()

</script>

<asp:datagridid="dgProjSumm"runat="server"

BorderWidth="0"

Cellpadding="4"

Cellspacing="0"

Width="100%"

Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"

Font-Size="xx-small"

AutoGenerateColumns="false">

<columns>

<asp:TemplateColumnHeaderText="Project Summary"HeaderStyle-Font-Bold="true">

<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem,"ProjStatus" ))%></itemtemplate>

</asp:TemplateColumn>

<asp:TemplateColumn>

<itemtemplate> <%# DataBinder.Eval(Container.DataItem,"Count" )%></itemtemplate>

</asp:TemplateColumn>

</columns>

</asp:DataGrid>

Please help if you can Im havin real trouble here.

Cheers

Since you have the count aliased as countE, it should be:

<%# DataBinder.Eval(Container.DataItem, "countE" )%>

|||

Thanks for pointing that out, I changed it and get the same error, any other ideas, do I have to create an output parameter or something.

Cheers

|||

Why are you using ExecuteScalar and a DataSet? Also, where is the SQL Connection as I didn't see that in your code?

Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
myCommandPS.CommandType = CommandType.StoredProcedure

Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()