Developer forum

Anything? Please :)

Hi Jonas :-)

Haven't tried to do what you are trying to, but sometimes you can't do the same things in datalists SQL as you can when using SMSS. I've experienced it several times - e.g when trying to run an update statement like this: http://developer.dynamicweb-cms.com/forum/cms-standard-features/datalist-update-problems.aspx

Can you run the SQL in the Firehose in Management Center?

Hi Rene,

Yes, the SQL executes correctly through the firehose aswell. 

It's pretty weird that the datalist denies the SQL statement.

Thanks for the reply.

Hi Jonas

I'll have someone look into this.

BR Nicolai

Hi Nikolaj, can i write someone an email with the correctly prefixed sql, so they can test it?

best regards.

Jonas

Can I please have you post "create table" SQL-statement for the tables inflicted. I tried to reproduce with a similar setup, but with no luck (sort of speak).

Regards /Snedker

I believe that this setup is similar to yours: http://screencast.com/t/hQmfGVaA8uu. This works fine for me. You notice anything sticking out?

Regards /Snedker

Hi Morten, not exactly no, close one :)

First tabel is :

CREATE TABLE [dbo].[TillidsmandsPortal_UserGroups](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [UserID] [nvarchar](255) NOT NULL,
    [UserSectionID] [nvarchar](255) NOT NULL,
    [UserUnitID] [nvarchar](255) NULL,
    [Deleted] [bit] NOT NULL,
    [DeletedDate] [datetime] NULL,
 

Second table:

CREATE TABLE [dbo].[TillidsmandsPortal_UserGroupUsers](

    [GroupID] [int] NOT NULL,
    [UserID] [nvarchar](255) NOT NULL
) ON [PRIMARY]

The first table holds the "group" or "list" object and its details ( name, date created etc )

The second tabel works as a linker, containing the user-id's of the people signed up to that list.

Now, i want to fetch all lists, but only if the current user is either owning the list, or part of it.

SELECT
    [TillidsmandsPortal_UserGroups].*

FROM
    [TillidsmandsPortal_UserGroups]

    LEFT JOIN [TillidsmandsPortal_UserGroupUsers] ON [TillidsmandsPortal_UserGroupUsers].GroupID = [TillidsmandsPortal_UserGroups].ID

WHERE
 (   [TillidsmandsPortal_UserGroups].UserID =  '@Session("DW_extranet_AccessUserID")' OR
      [TillidsmandsPortal_UserGroupUsers].UserID   =  '@Session("DW_extranet_AccessUserID")' )

    AND [TillidsmandsPortal_UserGroups].Deleted = 0

ORDER BY
    [TillidsmandsPortal_UserGroups].[ID] ASC

This works fine in FireHose and mssms, but not the datalist.

Thanks for the answers.

This is all just running fine on my half: http://screencast.com/t/uP9epreJmvyq.  I am not sure of the @Session part that you are having - if that return proper values? A SQL Profiler may give you the answer you're looking for.

Are you by any chance on a SQL 2005?

Let me have comment on the above.

Regards /Snedker

Hi Morten, weird.

No, im using a SQL 2008. Replacing the "Session" values with user ID's (static) gives me 0 results aswell.. double weird.

Again - I suggest that you try with a SQL Profiler to see what is actually passed to the SQL-server. Apart from that I'm out of ideas.

/Snedker