.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Query for Values based on Max Date

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :ASP.Net

I need to find distinct Courses a Student has taken but the ones with Max date. Suppose we have the following data:

ID               Student             Course                Date      

1               Stu-100             Crs1                23/03/2009

2               Stu-100             Crs1                20/06/2010

3               Stu-100             Crs1               14/01/2011

4  &n

View Complete Post

More Related Resource Links

Query Builder - Show values where date is greater than DateTime now


Visual Web Developer 2010 Express

ASP.Net and C#


Hi Guys,

My issue is that I am pulled three tables together using the Query Builder and I only want to show data where a specific date is greater than todays date. Here is what i have so far:

SELECT        salesitems.sona, salesitems.sonitem, ZOverdueOrders.ZOOType, ZOverdueOrders.ZOOReason, salesitems.sduedate, salesitems.soitemstate
FROM            salesorders INNER JOIN
                         salesitems ON salesorders.son = salesitems.sona LEFT OUTER JOIN
                         ZOverdueOrders ON salesitems.sona = ZOverdueOrders.ZOOSON AND salesitems.sonitem = ZOverdueOrders.ZOOSONI
WHERE        (salesitems.sduedate > CONVERT(DATETIME, '2010-09-01 00:00:00', 102))
ORDER BY salesitems.sona, salesitems.sonitem

Where it says '2010-09-01 00:00:00' I want that to be the todays date.


Is this possible? Can anyone help please?

Thanks in advance for any help!!!!




how to bind Dropdownlist based on textbox values



I have one drodownlist which show data based om textbox values...

Let us consider i have 

Select Name from table1 where Type IN(@typ)

here @typ is textbox values

here for example textbox will have some thing like this L1,L2,L3..

How to filter Name based and shown in drodownlist based on this L1,L2,L3(on textbox)..

Help me in this

On New Item form, select item on choice dropdown based on query result.


I posted this on Experts Exchange awhile back with no answer, so here's hoping someone here has an idea :)

So I've mostly figured this problem out, but I need some help in getting over the hump... note that I'm using JavaScript to do this (SPServices) on SharePoint 07'.

I'm on the New Items page of a list. Based on a user's selection in a "Teacher" dropdown, I'm doing the following:

1.) Get the value of the Teacher field and several other fields (School and Visit Date)
2.) Run a CAML query based on those fields to find the last list item entered for that Teacher

So here's where I'm stuck...

I have 10 choice fields, all with choices 0, 4, 7, and 10. Depending on what they were set to in the last entered item (which I queried), I want to set the selected item on each of those dropdowns to equal that. So "Score 1" was set to "4" on the la

Filtering/selecting rows based on date


I have a gridview, based on thsi datasource:


<asp:SqlDataSource ID="SqlDataSourceAllLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>"               
        SelectCommand="SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?') AS Expr1 FROM tblDelivered FULL OUTER JOIN tblDeliveredInfo ON tblDelivered.delivered_LoadNo = tblDeliveredInfo.deliveryInfo_LoadNr ORDER BY delivered_Date DESC">

Using a dropdownlist, I can filter (by changing datasource) the results based on customer names in a dropdownlist. This datasource, working perfectly, looks like this:


<asp:SqlDataSource ID="sqldataSourceKundeLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>" 
        SelectCommand="SELECT  DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?&#

gridview help on each dynamic row solve this show values based on all columns

<asp:GridView style="Z-INDEX: 101; LEFT: 1px; POSITION: absolute; TOP: 183px" id="Gridview1" runat="server" Width="613px" Height="127px" ForeColor="Teal" Font-Size="12pt" Font-Names="Palatino Linotype" Font-Bold="True" Visible="False" BorderColor="Transparent" AutoGenerateColumns="false" CellSpacing="2" GridLines="None" OnRowCreated="Gridview1_RowCreated" SelectedIndex="5" ShowFooter="true" OnRowDataBound="Gridview1_RowDataBound"><Columns>  <asp:TemplateField HeaderText="ItemCode"><ItemTemplate> <asp:DropDownList id="DropDownList1" tabIndex=16 runat="server" AutoPostBack="True" DataValueField=" ItemCode " DataTextField=" ItemCode" ></asp:DropDownList></ItemTemplate></asp:TemplateField>  <asp:TemplateField HeaderText="ItemName"><ItemTemplate> <asp:DropDownList id="DropDownList2" tabIndex=16 runat="server" AutoPostBack="True" DataValueField="ItemName" DataTextField="ItemName" ></asp:DropDownList> </ItemTemplate> </asp:TemplateField>  <asp:TemplateField HeaderText="Category"&

Application to Constantly query Table Values and Refresh with most recent value

Hello, I must apologize in advance because I am not sure if this is the correct place for this question. I am looking for ideas on how to build an application that in the most basic sense contains a field with the most current data from a table in a SQL Server database (this would be based off of a datetime timestamp in the table). I am new to this kind of problem and am unsure where to start looking for solution ideas. The table value updates variably and could possibly update every second to every hour. I would like to design an application that would be lightweight to both the server and the user's machine. This would take the place of an unneeded report on my Reporting Services server. Any and all suggestions would be welcome because I am not sure of options I even have on this. Thanks in advance for your time and consideration, Matt

Need help in Date Query

HI Guys I have date like 2008-10-31 16:36:567 I want to convert like 20081031 00:00:000 for this I'm using convert(datetime,(convert(varchar(8),replace('2008-10-31 16:36:567','-','')))) is there any option to do this?

Need to add hard coded values to LINQ query result

I am running a LINQ query which populates a list used by a DropDownList, I need to insert an "Unassigned" value to the list. The function below queries the values correctly from the db but does not insert the "Unaasinged" value to the list. Please help.   public List<KeyValuePair<int, string>> GetTestList() { KeyValuePair<int, string> item = new KeyValuePair<int, string>(0, "Unassigned"); using (DataClassesDataContext db = new DataClassesDataContext()) { var Query = from records in db.TEST_SUITEs orderby records.TEST_SUITE_ID select new KeyValuePair<int, string> ( records.TEST_SUITE_ID, string.Format(records.TEST_SUITE_ID + " " + records.SUITE_TYPE + " " + records.SUITE_NAME) ); //Query.ToList().Insert(0, item); Query.ToList().Insert(0,item); return Query.ToList(); } }  

Problem with date values in data-driven subscription reports

Hello together, I have a question in regards of data-driven subscription report. I already have an ad-hoc report in place which can be customized by date etc. I now want to run these reports with a subscription. My challenge is now that I don't know how I can pre-define the date. I basically want the start date of NOW() and the end date 7 days in the past. I was looking around but did not get any clue where and how I have to configure it the way it works. I found this thread but honestly do not understand it well... :( http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/fb48fd15-09dd-4f5f-a09f-d92bf304c1a2 Can someone help me out please? M.

Alert based on date

I have a list of SSL certificates and their expiration dates.  I created a column that calculates the date 45 days before the expiration and then a view that only shows those items where the (expiration - 45) > Today. I then set an alert for any changes to items in that view.  However, I don't get an alert for items that pop into the view...I guess because the item itself did not change.  Is there a way to send an alert based on date?  My SharePoint enviroment is locked down so I can't create custom workflows or connect via Designer.

SSRS Report based on SSAS Cubes doesn't show NULL values correctly

I have a SSRS report which is based on an SSAS Cube. In the Cube the formating of numeric fields that NULL values are properly shown as NULL values. In the Cube Browser and Excel this is shown correctly. However when I design the report already the query against SSAS shows these fields as "0" instead of NULL. Is there a way to fix this or is this a bug ?  

Number of query values and destination fields are not the same

If RadioButton1.Checked = True Then rbtn = 1 Else rbtn = 2 End If ' Our insert command strSQLQuery = "INSERT INTO tblJobs " _ & "(filingDate, filingDay, reportDate, reportTime, company, jobsite, location, duration, DrugTestReq, foreman, instructions, jobSteward, nPlumbers, nFitters, nWelders, nTradesmen, nPlAppr, nFitAppr, nServTech, nHelpers) " _ & "VALUES (@DTValue, @TextDay, @DTValue2, @TextValue, @TextValue2,@TextValue3,@TextValue4,@TextValue5,@rbutton,@TextValue6.@TextValue7,@TextValue8,@NumVal,@NumVal2,@NumVal3,@NumVal4,@NumVal5,@NumVal6,@NumVal7,@NumVal8)" ' Create new command object passing it our SQL insert ' and telling it which connection to use. objCommand = New OleDbCommand(strSQLQuery, objConnection) ' Add parameters that our SQL command takes: 'radio button 'OleDbType. objCommand.Parameters.Add(New OleDbParameter("@rbutton", OleDbType.LongVarChar)) '- objCommand.Parameters.Add(New OleDbParameter("@DTValue", OleDbType.Date)) objCommand.Parameters.Add(New OleDbParameter("@TextDay", OleDbType.LongVarChar)) objCommand.Parameters.Add(New OleDbParameter("@DTValue2", OleDbType.Date)) objCommand.

Search based on creation date

Hello All, I would like to ask which managed property I should use for searching based on creation date. It seems I can't find a managed property to do this. I created my own managed property with the right crawled properties, which works. But I think this should be something out of the box.  Please help, thank you  Anna

Export Data to Excel Based on Date Range

I have data from multiple tables that I would like to display in a gridview via a stored procedure.  I need to select the data based on a date range and display it.  Once displayed I need to include a button that would allow the user to export the gridview data to an excel spreadsheet.  Does anyone have an out of the box solution or know of a tutorial I can use to accomplish this feat?  I am currently using 2.0 for this project. I am newish to .net. Thanks in advance. Here is my stored procedure that calls the data from multiple tables.USE [DATABASENAME] GO /****** Object: StoredProcedure [dbo].[GetUsersAll] Script Date: 09/03/2010 08:20:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUsersAll] AS SELECT aspnet_Users.UserName, aspnet_Membership.Email, Team.Name, MemberTeamRole.InsertDate, MemberTeamRole.ManagerApprovedDate, MemberTeamRole.RegistrarApprovedDate, MemberTeamRole.RoleName, MemberInfo.firstname, MemberInfo.lastname FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN MemberInfo ON aspnet_Users.UserId = MemberInfo.memberid LEFT OUTER JOIN MemberTeamRole ON aspnet_Membership.UserId = MemberTeamRole

bind values for the drop down based on people picker value change

hi, i have a two lists in my sp. list 1 name  items user1 item1 user1 item2 user2 item1   and list 2 is in this way in this i have a people picker and a drop down when ever i change the name in people picker the value should get filled from list 1 and fill in drop down list so i need to get the on text changed event of people picker and bind it in the dropdown list could some help me out in fixing thisjayaraja

query negative and positive values and not their boolean counterpart

Good Day Need help.. I have this simple table, CREATE TABLE [dbo].[sample] ( [id] [int] NOT NULL , [number] [int] NULL , ) ON [PRIMARY] i wanted to make a select query that would display two columns(positive, negative) the sql statement that i made: "select (sample.number > 0) as positive, (sample.number < 0) as negative from sample " what happened is that, it displays the boolean counterpart and not the value.. I wanted to show this kind of data: substitute the "False" value into 0 and "-1/true" value with the exact absolute numerical value.   |debit||credit| |   0   ||  200 | |   0   ||  57   | |   50 ||  0     |   is this possible?  

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend