Developer forum

larsbb wrote:

I want to show some data from the AccessUser-tabel in my SQL-database, so I have decided to use the module Database Publishing.

 

I therefore use Dynamicweb Database as database type and connect to the sql-database and the table. So far so good.

 

I set my sorting criteria to AccessUserInitials <> 'Null' and choose sorting by the same table. Then I choose the fields that I want, accessuserinitials and accesscustomfield_custom. Finally I select which templates I want to use for this publishment and also deselect any view types, since I only want the rows listed.

 

The final sql-sentence generated by Dynamicweb is therefore:

 

select * from [accessuser] t1   where t1.[accessuserinitials] <>  'null'   order by t1.[accessuserinitials] asc

 

This is pretty simple, but yet it gives me the following error:

 

Warning: Incorrect sql statement

 

I have also tried different variants, like without the sorting and so on, but no luck. What am I doing wrong?

 

 

I also tried with custom SQL where I wrote:

 

SELECT     AccessUserInitials, AccessCustomField_custom
FROM         AccessUser
WHERE     (AccessUserInitials <> 'NULL')
Order by AccessUserInitials asc

 

Or in Dynamicweb:

 

SELECT t1.[accessuserinitials], t1.[accesscustomfield_custom]
FROM [accessuser] t1
where (t1.[accessuserinitials] <>  'NULL')
order by t1.[accessuserinitials] asc

 

This is actually the sql code from the database itself, where the output was correct. If I use the above in the module, it tells me:

 

Warning: Fields are not set

 

Can one not use AccessUser-table for this module or what am I doing wrong? The error-code that I get, doesnt help me much, since there isn't an errorlist, that I can find, to decode these messages and give me suggestions for what to do.

After some quick searching I found this:

"This is a limitation by design to avoid publishing login information for all users especially backend users. Set up a view on the SQL server or query on the mdb file and use that as source for your publishing instead."

http://developer.dynamicweb.dk/Forum-17061.aspx?action=ShowThread&ThreadID=1156

 Any work around to this, if you want to publish content from the accessuser-table? Creating a copy of the table and publishing it, wont work for me, I am afraid, since the content og the table and thereby the output, needs to be up to date.

I found a solution for this problem, which I thought I would like to share with anybody who might be interested:

First create a view on the SQL- Server. Access to the sql-database is needed here. You can order a websql-access from the Helpdesk, if your solution is running sql. If the solution is running access, you need access to the databasefiles to create a view here instead.
 

The view you create, could be:

Select * from accessuser

Save as UserInfo for instance.

Now you simply use the databasepublishing-module, using custom-sql code.

You could write:

select * from UserInfo

>> Press Update and now you should be able to see the fields under the fields tab, thus confirming that a connection has been made.