I'm trying to return values of a specific column using the below sql string, If I change it out to sql = "select * from a_page" and objRS("P_description") it returns the values but for some reason my page will not load when using the below code.
UPDATE: I turned off on error resume next and the error I'm receiving is select permission denied. How would I give myself permissions with the code below?
SQL = "select P_Name as P_Name, P_Description as P_Description from L_PagePermission inner join A_Permission on p_permissionID = pp_PermissionID inner join A_Page on P_PageID = PP_PageID where P_PageID = 84 order by p_Name"
Page_ID = 84
connectionstring = obj_ADO.getconnectionstring
Set objCon = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objCon.Open connectionstring
SQL = "select P_Name as P_Name, P_Description as P_Description from L_PagePermission inner join A_Permission on p_permissionID = pp_PermissionID inner join A_Page on P_PageID = PP_PageID where P_PageID = 84 order by p_Name"
objRS.open SQL, objCon
objRS.MoveFirst
while not objRS.EOF
response.Write objRS("P_Name")
objRS.MoveNext
wend
objRS.close
objCon.close
The error you get plus the code you mentioned that is working means one thing: on either L_PagePermission table or A_Permission (or both) the user passed in the connection string has no Read permissions.
To solve this, you have to either pass "stronger" user in the connection string, or grant Read permissions to the user over those tables via something like SQL Management Studio.
By the way, you can't "grant yourself permissions" through code due to obvious security reasons - permissions exist to prevent code from doing certain things in the first place.