asked on

Performing calculations in Gridview

I have a small ASP.Net Application (My first venture into .NET), It is a log file that tracks my time on different jobs. I have a total hrs budgeted field in my dBase and an Hrs worked field. I display everything in Gridview for my supervisors and would like to have a field in gridview that is representative of Budgeted hrs - Logged hrs. I am transitioning away from classic ASP and VB script and as this would be inline in a classic page, I am not too sure, first where I would perfom the simple calculation and second how to get it into gridview.
I assume I would use an ASP:Label for the display in a template but am unsure. ALso as I do not know where I would perform the calculation I do not know how I would bind it to the grid...

I am really liking the .NET experience and cringe when (for delivery time sake) have to go back to classic.

Thanks for the help

Dan

ASKER CERTIFIED SOLUTION

Dustin Hopkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

panhead802

ASKER

Thanks dusion, that works for this application. I would still like to know how and where to declare a variable write calcualtions to the variable and how to pass it back to the aspx on pageLoad or click event.

Simple vbs would be
<%
dim answer
answer = 5+5
response.write(answer)
%>

Then I can use <%=answer%> anywhere I want on the page or in different calculations.

This got the problem solved and you will ge the points, But I am looking for more.

Thanks
Dan

panhead802

ASKER

Alright I can get the calculation to function, in the label and it is fine. I need to update the database with this value.

When a job is created the remainign hrs field is the total hrs for the job. so this is to create a continual cowntdown.

If a job starts at 100 hours and you click edit key into the current_hrs field 20 I need the update to write the new total, 80 into the remaining hrs field.

Here is my template:

In my update of the datasetcode I have:

UPDATE [ItInfo_main] SET [proj_rem_hr] = @proj_rem_hr WHERE (([proj_ID] = @Original_proj_ID))

Currently it keeps inserting a Null value into the database.

Thanks
Dan

Dustin Hopkins

is your current hours field just a bound field? could you post a little more code releative to the gridview and datasource, and any relative code behind.

Thanks,
Dustin

panhead802

ASKER

No code behind, should be a simple process here is what I have I am sure the syntax and code are all messed up but here it is:

Data Set Update code:

UPDATE [Table1] SET [proj_act_hrs] = @proj_act_hrs WHERE (([ID] = @Original_ID))

Now here is what I would do in classic:

Form page:
<form action="update.asp" method="post">

<%= uset("proj_bug_hr") %>

<input type="text" name="proj_act_hrs_new" >
<input type="hidden" name="proj_act_hrs" value="<%= uset("proj_act_hrs")%>">
<input type="hidden" name="proj_ID" value="<%= uset("proj_ID")%>">

<%
proj_rem_hrs = uset("proj_bug_hr")-uset("proj_bug_hr")
response.write(proj_rem_hrs)
%>

<input type="submit" value="Update">

</form>

update page:
<%
Proj_ID = request.form("proj_ID")
proj_act_hrs = request.form("proj_act_hrs")
proj_act_hrs_new = request.form("proj_act_hrs_new")
proj_act_hrs_insert = proj_act_hrs + proj_act_hrs_new

update proj_table set proj_act_hrs = '"&proj_act_hrs_insert&"' where Proj_ID = '"&Proj_ID&"'
Left out abunch of stuff in the classic sample but it should be enough to diescribe what I am trying to do.

Thanks
Dan

Dustin Hopkins

and you are using a dBase database?

panhead802

ASKER

SQL 2005 Express MDF as built in VWD.

Dustin Hopkins

Which field(s) are you wanting to update?

Dustin Hopkins

could you please repost the code into the code snippet area it looks like some of it was left out. Are you getting any errors or is it just not updating?

panhead802

ASKER

I originally tried to update the proj_rem_hrs but as I had problems I reviewd the process and realized I would be better off updating the proj_actual_hrs field with a running total. If I can add the current bound value to the input value in the edit state and have the total updated to the table then I can display the proj_rem_hrs without binding it to the database. I actually can eliminate the field from the table.

Thanks for your patience and time
Dan

panhead802

ASKER

Here is the code again in the snippet area:

default.aspx
 
<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
        <div>
        
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
                AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ID" 
                DataSourceID="ObjectDataSource1">
                <Columns>
                    <asp:CommandField ShowEditButton="True" />
                    <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                        ReadOnly="True" SortExpression="ID" />
                    <asp:BoundField DataField="proj_bug_hrs" HeaderText="proj_bug_hrs" 
                        SortExpression="proj_bug_hrs" />
                    <asp:TemplateField HeaderText="proj_act_hrs" SortExpression="proj_act_hrs">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("proj_act_hrs") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label2" runat="server" Text='<%# (Convert.ToInt32(Bind("proj_act_hrs")) + Convert.ToInt32(Eval("proj_act_hrs"))) %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="proj_rem_hrs" SortExpression="proj_rem_hrs">
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# (Convert.ToInt32(Bind("proj_act_hrs")) - Convert.ToInt32(Bind("proj_act_hrs"))) %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        
            <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
                DeleteMethod="Delete" InsertMethod="Insert" 
                OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" 
                TypeName="DataSet1TableAdapters.Table1TableAdapter" UpdateMethod="Update">
                <DeleteParameters>
                    <asp:Parameter Name="Original_ID" Type="Int32" />
                </DeleteParameters>
                <UpdateParameters>
                    <asp:Parameter Name="proj_bug_hrs" Type="Int32" />
                    <asp:Parameter Name="proj_act_hrs" Type="Int32" />
                    <asp:Parameter Name="proj_rem_hrs" Type="Int32" />
                    <asp:Parameter Name="Original_ID" Type="Int32" />
                </UpdateParameters>
                <InsertParameters>
                    <asp:Parameter Name="proj_bug_hrs" Type="Int32" />
                    <asp:Parameter Name="proj_act_hrs" Type="Int32" />
                    <asp:Parameter Name="proj_rem_hrs" Type="Int32" />
                </InsertParameters>
            </asp:ObjectDataSource>
        
        </div>
    </form>
</body>
</html>
 
 
Dataset1.xsd:
 
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="DataSet1" targetNamespace="http://tempuri.org/DataSet1.xsd" xmlns:mstns="http://tempuri.org/DataSet1.xsd" xmlns="http://tempuri.org/DataSet1.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" attributeFormDefault="qualified" elementFormDefault="qualified">
  <xs:annotation>
    <xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">
      <DataSource DefaultConnectionIndex="0" FunctionsComponentName="QueriesTableAdapter" Modifier="AutoLayout, AnsiClass, Class, Public" SchemaSerializationMode="IncludeSchema" xmlns="urn:schemas-microsoft-com:xml-msdatasource">
        <Connections>
          <Connection AppSettingsObjectName="Web.config" AppSettingsPropertyName="DatabaseConnectionString" ConnectionStringObject="" IsAppSettingsProperty="true" Modifier="Assembly" Name="DatabaseConnectionString (Web.config)" ParameterPrefix="@" PropertyReference="AppConfig.System.Configuration.ConfigurationManager.0.ConnectionStrings.DatabaseConnectionString.ConnectionString" Provider="System.Data.SqlClient" />
        </Connections>
        <Tables>
          <TableAdapter BaseClass="System.ComponentModel.Component" DataAccessorModifier="AutoLayout, AnsiClass, Class, Public" DataAccessorName="Table1TableAdapter" GeneratorDataComponentClassName="Table1TableAdapter" Name="Table1" UserDataComponentName="Table1TableAdapter">
            <MainSource>
              <DbSource ConnectionRef="DatabaseConnectionString (Web.config)" DbObjectName="dbo.Table1" DbObjectType="Table" GenerateMethods="Get" GenerateShortCommands="true" GeneratorGetMethodName="GetData" GetMethodModifier="Public" GetMethodName="GetData" QueryType="Rowset" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="false" UserGetMethodName="GetData" UserSourceName="GetData">
                <DeleteCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="false">
                    <CommandText>DELETE FROM [Table1] WHERE (([ID] = @Original_ID))</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="@Original_ID" Precision="0" ProviderType="Int" Scale="0" Size="0" SourceColumn="ID" SourceColumnNullMapping="false" SourceVersion="Original" />
                    </Parameters>
                  </DbCommand>
                </DeleteCommand>
                <InsertCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="false">
                    <CommandText>INSERT INTO [Table1] [proj_act_hrs] VALUES ( @proj_act_hrs)</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="@proj_bug_hrs" Precision="0" ProviderType="Int" Scale="0" Size="0" SourceColumn="proj_bug_hrs" SourceColumnNullMapping="false" SourceVersion="Current" />
                      </Parameters>
                  </DbCommand>
                </InsertCommand>
                <SelectCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>SELECT     ID, proj_bug_hrs, proj_act_hrs
FROM         Table1</CommandText>
                    <Parameters />
                  </DbCommand>
                </SelectCommand>
                <UpdateCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="false">
                    <CommandText>UPDATE [Table1] SET [proj_act_hrs] = @proj_act_hrs WHERE (([ID] = @Original_ID))</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="@proj_bug_hrs" Precision="0" ProviderType="Int" Scale="0" Size="0" SourceColumn="proj_bug_hrs" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="@Original_ID" Precision="0" ProviderType="Int" Scale="0" Size="0" SourceColumn="ID" SourceColumnNullMapping="false" SourceVersion="Original" />
                    </Parameters>
                  </DbCommand>
                </UpdateCommand>
              </DbSource>
            </MainSource>
            <Mappings>
              <Mapping SourceColumn="ID" DataSetColumn="ID" />
              <Mapping SourceColumn="proj_bug_hrs" DataSetColumn="proj_bug_hrs" />
              <Mapping SourceColumn="proj_act_hrs" DataSetColumn="proj_act_hrs" />
                          </Mappings>
            <Sources />
          </TableAdapter>
        </Tables>
        <Sources />
      </DataSource>
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="DataSet1" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:Generator_UserDSName="DataSet1" msprop:Generator_DataSetName="DataSet1">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Table1" msprop:Generator_UserTableName="Table1" msprop:Generator_RowDeletedName="Table1RowDeleted" msprop:Generator_TableClassName="Table1DataTable" msprop:Generator_RowChangedName="Table1RowChanged" msprop:Generator_RowClassName="Table1Row" msprop:Generator_RowChangingName="Table1RowChanging" msprop:Generator_RowEvArgName="Table1RowChangeEvent" msprop:Generator_RowEvHandlerName="Table1RowChangeEventHandler" msprop:Generator_TablePropName="Table1" msprop:Generator_TableVarName="tableTable1" msprop:Generator_RowDeletingName="Table1RowDeleting">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" msdata:ReadOnly="true" msdata:AutoIncrement="true" msdata:AutoIncrementSeed="-1" msdata:AutoIncrementStep="-1" msprop:Generator_UserColumnName="ID" msprop:Generator_ColumnPropNameInRow="ID" msprop:Generator_ColumnVarNameInTable="columnID" msprop:Generator_ColumnPropNameInTable="IDColumn" type="xs:int" />
              <xs:element name="proj_bug_hrs" msprop:Generator_UserColumnName="proj_bug_hrs" msprop:Generator_ColumnPropNameInRow="proj_bug_hrs" msprop:Generator_ColumnVarNameInTable="columnproj_bug_hrs" msprop:Generator_ColumnPropNameInTable="proj_bug_hrsColumn" type="xs:int" minOccurs="0" />
              <xs:element name="proj_act_hrs" msprop:Generator_UserColumnName="proj_act_hrs" msprop:Generator_ColumnPropNameInRow="proj_act_hrs" msprop:Generator_ColumnVarNameInTable="columnproj_act_hrs" msprop:Generator_ColumnPropNameInTable="proj_act_hrsColumn" type="xs:int" minOccurs="0" />
              <xs:element name="proj_rem_hrs" msprop:Generator_UserColumnName="proj_rem_hrs" msprop:Generator_ColumnPropNameInRow="proj_rem_hrs" msprop:Generator_ColumnVarNameInTable="columnproj_rem_hrs" msprop:Generator_ColumnPropNameInTable="proj_rem_hrsColumn" type="xs:int" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
    <xs:unique name="Constraint1" msdata:PrimaryKey="true">
      <xs:selector xpath=".//mstns:Table1" />
      <xs:field xpath="mstns:ID" />
    </xs:unique>
  </xs:element>
</xs:schema>

Open in new window

panhead802

ASKER

I have simplified the request:

I have attached the code in a snippet below:

I want to be able to add the bound value in proj_act_hrs to the value keyed in in the edit field and then have the total posted to the database field when updated.

Make sense, let me know if you need the code for the xsd as well.

thanks
Dan

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
        <div>
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
                AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ID" 
                DataSourceID="ObjectDataSource1">
                <Columns>
                    <asp:CommandField ShowEditButton="True" />
                    <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                        ReadOnly="True" SortExpression="ID" />
                    <asp:BoundField DataField="proj_bug_hr" HeaderText="proj_bug_hr" 
                        ReadOnly="True" SortExpression="proj_bug_hr" />
                    <asp:TemplateField HeaderText="proj_act_hr" SortExpression="proj_act_hr">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("proj_act_hr") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("proj_act_hr") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        
            <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
                DeleteMethod="Delete" InsertMethod="Insert" 
                OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" 
                TypeName="DataSet2TableAdapters.Table2TableAdapter" UpdateMethod="Update">
                <DeleteParameters>
                    <asp:Parameter Name="Original_ID" Type="Int32" />
                </DeleteParameters>
                <UpdateParameters>
                    <asp:Parameter Name="proj_bug_hr" Type="Int32" />
                    <asp:Parameter Name="proj_act_hr" Type="Int32" />
                    <asp:Parameter Name="Original_ID" Type="Int32" />
                </UpdateParameters>
                <InsertParameters>
                    <asp:Parameter Name="proj_bug_hr" Type="Int32" />
                    <asp:Parameter Name="proj_act_hr" Type="Int32" />
                </InsertParameters>
            </asp:ObjectDataSource>
        
        </div>
        
        
        
    </form>
</body>
</html>

Open in new window

Dustin Hopkins

Hmm I don't see any asthetic problems with the code above, does the update command work if you do it manually? Also please post the dataset code.

Also I feel that while the initial question was worth 50 pts, the follow on question isnt. If I can solve it please consider increasing the points. If I cant solve it then it would be better to keep it at 50 and post the follow on as a new question.

Thanks,
Dustin

Dustin Hopkins

Oh one more question, is there a reason to use the original_ID instead of just using ID?

panhead802

ASKER

I have no problem increasing the points, I didn't think this should be a difficult question as it is quite a simple solution in classic ASP, as I said I am just learning .NET.
I think I will award you the 50 points and repost as this thread is getting kind of convoluted...

Thanks for the info on running calculations every bit of knowledge I can pick up helps.

Dan