Newbie Question: How Do I Show Rows From The 14 Last Days? (using Sqldatasource)
Feb 12, 2008
I am using sqldatasource to make a connection to my access database. I have a table with a field called "last_activity" (Date/Time datatype).
I want to make a query which shows users/players (the rows in the table) that have been active the last 14 days, so I have to use "last activity" as parameter.
But how do I code this?
<asp:SqlDataSource runat="server" id="race_a_car_Source" ProviderName="<%$ ConnectionStrings:speedracerConnectionString1.ProviderName %>" ConnectionString="<%$ ConnectionStrings:speedracerConnectionString1 %>" SelectCommand="SELECT * FROM [race_a_car] WHERE ([last_activity] >= ?)">
<SelectParameters>
<asp:parameter DefaultValue=" what should I write here? " Name="last_activity" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
View 3 Replies
ADVERTISEMENT
Jan 25, 2007
I am pretty much brand new to reporting services so I apologize for any simple questions I may ask.
I have created a call report that shows data broken down by day and grouped by week. Everything works great. However, if there is no data on a certain day is does not show me that day on the report. Is there a way I can make it display the day even if there is not data for that day and just give me 0 values? Or do I need to modify my query?
Thanks in advance.
View 1 Replies
View Related
Feb 27, 2007
Hello,I would like to show the result fo each day of the past 30 days. I might not have data to display for everyday but I still want the date to show up01/27/2007 resultA01/28/2007 resultB01/29/2007 .....02/26/2007 resultC02/27/2007 resultD
Thanks for your helpArnold
View 3 Replies
View Related
Aug 26, 2015
I want to show this kind of output
UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
View 10 Replies
View Related
May 21, 2008
Why does this not work? <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:Parameter Name="@employeeID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>In code behind: protected void OnSourceSelecting(object sender, SqlDataSourceCommandEventArgs args) { // THIS LINE THROWS EXCEPTION: An SqlParameter with ParameterName '@campaignID' is not contained by this SqlParameterCollection. args.Command.Parameters["@employeeID"].Value = 3; // In future, will use dynamic value. } If, instead I do this: <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:QueryStringParameter Name="@campaignID" QueryStringField="id" /> </SelectParameters> </asp:SqlDataSource>(Of course I call page with EmployeePage.aspx?id=123)I get the exception: Must declare the scalar variable "@employeeID".
View 5 Replies
View Related
May 19, 2008
Hi,I have two SQLDataSources called "LeagueTableHome" an "LeagueTableAway" on my page.
I want to create another SQLDataSource called "LeagueTableTotal" on my page which adds up all the totals from each of the other two sources.
The datasource looks like this:
Team, Pld, W, D, L, F, A, Agg, Pts
my code for LeagueTableHome looks like this:
SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
I want then to show LeagueTableTotal in a GridView.
Can anybody help?
View 4 Replies
View Related
Aug 29, 2006
Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (???) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance. Bill
View 2 Replies
View Related
Aug 22, 2007
So i have a data table with a group in it.
I want a row for every item and i'ts count displayed. the problem is, if the count is 0, the row is not displayed.
how can i make all the rows show?
View 3 Replies
View Related
Apr 25, 2001
I want to show a row number for each row returned by a SELECT starting at 1 and incrementing by 1 for each row.
Anyone got any ideas?
View 2 Replies
View Related
Oct 25, 2004
Newbie to the SQL Server.
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
Pam
View 8 Replies
View Related
Sep 28, 2007
I am trying to create an exception report that will show the difference between two versions of the same row. (Combination of two different sources in sql, with source 1 having childID = 0 and the other source having childID = 1; parentID is the link between them)
The results are as follows:
ParentID - ChildID - Col1 - Col2 - Col3
1 - 0 - AA - BB - CC
1 - 1 - AA - BF - CC
2 - 0 - GG - NN - TT
2 - 1 - DE - NN - TA
3 - 0 - etc
3 - 1 - etc
4 - etc
How can I render the differences in red in RS?
View 1 Replies
View Related
Mar 30, 2007
Hi!
I've created a report in Report Builder, based on a query. When I run the report in Report Builder, it doesn't show the duplicates rows, even if it works fine in Data source view.
How can I see al rows in Report Builder(even if they're duplicates) ?
Thank you in advance.
Best regards,
VV
View 1 Replies
View Related
May 20, 2012
I need to show the total amount of rows in a specific table?
The query is as follows:
As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.
The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).
View 4 Replies
View Related
Apr 19, 2013
I have an requirement where i need to show Employee Table and CustomerMeta Table joins In CustomerMeta Table (CustID)
Reference to Employee Table and Metavalues table Metavalues table is like master table.
In Application i will get multiple select box selection (DrivingLicense,Passport etc;) so that data will be inserted in comma(',') separated values So in my desired output i need to show as i need to show split those comma separated and for every MetaTypeID MetaTypeName as a row as showed in desired output
Metavalues table :
MetaID Metavaluedescription
1 Driving License
2 Passport
3 AadharCard
4 EducationalProof
5 ResidentialProof
CustomerMeta Table :
CustID MetaTypeID MetaTypeName
2 1,2,3,4,5 DrivingLicense,Passport,AadharCard,EducationalProof,ResidentialProof
3 1,2,3DrivingLicense,Passport,AadharCard
Employee Table
EmpID CustID EmPname
1001 2Mohan
1002 3 ramu
Desired OutPut :
EMPID CustID EmPname MetaTypeID MetaTypeName
1001 2 Mohan 1 Driving License
1001 2 Mohan 2 Passport
1001 2 Mohan 3 AadharCard
1001 2 Mohan 4 EducationalProof
1001 2 Mohan 5 ResidentialProof
1002 3 ramu 1 Driving License
1002 3 ramu 2 Passport
1002 3 ramu 3 AadharCard
View 20 Replies
View Related
Mar 27, 2008
Hi All,
I have following problem
please help me to resolve it.
There is table for shipping
as follows:
City
Kg
Rate(rs)
Rajkot
1
25.00
Rajkot
2
30.00
Rajkot
3
42.50
Ahemedabad
1
42.50
Ahemedabad
2
55.00
Ahemedabad
3
67.50
Ahemedabad
4
80.00
Goa
1
90.00
Goa
2
105.00
Goa
3
120.00
Gondal
1
25.00
Gondal
2
30.00
Gondal
3
42.50
Morbi
1
25.00
Morbi
2
30.00
Morbi
3
42.50
Gandhinagar
1
42.50
Gandhinagar
2
55.00
Gandhinagar
3
67.50
Gandhinagar
4
80.00
We need to display
the table in html page using any Asp.Net language.
The result table should be:
City(s)
1
2
3
4
Rajkot,Gondal,Morbi
25.00
30.00
42.50
-
Ahemedabad,Gandhinagar
42.50
55.00
67.50
80.00
Goa
90.00
105.00
120.00
-
Baruch
70.25
80.00
92.00
120.50
Description:
- Display all the cities
together for which all the shipping charges for (1-4) kgs are same. The range
of kgs is not fixed.
Reply as soon as possible.
Thanks
View 3 Replies
View Related
May 31, 2007
I have one column in a matrix component and it has about 7 items, but the only the items which have values on the page appear at the top of that page.
This is for a labratory so the columns are the different Patient Types and the rows are the different Test Mnemonics. If one of the Patient Types is not used in any of the tests on that page, it doesnt show up. How to I make sure all Patient Types show up on every page?
Thank you all.
View 2 Replies
View Related
Feb 3, 2008
Hello and thank you taking a moment. I have created a simple login page where the user will pass credentials. I have a sqldatasource that will query a database to see if the user exists. What I would like to do is have the user click a button after entering their credentials. When the button is clicked I would like the SqlDatasource SelectCommand to fire and if there are rows returned then redirect the user to a new page.
I know I can do this with ADO and a datareader with the HasRows property. But what I would eventually like to do is cache the data and then bind the cached data to a control(like a dataview) on the page the user is redirected to. If anyone can tell me how to get the Select command to fire on a button click I would be eternally grateful. Any help will be greatly appreciated.
Thanks,
Jason
View 1 Replies
View Related
May 20, 2008
I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.
However sometimes the filter I have on a column group returns no records and the whole column group disappears.
Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.
In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.
Am I just missing a simple option?
Nathan
View 1 Replies
View Related
Jun 13, 2007
Hello. I was using the new sys.dm_db_index_operational_stats function which is nice for seeing counts of insert/update/delete actions per table index, bla bla bla... Anyways, question, can I do the same thing with Profiler? meaning, can I trace stored procs and sopmehow see the proc exec WITH each table it does actions against? Not talking about filtering on table names in the text, talking I just want to run an application, which uses all stored procs, and see every table used by that execution of the proc, and also the number of rows inserted,updated,deleted.... If so, which Profiler events/columns must I flick on to gather that? Thanks, Bruce
View 4 Replies
View Related
Aug 1, 2006
I have a charting control that is fed from an SqlDataSource. How do I set the SqlDataSource to only return the top N records where N is set by a web form control?The user should have the option to chart the top 3, 5, or 10 items.Is the best approach to switch to an ObjectDataSource?
View 3 Replies
View Related
Oct 10, 2007
TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas?
Thanks Andy,
View 5 Replies
View Related
Nov 5, 2006
I have a SqlDataSource that I need to remove the first 3 rows from before it is bound to a GridView. How would I go about doing this?
(if I could remove them at the db level in the sproc I would, but right now that is not an option - so I need to do it once I've already received the data)
Thanks.
View 9 Replies
View Related
Jan 15, 2008
I current have a SqlDataSource with a querystring that uses the following code:<asp:SqlDataSource ID="SearchQuery" runat="server" ConnectionString="<%$ ConnectionStrings:Connect %>" SelectCommand="SELECT Title, ArticleID, REPLACE(SUBSTRING(Article,0,250), '<br />', ' ')AS Article FROM [Articles] WHERE FREETEXT([Article], @q)"> <SelectParameters> <asp:QueryStringParameter Name="q" QueryStringField="q" DefaultValue="*" Type="String" /> </SelectParameters> </asp:SqlDataSource>The string works fine, however is there a way to show how many results where found? Also is there are no results found, can I have it report that as well? Thanks!
View 5 Replies
View Related
Mar 29, 2007
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
View 1 Replies
View Related
Jan 7, 2014
I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.
View 1 Replies
View Related
Oct 25, 2006
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
View 1 Replies
View Related
Aug 14, 2015
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
View 3 Replies
View Related
Feb 24, 2007
Hi there
I sorry if I have placed this query in the wrong place.
I'm getting to grips with ASP.net 2, slowly but surely!
When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.
Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735075
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
The database is definitly in the folder that the error message relates to.
What I'm finding confusing is that the connection string seems to be finding "aranga"s database.
Is it something daft?
Many thanks.
James
View 1 Replies
View Related
Dec 31, 2007
Hi all,
I have two problems.
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
Thanks,
Mahesh Manthena
View 1 Replies
View Related
Jul 23, 2007
What is the C# code I use to do this?
I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
View 7 Replies
View Related
Mar 8, 2007
i am using visual web developer 2005 with SQL Express 2005 with VB as the code behindi have one database and three tables in itfor manipulating each table i am using separate SqlDataSource() is it sufficient to use one SqlDataSource() for manipulating all the three tables ? i am manipulating all the tables in the same page only please help me
View 1 Replies
View Related
Oct 28, 2014
I have 3 tables...
JobRequirements (A)
JobID int
QualificationTypeID int
EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int
Employee (C)
EmployeeID int
EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
View 5 Replies
View Related
Jun 9, 2008
How would I bring back a column that shows all the days from the begining of the year to the current date. Also make sure this moves onto the next year.
View 9 Replies
View Related