Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Mar 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ASP SQL Preference Ordering

    Greetings, I'm having a problem with order preference.

    I know that I may not have the wording correct but this is what I'm trying to do, a manager will have priorities they want to set to a list of items. That manager gets a page with all of the unassigned tasks and then gets arrows to assign the priority with 1 being highest priority. In this situation any item that doesn't have a priority has a null or 0 value.

    Currently I have problem with my code as I can go from 1 to 2 and 2 to 1 and so forth but to go from 0 or unassigned priority to a number (in my case making the unassigned item #1) it doesn't properly renumber all the sub-sequential numbers.

    Code:
    function GoodNum(intParam)				
    	If len(intParam) > 0 and isNumeric(intParam) then
    		GoodNum = intParam
    	else
    		GoodNum = 0
    	end if
    end function
    
    Function BOPriDecrease(id)
    	strSQL = "SELECT bo_priority FROM tblRequestInfo WHERE id = "&id
    	rsFunctionUse.open strSQL, objConnection, adCmdText
    	If Not rsFunctionUse.EOF then
    		sTempValue = GoodNum(rsFunctionUse("bo_priority"))
    	end if
    	rsFunctionUse.close
    	
    	sTempValue = Cstr(Cint(sTempValue) + 1)
    	strSQL2 = ""
    	
    '	Response.Write("<P>Temp Value for "&id&": " & sTempValue)
    	
    	strSQL1 = "SELECT bo_priority, id FROM tblRequestInfo WHERE departmentid = 13 AND (programmerid is null OR programmerid < 1) AND (statusid = 1 or statusid = 3) AND bo_priority = '" & sTempValue & "'"
    	
    '	response.write("<P>"&strSQL1)
    '	Response.End()
    	
    	rsFunctionUse1.open strSQL1, objConnection, adCmdText
    	If Not rsFunctionUse1.EOF then
    		sTempValue1 = GoodNum(rsFunctionUse1("bo_priority") - 1) 
    		strSQL2 = "UPDATE tblRequestInfo SET bo_priority = " & sTempValue1 & " WHERE id = " & rsFunctionUse1("id")
    		response.write("<P>" & strSQL2)
    		rsFunctionUse.open strSQL2, objConnection, adCmdText
    	end if
    		rsFunctionUse1.close		
    	
    	strSQL = "UPDATE tblRequestInfo SET bo_priority = "&sTempValue&" WHERE id = "&id
    	response.write("<P>" & strSQL)
    	rsFunctionUse.open strSQL, objConnection, adCmdText
    	'response.End()
    End Function
    
    Function BOPriIncrease(id)
    	strSQL = "SELECT bo_priority FROM tblRequestInfo WHERE id = "&id
    	rsFunctionUse.open strSQL, objConnection, adCmdText
    	If Not rsFunctionUse.EOF then
    		sTempValue = GoodNum(rsFunctionUse("bo_priority"))
    	end if
    	rsFunctionUse.close
    
    	response.Write(sTempValue)
    
    	if sTempValue <> 0 then		
    		sTempValue = Cstr(Cint(sTempValue) - 1)
    	else
    		if sTempValue = 0 then	
    			sTempValue = "1"
    		end if
    	end if
    
    	strSQL2 = ""
    	 
    	if sTempValue > 0 then
    		Response.Write("<P>Temp Value for "&id&": " & sTempValue)
    		strSQL1 = "SELECT bo_priority, id FROM tblRequestInfo WHERE departmentid = 13 AND (programmerid is null OR programmerid < 1) AND (statusid = 1 or statusid = 3) AND bo_priority = '" & sTempValue & "'"
    		response.write("<P>"&strSQL1)
    
    		rsFunctionUse1.open strSQL1, objConnection, adCmdText
    		If Not rsFunctionUse1.EOF then
    			sTempValue1 = GoodNum(rsFunctionUse1("bo_priority") + 1)
    			
    			Response.Write("<P>Temp Value for "&id&": " & sTempValue1)
    			
    			strSQL3 = "SELECT bo_priority, id FROM tblRequestInfo WHERE departmentid = 13 AND (programmerid is null OR programmerid < 1) AND (statusid = 1 or statusid = 3) AND bo_priority = '" & sTempValue1 & "'"
    			
    			rsFunctionUse2.open strSQL3, objConnection, adCmdText
    			
    			If Not rsFunctionUse2.EOF then
    				sTempValue2 = GoodNum(rsFunctionUse2("bo_priority") + 1)
    				
    				strSQL2 = "UPDATE tblRequestInfo SET bo_priority = " & sTempValue2 & " WHERE id = " & rsFunctionUse2("id")
    				
    				response.write("<P>" & strSQL2)
    				
    				rsFunctionUse.open strSQL2, objConnection, adCmdText
    			end if
    			
    			strSQL2 = "UPDATE tblRequestInfo SET bo_priority = " & sTempValue1 & " WHERE id = " & rsFunctionUse1("id")
    			response.write("<P>" & strSQL2)
    			rsFunctionUse.open strSQL2, objConnection, adCmdText
    		end if
    		rsFunctionUse1.close	
    	end if
    	
    	if sTempValue > 0 then	
    		strSQL = "UPDATE tblRequestInfo SET bo_priority = "&sTempValue&" WHERE id = "&id
    	else
    		strSQL = "UPDATE tblRequestInfo SET bo_priority = 1 WHERE id = "&id
    	end if
    	response.write("<p>" & strSQL)
    	response.write("<p>")
    	'Response.End()
    	rsFunctionUse.open strSQL, objConnection, adCmdText
    End Function

  • #2
    New to the CF scene
    Join Date
    Mar 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I seem to have got the increase function working now but when I try to reverse the math for decrease it doesn't work yet.

    Code:
    Function BOPriIncrease(id)
    	strSQL = "SELECT bo_priority FROM tblRequestInfo WHERE id = "&id
    	rsFunctionUse.open strSQL, objConnection, adCmdText
    	If Not rsFunctionUse.EOF then
    		nTempValue = GoodNum(rsFunctionUse("bo_priority"))
    	end if
    	rsFunctionUse.close
    	if nTempValue <> 0 then		
    		nTempValue = Cstr(Cint(nTempValue) - 1)
    	else
    		if nTempValue = 0 then	
    			nTempValue = "1"
    		end if
    	end if
    	strSQL2 = ""	 
    	if nTempValue > 0 then
    		strSQL1 = "SELECT count(*) as totalrun FROM tblRequestInfo WHERE departmentid = 13 AND (programmerid is null OR programmerid < 1) AND (statusid = 1 or statusid = 3) AND bo_priority >= '" & nTempValue & "'"
    		rsFunctionUse1.open strSQL1, objConnection, adCmdText
    		If Not rsFunctionUse1.EOF then
    		nTotalRun = GoodNum(rsFunctionUse1("totalrun") + 1)
    		end if
    		Dim my_num
    		my_num=1
    		Do Until my_num > nTotalRun
    			strSQL3 = "SELECT bo_priority, id FROM tblRequestInfo WHERE departmentid = 13 AND (programmerid is null OR programmerid < 1) AND (statusid = 1 or statusid = 3) AND bo_priority = '" & nTempValue2 & "'"
    			rsFunctionUse2.open strSQL3, objConnection, adCmdText
    			If Not rsFunctionUse2.EOF then
    				nTempValue2 = GoodNum(rsFunctionUse2("bo_priority") + 1)
    				strSQL2 = "UPDATE tblRequestInfo SET bo_priority = " & nTempValue2 & " WHERE id = " & rsFunctionUse2("id")
    				rsFunctionUse.open strSQL2, objConnection, adCmdText		
    			end if
    			rsFunctionUse2.close
    			my_num = my_num +1
    		Loop
    	end if
    	if nTempValue > 0 then	
    		strSQL = "UPDATE tblRequestInfo SET bo_priority = "&nTempValue&" WHERE id = "&id
    	else
    		strSQL = "UPDATE tblRequestInfo SET bo_priority = 1 WHERE id = "&id
    	end if
    	rsFunctionUse.open strSQL, objConnection, adCmdText
    	'rsFunctionUse.Close
    End Function

  • #3
    New to the CF scene
    Join Date
    Mar 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I thought I had it but still having some issues.
    Last edited by SarkanyVillam; 03-27-2012 at 05:50 PM. Reason: Was wrong :(

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,084
    Thanks
    80
    Thanked 4,552 Times in 4,516 Posts
    Could you please just explain what you are trying to do instead of showing code that probably isn't correct? Maybe show a screenshot of the page? Or give us the URL of a live page to look at?

    And you have no code in there at all to account for null values.

    And you don't say what database you are using. Yes, it can make a BIG difference.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Mar 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The sole purpose is to priotize tasks for users to work on.

    This is running on Windows 2003 but I have been told not to upgrade the code to aspx
    This uses Microsoft SQL 2000

    Attached is a screen shot of one of the pages, unfortunately this is an intranet site so sending the URL wouldn't help.
    Attached Thumbnails Attached Thumbnails ASP SQL Preference Ordering-capture.png  

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,084
    Thanks
    80
    Thanked 4,552 Times in 4,516 Posts
    Okay, so how is it supposed to work?

    I see up and down arrows in both columns.

    And what is the meaning of the 99999 numbers? And the up arrow beside each of them?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New to the CF scene
    Join Date
    Mar 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Poor choice of color, the up and down arrows on top part to sort the column.

    The up arrow next to the nines is to change it to priority 1, once it has a number other than all nine it will have an up and down arrow to change the priority.

    I inherited this project and it has have a lots of poor code and poor color choices

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,084
    Thanks
    80
    Thanked 4,552 Times in 4,516 Posts
    Okay, so the user can change the priority of several items (rows in that display) and then hit s SUBMIT button that goes out to the DB and changes the priorities in the DB to match, is that the idea?

    I'm assuming that it uses JavaScript to change the priority numbers?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •