Need Gridview To Display Different Columns Based On New SELECT Statement
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great.
My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around.
So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting.
I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions?
Thanks,
Brad
View Complete Forum Thread with Replies
Related Forum Messages:
GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following): SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated! Thanks, Randy
View Replies !
How To Do Update Of Select Columns Based On...
the following criteria. i have the selection all done but am trying to figure out how to do the following: if column4 < 0 then add column4 to column3, move 0 to column4; if column3 < 0 then add column3 to column2, move 0 to column3; if column2 < 0 then add column2 to column1, move 0 to column2; add column3 to column4; move column2 to column3; move column1 to column2; if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1; these are all numeric data types.
View Replies !
SELECT Statement To Display Combined Fields
Dear All I am trying to populate an OledbDatareader for binding to a ASP datagrid. For this I use select statement to display combined fields in a datagrid cell. Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on. But the problem is if any of the three field is null the combined field 'Address' returns as Null. Please help me to overcome this problem. Regards kalanad ( beginner)
View Replies !
Select Statement Based On A Date
i am not getting a result back when i run the query below. select * from users where DateCreated = '2004-02-19' so i went into the table and looked at the record. for DateCreated field i have both date and time. ex: 2004-02-19 08:40:00 how can i select this record with out using the time in the select statement. what i want to see is how many users signed up for a day. any ideas?
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
Help With Totalling Columns In SQL Select Statement
Maybe its a friday, but I can't figure out how to total the returned results so I just get a sum of the 'Items' and 'Total'. I thought it was Compute but that did not work! I'm pulling data from 2 tables, Order and Order_Item and what Im trying to achieve is to total up how many items were sold and the total. Each one of the rows below are for each order. I must be doing something wrong, can anyone help? SELECTsum(Order_Item.Quantity) as Items, sum(Order_Item.Price*Order_Item.Quantity)-[Order].Discount as Total FROM[Order] JOINOrder_Item ON Order_Item.[OrderID] = [Order].[ID] GROUP BY[Order].[ID], [Order].OrderRef, [Order].CustomerID, [Order].ReceivedDate, [Order].Postage, [Order].Discount ORDER BY [Order].ReceivedDate DESC Items Total ==== ===== 114.9900 113.5920 333.6750 227.1840 567.9600
View Replies !
Is There A Way To Create Columns From A Row Value In A Select Statement?
Hi! I have a select question that look like this: DECLARE @ID uniqueidentifier; SET @ID = '40bd3052-60f4-414a-99df-ca882c128493'; SELECT rp.ID AS ReportPackId ,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier ,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier FROM ReportPack rp INNER JOIN ReportPackAdditionalParty rpap ON rpap.fk_ReportPack = rp.ID WHERE rp.ID = @ID The result a get when running the select question is: ReportPackId SupplierPartyIdentifier AdditionalPartyIdentifier 40BD3052-60F4-414A-99DF-CA882C128493 String addPartyId1 40BD3052-60F4-414A-99DF-CA882C128493 String addPartyId2 My problem is that the result I want is the following: ReportPackId SupplierPartyIdentifier AdditionalPartyIdentifier AdditionalPartyIdentifier 40BD3052-60F4-414A-99DF-CA882C128493 String addPartyId1 addPartyId2 I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?
View Replies !
Select Statement To Pull Columns From Same Table For Different Ids
I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query. The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005. Thanks
View Replies !
Using One Alias For Mutiple Columns In A SELECT Statement
Hi all,Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?SELECT RTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.], LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building], LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street], LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District], LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town], LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County], RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]ThanksTryst
View Replies !
SELECT Statement With 2 Columns Originating From The Same Fields
Hello, I've been busy all night searching and reading trying to figure out how I can do the following. I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far... SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ??? How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB? Your help is greatly appreciated. Thanks, -Mike Ontario, Canada
View Replies !
Using One Alias For Mutiple Columns In A SELECT Statement
Hi all, Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done? Code: SELECTRTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.], LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building], LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street], LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District], LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town], LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County], RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode] Thanks Tryst
View Replies !
Display Data In A Gridview In A Little Diiferent Way
Good Day, Please help me, I have a database that looks like this, Building No. Floor No. Room No. Customer Name Appliances Appliances Codea 1 01 John TV 12a 1 02 //up tob 20 10 james Ref. 22b 20 10 joe Microwave 15 //and soon so if you would notice some rooms are vacant, as it usually happens, the buildings, floors and rooms are fixed except for the customers and the appliances. so at first i made this gridview and display this data in this manner:Customer Location (Building + floor) total rooms Used Rooms Available rooms A1 2 1 1 B2010 2 2 0 so if you would look at the table above, it just SUM up all tenants in a specific Location then Group it by Customer location,but i need to add the appliances in a different format like this: Customer Location (Building + floor) total rooms Used Rooms Available rooms TV Ref Microwave A1 2 1 1 tick B2010 2 2 0 tick tick so this is what i need to show, i already have done the first table (w'o appliances) but how can i make this second table?? please help me, SALAMAT PO.,Thanks
View Replies !
Display Data From A Database Without Using Gridview Etc.
I have a webform that I am wanting to display data from a database. Right now I am using the gridview but for future purposes when it comes to maintenance what would be the easiest way to do updates? For example, if I add a new record into the database I would like the webform to automatically update itself to show all the records including the new record I added. Do I need to use a "table" and somehow connect it to a database? Do any stored procedures need to be created? Suggestions/ideas/codes help help help would be verrrry much appreciated!!! Also I am using MS Server 2003 and C# as the programming language. Thank you!!!!
View Replies !
Search Database And Display In Gridview
I'm trying to do a search but it seems like it couldnt get the text in my text book and run the command. Can anyone help me here?Function searchBook(ByVal booking_id As Integer) As System.Data.DataSet Dim search As Integer = txtSearch.Text Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Speedo'"Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [booking].* FROM [booking] WHERE [booking].[booking_id] LIKE '%1%'"Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnectionDim dbParam_booking_id As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_booking_id.ParameterName = "@booking_id" dbParam_booking_id.Value = booking_id dbParam_booking_id.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_booking_id)Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommandDim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)Return dataSet End Function Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click ' If Not Page.IsPostBack Then 'Dim search As String = txtSearch.Text 'MsgBox(search) If ddSearchCat.SelectedValue = "booking" ThenMsgBox("you have selected booking") GVSearch.DataSource = searchBook(txtSearch.Text) GVSearch.DataBind() ElseIf ddSearchCat.SelectedValue = "Log" ThenMsgBox("you have selected Log") GVSearch.DataSource = searchLog(txtSearch.Text) GVSearch.DataBind() ElseIf ddSearchCat.SelectedValue = "UserName" ThenMsgBox("you have selected UserName") GVSearch.DataSource = searchUserName(txtSearch.Text)MsgBox("HELLO IN GRID") GVSearch.DataBind() GVSearch.Visible = TrueMsgBox("out of grid") End If 'End If End Sub
View Replies !
SELECT Statement With Two Retrieved Columns Originating From The Same Dbase Fields
Hello, I've been busy all night searching and reading trying to figure out how I can do the following. I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far... SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ??? How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB? Your help is greatly appreciated. Thanks, -Mike
View Replies !
Select Query Results In Multiple Columns Based On &&"type&&" From Another Table
Using SQL Server 2005 Express: I'd like to know how to do a SELECT Query using the following tables: Miles Table: Date/Car/Miles/MilesTypeID =============== (some date)/Ford/20/1 (some date)Ford/20/2 (some date)Chevy/30/1 (some date)Toyota/50/3 (some date)Ford/30/3 Miles Type Table MilesTypeID/MilesType ================= 1/City 2/Highway 3/Off-Road I'd like the results to be like this: Date/Car/City Miles/Highway Miles/Off-Road Miles ===================================== (date)-Ford-20-0-0 (date)-Chevy-0-20-0 (date)-Ford-20-0-0 (date)-Toyota-0-0-50 (date)-Ford-0-0-30 Anyone? Thanks in advance!
View Replies !
Stored Procedure To Display The Relevant Data Of The IDs In The Database To A Gridview
Here is the Stored procedure ALTER procedure [dbo].[ActAuditInfo](@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) output )asbegindeclare @AuidtID as varchar(30)Select @IndustryName=Industry_Name from Industry whereIndustry.Ind_Id_PK =(Select Audit_Industry from Audits whereAd_ID_PK=@AuidtID)Select @CompanyName=Company_Name from Company whereCompany.Cmp_ID_PK =(Select Audit_Company from Audits whereAd_ID_PK=@AuidtID)Select @PlantName=Plant_Name from Plant where Plant.Pl_ID_PK=(Select Audit_Plant from Audits where Ad_ID_PK=@AuidtID)Select @GroupName=Groups_Name from Groups whereGroups.G_ID_PK =(Select Audit_Group from Audits whereAd_ID_PK=@AuidtID)Select @UserName=Login_Uname from RegistrationDetails whereRegistrationDetails.UID_PK =(Select Audit_Created_By fromAudits where Ad_ID_PK=@AuidtID)SELECT Ad_ID_PK, Audit_Name, @IndustryName, @CompanyName, @PlantName,@GroupName, Audit_Started_On, Audit_Scheduledto, @UserName FROMAudits where Audit_Status='Active'end U can see here different parameters,my requirement is that iam havingID's of Industry,company,plant,group,username stored in a table calledPcra_Audits and i must display their related names in the front end.so this is the query iam using for that. Data in the database:Commercial83312 2 2 2 1 1 InactiveHere u can see 2,2,2,1,1 these are the IDs ofindustry,company,plant,group and username and Commercial83312 is tehaudit ID.now i want to display this data in teh front end as i cannot displaythe IDs i am retrieving the names of the particular IDs from therelated tables.Like iam getting name of the IndustryID from Industry Table,in thesame way others too.when iam running this procedure iam getting the gridview blank.iam passing the output parameters:@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) outputinto the function in the frontend and iam calling that into the pageload method.please help me with this.
View Replies !
Hiding/Showing Columns Based On The Columns Present In The Dataset
I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report. Can I do that?? Any reply showing me the right way is appricited. -Thanks, Digs
View Replies !
How Do I Populate A Formview From Multiple Columns In A Gridview
Hi, I have a master/detail scenario whereby to populate a formview, the user selects a row in the gridview. The SqlDataSource used to populate the formview needs to check 2 columns from the gridview, ZRef (which is the SelectedValue of the Gridview) and ZName (a string). From searching the forums it looks like I have to programmatically assign the SelectParameters in order to get ZName from the gridview. Seems fair enough, so I set the parameters in the 'Selecting' event of the SqlDataSource. (Code shown below). However the formview never shows. In debug I can see that the values I'm setting in the SelectParameters.Add are correct; and if I set the default values for those parameters in the SqlDataSource itself, everything works fine. Can anyone point me to some sample VB code that does this - I'm sure this must be a common situation. Thanks. Protected Sub sdsOff_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Dim tmpSDS As SqlDataSource Dim tmpLBL As Label tmpSDS = CType(FormView1.FindControl("sdsOff"), SqlDataSource) If tmpSDS Is Nothing Then Server.Transfer("ErrorOnPage.htm") End If tmpLBL = Me.GridView1.Rows(GridView1.SelectedIndex).FindControl("lblName") If tmpLBL Is Nothing Then Server.Transfer("ErrorOnPage.htm") End If tmpSDS.SelectParameters.Clear() tmpSDS.SelectParameters.Add("ZRef", GridView1.SelectedValue) 'In debug, this shows 7 - which is correct tmpSDS.SelectParameters.Add("ZName", tmpLBL.Text) 'In debug, this shows 'Fred Bloggs' - which is correct End Sub
View Replies !
How Do You Place A Variable In Your Gridview Sql Statement?
I have the below select command in my gridview that uses a library name.table approach in the sql. I created a label on the page and want to pass the label in where i have the libarary1 text in the select statement. This will allow me to swap out the production library with my development library when im working on the site (thru the use of a site variable) <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:as400con %>"ProviderName="<%$ ConnectionStrings:as400con.ProviderName %>" SelectCommand="SELECT * from library1.table WHERE COMPANY = ? and ack = ? and HORZN_DATE <= ?"> <selectparameters><asp:controlparameter name="company" controlid="accts" propertyname="SelectedValue"/><asp:controlparameter name="ack" controlid="podropdown" propertyname="SelectedValue"/><asp:controlparameter name="dt" controlid="htoday" propertyname="Text"/></selectparameters> How would I insert a label.text value into the select statement replacing the library1 ? I tried to use the asp:controlparameter adding the text and placing a ? mark in place of the library1 but it failed. Thanks for any help you can lend on this, Todd
View Replies !
Different Display Based On Text
I have an HTMP form and for some reason, when I use a regular TextBox to display a field, Via an ASP conneciton to the database, it cuts it off when it gets to a " (A quote) Example: Field: Title Data: Where do "rabbits" come from? If I try to pull that field into a regular html textbox it dispays, Where do Then if I change the textbox to a textarea it displays the entire field Where do "rabbits" come from? Is this a setting that causes this in SQL Server on the table. I am new to SQL Server and am trying to learn these little differences. Any help would be greatly appreciated. Thanks,
View Replies !
Gridview Select
I am using a GridView which is select enabled. I have another sqldatasource control on my page which will run a SQL query but needs a parameter. This parameter is from a column (EmployeeID) of the selected row in the gridview. In the Sqldatasource control I have specified the parameter source as a control which is Gridview .But I dont know how to point to the EmployeeID column of the selected row.Please help.
View Replies !
Display A Table From A Gridview But Every Column Has Its Own &"where&" Arguments??
Hi,please help me, i have a data driven site,i need to display a table who look like this Name Total Used RemainingA 10 5 5B 20 13 7C 12 12 if you would look on the field of total, used and remaining, they look like an excel formula which is total - used = remaining or vise versa.but the problem in this table is the database, it look like thisname On/OffA 1A 1A 0and soon....... B 1B 0B 1and soon.... until C so how do i count the total, is the number of A's or B's or C's in the whole database then Group By the 'name' fieldthen how do i count the used is the number of ON in the On/Off field while remaining is counting the Off,so my question is i have RUN this using 3 different gridview each has its own datasource, that look like this for the used select name, count(on/off) from myTable where on/off = 1 group by name for the remaining select name, count(on/off) from myTable where on/off = 0 group by name and for the total, just used the same Select but without the where clause,i get this problem when the field "used" (please refer from the 1st table) it has a blank value, which is because my data base show that in deed name "C" has not been used, but eve though it should show '0' instead of a blank, so how can i do that???please help me, thanks
View Replies !
Display Results Based On Query
Hi, I don't know if anyone has encountered this before but here goes: I've a select clause below: result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'" queryLocalName is an input field that allows the user to search for non-English characters in the database. What I'm wondering is what kind of effect is the N in the where clause is having? I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.
View Replies !
Select Parameters With Gridview
I have a form that has 4 fields to fill in. I have a button that can add these fields to a sql database. I also want to put a button next to the "add" button so you can fill out the same fields and search for those values. Here's what i have so far. I want to select based on the fields, but i'm having trouble with the syntax of the parameters. I also want to add something, so if nothing is filled out in one of those boxes, it retuns back all records for the default value. string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnection); String selectCmd = "SELECT * FROM users WHERE firstname = @firstame or lastname = @lastname or office = @office or team = @team"; SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, myConnection); myCommand.SelectCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50)); myCommand.SelectCommand.Parameters.Add("@firstname", txtFirstName.Text); myCommand.Parameters.AddWithValue("@lastname", txtLastName.Text); myCommand.Parameters.AddWithValue("@team", dwnTeam.Text); myCommand.Parameters.AddWithValue("@office", dwnOffice.Text); DataSet ds = new DataSet(); myCommand.Fill(ds, "users"); MyDataGrid.DataSource = ds.Tables["users"].DefaultView; MyDataGrid.DataBind();
View Replies !
[:#] Problems Viewing The Custom Sql Statement In The Gridview Control
Hi, I really need some help trying to figure out why my gridview is not working when I create a custom sql statement. It "executes" the query, but gives me an error message when I "test the query". Here is the error message: "There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct. Syntax error: Expecting '.', identifier or quoted identifier." Here is my sql statement: SELECT TBLPROJECTS.NAME, TBLPROJECTTYPES.NAME AS PROJECTTYPE, TBLPROJECTS.DESCRIPTION, TBLUSERS_1.LOGIN AS OWNERNAME, TBLUSERS.LOGIN AS MANAGERNAME, TBLPROJECTS.START_DATE, TBLPROJECTS.END_DATE, TBLAOI.NAME AS AREAOFINTEREST, TBLPROJECTS.MANPOWER, TBLUNITS.NAME AS MANPOWERUNITFROM TBLPROJECTS INNER JOIN TBLAOI ON TBLPROJECTS.AOI_ID = TBLAOI.ID INNER JOIN TBLPROJECTTYPES ON TBLPROJECTS.PROJECTTYPE_ID = TBLPROJECTTYPES.ID INNER JOIN TBLUNITS ON TBLPROJECTS.MANPOWERUNITS_ID = TBLUNITS.ID INNER JOIN TBLUSERS ON TBLPROJECTS.MANAGER_ID = TBLUSERS.ID INNER JOIN TBLUSERS TBLUSERS_1 ON TBLPROJECTS.OWNER_ID = TBLUSERS_1.ID I have tested it on a new project and still it does not work, I cannot find any problem, please help!!!!!!!!!!!!!!!!!!!!!!!!
View Replies !
Display Results Based On Percentage Match
Dear Pals, I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match. I mean the result set should be displayed based on percentage match. For example i have the below table. create table test ( id int identity(1,1) primary key, ename varchar(10) ) insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg' I need the output something similar as below REG Reg Regsxysn psReg I have tried out with full text indexing but i could'nt get the required output. Any suggestions would be appreciated. Thanks in Advance.
View Replies !
Display Report Based On User Role
I have created reports using SSRS 2005 and deployed in ReportServer. I calling these reports form my web application.Users have different roles based on their login into web application.I need check these user roles and display the reports based on their roles. There could be 10 reports in total, but for this user i should display only 4 out of them. Its pretty urgent. Can somebhelp on this?
View Replies !
Based On Conditions Display Only One Particular Value In COLB Against ColA
Hi all sql experts, I have a table that has columns 1)ProductName and 2)qualification. The reporting scenario is i have to display the highest qualification among all qualifications for the particular product name: Suppose 3 qualifications(B,P,D) D__lowest B__Medium P__highest So i need P always if P is there among the group. The different conditions would be 1)If the ProductName has only only type of qualification then display that. 2)If both B and D, then display only B. 3)If both P and D, then display only p. 4) If p,B and D then display only P. The current table would be like: ProductName Qualification Col3 Col4 P1 B P1 P P1 D P2 B P2 D P3 P P3 D P4 D P5 B P5 B P5 B The expected Result: ProductName Qualification Col3 Col4 P1 P P2 B P3 P P4 D P5 B Any sort of help would be highly appreciated
View Replies !
Display Results Based On Percentage Match
Dear Pals, I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match. I mean the result set should be displayed based on percentage match. For example i have the below table. create table test ( id int identity(1,1) primary key, ename varchar(10) ) insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg' I need the output something similar as below REG Reg Regsxysn psReg I have tried out with full text indexing but i could'nt get the required output. Any suggestions would be appreciated. Thanks in Advance.
View Replies !
Changing The SQLDatasource SELECT For A Gridview
I cant seen to change the Select command for a SQL Datasourcetry #1 SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters("ProfileID").DefaultValue = pProfileID SqlDataSourceProfilesThatMatch.SelectParameters("LoggedInUsersZipcode").DefaultValue = pUsersZipCode SqlDataSourceProfilesThatMatch.SelectParameters("ZipDistance").DefaultValue = pDistance NewProfilesThatMatchGridView.DataBind() try #2 SqlDataSourceProfilesToBeMatched.SelectParameters.Clear() SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters.Add("ProfileID", pProfileID) SqlDataSourceProfilesThatMatch.SelectParameters.Add("LoggedInUsersZipcode", pUsersZipCode) SqlDataSourceProfilesThatMatch.SelectParameters.Add("ZipDistance", pDistance) NewProfilesThatMatchGridView.DataBind() No errors but no rows show in the gridview. If I debug and get the value strSQLForSearch and paste it into a new SQL query window I get results. Any ideas???? Thanks
View Replies !
Select Just A Couple Of Rows Into Gridview
I am using a sqlDataSource to read my query and to put the rows into a gridview. Now I want to read just a couple of rows from my query based on a maximum number, also defined in the table. I have absolutely no idea how to do that..for example: I want to view only the 10 most recent subsciptions into my gridview. The subscriptions are stored into tabel Subscriptions which has values id, userid,date. Table Event has a parameter "maximum subscriptions" (which is 10 in my example) I hope it is clear enough?Thank you in advance!BlueiVeinz
View Replies !
Data Display In Reports Based On Windows Authentication ID
Hi Experts, I have a reporting scenario, where the reports are fetched from Analysis Services. The reports should display data only spcecific to that user. All users except those in admin roles should be validated using the Windows Authentication ID and data specific to them has to be displayed. Any pointers/suggestions on how to implement this in Reporting services/ Analysis Services 2005 would be highly appreciated Thanks,
View Replies !
How Can Display Items In The Report Based On This Dataset. (urgent)
Hi, I have a web form which has 5 check boxes and i storing the values 1 - 5 for each check box the user clicks . I want to design part of a report in this fashion, if the user clicks on the first checkBox i want A to appear in the report, and if the user clicks on the second i want B and so forth. If the user clicks on A& B i want the data to be displayed as A,B. This is my sproc i am using. Select laa.PlanId, laa.LoanId, ( 1-5 values are stored) los.Name From LoansAttriApplied laa Inner Join LoanOptions los on laa.LoanId = los.LoanId Where PlanId = @PlanId So based on this Query if the user select 4 check boxes for plan No, 104 , I will get 4 rows. So based on the dataset i get can I display the data in A,B or 1,2,3 instead of 1 2 3 Can someone please give me some insight into this. Regards Karen
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
Select Command Lost Thus Gridview Shows Nothing
Hi there,I am not sure how many of you have experienced this problem before but here is how to replicate it: Add a GridView, SqlDataSource and Button to a new projectSet the DataSourceID to the SqlDataSource objectSet AllowPaging to True on the GridViewWithin the Button1_Click sub, change the SqlDataSource.SelectCommand to a new Query e.g SELECT * FROM table WHERE ID > 1Run the project Click on another page, i.e. "3" on the GridView SqlDataSource.SelectCommand is now blank(!), thus Gridview displays nothing. Has anyone else found a solution to this annoying issue? Any help is appreciated.Regards
View Replies !
Change/Add Report Fields To Display Based On The Parameter Selected
I have a report parameter named "Schools" which display a list of schools. For example, Alo elementary school, Balo middle school and Calo high school. When "Alo elementary" is selected the report only display students from that school along with other assessments data fields. same goes for other schools too. But I want to display different data fields for "Calo high school" when it is selected. It is not currently possilbe becasue I am using the same template for all types of schools. There some fields only should be displayed for "Calo high school" but not for any other type of schools. I can accomplish this by creating two separate report, one for "Calo high school" and the other for other schools. But I want to accomplish this just by creating one report. So when "Alo elementary school" is selected it displays report with certain fields and when "Calo high school" is selected then it displays same fields as "Alo elementary school" but as well as some other fields too in the report. Is this possible? Need help.
View Replies !
Sqldatasource, Does It Select If Not Bound To A Gridview, Formview On The Page?
Using 3.5 If I have a sqldatasource on the page, is it run if it is not bound to a data object like a gridview? Seems like if i want to access the data (like set a label text) from the sqldatasource I have to use code to first create a dataview then pick throught it. This seems like I'm running it twice. I'm new at .net so I dont know how to tell. I don't want to write data select code programatically when I can just through an SDS on the page, but wondered it it ran just because it's on the page.
View Replies !
Display Analysis Services Server-Based Formatting In RS 2005 Report
I'm looking for a new way to publish reports based on OLAP data and was very disappointed to find that Reporting Services does not natively support displaying MDX query results in a matrix. While it is possible to assign an MDX query to the matrix control in Reporting Services, the two main problems are that the columns of the query must be measures (not dimensions), and it does not support display of server based formatting (fore color, back color, and font flags). Does anyone know of any custom control that properly deals with Analysis Services data? Thanks for any info you have!
View Replies !
Display Data In Reports Based On Active Directory Group Membership...
Hello, I am fairly new to SQL 2005 and Reporting Services. We are trying to create a report that will display sales data based on group membership from Active Directory. For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group. If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group. Background Information: We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services. We are delivering the reports through a Sharepoint site. Please let me know if anyone has a good way to tackle this. Thanks, Justin
View Replies !
Display Multiple Columns Into One Column
My first ASP.NET/SQL project. I'm creating an Asset Management DB. I wish to view the Asset number, the full name of the user it's assigned to and the Make and Model of each record in the DB. However assets are split into different categories e.g. monitors, PCs, Preinters etc. and they are all in different tables. The SQL below displays the asset number, Name of person assigned and the model of the asset. SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model, PCModel.Model AS Expr1, PrinterModel.Model AS Expr2 FROM Hardware INNER JOIN [User] ON Hardware.UserID = [User].UserID INNER JOIN Model ON Hardware.ModelID = Model.ModelID LEFT OUTER JOIN MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID This outputs:- Asset number FullName Model Expr1 Expr2 00000 User Name Model NULL NULL 00001 User Name NULL Model NULL 00002 User Name NULL NULL Model However what i hope to acheive is output Model, Expr1, Expr2 into one column like so:- Asset number FullName Model 00000 User Name Model Can i do this in the SQL or do i have to do it in my ASP.NET (VB) Page? Using VSWD 2005 Ex Edition and SQL Server 2005 Ex Edition Thank you for your replies
View Replies !
|
TRACKER
