1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help me rewrite my ASP code to join tables in my DB :)

Discussion in 'C#' started by MidoriWeb, Jan 15, 2007.

  1. #1
    Hey everyone,

    I have some code I need modified and I can't seem to figure it out. Here's the section of code I need to change:

    	SELECT CASE True
    	CASE Current_Category <> "" OR Request.Querystring("Search") <> "" OR Request.Querystring("mfg") <> ""
    		'Display records from the chosen category...
    		CurrentCat = Current_Category
    
    		SQL = "SELECT " & Config_ProductsTable_Fields
    		SQL = SQL & " FROM Products_Joined P " & SQL_LockType("NOLOCK")
    
    		'-------------------------------------------------------------------------
    		If Current_Category <> "" Then
    			If RS_GetCatInfo.EOF Then
    				SQL = SQL & " WHERE 1=2 "
    			Elseif RS_GetCatInfo.Fields("Custom_Where_Clause") <> "" Then
    				SQL = SQL & " WHERE " & AddSQLConstraints()
    				SQL = SQL & " " & RS_GetCatInfo.Fields("Custom_Where_Clause") & " "
    			Elseif RS_GetCatInfo.Fields("CategoryVisible") = "W" Then
    				SQL = SQL & " WHERE " & AddSQLConstraints()
    				'SELECT ALL NEW PRODUCTS ONLY...
    				If (Config_DatabaseServer = "SQL") OR (Config_DatabaseServer = "MySQL") Then
    					SQL = SQL & " DateAdd(DD," & Config_NewGraphicDays & ",P.DisplayBeginDate) > " & FormatDateConstant(LocalDateTime) & " "
    				Else
    					SQL = SQL & " DateAdd('D'," & Config_NewGraphicDays & ",P.DisplayBeginDate) > " & FormatDateConstant(LocalDateTime) & " "
    				End If
    			Else
    				SQL = SQL & " WHERE p.ProductID IN ( "
    				SQL = SQL & " SELECT p2.ProductID FROM Products p2 INNER JOIN Categories_Products_Link cpl ON cpl.ProductID = p2.ProductID WHERE " & Replace(AddSQLConstraints(),"P.","p2.") & " "
    
    	
    				Dim Refinement_CategoryIDs_Array, Refinement_Total_Cats, Refinement_CategoryIDs_CSV
    				Refinement_CategoryIDs_Array = Split(Current_Category & Refinement_CategoryIDs,",")
    				Refinement_Total_Cats = Ubound(Refinement_CategoryIDs_Array)+1
    				Refinement_CategoryIDs_CSV = Current_Category & Refinement_CategoryIDs
    				
    				SQL = SQL & " AND cpl.CategoryID IN (" & Refinement_CategoryIDs_CSV & ")"
    				SQL = SQL & " GROUP BY p2.ProductID "
    				SQL = SQL & " HAVING Count(cpl.CategoryID) = " & Refinement_Total_Cats
    				SQL = SQL & " ) "
    			End If
    		End If
    	
    		'-------------------------------------------------------------------------
    		'If we haven't put the where clause in yet, do it now...
    		If NOT InStr(SQL," WHERE ") > 0 Then
    			SQL = SQL & " WHERE " & AddSQLConstraints()		
    		End If
    		
    		'-------------------------------------------------------------------------			
    		If Request.Querystring("Search") <> "" Then
    			'Split search words into multiple words...
    			SearchText = Trim(Replace(Request.Querystring("Search"), "'", ""))
    			Split_SearchText = Split(SearchText," ")
    			
    			SQL = SQL & " AND "
    			SQL = SQL & "( ("
    			Call GenerateSearchQuery(Split_SearchText, "P.ProductCode")
    			SQL = SQL & ") OR ("
    			Call GenerateSearchQuery(Split_SearchText, "P.ProductName")
    			SQL = SQL & ") OR ("
    			Call GenerateSearchQuery(Split_SearchText, "p.ProductManufacturer")
    			SQL = SQL & ") OR ("
    			Call GenerateSearchQuery(Split_SearchText, "p.ProductKeywords")
    
    			If Request.Querystring("Extensive_Search") = "Y" Then
    				SQL = SQL & ") OR ("
    				Call GenerateSearchQuery(Split_SearchText, "p.ProductDescriptionShort")
    				SQL = SQL & ") OR ("
    				Call GenerateSearchQuery(Split_SearchText, "p.ProductDescription")
    			End If
    
    			SQL = SQL & ") )"
    		End If
    Code (markup):
    This is for a product search on one of my websites. If you see the code above you'll see the following:

    			SQL = SQL & " AND "
    			SQL = SQL & "( ("
    			Call GenerateSearchQuery(Split_SearchText, "P.ProductCode")
    			SQL = SQL & ") OR ("
    			Call GenerateSearchQuery(Split_SearchText, "P.ProductName")
    			SQL = SQL & ") OR ("
    			Call GenerateSearchQuery(Split_SearchText, "p.ProductManufacturer")
    			SQL = SQL & ") OR ("
    			Call GenerateSearchQuery(Split_SearchText, "p.ProductKeywords")
    
    			If Request.Querystring("Extensive_Search") = "Y" Then
    				SQL = SQL & ") OR ("
    				Call GenerateSearchQuery(Split_SearchText, "p.ProductDescriptionShort")
    				SQL = SQL & ") OR ("
    				Call GenerateSearchQuery(Split_SearchText, "p.ProductDescription")
    			End If
    Code (markup):
    Basically... the script is checking the productcode field, productname, productmanufacturer, and productkeywords as part of the search. What I want to do is search the metatag_keywords (not the productkeywords). But here's the problem... metatag_keywords is not in the current table being used for this search already. So I need to open up a new table to search for information out of that table. I'm guessing using SQL Inner Join between 2 tables? The current script is joining 2 tables together using this code:

    				SQL = SQL & " WHERE p.ProductID IN ( "
    				SQL = SQL & " SELECT p2.ProductID FROM Products p2 INNER JOIN Categories_Products_Link cpl ON cpl.ProductID = p2.ProductID WHERE " & Replace(AddSQLConstraints(),"P.","p2.") & " "
    Code (markup):
    Now... what I need to do is something similar but instead of joining the Products table with the Categories_Products_Link table I need to join Products with the Products_Memos table. Inside the Products_Memos table I have a column named METATAG_Keywords that I want to run my search againast.

    So...... :)

    Can someone help me get this working? I've tried modding the code around myself but everything I do seems to cause an error.

    Idealy I could take the code above joining two tables together and joing a thrid table. But if I remember correctly, you can only join 2 tables at a time. What can I do to solve this problem and enable being able to search on another table in my database?
     
    MidoriWeb, Jan 15, 2007 IP
  2. ccoonen

    ccoonen Well-Known Member

    Messages:
    1,606
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    160
    #2
    Please post a location where we could download the ASP and maybe a SQL script to rebuild the Tables with junk data?

    Thanks... this will make it easier to help you out.
     
    ccoonen, Jan 16, 2007 IP
  3. MidoriWeb

    MidoriWeb Member

    Messages:
    62
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #3
    Hi ccoonen,

    Well... I finally got this working... but I have a question. My original code before the change was:

    SQL = SQL & " FROM Products_Joined P " & SQL_LockType("NOLOCK")

    And my new code is:

    SQL = SQL & " FROM Products_Joined P " & SQL_LockType("NOLOCK") & " INNER JOIN Products_Memos pm " & SQL_LockType("NOLOCK") & " ON p.ProductID = pm.ProductID "

    I also found that this works as well:

    SQL = SQL & " FROM Products_Joined P INNER JOIN Products_Memos pm ON p.ProductID = pm.ProductID "

    So my question is... what is SQL_LockType("NOLOCK")? Do I need to use it in my code or not? I found that with or without it everything seems to work like I need it to. I just don't know wether it's a good idea to use it or not.

    Does anyone know?
     
    MidoriWeb, Feb 5, 2007 IP
  4. TasteOfPower

    TasteOfPower Peon

    Messages:
    572
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #4
    please upgrade to .net. thx.
     
    TasteOfPower, Feb 5, 2007 IP
  5. ccoonen

    ccoonen Well-Known Member

    Messages:
    1,606
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    160
    #5
    LOL TasteOfPower: Not everyone has a .NET server available to them, hehe.

    Truthfully MidoriWeb: I've never seen or used SQL_LockType("NOLOCK"). I've locked the tables with SQL while doing an update or insert before but never with an ASP command...
     
    ccoonen, Feb 6, 2007 IP
  6. JenniP

    JenniP Peon

    Messages:
    250
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #6
    I've used NOLOCK quite a bit, although its never by choice as its widely regarded as a bad move.

    Basically it tells SQL Server to ignore any locks on a row and just give you data, its commonly used because its faster than a normal select because SQL Server doesn't have the overhead of checking the row for locks.

    It does have one major drawback however (although this is sometimes exactly why its used), it will see a uncommitted row change and return that data, IE you have started a transaction, updated a row but not yet committed the transaction, then you run a SELECT with NOLOCK you will see that uncommitted changed data where as normally the SELECT would wait until the transaction had been committed.

    There are times of course where you don't really care about the state of the data however using NOLOCK is often the sign of a under maintained database which has deadlock problems, to get around the deadlocks people used NOLOCK to avoid the problem without fixing the underlying issue.

    Jen
     
    JenniP, Feb 21, 2007 IP
  7. MidoriWeb

    MidoriWeb Member

    Messages:
    62
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #7
    Hi Jen,

    Thank you for the wonderful explanation. So the upside is it's one less step that's needed before SQL returns the information to the application (speeds things up). The downside is it will return a row that has not yet committed it's changes.

    So if you're working on a row in a table, and update half of that information but haven't entered the 2nd half yet, and call that information using nolock, it will display only half the information. But, if you call that information using nolock it won't display anything because the data hasn't been committed yet.

    Do I have that right?

    Why then, is this regarded as a bad move? If I don't care about uncommitted data being shown, then this is one less step SQL needs to take and therefor it speeds everything up.
     
    MidoriWeb, Feb 23, 2007 IP
  8. JenniP

    JenniP Peon

    Messages:
    250
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Almost

    If you use NOLOCK you will see any data changed/updated thats in a transaction or generally locked.

    If you dont use NOLOCK if there are any locks on the table it will wait for those locks to be released before reading the completed data.

    Its a bit of an over simplification of it, as there are different levels of locking but its a good rule of thumb.

    Generally you should let SQL Server manage itself, if you have to tell SQL Server to lock a certain way or use specifically a certain index you reduce SQL Servers ability to manage itself.

    Specifically NOLOCK is seen as a bit of a hack as its often used to gloss over serious problems with the database performance that could by solved by proper indexing and tuning or improving the design of the DB.

    There are times when NOLOCK or any other optimization hint is exactly the right thing to do, the trick is choosing those times carefully.

    Jen
     
    JenniP, Feb 23, 2007 IP
  9. MidoriWeb

    MidoriWeb Member

    Messages:
    62
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #9
    Gotcha,

    Thanks again Jen... that all makes sense. 99% of the time our software does not use the nolock command when accessing SQL... so I'm guessing the developers knew what they were doing when they decided to use it on a select few calls to the DB.

    Thanks again :)
     
    MidoriWeb, Feb 23, 2007 IP