Filtered Data And SQLDatasource
Apr 21, 2008
In asp.net 3.5.....
To get the number of rows returned when the select is executed.......
Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected
Session("MessageText") = e.AffectedRows & " Profile(s)"
End Sub
How do you get the number of rows returned when you apply a filter expression to a selection of rows?
Thanks
Craig
View 3 Replies
ADVERTISEMENT
Jan 23, 2007
Hi
I need to create an sqlDataSource that filters a table based on the users ID – in other words, I have a table with a list of users, and when a logged in user loads the page, the results should only show records with their user name.
How would I do this?
Thanks for any help in advanceRichard
View 3 Replies
View Related
Apr 11, 2007
I got this table named 'Streets':
Street (varchar)
PLZ (varchar)
'A'
'?'
'B'
'1'
SELECT * FROM (
SELECT * FROM (
SELECT Strasse,PLZ FROM Streets
) SUB WHERE IsNumeric(PLZ) = 1
) SUB WHERE PLZ = 1
I always receive this error:
Conversion failed when converting the nvarchar value '?' to data type int.
How must the statement look like, to execute this successfully?
I also tried to use a view which returns only valid streets, also a function, but it seems sqlserver tests the row with the '?' anyways
PLZ HELP!
PS: If you want to try it, create the table because using the following instead the table works fine:
SELECT 'A' as Street ,'?' as PLZ
UNION
SELECT 'B','1'
View 9 Replies
View Related
Oct 10, 2007
I'm using the asp.net login controls and am storing the user data in the sql table that get's automaticaly built by visual studio. I display records based on who is logged in. Currently i use a Session parameter in my datasource for the user name and store the username in a session variable on the page load event, Session["UserName"]= User.Identity.Name.
Is there a way i can use a Profile parameter in my data source and skip setting session variable all together? I tried putting Name and UserName and User.Identity.Name in the Profile property but nothing seems to work. Thanks for any help.
View 6 Replies
View Related
Jan 16, 2006
Hi, I am using SQL Server 2005.
I use the query SELECT * FROM APPOINTMENT WHERE [DATE] = '2006-01-15' and it doesn't work. However, this query SELECT * FROM APPOINTMENT WHERE [DATE] = '2006-01-15 19:55:44.000' is working.
Is there a way for me to select data field by not having to specifying the time coz it is annoying.
Thanks for any reply.
View 3 Replies
View Related
Apr 15, 2015
From distribution db, which table(s) store info about filtered data?
View 0 Replies
View Related
Feb 1, 2012
I need to include pre k kids in the is script for one school and exclude them for the others. I have them all excluded with the last statement in the where clause. How would I go about accomplishing this.
Code:
SELECT DISTINCT
C.Student_ID,
isnull(left(dbo.capfirst(rtrim([first_name])),12),' ') as First_Name,
isnull(left(dbo.capfirst(rtrim([last_name])),17),' ') as Last_Name,
LEFT(Middle_Name, 1) as Middle_Initial,
RTRIM(CONVERT(CHAR,Birth_Date,101)) AS DOB,
[Code] .....
View 2 Replies
View Related
Jul 23, 2005
I am having some difficulty in constructing outer joins. I havesimplified what I need to do and have included sample SQL statements:create table tab_a (id int, descr varchar(10), qty int)insert into tab_a values (1, 'item one', 10)insert into tab_a values (2, 'item two', 20)insert into tab_a values (3, 'item three', 30)insert into tab_a values (4, 'item four', 40)create table tab_b (id2 int, descr2 varchar(10), qty2 int)insert into tab_b values (1, 'item one', 10)insert into tab_b values (2, 'item two', 20)insert into tab_b values (3, 'item three', 30)insert into tab_b values (4, 'item four', 40)Here is the statement that I have:SELECT tab_a.id,tab_a.descr,tab_a.qty,tab_b.id2,tab_b.descr2,tab_b.qty2FROM tab_a LEFT OUTER JOIN tab_bON (tab_a.id = tab_b.id2 )WHERE tab_a.qty <= 30 ANDtab_b.qty2 > 20What I am trying to do is left outer join between tab_a and tab_b afterthey have been filtered based on the qty column. (for tab_a: qty <=30; and for tab_b: qty > 20).How would I go about that? I would like to do this efficiently sincethe two tables have about a million records and several other columnseach.
View 5 Replies
View Related
Jul 20, 2005
Hi all,I’m a bit new to this so hope this is not too obvious!I am running a query like so (simplified form)Select Cus_no, Cust_Name, course_idWhere Cust_id = 2From course tableHowever not all the Cust_Id’s have been entered therefore so using a twofilters out the information I might need.Is there a way I can get around this?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
May 15, 2013
I have a table in SQL 2012 where we add records on daily bases. A col is for user name.
I want to select records with each user and send the same to that user with db mail.
I have worked with VB but I am totally new in this field.
View 12 Replies
View Related
Aug 7, 2014
I am trying to multiply 2 columns and filtered by a 3rd.
View 3 Replies
View Related
Oct 19, 2007
Hello Everyone,
I'm just trying something that I haven't tried before, and I'm wondering if anyone has done it before. I'm building a report with three charts. This chart is going to go inside of Microsoft CRM. One of the charts has to use CRM filtered views, so that a manager can see his whole team's numbers, but a team member will only see his numbers. I can't do this any other way because of the complexity of the query, which has 3 select statements joined together as tables. It would be too complex to try to use a parameter throughout all of those tables, and I'm not sure how I would set up a parameter to show all of the data if the user were the manager, and only the single user's data if it were someone else.
The other two charts use filtered views as well, but they have similar joins, and I had to hard code a column with meaningless data into each SELECT table so that I could use it to join, as the tables had no other similarities.
The problem is that when you upload a report into CRM, if it uses filtered views, you don't have to go into Report Manager and change the data source from a shared to a custom data source. But on reports where I don't use the filtered views, I find that they always break when upload them, because they use the shared data source. This is the case even when I have created the report without the shared data source. So I usually have to go in and change the data source from shared to custom every time I upload a report without filtered views.
Because of the situation I described, this report uses both filtered views and non-filtered views (for the hard-coded columns). When I upload it into CRM, it won't work either way, with a shared or a custom data source.
Has anyone had this problem before?
View 1 Replies
View Related
Aug 14, 2014
What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?
There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately
View 8 Replies
View Related
Aug 5, 2015
I have a query that I'm filtering using Customer ID, CustomerID = '12345', even though I need the query to filter that data, I don't need to see that column in my results. I tried removing it from my Select Distinct group but I'm guessing it needs to be there or the filter won't work(like I said, very green). Is there something that I can add to hide this column?
SELECT DISTINCT
RG.ResNumber,
ResWithSupp.SupplierID,
ResWithSupp.ServiceType,
RG.CustomerID,
[code]......
View 4 Replies
View Related
Feb 16, 2008
I am relatively new to SSRS and having some problems showing the right results from my subreport. I am using a cube for my datasets. I have created a report parameters where I can filter out year and months to see the sales in certain month. I also want to add a calculation to display the sum for the choosen period.
f.exs. if I choose january 2004 my result look like this:
Key Product Amount
41999 prod. x 5,000
42999 prod.y 2,000
Totals: [results from subreport showing total of 7,000]
What I would like to do is to add a total that calculates the total according to the filter. I tried to add a subreport beneath, but I don´t know how to link the filtered condition to the subreport, so it displayes always the total amount for all years from the cube.
Hope there is some neat way to do this
View 2 Replies
View Related
Apr 4, 2007
I the following report with one group:
Month Number of Sales Total Sales
+ Jan 2007 100 $1,000.00
When you dril down on MonthYear you get the detail data:
Month Number of Sales Total Sales
- Jan 2007 10 $610.00
1 $10.00
1 $20.00
1 $30.00
1 $40.00
1 $50.00
1 $60.00
1 $70.00
1 $80.00
1 $100.00
1 $150.00
My question is. I added a filter to the detail data to give the Bottom % =75 of sales. So My detail data only displays the following rows:
Month Number of Sales Total Sales
- Jan 2007 10 $610.00
1 $10.00
1 $20.00
1 $30.00
1 $40.00
1 $50.00
My problem is the group still displays the total of my dataset (as seen above), but I want it to display the total of the detail data group, like below:
Month Number of Sales Total Sales
- Jan 2007 5 $150.00
1 $10.00
1 $20.00
1 $30.00
1 $40.00
1 $50.00
If I change the fields in the group to look at the detail data ,for instance =count(Fields!NumberofSales.Value,"Details_Group") I get an scope error.
How can I display the totals of the detail data in the parent group after I added a filter to the detail data?
View 6 Replies
View Related
May 14, 2008
I am trying to show aggregate information in a grouping and report footer. The details section has a filter applied successfully. For example, if there are three records and one should be filtered out, then only two display. However, the count function returns 3 instead of the desired 2. I have tried to set the scope parameter to body, the table name and every group name on the report. Either this has no effect or returns an error message stating that the appropriate scope isn't applied.
Does anyone know how to perform aggregate functions and exclude the filtered rows?
Thanks,
Dennis
View 3 Replies
View Related
May 16, 2008
Hi
I've been surprised with upleasant issue in RS Matrix group.
I've tried to filter Matrix group but failed in using "OR" predicat in Filter expression:
ScreenShot
"AND" is used by default despite of header "And/Or" which is disabled!
How can I use "OR" ?
Andriy Zhornyk
View 1 Replies
View Related
Sep 6, 2006
I am using SQL2005.There si a field called"EXPDATE". I need a query that shows the table info, if the date that is exist on "EXPDATE" is greater than today. In summary How to write a code that if EXPDATE> "today (I do not know what to put instead of today)" then show the contents of date
View 2 Replies
View Related
Apr 4, 2007
I've got a listbox that displays a list of employee's names. The employee number is the value stored in the listbox. I then have a vaccinations gridview that displays all the vaccinations received by the selected employee in the listbox. For some reason, when I click edit and modify a record and then click update it doesn't want to actually update the record. It just appears to do a postback and redisplay the record without any changes. My sqldatasource control is configured as follows: SelectCommand="SELECT * FROM [tblVaccinations] WHERE ([EmpNum] = @EmpNum)" @empnum = mylistbox.selectedvalue the update command is as follows: UpdateCommand="UPDATE [tblVaccinations] SET [EmpNum] = @EmpNum, [VacType] = @VacType, [VacIssueDate] = @VacIssueDate, [VacExpDate] = @VacExpDate, [VacInstitution] = @VacInstitution WHERE [VaccinationNum] = @VaccinationNum"
View 13 Replies
View Related
Jun 18, 2015
I would like to get the size of the tables in my database BUT as follows:
SIZE OF (SELECT * FROM myTable WHERE Section = 1)
...what is the fastest way to do it?
View 2 Replies
View Related
May 2, 2009
I believe query optimizer can leverage the predicate used to define the filtered index. But I came to a situation that's not true:
USE tempdb;
GO
IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees;
GO
CREATE TABLE Employees
[Code] ....
If we take a look at the query plan. we'll find an unnecessary Nested Loop and Key Lookup operator there with the IS NULL predicate.
Ironically, If I change the IS NULL to IS NOT NULL in both the index and Query, everything goes fine.
View 9 Replies
View Related
Dec 14, 2006
I am trying to report against a cube and I want the user to be able to choose the date for the report.
Now the cube dimension has been built against a calendar which contains values for the next 30 years(!) so when I create a parameterized report the list of dates is ridiculously huge.
I want to filter the dates out and show only the dates for which there is data. How do I filter out the list of dates for a parameter?
View 3 Replies
View Related
May 4, 2008
I am trying to build a report that allows a user to enter partial text that filters the values in a drop down
For examle;
Lets say I am selecting a value from a drop down for States and I want to type in FL to filter the collection down so that only Florida appears in the select list.
I know the above example wont work since I cannot actaully enter text into a drop down, however
I have also tried to seperate this functionality by creating one report parameter for entering the search text and another parameter that will display a filtered data set using the text entered in the first parameter.
Is this something that reporting services can handle ? If it is please provide a brief example.
Thanks
View 6 Replies
View Related
Dec 11, 2006
I have a page containing a FormView, which gets its data from a SqlDataSource control and displays details of a job. Two of the fields are location and job title. I want to re-use this data to create a dynamic page title. I know I can do this by setting the Page.Title to what I want to, but do I have access to the data outside of the FormView to which the data source is bound? If so how? Or will I have to perform an additional SELECT statement to get this data again?
View 2 Replies
View Related
Feb 14, 2006
I am used to using asp.net 1.1
and I want to do the equivilent of the following with an SqlDataSource
da.fill(dst1)
lbl.text=dst1.tablename(0).fieldname
and what this does is fills the dataset with the information from the select command in the dataadapter and then gets the value for the first row of the fieldname
It doesnt seem like this should be that big of a deal, but it has become very frustrating trying to find the answer, does anyone know how to do this? Please post some demo code if possible
View 2 Replies
View Related
Mar 9, 2006
Hi Everybody,I have an ordinary SqlDataSource on my page <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:P4SConnection %>" ProviderName="<%$ ConnectionStrings:P4SConnection.ProviderName %>" SelectCommand="SELECT CO_Id,CT_Name,CO_Username,CO_Password,CO_Company,CO_Email,CT_Id,CT_Position,CT_Admin, CO_Session,CO_LastLogin FROM company_contact LEFT JOIN company ON company_contact.CT_Company = company.CO_Id WHERE (CT_Username= & LCase(txtName.Text) & "> </asp:SqlDataSource>How do i use any of these values?I know I can display the values in a GridView or something but I dont want to display them.I originaly was using ODBC and just used the followingDim Row As DataRow = thisTable.Rows(0) Dim lP_ID As String = Row(0).ToString() Dim TheContact As String = Row(1).ToString() Dim theName As String = Row(2).ToString() Dim thePassword As String = Row(3).ToString() Dim TheCompany As String = Row(4).ToString() Dim TheEmail As String = Row(5).ToString() Dim lP_ContactID As String = Row(6).ToString() Dim ThePosition As String = Row(7).ToString() Dim bAdmin As Boolean = Row(8).ToString() Dim sP_Session As String = Row(9).ToString() Dim sP_Last As String = Row(10).ToString()Then I could use the values wherever I wanted.How do I do this with a SqlDataSource?Thanks,G
View 4 Replies
View Related
Feb 11, 2008
Hello,
I have a table that I want to cache. So, if this is my query "SELECT * FROM myTable" that I create the cache from, how can I filter the data?
I really need to cache the whole table, since there are a myriad of different statements being executed against the table, so just caching a specific query won't do.
I've found the best approach to make a question is writing it in code, so how can I do the following:
SELECT ColNames FROM MyTableWhichShouldNowBeCached WHERE whereColumns=someParams.
ColNames and someParams make up for a variation of about to 120 different queries. I use SQL 2005 express edition with advanced services.
Cheers!
/Eskil
View 2 Replies
View Related
Nov 26, 2015
Is there any windows command or any tool which would tell me what all 3rd party s/w's or Filtered drivers on my server (like antivirus, monitoring tools like Diskeeper or any I/O throttling s/w etc..)
View 3 Replies
View Related
May 2, 2007
Good morning,
I'm in the process of creating a report to show employees and managers holiday and absence information. Is there a way of filtering the results of the report based on who is running the report, so that employees could only see their own information and managers could only see theirs and their subordinates information?
What I was hoping to do was create a lookup table which cross-references Windows logins with employee numbers and then use this information to pass a parameter to the SQL query, but I don't know how to retrieve the login from the machine being used to view the report.
I've heard about row level security and it seems ideal in theory but I fear the implimentation of row level security would be far beyond my meagre knowledge.
Any constructive suggestions welcomed.
Thanks,
Paul
View 3 Replies
View Related
Apr 21, 2006
hi all,
Im running a snapshot replication on table.
first, i gonna copy the entire table with a regional filter other than date
second i'm going to replicate a days worth of data by using both the regional filter and date filter (getdate) to capture a days wordth of data.
the publication are working fine.
my question is how am i going to accumulated the result of
the filtered snaphot replication in a single table in my db.
many thanks,
joey
View 1 Replies
View Related
Nov 6, 2006
Hi
I'm working with merge replication between Sql Server 2005 and Sql Server 2005 Mobile. I'm using dynamic filtering by function HOST_NAME (... where Table.FilteredColumn = HOST_NAME())
When I'm trying to chagne values from filtered column on the client side (Table.FileteredColumn) I get error message that the column is read-only.
How can I change data in filtered column? Is this possible?
View 3 Replies
View Related
Jul 18, 2006
Okay, I used the SQLDataSource control to get my data from the database
table. What or how do I retrieve individual data from the
sqldatasource? I want to do some string comparison and manipulation
before I display it to the browser. How can this be accomplish?
Help is appreciated.
View 21 Replies
View Related
