Retrieve XML Data From DNN DynamicForm Datagrid Control
Posted: May 25, 2012 Filed under: DotNetNuke (DNN), SQL Server 2005, SQL Server 2008, SQL Server 2012 Leave a commentThe Datagrid is a new control added to DynamicForm 4 . It is handy to design a set of controls dynamically, for example, you can reuse the same set of controls to collect automobile information for a user if the user has more than one vechile.
You can view the result or email the result without any difficulty. However, if you want the dataset in a relational manner, you need to know some basic query skill to convert XML data into relational table columns.
Here is the code snippet which may be helpful if you come to this page:
with mycte as
(SELECT DynamicForms_ResultsID,
ResponseDateTime,
n.l.value(‘td[1]’,’VARCHAR(200)’) AS td1,
n.l.value(‘td[2]’,’VARCHAR(200)’) AS td2,
n.l.value(‘td[3]’,’VARCHAR(200)’) AS td3
FROM dbo.formdata_mytable
CROSS APPLY MyDGControlName.nodes(‘//tr’) n(l)
)
select * from mycte
Get Day Counts in Months from a Date Range
Posted: May 23, 2012 Filed under: SQL Server 2005, SQL Server 2008, SQL Server 2012 Leave a commentDECLARE @fromDate DATETIME
DECLARE @toDate DATETIME
SET @fromDate='2/2/2012'
SET @toDate='4/2/2012';
WITH mycte
AS (SELECT @fromDate AS DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 <= @toDate),
mycte2
AS (SELECT DateValue,
Dateadd(mm, Datediff(mm, 0, DateValue), 0) as myMonth,
Count(*) OVER(Partition BY Dateadd(mm, Datediff(mm, 0, DateValue), 0)) AS TotalDays
FROM mycte)
SELECT CONVERT(VARCHAR(10), Min(DateValue), 101) fromDate,
CONVERT(VARCHAR(10), Max(DateValue), 101) ToDate,
Max(TotalDays) AS TotalDays
FROM mycte2
GROUP BY myMonth
OPTION (MAXRECURSION 0)
Book: The Start-Up of You
Posted: May 10, 2012 Filed under: Uncategorized Leave a commentToday, I came across an article at cnn.com : “Permanent beta: Why your career is a work in progress”, which is an specially adapted excerpt of the book titled ” The Start-Up of You”, by Reid Hoffman and Ben Casnocha. Here is the link to the article:
After reading this article online, I bought a copy for myself and put it on my next week’s reading list. I really recommend to anyone who comes to this blog to read this book too.
Jingyang Li 