Insert calculated values using sqldatasource

nuttylife2 · June 26th, 2006, 05:26 PM

I have created a sqldatasource to capture input values from a form, I also need to add calculated values, ie. txtbox value a * txtbox value b, =total charge etc. I am using a store procedure to capture these values. for the parameter collection, I have all the input values stated as follows:
<InsertParameters>
                    <asp:FormParameter DefaultValue="0" FormField="txtaccount" Name="ActNumber" Type="String" />
                    <asp:FormParameter DefaultValue="0" FormField="txtmeter" Name="MeterNumber" Type="String" />
                    <asp:FormParameter DefaultValue="0" FormField="txtdov" Name="VisitDate" Type="DateTime" />
                    <asp:FormParameter DefaultValue="" FormField="txtsvadd" Name="SvAdd" Type="String" />
                    <asp:Parameter Name="TampId2" Type="Int32" />
                    <asp:Parameter DefaultValue="64.44" Name="charge1" Type="Decimal" />
                    <asp:FormParameter FormField="txtq1" Name="quantity1" Type="Int32" />

                    <asp:QueryStringParameter DefaultValue="1" Name="subtotal1" QueryStringField="sub1" Type="Decimal" />
                    <asp:FormParameter DefaultValue="1" FormField="txtpeople" Name="labor" Type="Int32" />
                    <asp:FormParameter DefaultValue="1" FormField="txthrs" Name="laborhrs" Type="Decimal" />
                    <asp:FormParameter DefaultValue="1" FormField="txttotlab" Name="totlabchg" Type="Decimal" />
                    <asp:Parameter DefaultValue="300.00" Name="flatrate" Type="Decimal" />
                    <asp:FormParameter DefaultValue="1" FormField="txttotalcharge" Name="totalcharge"
                        Type="Decimal" />
                    <asp:FormParameter DefaultValue="1" FormField="txtgrandtotal" Name="grandtotal" Type="Decimal" />
                    <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
                </InsertParameters>
___________________________
here the values total charge, grand total etc are calculated fields that I set to a text box, and then I am attributing that as parameters.
I also tried querystring parametes as follows:
<asp:QueryStringParameter DefaultValue="1" Name="totlabchg" QueryStringField="laborcharge"
                        Type="Decimal" />
But that did not work either.
PLEASE HELP, i AM DESPERATE.
THANKS IN ADVANCE

rrmarriott · June 27th, 2006, 03:30 AM

Hi There,

If I understand your situation correctly, you want to take the value of two (maybe more) fields, do a calculation and assign the result to a single parameter. If so, you should use the RowUpdating event of your GridView.

Code:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//concatenating two text fields   
e.NewValues["totalcharge"] = txtFirstValue.Text + txtSecondValue.Text;

//using value of other parameters
e.NewValues["totalcharge"] = Convert.ToInt32(e.NewValues["laborhrs"]) * 7.5;
}

This should then alter the values of your parameters before they are sent to the database.

Hope This Helps,
Rich

nuttylife2 · June 27th, 2006, 10:50 AM

Hi Rich,
I really appreciate your response to this. I will try what you suggested and let you know how it went. Actually I have designed a form the old fashioned way, because it is mainly for input and the Grid view always showed the existing data as default, (unless the insert is shown as default?). You got me right, I am trying to calculate values before I set them up as parameters, can I do it on a regular form design with the sql data source?
thank you very much? your reply will be very much appreciated..:)
Pam

Quote:

quote:Originally posted by rrmarriott
Hi There,

If I understand your situation correctly, you want to take the value of two (maybe more) fields, do a calculation and assign the result to a single parameter. If so, you should use the RowUpdating event of your GridView.

Code:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//concatenating two text fields   
e.NewValues["totalcharge"] = txtFirstValue.Text + txtSecondValue.Text;

//using value of other parameters
e.NewValues["totalcharge"] = Convert.ToInt32(e.NewValues["laborhrs"]) * 7.5;
}

This should then alter the values of your parameters before they are sent to the database.

Hope This Helps,
Rich

rrmarriott · June 27th, 2006, 01:32 PM

Pam,

I don't think that there is any design time support for inserting calculated fields (I'm not fully sure, correct me if wrong anyone). But handling the events of the SqlDataSource is nearly as easy, honestly! Here's how...

In my previous code I said to handle the RowUpdating event of the GridView. But you don't have a grid view! So a different event needs to be handled instead, with the same code (pretty much) inside the event handler, but this time of your SqlDataSource.

The two events of your SqlDataSource that you need to handle are the Updating and Inserting events. To do this, just select the SqlDataSource in Visual Studio (Web Developer) and from the events list on the right hand side double click the Updating event, then the Inserting event. This will add the handlers in the code behind.

In the code behind, do the same calculations on the parameters as before, but in the new event handlers. Something like:

Code:

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
    //concatenating two text fields   
    e.NewValues["totalcharge"] = txtFirstValue.Text + txtSecondValue.Text;

    //using value of other parameters
    e.NewValues["totalcharge"] = Convert.ToInt32(e.NewValues["laborhrs"]) * 7.5;
}

protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    //concatenating two text fields   
    e.NewValues["totalcharge"] = txtFirstValue.Text + txtSecondValue.Text;

    //using value of other parameters
    e.NewValues["totalcharge"] = Convert.ToInt32(e.NewValues["laborhrs"]) * 7.5;
}

This should then calculate the values before they are committed to the database for you. Is this more what you were looking for?

Sorry if this is simple (or not) for you, as I don't know what level you are with ASP.NET.

Rich

nuttylife2 · June 29th, 2006, 12:05 PM

Hi Rick,
I tried that, but the e.newvalue was not recognized, the intellisense only showed, e.cancel or e.command....So I gave up on it, spent ( almost a week on this.:(.) I decided to do the calculations in the store procedure itself, and that finally worked. thanks for your help, I really appreciate it.
NOW, I have another problem, when I click the submit button, the data is getting submitted twice into the database. I have sqldatasourc1.Insert set up in the submit button, any ideas?
Thanks:)

cyp7 · November 3rd, 2006, 10:22 AM

Here's the rest of the solution.

e.Command.Parameters["@ID"].Value = 2;

Where @ID is the name of your column in your
SQL table in the db.

Hope this works for you!

Carolyn