So far I'm able to populate an ajax control toolkit reorder list with an SQL table, but I can't get it to reorder through the database.
The SQL table is something like this-
int ListID, varchar Title, int SortingOrder (SortingOrder always goes 1-n, there are no duplicates and no gaps)
When the user changes the position of an entry, I need the reorder list to update the SortingOrder of all the pages between the starting and ending place of the reordered entry. I already have that function written, ReorderListEntries (int OldSortingOrder, int NewSortingOrder), but how and where do I call it?
Basically, when the user reorders the list, I need it to-
1. get the old and new positions of the reordered entry.
2. pass this information back to the server
3. call ReorderListEntries (old,new)
4. partial page refresh the list
Help with this one will be greatly appreciated.
Ive got a table of items which holds the privacy settings for each user. The items can either be the value 1 = Yes ,or 0 = No. Is it possible to bind these two options to a checkbox? I tryed to simply bind it to the checkboxes "checked" property, But it errored. Does anyone know how to bind an int feild to a checkbox? cos in the long run I want to add ajax toggle items to the checkboxes, but i was also wondering why that errored, but i think its cos i did my binding wrong. thanks si!
Hi, I Use Ajax auto suggest text box. this autosuggest text box lookup throw table string field(nvarchar).It works find for Numbers and English words. But When I use LTR languages like arabic,the textbox does not show anything at all. I use UTF-8 and when I search this field it leads to correct results. and I use "sql Latin1 CP CI AS"but I don't know what is the problem with it.
Howdie y'all, Im adding rows to my database like this: GroupID = IdentityGroupUser = UserIdGroupColumn = 1 GroupPosition = Count (*) where UserId = UserId AND Column = 1 Int this way my table looks like this on a certain moment: 1 ; 1 ; 1 ; 02 ; 1 ; 1 ; 13 ; 1 ; 1 ; 24 ; 1 ; 1 ; 3
But then i would, for example, like to delete the 3rd row and i like to renumber the last column so form this:1 ; 1 ; 1 ; 02 ; 1 ; 1 ; 14 ; 1 ; 1 ; 3 <- this is my problem to this:1 ; 1 ; 1 ; 02 ; 1 ; 1 ; 14 ; 1 ; 1 ; 2 <- here it is solved
Whats the simplest sql stored procedure to perform this reordering task??? Thanks, Wes
default web site runs as a 2.0 application in a 2.0 application pool, the reports and reportserver run in application pool for 1.1
after adding the entries necessary to run ASP.NET AJAX stuff on my default web site the report manager and server both error out when trying to browse to
http://server/reports or http://server/reportserver
do I need to add something into the ReportManager web.config?
Server Error in '/Reports' Application.
Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Child nodes are not allowed.
Source Error:
Line 33: --> Line 34: <pages autoEventWireup="true" validateRequest="false"> Line 35: <controls> Line 36: <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/> Line 37: </controls> Source File: d:inetpubwwwrootweb.config Line: 35
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300
How or is there a way to reorder the packages in BIDS? Right now they are ordered by when they are created with the newest ones on the bottom. I'd like to organize these in more of a logical order but am unsure of how to do so.
Actually i have 20 reports like this (ie;2O DROPDOWNLIST VALUES AND 20 ACCORDIONS ONE FOR EACH),for reference i have added only 3 dropdownlist values and 3 accordions of code here.
My requirement is , on page_load only the first report (Patient Information Report)should be selected in dropdownlist and the report for the same(accordion)should be visible, and now if i select some other report name in the dropdownlist then that particular report accordion only should be visible,and all other reports(accordions)should be not visible.
The business operation is this: The data is stored and accessed by Invoice Number and Line Number. If there are 4 lines, 1,2,3,4 and number 2 is deleted the users want lines 3 and 4 reordered to be 2 and 3. The existing code does things the following way, but I have my suspicions about how bulletproof it is: Existing Data: 123,1 123,2 123,3 123,4
Begin Transaction Delete where InvoiceNumber = 123 and LineNumber = 2. Update set LineNumber=2 where InvoiceNumber=123 and LineNumber=3 Update set LineNumber=3 where InvoiceNumber=123 and LineNumber=4 Commit Transaction
Quote: Originally Posted by mrtwice99 Yes, but how would you figure out which row was "before" it? Code:
SELECT id, sortOrder, name FROM daTable WHERE id = 937 OR sortorder = ( select max(sortorder) from daTable where sortorder < ( select sortorder from daTable where id = 937) )
Use the Northwind database Products table as an example.Purchasing dept gets a report showing when inventory items on hand qty arebelow the reorder level.easy enough:Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere (UnitsInStock < ReorderLevel)Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel2 Chang 1 17253 Aniseed Syrup 1 1325It would be nice to know what other products are purchased from this samevendor in case other items are close to their reorder level.All products for Supplier ID 1Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere SupplierID = 1Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel1 Chai 1 39102 Chang 1 17253 Aniseed Syrup 1 1325This shows there is 1 more product (Chai) that also comes from Supplier 1.Is there a way to show all items from a vendor when some of the items arebelow the reorder level without needing a separate query for each vendor?Thanks
Hi,Is it possible to reorder the table definition in a SQL 2005 table?Example : i want to put to table definition in alphabetical order.Thanks in advance
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
Ok, I want to write a stored procedure / query that says the following: Code: If any of the items in list 'A' also appear in list 'B' --return false If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF (SELECT values FROM tableA) IN(SELECT values FROM tableB) Return False ELSE Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this?
Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2 Windows Server 2003 1GB Ram....(I know, I know)
TIA
Code Snippet GO CREATE TABLE [dbo].[Tokens]( [TokenID] [int] IDENTITY(1,1) NOT NULL, [Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED ( [TokenID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens] ( [Token] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE FUNCTION [dbo].[SplitTokenList] ( @TokenList varchar(max) ) RETURNS @ParsedList table ( Token varchar(255) ) AS BEGIN DECLARE @Token varchar(50), @Pos int SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ',' SET @Pos = CHARINDEX(',', @TokenList , 1) IF REPLACE(@TokenList , ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1))) IF @Token <> '' BEGIN INSERT INTO @ParsedList (Token) VALUES (@Token) --Use Appropriate conversion END SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos) SET @Pos = CHARINDEX(',', @TokenList, 1) END END RETURN END GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList] @TokenList varchar(max) AS BEGIN SET NOCOUNT ON; create table #Tokens (TokenID int default 0, Token varchar(50)) create clustered index Tind on #T (Token) DECLARE @NewTokens table ( TokenID int default 0, Token varchar(50) )
--Split ID's into a table INSERT INTO #Tokens(Token) SELECT Token FROM SplitTokenList(@TokenList) BEGIN TRY BEGIN TRANSACTION --get ID's for any existing tokens UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0) FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token) OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old SELECT TokenID, Token FROM #Tokens WHERE TokenID <> 0 UNION SELECT TokenID, Token FROM @Tokens COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @er nvarchar(max) SET @er = ERROR_MESSAGE(); RAISERROR(@er, 14,1); ROLLBACK TRAN END CATCH; END GO
Hi, I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task-> dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
PROCEDURE ListFilteredEvents @FilterList varchar(200) -- contains ‘3,5’ AS SELECT EventID FROM Events WHERE (any value in Categories) IN @FilterList
Result:
EventID ---------- 2
How can I select all records where any value in the Categories column matches a value in @FilterList. In this example, record 2 would be selected since it belongs to category 3, which is also in @FilterList.
I’ve looked at the table of numbers approach, which works when selecting records where a column value is in the parameter list, but I can’t see how to make this work when the column itself also contains a comma delimited list.
Amol writes "I wish to write a UDF that will concatenate values from each row of a column for a select query. for each invocation of the UDF it should check the callee query to see if its the next row of the current query and concatenate the value or if its a new query start from null.
eg. : select list(name) from employee
will return a "single" list of all names in the employee table as a comma delimited string.
I am new to the whole reporting World and I was wondering if their is a solution for my problem. I searched for one and a half hour and I am getting desperate.
I need to repeat a report. So I thought: I can use a list for that. But when I placed all my objects in that list and linked it to a data source it gives an error at every object. I had this problem before and I made a work around so I did not solve it. I need a list in a list or a matrix in a list. I get the following errors:
Error 1 [rsDataRegionInDetailList] The list €˜list3€™ is in a list that has no group expressions defined for it. To use a data region in a list, the list must have group expressions. c:projectencitrix estcitrixusagedb eportCitrixUsage.rdl 0 0
Error 2 [rsFieldReference] The Value expression for the textbox €˜textbox16€™ refers to the field €˜USERNAME€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. c:projectencitrix estcitrixusagedb eportCitrixUsage.rdl 0 0
I have done queries before where you have something like this: Select name From people Where uid in (Select UID from employees where salary > 500000) as an example. I have to use stored procedures, I can't connect directly... I have a datatable of records that I select from a database. In the database table there is a flag 'checkedOut' (bit field) wich I want to set to true (1) for each of these records. Since I have to use stored procedures I think I am limited to passing in parameters. I wrote a function using a stringbuilder to concatonate the key fields into a comma delimited list "1, 55, 98" etc. and thought to use it as the clause in the 'in' sample ie:Update ServiceRequests set CheckedOut = 1 Where UID in (@UIDList)
I have tried a few different things, none works. Any ideas how you might accomplish this with stored procedures? The records are passed to a thread to be dealt with and it's possible the next time it runs it will pick up the same records for processing if the first thread isn't through. I needed the 'CheckedOut' to ensure I don't accidentally do that. Thanks.
How can I get a list of the last payment for each client? SELECT ClientID,PaymentDate,PaymentAmount FROM tblClients INNER JOIN tblPayments ON tblPayments.ClientID = tblClients.ClientID
say I have a list from an sql statement (results list) this list contains 10 items
In another table, in one particular column - there is a match for one of these items from the initial list.
SO... this may be the list _____________________ itemnumber 1 2 3 4 5 6 7 8 9 10 ------------------------------
in the other table there is a match... but just for one item on that list. ____________________ othertablefield 11 13 14 3 <------ match 99 78 ----------------------------
