Forums (Obsolete)

Old Forums

Portal Engine Questions on portal engine and web parts.

Version 6.x > Portal Engine > Pass SQL parameter to query in Query DataSource

View modes:

Member

jheavner-ce - 3/19/2012 10:52:43 AM

Pass SQL parameter to query in Query DataSource

I have a document with a "SELECT" query defined as follows

SELECT ##COLUMNS## FROM View_person_ExecutiveBoardMembers_Joined p
INNER JOIN View_CMS_User u ON p.UserName = u.UserName
WHERE [ExecutiveBoardMembersID] = @ID

I am trying to use this query in a QueryDataSource but I don't know how to pass a value to the parameter @ID. There's nothing in the web part that indicates a SQL Parameter. I know how to do this changing it to

SELECT ##COLUMNS## FROM View_person_ExecutiveBoardMembers_Joined p
INNER JOIN View_CMS_User u ON p.UserName = u.UserName
WHERE ##WHERE##

but it seems like I should be able to pass a parameter just as easily as one would pass a Where clause.

Is this possible or would I need to write custom code to do this?

Kentico Support

kentico_janh - 3/20/2012 3:54:40 AM

RE:Pass SQL parameter to query in Query DataSource

Hello,

It can be done only through the WHERE condition property as you have mentioned it in your post, because a parameter can be anything so you would have to specify its name (column) anyway and that is practically the same thing, which you do in the WHERE condition. But if you want to pass only a parameter as a number or a string to defined field in your query, you can just place the ##WHERE## macro after the parameter assignment:

SELECT ##COLUMNS## FROM View_person_ExecutiveBoardMembers_Joined p
INNER JOIN View_CMS_User u ON p.UserName = u.UserName
WHERE [ExecutiveBoardMembersID] = ##WHERE##

So you will fill the WHERE condition only with a number like:

WHERE condition: 4

Best regards,
Jan Hermann

Member

jheavner-ce - 3/20/2012 11:11:36 AM

RE:Pass SQL parameter to query in Query DataSource

I get that, I really do. My question is, why is the parameter there? If I look at the queries that are generated for DocumentTypes there are queries there that have parameters. It sounds like you're saying if I wanted to use any of those queries directly in portal then I would have to change them to remove the parameter. It seems like there should be a way to pass a parameter in portal (not in a code-behind).


Kentico Support	kentico_janh - 3/21/2012 7:06:51 AM RE:Pass SQL parameter to query in Query DataSource Hello, The default SQL queries contain parameters, because they are used directly by Kentico CMS (Kentico can work with those parameters), so please don't change/delete them. If you want to use your own queries, please create new ones. Best regards, Jan Hermann

Top