Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
| Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 26th, 2006, 05:26 PM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert calculated values using sqldatasource

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
  #2 (permalink)  
Old June 27th, 2006, 03:30 AM
Authorized User
 
Join Date: Jun 2006
Location: Manchester, , United Kingdom.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #3 (permalink)  
Old June 27th, 2006, 10:50 AM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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

  #4 (permalink)  
Old June 27th, 2006, 01:32 PM
Authorized User
 
Join Date: Jun 2006
Location: Manchester, , United Kingdom.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #5 (permalink)  
Old June 29th, 2006, 12:05 PM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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:)
  #6 (permalink)  
Old November 3rd, 2006, 10:22 AM
Registered User
 
Join Date: Nov 2006
Location: Titusville, FL, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple values insert into one field mateenmohd Classic ASP Basics 0 May 29th, 2007 02:11 AM
insert using stored procedure & sqldatasource ctrl mahajanvit ASP.NET 2.0 Basics 10 September 13th, 2006 09:03 AM
Sqldatasource Inserting calculated values: nuttylife2 ASP.NET 2.0 Professional 0 June 26th, 2006 05:36 PM
How t o insert multiple values qazi_nomi Access ASP 2 May 3rd, 2005 12:45 AM
Insert ODBC Values into Table Ben Access VBA 3 January 26th, 2005 01:12 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.