I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
We had a divestiture within our company. Now what used to be contained in one database in now split into two databases. One showing all history and one being all current data as of 6/1/2008. Is there an easy way to Union or Join these? Right now I'm currently doing a simple UNION ALL, but can't group the two select statements:
SELECT Year,Location,QtySold FROM historydb
UNION ALL
SELECT Year,Location,QtySold FROM currentdb
Can't do a subset and group both of these selects. How would some of you pro's do this? Right now I can put this in a simple view and then create a SP off of this view that would do this grouping, but it seems like I should be able to do it all in one query. Thanks.
Hello,So my table contains say 100,000 records, and I need to group thecategories in fld1 by the highest count of subcategories. Say fld1contains categories A, B, C, D, E.All of these categories contain subcategories AA, AB, AC, AD,...AJ, BA,BB...BJ, CA, CB, CC...CJ, etc in fld2.I am counting how many subcategories are listed for each category. LikeA may contain 5 of AA, 7 of AB, 3 of AC, 11 of AD...1 for the rest and20 of AJ. B may contain 2 of BA, 11 of BB, 7 of BC, and 1 for the rest.I want to pick up the top 3 subcategory counts for each category. Wouldlook like this:Cat SubCat CountA AJ 20A AD 11A AB 7B BB 11B BC 7B BA 2So event though each category contains 10 subcategories, I only want tolist the top 3 categories with the highest counts as above. If I justdo a group by and sort I can get this:Cat SubCat CountA ... ...AAAAAA...B ... ...BBBBB...But I just want the top 3 of each category. The only way I can think ofto do this is to query each category individually and Select Top 3, andthen Union these guys into one query. The problem is that I have tohardcode each category in the Union query. There may be new categoristhat I miss. Is there a way to achieve what I want without using Union?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Table1 -------------------------------------------------------------------- A C1 C2 C3 C4 -------------------------------------------------------------------- x 0 0 3 2 x 0 1 0 2 x 0 0 2 1 y 1 5 2 0
Table2 -------------------------------------------------------------------- A C1 C2 C3 C4 -------------------------------------------------------------------- x 0 0 1 4 y 1 0 3 1 y 1 2 0 0 y 0 0 5 1
select * from( select A,C1,C2,C3,C4 from Table1 group by A union select A,C1,C2,C3,C4 from Table2 group by A )as t
Result: -------------------------------------------------------------------- A C1 C2 C3 C4 -------------------------------------------------------------------- x 0 1 5 5 y 1 5 2 0 x 0 0 1 4 y 2 2 8 2
But i need the result like i.e grouped by column 'A' -------------------------------------------------------------------- A C1 C2 C3 C4 -------------------------------------------------------------------- x 0 1 6 9 y 3 7 10 2
select * from( select A,C1,C2,C3,C4 from Table1 group by A union select A,C1,C2,C3,C4 from Table2 group by A )as t group by A
The above query gives the following error [Error Code: 8120, SQL State: S1000] Column 't.C1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Here is my sql code. I'm using a "union all" to merge Incidents and Service Requests into one table. This works fine when I don't use the "group by". When using "group by" to get the total number of tickets per "Area" it is giving me duplicates. So it is returning a distinct list of "Area" from both select statements.
SELECT IRAreaDN.DisplayNameas 'Area' ,Count(IR.Id) as 'Total Requests' --,IRSupportGroupDN.DisplayNameas 'Support Group' --, CAST(DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.CreatedDate) AS DATE)as 'Created Date' --, CreatedByUser.UserName as 'Created By User ID'
I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.
What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).
I have tried 2 statements:
#1: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) GROUP BY tblTable1.Name, tblTable1.INDate ORDER BY tblTable1.Name;
#2: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));
Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.
Example:
NameINOUT John Smith1/1/20141/2/2014 John Smith1/1/2014(blank)
I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?
Well adding it to a group by or function skews the result set. How to write this query so it displays as I need it to? This is what I have thus far, and it works as it should UNTIL I add in the line of
cast(cte.[C] As float)/cast(sum(cte.[C]) over() as float)*100 As [Rate1],
Presents the error of: Msg 8120, Level 16, State 1, Line 35 Column 'cte.[C]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This is my full on query -- with 3 CTE's involved to get me the actual result set I am after.
;with cte as ( select [state], case when exists (select 1 from table2 R where R.centername = d.centername) then 1 else 0 end as [L], case when exists (select 1 from table3 C where C.centername = d.centername) then 1 else 0 end as [C] FROM maintable d ),
ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int) As Begin Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1' print @Sel Exec Sp_executesql @Sel End
--Execute Mypro 1,4 --->>Here I Executed Error Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between 1 and (1+4)-1 Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM Procedure successfully created but giving error while Excuting'. Please anybody give reply Thanks
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year Month Date
And the following row groups:
Region Product SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
I have a problem with a union all that seems to be changing the results of a column based on using the union, when I run the querys separatly I get the expected results in the field!
Basically 3 queries pulled together for reporting purposes.
Orders (which can include a gift so the gifts are separated in the second query). OrderGifts (pulled out of the orders). Gifts (straight gifts with no associated orders).
The problem is showing up in the first ORDERS query. I can pull a specific order and the calculation for the AMOUNT(eg:$15.20) is correct, but when I combine the tables I get a different AMOUNT(eg:$15.199999999999999).
Here are the values for the fields in the amount calc: totitems=12.9500 totcredits=.0000 discount=.0000 tax=.0000 refund=.0000 convrate=1.0 postage=2.2500 giftamt=15.0000
I tried converting each of the fields to an decimal befre the calc and same results
I tried ROUND and same thing!
I have been trying to chase this down all day, cant figure out what the problem is or how to get around it...whats going on here I am missing?
Here is the query, if you need any other info just let me know, I would GREATLY appreciate ANY direction of figuring this out!
select * from ( --orders select acctnbr as PartnerId, 'O' as TrnType, ordnbr as TrnId, 0 as FundId, odate as WorkDate, adate as PostDate, sdate as ShipDate, cast( ( IsNull(totitems, 0) - IsNull(totcredits, 0) - IsNull(discount, 0) - IsNull(tax, 0) - IsNull(refund, 0) + IsNull(postage, 0) ) * IsNull(convrate, 1) as Decimal(10,2)) as Amount, batchnbr as BatchId, motvcode as MotivationCode, currcode as CurrencyType, Status, paycode as PaymentType, paytype as CardType, paynbr as CardNbr, expire as CardExpire, ChargeAuth, ConvRate, MediaCode, hcf.dbo.KmaiProjMotvTest(0,motvcode) as ProjMotvName from kmai.dbo.o01omst
UNION all
--orders gifts select acctnbr as PartnerId, 'G' as TrnType, giftref as TrnId, giftfundid as FundId, odate as WorkDate, null as PostDate, null as ShipDate, ROUND((giftamt * IsNull(convrate, 1)),2) as Amount, batchnbr as BatchId, giftmotvcode as MotivationCode, currcode as CurrencyType, Status, paycode as PaymentType, paytype as CardType, paynbr as CardNbr, expire as CardExpire, ChargeAuth, ConvRate, MediaCode, hcf.dbo.KmaiProjMotvTest(giftfundid,giftmotvcode) as ProjMotvName from kmai.dbo.o01omst where adate is null and giftfundid <> ''
UNION ALL --gifts select acctnbr as PartnerId, 'G' as TrnType, gh.giftref as TrnId, convert(int, fundid) as FundId, gdate as WorkDate, applydate as PostDate, null as ShipDate, ROUND((IsNull(gd.amt, 0) * IsNull(convrate, 1)),2) AS Amount, batchnbr as BatchId, motvcode as MotivationCode, currcode as CurrencyType, Status, paycode as PaymentType, paytype as CardType, paynbr as CardNbr, expire as CardExpire, ChargeAuth, ConvRate, MediaCode, hcf.dbo.KmaiProjMotvTest(fundid,motvcode) as ProjMotvName from kmai.dbo.g03ghdr gh inner join kmai..g04gdtl gd on gh.giftref = gd.giftref
I get a Invalid Column Name ' '. with this procedure. Can anyone see what migh be wrong?
Thanks,
SELECT A.CompanyName,C.FirstName,C.LastName,C.Client_ID, CASE WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +" "+ C.[LastName] ELSE A.[CompanyName] END AS DRName, C.Client_ID FROM tblClients C INNER JOIN tblClientAddresses A ON C.Client_ID = A.Client_ID WHERE (C.Client_ID = 15057) AND (A.MailTo=1) AND Convert(varchar(5), GETDATE(), 10) BETWEEN Convert(varchar(5), A.Startdate, 10) AND Convert(varchar(5), A.Enddate, 10) OR (A.Startdate Is Null) AND (A.EndDate Is Null) GO
Declare @strSql nvarchar(255) Set @strSql="Select * from table WHERE " Set @strSql=@strSql + 'Price BETWEEN ' + CONVERT(nvarchar(20),@MinPrice) + ' and ' + CONVERT(nvarchar(20),@MaxPrice )
If @TypeHome != "No Preference" Set @strSql=@strSql + ' and Type = ''' + @TypeHome+ ''''
If @Location != "No Preference" Set @strSql=@strSql + ' and City = ''' + @Location+ ''''
Set @strSql=@strSql + ' and IDX = ''Y'' ORDER BY Price' Exec(@strSql) GO
The Error I get is: "Error 207: Invalide Column Name 'Select * from table WHERE' Invalid Column Name 'No Preference' Invalid Column Name 'No Preference'
I have checked the table and the columns do exist, spelled correctly and caps are all the same. Also, this same SP in another table works just fine.
Can anyone tell me why I get the above message using the following stored procedure and passing in a value of 120: CREATE Procedure qryAnalysisCountMain (@WizardGroup1Question int) As EXEC("SELECT QuestionDescription FROM Questions WHERE QuestionCode = " + @WizardGroup1Question)
120 just happens to be the asci value of 'x' and whatever number I pass in gets converted into it's character equivalent and the sp tells me it can't find that column name. QuestionCode is an int field so there is no problem there
The procedure works OK with: SELECT QuestionDescription FROM Questions WHERE QuestionCode = @WizardGroup1Question
However I need the SQL in an EXEC as the sp will eventually be dynamic so that i can pass in the name of the table to select from.
Hi I have a dynamic select statement which is showed below. declare @query varchar(100) set @query = 'select * from undergraduate where Gender =' + @Gender exec (@query)
// When I execute the @query, I get an error message like "Invalid Column Name Male". I think I need to put a single quotation around the dynamic variable, so that I have select * from undergraduate where Gender ='Male'. But I am not sure how to do that.
SELECT PRODUCT_ID, PRODUCT_END_DATE, CASE WHEN PRODUCT_ID = 1 THEN DATEADD(YY,-5,PRODUCT_END_DATE) WHEN PRODUCT_ID = 2 THEN DATEADD(YY,-10,PRODUCT_END_DATE) WHEN PRODUCT_ID = 3 THEN DATEADD(YY,-15,PRODUCT_END_DATE) END AS MODIFIED_END_DATE FROM PRODUCTS WHERE MODIFIED_END_DATE BETWEEN '2008-04-01' AND '2008-04-30'
when I execute this query returns an error as Invalid column name MODIFIED_END_DATE
I have my column names right but its telling me they are invalid. It must be something to do with how I have my subquery formatted but I don't see it. I was wondering if anyone else can see it? It tells me payer_id is not right and I know its coming from the bolded section. I just added that line to do some additional grouping. I know that the query above aliased as D was working before I put the bolded line in. Am I setting this up wrong?
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name, tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp from PersonMIA tmp join person a on a.person_id = tmp.person_id join patient_encounter b on a.person_id = b.person_id join provider_mstr c on b.rendering_provider_id = c.provider_id cross apply(select top 1 payer_name from person_payer where person_id = tmp.person_id order by payer_id) d join payer_mstr e on d.payer_id = e.payer_id join mstr_lists f on e.financial_class = f.mstr_list_item_id where c.description = 'Leon MD, Enrique' group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name, tmp.date_of_birth,d.payer_name,b.create_timestamp )tmp2 where year(create_timestamp) IN (2005,2006) group by person_nbr,payer_name,first_name,last_name,description,create_timestamp
I am building a query and thought I had completed it but I get 'Invalid Column Name "A1" when I run it? SELECT Groups.GroupID, Sum(Stages_On_Route.Distance) AS Miles_Covered, Groups.Group_Name FROM Groups INNER JOIN ((Route INNER JOIN Departure ON (Route.GroupID=Departure.GroupID) AND (Route.RouteID=Departure.RouteID)) INNER JOIN Stages_On_Route ON Route.RouteID=Stages_On_Route.RouteID) ON Groups.GroupID=Departure.GroupID GROUP BY Groups.GroupID, Groups.Group_Name HAVING (((Groups.GroupID)="A1"));
Hi I'm new to this forum and I seem to be having a basic problem. I have moved from using MS Access to SQL Server and I seem to be having problem with the SQL below.
SELECT Table_A.*, (Statement_A) AS Value_A , (Value_A + 4) AS Value_B
FROM Table_A
I keep on getting an error stating invalid column name, I know that statement A is fine because when I move the calculation into Value_B it works.
When I try to add the line . . .CPM * MOU AS COST,after all the CASE lines I get the response in SQL Query Analyser,Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'CPM'.Is it because I can not do this on this particular query since CPM isyet to be defined or do I just need to rephrase the request anotherway?The query now looks like this. . .SELECT DISTINCTDATA.dbo.[2004_JANUARY_SUM].RATEKEY,DATA.dbo.[2004_JANUARY_SUM].[DATE],DATA.dbo.[2004_JANUARY_SUM].CXRKEY,DATA.dbo.[2004_JANUARY_SUM].Area,DATA.dbo.[2004_JANUARY_SUM].Region,DATA.dbo.[2004_JANUARY_SUM].Market,DATA.dbo.[2004_JANUARY_SUM].AKA,DATA.dbo.[2004_JANUARY_SUM].MARS_NAME,DATA.dbo.[2004_JANUARY_SUM].O_MTA,DATA.dbo.[2004_JANUARY_SUM].O_MTA_NAME,DATA.dbo.[2004_JANUARY_SUM].O_STATE,DATA.dbo.[2004_JANUARY_SUM].O_LATA,DATA.dbo.[2004_JANUARY_SUM].O_LATA_NAME,DATA.dbo.[2004_JANUARY_SUM].MSC_CLLI,DATA.dbo.[2004_JANUARY_SUM].Trunk,DATA.dbo.[2004_JANUARY_SUM].Carrier,DATA.dbo.[2004_JANUARY_SUM].NPA_NXX,DATA.dbo.[2004_JANUARY_SUM].CALLS,DATA.dbo.[2004_JANUARY_SUM].MOU,DATA.dbo.[2004_JANUARY_SUM].TANDEM,DATA.dbo.[2004_JANUARY_SUM].T_MTA,DATA.dbo.[2004_JANUARY_SUM].T_MTA_NAME,DATA.dbo.[2004_JANUARY_SUM].T_STATE,DATA.dbo.[2004_JANUARY_SUM].T_LATA,DATA.dbo.[2004_JANUARY_SUM].[RC ABBRE],DATA.dbo.[2004_JANUARY_SUM].RC_ID,DATA.dbo.[2004_JANUARY_SUM].SWITCH,DATA.dbo.[2004_JANUARY_SUM].[OCN],DATA.dbo.[2004_JANUARY_SUM].[OCN_NAME],DATA.dbo.[2004_JANUARY_SUM].[CATEGORY],CASE WHEN SUBSTRING(DATA.dbo.[2004_JANUARY_SUM].[MSC_CLLI], 5, 2) =DATA.dbo.[2004_JANUARY_SUM].[T_STATE] THEN(CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_GXWHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR3WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR2WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THENTELECOM.DBO.DOMESTIC_LD_RATES2.[INTRA_VENDOR1]ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_TANDEMEND)ELSE(CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTER_GXWHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR3WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THENTELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR2WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THENTELECOM.DBO.DOMESTIC_LD_RATES2.[INTER_VENDOR1]ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_TANDEMEND)END AS CPM,CPM * MOU as [COST]INTO TEST.dbo.[2004_JANUARY_RATES]FROM DATA.dbo.[2004_JANUARY_SUM] LEFT OUTER JOINTELECOM.dbo.DOMESTIC_LD_RATES2 ONDATA.dbo.[2004_JANUARY_SUM].RATEKEY =TELECOM.dbo.DOMESTIC_LD_RATES2.RATEKEYORDER BY DATA.dbo.[2004_JANUARY_SUM].[T_LATA] DESCOPTION (MAXDOP 2)
SELECT ci.name, ci.address, CASE ISNULL(cci.geography,'') WHEN 'P' THEN 'port' WHEN 'A' THEN 'appt' WHEN 'X' THEN 'xatt' ELSE '' END AS Link, ci.InsuranceType FROM dbo.Insurance ci INNER JOIN dbo.Contract cci ON ci.InsuranceId = cci.InsuranceId AND cci.ContractId = 1266 ORDER BY ci.Link, ci.InsuranceType
===========================
My work environment is all in SQL SERVER 2005.
I have a problem with above T-SQL. There is an error while I run above code in Develoment Server.The error disappears when I put 'ci.Link' as 'Link' in OrderBy clause. My work environment has TEST, PRE-PRODUCTION and PRODUCTION. In all other environments the code runs fine with ci.Link but only in DEVELOPMENT the error persists. It has an error as 'Link is invalid column name'.
I need to identify where this column could be, I have checked in the syscolumns but did not find it as a result I am now having to manually check each view and sp that could use the column so I can transfer the database to my new SQL 2005 machine.
Is there an easier way to identify this coloumn by some sort of search??
Code Snippet SELECT 'N' AS LiquidatingFlag, Report, RptSection, Portfolio, RepExcRsn, Units, FROM Exc_SummaryData_Custom WHERE (Report IN (@Report)) AND (Portfolio IN (@Portfolio)) AND ( LiquidatingFlag IN(@LiquidatingFlag))
UNION ALL SELECT 'R' AS LiquidatingFlag, Report, RptSection, Portfolio, RepExcRsn, Units, FROM Exc_SummaryData_Custom_LIQ WHERE ( Report IN (@Report)) AND (Portfolio IN (@Portfolio)) AND ( LiquidatingFlag IN (@LiquidatingFlag))
I get a error message saying invalid column name LiquidatingFalg. (Actually there is no clumn in the original table)Can any one help to work this correctly?
Hi I have the following problem. I am trying to get some data from a database which matches the name in a session from a previous page:e.g. SqlCommand menubar = new SqlCommand("Select pernme from Person where pernme = " + (string)Session["tbname"], sqlConn); SqlDataAdapter dataAdapter5 = new SqlDataAdapter(); dataAdapter5.SelectCommand = menubar; DataSet dataSet5 = new DataSet(); dataAdapter5.Fill(dataSet5); DataTable selcartest4 = dataSet5.Tables["table"]; if (selcartest4.Rows.Count != 0)The session is called tbname and in that session is a users name however insetad of doing the nornal thing and retrieving the data in the sql database table matching that name it comes up with the following error message:System.Data.SqlClient.SqlException: Invalid column name 'jamie'this is weird as the 'jamie' is the name in the session from the previous page and in fact not a column name at all the column name is pernme I am totally stuck any help would eb great thanksJ
When i try to execute i receive following error: Msg 207, Level 16, State 1, Procedure WedstrijdDeelnemersSelectAllMPNietGoedgekeurd, Line 85 Invalid column name 'Ploeg'. I dont really see whats wrong with the select... it works fine in the 2 first parts of the querryALTER PROCEDURE [dbo].[WedstrijdDeelnemersSelectAllMPNietGoedgekeurd] -- Add the parameters for the stored procedure here @WedstrijdID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
... UNION
SELECT dbo.fncGetPersoonNaam(L.PersoonID,0) as Persoon, 'Ploeg: ' + WDPI.Ploeg as TypeInschrijving, WDPIL.LidClubID, WT.Omschrijving as WedstrijdType, C.Omschrijving as Categorie, WDC.WedstrijdDetailID, WDC.ID as WedstrijdDetailCategorieID, WDPI.ID as TypeInschrijvingID FROM Wedstrijd W INNER JOIN WedstrijdDetail WD ON W.ID = WD.WedstrijdID INNER JOIN WedstrijdType WT ON WD.WedstrijdTypeID = WT.ID INNER JOIN WedstrijdDetailCategorie WDC ON WD.ID = WDC.WedstrijdDetailID INNER JOIN Categorie C ON WDC.CategorieID = C.ID INNER JOIN WedstrijdDetailPloegInschrijving WDPI ON WDC.ID = WDPI.WedstrijdDetailCategorieID INNER JOIN WedstrijdDetailPloegInschrijvingLid WDPIL ON WDPIL.WedstrijdDetailPloegInschrijvingID = WDPI.ID ...END
Hello, I have a problem with my Update sql server command. The error message is strange : System.Data.SqlClient.SqlException: Invalid column name 'CMD_DATE_ORDER'. Invalid column name 'CMD_REF_CLIENT'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.addorder_aspx.MasterGrid_Update(Object Sender, DataGridCommandEventArgs E) in C:Inetpubwwwrootproject1addorder.aspx:line 157 On line 157 I have : UpdateCommand.ExecuteNonQuery()
The Update code : Sub MasterGrid_Update(Sender As Object, E As DataGridCommandEventArgs) ' update the database with the new values ' get the edit text boxes Dim dateCom As String = CType(e.Item.FindControl("dateCom"), TextBox).Text Dim NumBon As String = CType(e.Item.FindControl("NumBon"), TextBox).Text Dim NomDest As String = CType(e.Item.FindControl("NomDest"), TextBox).Text Dim NovoieDest As String = CType(e.Item.FindControl("NovoieDest"), TextBox).Text Dim nomvoieDest As String = CType(e.Item.FindControl("nomvoieDest"), TextBox).Text Dim cpDest As String = CType(e.Item.FindControl("cpDest"), TextBox).Text Dim villeDest As String = CType(e.Item.FindControl("villeDest"), TextBox).Text Dim paysDest As String = CType(e.Item.FindControl("paysDest"), TextBox).Text Dim telDest As String = CType(e.Item.FindControl("telDest"), TextBox).Text Dim dateExp As String = CType(e.Item.FindControl("dateExp"), TextBox).Text
Dim myConnection As New SqlConnection(strConnect) Dim UpdateCommand As SqlCommand = new SqlCommand() UpdateCommand.Connection = myConnection ' Add to CLIID_LGN the selected value in the DropDownList Dim cliid As String cliid = Trim(DDL.SelectedItem.Value)
' execute the command Try myConnection.Open() UpdateCommand.ExecuteNonQuery()
Catch ex as Exception Message.Text = ex.ToString()
Finally myConnection.Close()
End Try
' Resort the grid for new records If AddingNew = True Then MasterGrid.CurrentPageIndex = 0 AddingNew = false End If
' rebind the grid MasterGrid.EditItemIndex = -1 BindMasterGrid() End Sub I have send a response.write instruction : the values are all good. In the sql server database, the syntax of this 2 fields CMD_DATE_ORDER and CMD_REF_CLIENT are good.
"SELECT Test_Question.Question_ID, Test_Question.Grade_Number as GNum, Test_Question.Question_Number as QNum, Question.Question_Text as QText , Answer.Answer_Number as AnsNum, Answer.Answer_Text as AnsTxt, Answer.ID AS Ans_ID FROM Test_Question, Question, Answer WHERE Test_Question.Active=1 AND Test_Question.Question_ID = Question.ID AND Test_Question.Deleted=0 and Test_Question.Test_Detail_ID ="+ currPTestId +" AND Question.ID = Answer.Question_ID GROUP BY Test_Question.Question_ID ORDER BY Test_Question.Question_Number, Test_Question.Question_ID, Answer.Answer_Number";
But I get an exception column Test_Question.Grade_Number is invalid in the select list because it is not contained in either an aggregate function or the group by clause.
Could some one point out what is the problem in the above query.
I am trying to develop this query in MSSQL but am having a problem with the syntax.
I don't know why but the query is breaking on 'Invalid Column Name A1'. Here is my query.
Code:
SELECT Groups.GroupID, Sum(Stages_On_Route.Distance) AS Miles_Covered, Groups.Group_Name FROM Groups INNER JOIN ((Route INNER JOIN Departure ON (Route.GroupID=Departure.GroupID) AND (Route.RouteID=Departure.RouteID)) INNER JOIN Stages_On_Route ON Route.RouteID=Stages_On_Route.RouteID) ON Groups.GroupID=Departure.GroupID GROUP BY Groups.GroupID, Groups.Group_Name HAVING (((Groups.GroupID)="A1"));