[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

SQL Help

Hello, hope someone can point me in the right direction! Please let me know if I can make the questions easier to understand, as I could really do with the help...

We have a directory with categories and aliases, and I'm trying to add the ability to create alias subcategories to top level categories, where currently we can only add alias subcategories to other subcategories, which is not what we want.

For example, I would like to add the subcategory 'Estate Agents' to both the main categories 'Home and Garden' and 'Finance and Mortgages'.

I am only able to add 'Estate Agents' however to a subcategory of either, so would like to include the top-level categories in the drop down select option along with the sub-categories. Hope that makes sense!

The code is posted below and consists of 2 separate files to handle the data. It is currently requesting the PGID - which is the Parent Directory GID - i.e. the top level category.

We want the alias drop down select option to show not just the subcategories (GID) that come under the parent categories (PGID) top-level categories, but also the PGID top-level categoires themselves.

E.g. Part of the Select Option for aliases to be placed into:

Home and Garden (PGID)
Estate Agents (GID)
Furniture Suppliers (GID)
Garden Centres (GID) etc.

Not just:

Estate Agents (GID)
Furniture Suppliers (GID)
Garden Centres (GID)

Please let me know if I can clarify further as it's simple, but difficult to explain!

AddAlias.asp

<!--#include virtual="/Admin/FranchiseAdmin/DirectorySetup/Aliases/i_Alias.asp"-->

    <%
    objConn.Open strConn
    %>

   <input type="hidden" **name="PGID" value="<% = Request("PGID") %>"** />
         <%
           SQLCommand = "SELECT * FROM Directories WHERE GID='" & CleanSQLText(Request("PGID")) & "'"
            rsTemp.Open SQLCommand, objConn, adOpenStatic, adLockReadOnly
            if rsTemp.RecordCount <> 1 then
            FailSystemEvent "AddAlias:  Failed to load Existing Parent"
            Else
           %><option value="<% = rsTemp("GID") %>"><% = rsTemp("DirectoryName") %></option><%
             End if
             rsTemp.Close
             SQLCommand = "SELECT * FROM Directories WHERE ParentDirectoryGID IS NOT NULL AND GID <> '" & CleanSQLText(Request("PGID")) & "' ORDER BY DirectoryName"
             rsTemp.Open SQLCommand, objConn, adOpenStatic, adLockReadOnly
             While Not rsTemp.EOF
             CreateSelectOption rsTemp("GID"), rsTemp("DirectoryName"), CleanSQLText(Request("PGID"))
             rsTemp.MoveNext
             Wend
             rsTemp.Close
             %>
            </select>
    <%
    objConn.Close
    %>

Open in new window



i_Alias.asp


<%

Dim SQLCommand : SQLCommand = ""
Dim SQLFilter : SQLFilter = ""
Dim objConn : Set objConn = Server.CreateObject("ADODB.Connection")
Dim rsAlias : Set rsAlias = Server.CreateObject("ADODB.RecordSet")
Dim rsTemp : Set rsTemp = Server.CreateObject("ADODB.RecordSet")
Dim strUserError : strUserError = ""

Dim strFranchiseGID
Dim strDirectoryGID

Dim objEditor

Select Case Request.Form("Action")
    Case "AddNew"

        'strFranchiseGID = CleanSQLData(Request.Form("strFranchiseGID"))
        'If strFranchiseGID = "" then strFranchiseGID = Null
        strFranchiseGID = Session("AdminFranchiseGID")

        strDirectoryGID = CleanSQLData(Request.Form("PGID"))
        If strDirectoryGID = "" then 
            strUserError = strUserError & "An error has occured.  The Directory GID is invalid"
        end if

        if strUserError = "" then
            objConn.Open strConn
            SQLCommand = "SELECT TOP 1 * FROM DirectoryAliases"
            rsAlias.Open SQLCommand, objConn, adOpenKeySet, adLockOptimistic
            rsAlias.AddNew
            rsAlias("FranchiseGID") = strFranchiseGID
            rsAlias("DirectoryGID") = strDirectoryGID
            rsAlias.Update
            rsAlias.Close
            objConn.Close
            'Response.Redirect "Default.asp?PGID=" & Request("PGID")
            ' Redirect to the New Parent Folders
            Response.Redirect "Default.asp?PGID=" & strDirectoryGID
        End if
    Case "Update"
        if Not Session("Role_Franchise_ManageDirectory") = True then
            AccessDenied
        End if
        objConn.Open strConn
        SQLCommand = "SELECT * FROM DirectoryAliases WHERE GID = '" & CleanSQLData(Request.Form("GID")) & "'"
        rsAlias.Open SQLCommand, objConn, adOpenKeySet, adLockOptimistic
        if rsAlias.RecordCount <> 1 then
            FailSystemEvent "EditAlias Update failed - could not load unique record"
            rsAlias.Close
            objConn.Close
            Response.End
        End if
        'strFranchiseGID = CleanSQLData(Request.Form("strFranchiseGID"))
        'If strFranchiseGID = "" then strFranchiseGID = Null
        strFranchiseGID = Session("AdminFranchiseGID")
        rsAlias("FranchiseGID") = strFranchiseGID

        strDirectoryGID = CleanSQLData(Request.Form("PGID"))
        If strDirectoryGID = "" then 
            strUserError = strUserError & "An error occured.  The Directory GID is invalid"
        else
            rsAlias("DirectoryGID") = strDirectoryGID
        End if
        rsAlias.Update
        rsAlias.Close
        objConn.Close
        if strUserError = "" then
            Response.Redirect "Default.asp?PGID=" & Request("PGID")
        end if
    Case "Delete"
        if Not Session("Role_Franchise_ManageDirectory") = True then
            AccessDenied
        End if
        SQLCommand = "DELETE DirectoryAliases WHERE GID = '" & CleanSQLText(Request.Form("GID")) & "'"
        objConn.Open strConn
        objConn.Execute SQLCommand
        objConn.Close
End Select
%>

Open in new window

0
UName10
Asked:
UName10
  • 10
  • 6
5 Solutions
 
Bhavesh ShahLead AnalysistCommented:
Hi,

I read your Q and what you wanted to do is, you wanted to add sub category for 2 main category.

In your AddAlias.asp, your starting <select is not in given example, so pls update your code.

Did you put multiple attribute in "<Select" ?

You might need to insert based on how many main category are selected.


- Bhavesh
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If I understood well what you want then you need to change the following query in AddAlias.asp:

SQLCommand = "SELECT * FROM Directories WHERE ParentDirectoryGID IS NOT NULL AND GID <> '" & CleanSQLText(Request("PGID")) & "' ORDER BY DirectoryName"

TO:
SQLCommand = "SELECT * FROM Directories WHERE ParentDirectoryGID IS NULL OR (ParentDirectoryGID IS NOT NULL AND GID <> '" & CleanSQLText(Request("PGID")) & "') ORDER BY DirectoryName"
0
 
UName10Author Commented:
Hi thank you for the response,

I tried deleting the ParentDirectoryGID IS NOT NULL as well as your example and whilst it does now show the top-level categories, I'm getting the error and can't save it: An error has occured. The Directory GID is invalid

This error code is generated from i_Alias.asp, the include file with the code below AddAlias.asp.

Could you have a look perhaps at that and see what could be wrong there?  Also, there are other references to the PGID elsewhere in AddAlias.asp, and wonder if the combination are causing the errors.  

Many thanks.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Where I told you to delete the "ParentDirectoryGID IS NOT NULL"?
Please check well the new SQLCommand that I posted. It's to substitute the other one.
0
 
UName10Author Commented:
You didn't tell me to - that's not what I said.

I tried both ways - both deleting altogether AND your suggestion, but neither are working - as per my previous comment.

Please read what I wrote as all the information is there.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The error occurs because the code in
i_Alias.asp

Open in new window

is not prepared to process Parent Directory records (PGID is NULL):
        If strDirectoryGID = "" then 
            strUserError = strUserError & "An error has occured.  The Directory GID is invalid"
        end if

Open in new window


What should be done when these cases happens? Functionally, I mean.
0
 
UName10Author Commented:
Thanks very much - yes we're getting there....

Well initially the directory structure was set out differently and any alias should have had a parent GID.  

It's different now as I want to create subcategory aliases in top-levels (as mentioned obviously), but the error producing code is a remnant of the previous directory structure.  It should be an easy fix, but because of the constraint that you pointed out, we're getting errors when trying to save an alias in a top-level category.

I tried removing the above clause and it just breaks the entire code.

Any idea how to go around it?

There is a field in the Aliases table called 'ShowInCategoryGID', which the GID from adding an alias gets uploaded to somewhere, as it cannot accept nulls.

Hope that makes sense!  Please let me now if you need more, but it was very useful for you to explain why the error is occuring.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
UName10, the error occurs because the record you selected have no value in ParentDirectoryGID column.
Try to make this test:
- In i_Alias.sp delete (or comment) the following code:

         If strDirectoryGID = "" then
            strUserError = strUserError & "An error has occured.  The Directory GID is invalid"
        end if


Check if gives you error or it acts as expected.
0
 
UName10Author Commented:
Hi there, yes there's an error and it's breaking the code - any other ideas?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please post the new error
0
 
UName10Author Commented:
Hi - thanks for the resply and sorry for my late one...

The error coming up is:


Microsoft OLE DB Provider for SQL Server error '80040e2f'

Cannot insert the value NULL into column 'ShowInDirectoryGID', table 'insguides.dbo.DirectoryAliases'; column does not allow nulls. INSERT fails.

/Admin/FranchiseAdmin/DirectorySetup/Aliases/i_Alias.asp, line 61

Any ideas would be appreciated a lot... Getting real urgent now and don't know how to fix it, it just doesn't make sense
0
 
UName10Author Commented:
So basically, i need to stop it from having to insert the 'GID' and allow the 'PGID'?  but I can't find any mentioned of the 'ShowInDirectoryGID' anywhere in the code - it's not in either of the above files.

Shall I just removed the Not Null clause in the database?
0
 
UName10Author Commented:
I've just found this which related to it:

        <tr>
            <th align="left" valign="top">
                Show In Directory:
            </th>
            <td align="left" valign="top">
                <select name="strShowInDirectoryGID">
                    <%
                    SQLCommand = "USP_Helper_GetSelectableDirectoriesForAliases"
                    rsTemp.Open SQLCommand, objConn, adOpenStatic, adLockReadOnly
                    While Not rsTemp.EOF
                        CreateSelectOption rsTemp("GID"), rsTemp("DirectoryName"), ChooseValue(Request.Form("strShowInDirectoryGID"), Request("PGID"))
                        rsTemp.MoveNext
                    Wend
                    rsTemp.Close
                    %>
                </select>
            </td>
        </tr>

Open in new window


What would I need to change here on the PGID side?

This is the 'Master Admin' Alias file, the one before was the Sub-Master - if we can work it out without me having to explain what that means that would be good!
0
 
UName10Author Commented:
This is the USP_Helper_GetSelectableDirectoriesForAliases in the database:  Could you help with what needs modifying?



USE [insguides]
GO

/****** Object:  StoredProcedure [dbo].[USP_Helper_GetSelectableDirectoriesForAliases]    Script Date: 05/10/2011 12:56:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[USP_Helper_GetSelectableDirectoriesForAliases]

AS

	SELECT	GID, DirectoryName 
	FROM	Directories 
	WHERE	ParentDirectoryGID IS NOT NULL
	ORDER BY DirectoryName

GO

Open in new window

0
 
UName10Author Commented:
Fixed!!! thanks for your help - I'll give you the points anyway :)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm sorry UName10. I was out of office these days and couldn't help you more.
I'm glad that your problem it's solved.

Cheers
0
 
UName10Author Commented:
done
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now