DataSource Controls :: Get Calculated Field Into SQL?
Jan 14, 2011
At the top of my form I have some code that calculates a field
[Code]....
In my form I have this code. What changes below do I need to make to have the value of Date in XXXX?
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:sqlConnection %>"
SelectCommand="SELECT NumPeople, Date1,Time1,Venue FROM People_Count WHERE ((Date1=@ArvDate and Time1 >= '06:00:00.000') or (Date1=XXXX and Time1 <= '06:00:00.000')) and Venue='F & B' ORDER BY Date1 ASC, Time1 ASC">
<SelectParameters>
<asp:QueryStringParameter Name="ArvDate" QueryStringField="ArvDate" />
</SelectParameters>
</asp:SqlDataSource>
I have a simple query on a table to calculate the sum of a field (This field is a calculated field from within SQL 2005)SELECT SUM(RunTime) AS TotaL FROM TimeSheets.It errors out with: Error Message: Operand data char type char is invalid for sum operator.If I use the MAX or MIN function, no isssues. The data in this field is numeric.
although i had to take the table that has my calculated column and make a view (so i could filter) and then bind the gridview to the view.
My question is as this.
1.) in order to get my calculated field i had to load my data into a table (as suggested)
2.)Add a column to the table and perform calculations
3.)make a dataview from the tables because i could not get the Gridview.datasource= ds.tables("loads").select("Deadhead <= " & sngRadius
4.)the largest of the 3 tables my query is based on contains about 7000 records. The way i have the logic & programming now everytime a new "truck location" is put in the textbox1 and "find loads/button1" is clicked. It calculates the deadhead miles of all 7000 records before filtering. I am filtering based on the deadhead miles.
Does anyone have any recomendation about how to speed this up a bit.
Also in order to get paging to work i had to rebind/filter the data again ... means looping through all 7000 records each time paging is called. This seems terribly ineffecient as i am already forced to use access instead of SQL.
Would i see a performance increase if i installed SQL or mySQL and create linked tables to the ones i use in access. or would access still be the bottleneck?
I don't know if the performance increase would be negligible as i still ultimately be getting the data from Access.
I have posted the entire code for the page below. When i originally started i didn't think there would be to much code so i left it all on one page instead of using code-behind. Would i gain anything by swapping to code-behind now?
I'm using a gridview to extract a set of records. I need to add another field to the gridview, which can calculate the difference between a date associated with each record and the current date.
I thought this would be easy but cannot now find out of to do it. All I want to do is execute an sql query on a table and in addition to the table's columns I want to also select/output a field that is calculated within the query. The table has a column "Owner" and I want to compare this to the UserId to produce a bool column "mine". Effectively doing:Select *, (Owner=@UserId) as mine where.....however, this doesn't work so how can I calculate a column in the select statement?
I am having problem with calculated columns in the following code
[Code]....
orderDT is the DataTable. When "Extra" column has nothing (I am NOT using Default), "Total" comes out to be blank? "Price" will have a value (always) "Extra" may not have any value
I have a 20 to 30 fields on a form. Most of them are optional. However, the users can fill out what they want. If a user dosen't fill in a field when data is sent to the database it's sent as blank, instead of NULL. Is there a way I can generically say "for all textboxes if Equals("") then DBNull.Value". Without going through each and every textbox?
I'm a college intern at a small software company. I'm a novice when it comes to ASP.net and Expression Web, so sorry if these are obvious questions.
1. After figuring out how to get fields calculated before they get displayed (did this with SQL), I need to find a way to put certain characters before the output in the fields. I'm fairly certain this is done with a template field, but I have no idea how to touch those without making them blow up.
2. I would also like to have color in the GridViews rows or cells depending on a value returned from the database. Again, I think this would be done with template fields.
3. Thirdly, I need to be able to limit the characters in a few cells. I've searched and found some answers with this, but still Template fields go right over my head.
Split off from http://forums.asp.net/t/1595139.aspx:
I have an additional column called 'KPI %' in my gridview that calculates the percentage of two template fields and thus I cannot use the sortexpression on it. How can I sort the contents of the 'KPI %' column in ASC or DESC format?
I have 2 amount columns (Amount and TotAmount) in my gridview. In Amount column data comes from database. I want to display the total of amounts in Amount column in my TotAmount column (So there would be just one value in TotAmount column irrespective of the number of columns in Amount column). How can I do this.
My gridview displays fields from a table and allows for edit, delete and insert. I need to add a column with a derived field which will be calculated every time the user is either entering a new row or editing the value of the weight field which is one of the fields of the gridview. This calculation is done in the fly and won't be saved in the database.
Is there a way to Sort a Gridview by a Calculated Column? I have a gridview with template fields using lablels and a linq datasource. Those columns sort just fine. I can not get the Calculated column to sort, because there is no sort expression to use...
I have a GridView bound to an ObjectDataSource. Most of the columns are directly databound, however one column is not - it's value is determined in code. The value is being calculated correctly at the RowDataBound event, however when there is more than one data row in the GridView, the calculated fields for all of the rows is blank, but the top row displays the bottom row's calculated field
I have tried several different approaches .SOMEONE PLEASE sow me how to sort on template field with a calculated value.Basically i have half the data i need in the DB and the other half comes from textbox1.textI take the text in textbox1 and pass it along with a value from the DB (Orig) to a function that returns miles.
[Code]....
Whit the above method everything displays correctly but i cannot sort on that column .I also tried using the miles() function in my select statement for my datasource but that was a nightmare and couldn't get that to work i either got "undeclared function "miles()" or when i fixed that i got "orig" not declared.Sorry code is so jumbled , entire code is below:
I need a form that displays three columns drawn directly from a database (no problems here) as well as two fields that require some complex calculations. Not only am I lost as to how to incorporate the calculations into the control, I don't know exactly how to write the calculations. I am working in Visual Web Developer 2008 with Vb.NET.
The first of the two fields should take the sum of all payments made by a given customer and divide it by a DailyMembershipRate, returning a DaysPaid variable. It should take this variable and a CustomerSince field for that particular customer and calculate a PaidThrough field.
The second of the two columns similarly requires the DaysPaid variable described above. It should subtract the CustomerSince field from the current date and return the value in days, giving a MembershipDays variable. Finally, it should subtract MembershipDays from the DaysPaid variable, again returning a value in days, and then multiply this by the DailyRate value for a Credit/Deficit field.
I realize this is a lot, but I haven't been able to find any documentation either online or in VWD or ASP.NET texts on this topic. I could really use some fellow user input.
Dim abc As String abc = Request.QueryString("str") Dim userName As String = CStr(Session("user")) Label2.Text = userName Dim MyConnection As New OdbcConnection(ConnStr) MyConnection.Open() Dim MyCommand As New OdbcCommand MyCommand.Connection = MyConnection If MyCommand.CommandText = "Select userid from week where userid ='" & userName & "'" Then MyCommand.CommandText = "Update week Set value = '" & abc & "' where userid ='" & userName & "'" Else MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES('" & abc & "','" & userName & "')" End If MyCommand.ExecuteNonQuery() MyConnection.Close()
I used sqldatasouce and storeprocedure to populate DetailView. I need update few tables by pass parameters to my storeprocedure. but some value always lost (most of them is keyfield). when I checked those value through sqldatasource_updating event they are null . How can I get the correct value?
