.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to create a View to return all multiple child fields in one record?

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server

I have an application where the users can define up to 5 extra fields for a particular table (e.g. tblMaster). The custom data is all held in one table (e.g. tblCustom) so there could be up to 5 records in tblCustom for each tblMaster record. In my application I build a select statement based on the number of custom fields so that I can get a single row of data for each tblMaster record (sample below). I am now adding new features to the application and I want to expose an SQL View to the users for a report writer that will achieve the same result but still cater for the variable number of custom fields. I realise this needs to use probably a RIGHT OUTER JOIN so that NULL is returned if there is no matching field but I can't work it out. If I had five separate files it would be easy as each one would be a RIGHT OUTER JOIN for each. I could build 6 views (e.g. with no custom fields, with one custom field, with two custom fields etc) but that is not very efficient. I don't particulaly want to restructure the database as there would be a lot of work and retesting.
Any help would be very welcome!

SELECT tblProcess.ProcessNumber, tblProcess.Process, tblProcess.Risk, tblProcess.MRR, tblProcess.Version, tblProcess.LastChange, tblProcess.SecurityLevel, 

View Complete Post

More Related Resource Links

Multiple "Name" and "Title" columns when I create a view progammatically


I am trying to progammatically create some datasheet views to make it easy for my users to have grids to edit bulks of documents at once.  Everything seemed to work fine at first but then I discovered that when I switched from a Datasheet View to a Standard View, I noticed that the "Name" and "Title" columns repeat themselves.  Upon further investigation, I discovered that it actually added another "Name" and "Title" column to my document library. 

I think this may be due to the "Show with edit menu" values but after trying to filter out columns where AuthoringInfo == null, I still get the same problem.

Does anyone have a solution for this? 

My code is below: 

if (contenttype.Name !=

Cannot insert new record into multiple tables thru. sql 2005 View

I have a sql server 2005 database containing several tables: Orders, Shipping, and Planning.  They are joined together in the sql view V_Main.  I also have a ms access 2007 mdb that links to V_Main via odbc.  I created an access form using the view V_Main as the record source.  I cannot insert a new record into each of the 3 base tables using V_Main as the record source.  A new record is inserted into only one table.
I did find a suggestion in another forum that suggested copying the sql that creates the view into the record source, rather than the name of the view.  This does work, but adds another place to be updated manually should and other table be added to the view in the future.
I did verify that all tables have primary keys.
How can I add a new record for each of the 3 base tables using the view as the record source?  What is the code that could be placed in vb of the form?
Any suggestions would be helpful.
Thank you.

custom field type works in view properties, but not in list view: 3 parent fields filter child list,


My situation:

A) odd thing is that no matter where I put a stop point in my custom field type, it is NEVER hit when the items are displayed in list view.  Event with <Field Name="CAMLRendering">TRUE</Field> which causes the list to error out, but again no stop point to help me know why.

B) the value of the column in question will be different for every user who views the list, I'm querying a secondary list based on the user's loginName, and two of the fields from the list we're in. 

C) the display is customized based on whether any records were returned and the current DateTime, so possible displays would be "", "! 12/1/2010", and "7/7/2011"

D) and the text displayed links over to a form for them to do their assignment. the link url would be a config setting url + "?[one of the fields form the list we're in]"

My questions:

1) Is it possible for fieldtype code to execute while a list view is being rendered?

2) If not, then should I chase down the path of a content query webpart?  Will it be able to filter on the current users login name?

3) If not on 1 or 2, can you think of any other way to do this beyond creating a custom application page? (need to having filtering and sorting on this page)

GridView Parent Child View with JQuery Effects

I was just checked how to add some more cool effects to the GridView control using JQuery. In this post I will display parent-child data in the GridView control with some help of the wonderful JQuery library.

Let's first populate the GridView control.

Create ID (uniqueid?) from two fields when data is entered


I need to create an ID from two fields when they are entered into the db for the first time.  I thought Uniqueidentifer would do this, but it looks like uniqueidentifier is random and i have no control of the process. 

My user will enter 4 letters into a column called INIT and 4 numbers into a column called NUMB.  What I would like to do is create an id by combining those fields.

How can I do this?



Linq join fields for return data


  how would I join fields together?

           return (from c in storedb.Product_Categories

                         where c.Category_Name.Contains(searchText)
                         orderby c.Category_Name
                         select new {
                                    c.Category_Key && " ;" && c.Category_Name // HOW CAN I DO THIS.....

[newbie] How to return a data record from a webservice (wsdl)?



vs2010, .NET 4.0

I have a webmethod which I want to return a data record (for example: name, surename, address, age, zip etc)

I wonder how to accomplish this. I think the solution is about creating an object which contains all the records serialized. And then the webmethod returns this serialized object. I'm not 100% sure how to accomplish this, if some of you good provide me with some tips, tutorial or link to an article explaining it, that would be great

Linq to sql does not return appropriate multiple result sets with "If exists" conditions in the stor


Hello All,

I have a stored proc that returns 4 resultsets, they are in the format.

ALTER PROCEDURE [dbo].[pGetManagerEmployeeSummaryDummy]

    @ManagerStaffID int,
    @PeriodID int



IF EXISTS (select 1 from dbo.temployee e
    inner join dbo.temployeedetail ed
        on e.staffindividualid = ed.staffindividualid
        and ed.periodid = @PeriodID  
    inner join Incentive.tRole r
        on rm.IncentiveRoleID = r.RoleID
    where ed.ManagerIndividualStaffID = @ManagerStaffID
    AND r.RoleID = 1) --Closer
        SELECT e.StaffIndividualID, e.LastName, e.FirstName, r.RoleName, e.xname, e.yname

from dbo.temployee e

inner join dbo.temployeedetail ed
            on e.staffindividualid = ed.staffindividualid
            and ed.periodid = @PeriodID

where ed.ManagerIndividualStaffID = @ManagerStaffID

How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

How do you create a custom BDC data field that allows for multiple selected values?

I need help creating a custom data field using the BDC column as a base.  We need to allow for multiple selected values instead of just a single one.  I can't find anything on the net which shows how to do this.

create a dynamic view on code

How i can create a new view on code (on runtime) if this view dont exits?   Thanks

Create a dimension based on 2 fields

Hello I have a table as follow: No   Placestart    Placeend 1      DK                USA 2      UK                USA 3      USA              DK Now, I want a dimension called Country, which selects either of the rows where a value exist In SQL it would be ex (SELECT * FROM table WHERE placestart = 'USA' OR placeend = 'USA) So, when I select USA in the dimension all 3 rows are listed, as USA is included either in placestart or placeend. If I select DK row 1 and 3 is selected etc... Is this somehow possible?      

Create view

I need to create aview like this. Field1        Field2 James         Here it will be a SELECT from various tables where name='James' Helen          Here it will ............................................    where name='Helen' How to create this view.  Field1 is also from some table. NSG12

How to create a view in my database from a table in another server/database

Is there a way to create a view in my database referencing a table in another server/database if I have the connection information.  I know how to do it in Oracle, simply create a database link, but have never done so in sqlserver.

Multiple Child Nodes with For XML Path

I am trying to generate this format of xml and having difficulties because of the properties section. I can have unlimited number of properties and "For XML" tells me it can't generate the same node "Properties/@Name". I have tried several methods online, but can't seem to get it to work. OptionLists> <OptionList Name="Functions"> <Option Value="L9496"> <Properties Name="Description" Value="L9496 - Privacy Lock With Indicator" /> <Properties Name="Locked" Value="False" /> </Option> <Option Value="LV9040" /> <Option Value="L9485" /> </OptionList> </OptionLists>   My SQL currently looks like this: SELECT 'Functions' as '@Name', ( Select coalesce(nullif(No_,''),'NONE') "Option/@Value", Case NO_ when isNull(nullif(NO_,''),null) Then 1 Else 99 END "Option/@Order", 'TRUE' "Option/@Visible", 'FALSE' "Option/@Locked", '' "Option/@ToolTip", '' "Option/@ImageLink", '' "Option/@InfoMessage", 'QtySensitive' "Option/Properties/@Name", Case [Quantity Sensitive] When 0 Then 'False' When 1 Then 'True' End "Option/Propert

How to create an activity that allows multiple exit paths similar to FlowSwitch and FlowDecision

There have been several threads around this type of request but none have provided a definitive outcome for my requirements:I am looking to emulate the FlowSwitch or FlowDecision in a custom activity so as to simplify end user workflow authoring in a rehosted environment.Currenty I am limited to linking my custom activity to a FlowSwitch or FlowDecision to allow for simple logic to be visually composed. This creates additional complexity that I am trying to avoid.I understand that these are sealed types and even the abstract FlowNode base class of these types does not have any overridable types by design as WF 4.0 favours composition over inheritance.What options do I have to accomplish my objective?If activity composition is the response to my question then could I kindly request that some samples or links to samples are provided?

Insert Same Record Multiple Times.

I have a simple form that inserts a record into a SQL table. The fields are: SerialNumTagNumPONumLocationAddressDescription   This works great. However, I have now been asked to have the form insert x number of records at one time. They want to be able to fill out the following fields: PONumLocationAddressDescription and then have texbox where they can add a number and have that number of the same record inserted.    I hope this makes sense.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend