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 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2006
    Location
    Texas
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Trouble with if elseif statements

    A little background. There are 3 tables I'm pulling data from. The first is where I got "XPARTNUMBER", and that particular variable is not giving me any trouble. The other 2 are IMA and IMV.

    XPARTNUMBER is going to be equal to IMV_ItemID in the table IMV. It is the primary part number. There is a secondary part number associated with some of the IMV_ItemID #s which is IMV_MfgItemID. IMA_ItemID can be equal to IMV_ItemID OR IMV_MfgItemID.

    What I'm trying to do is populate a table so that it goes through the following steps:

    1) if IMA_ItemID is equal to XPARTNUMBER (or IMV_ItemID since they will always be the same number) and IMA_OnHandQty is greater than zero, then values for will be pulled that satisfy that criteria to populate the table.
    2) if IMA_ItemID is equal to IMV_MfgItemID and...
    a) IMA_OnHandQty is greater than zero, then values for will be pulled that satisfy that criteria to populate the table.
    b) IMA_OnHandQty is equal to zero, then values for will be pulled that satisfy that criteria to populate the table.
    3) if IMA_ItemID is equal to XPARTNUMBER (or IMV_ItemID since they will always be the same number) and IMA_OnHandQty is equal to zero, then values for will be pulled that satisfy that criteria to populate the table.

    Below is the snippet of code that is giving me problems. I know that Step 1 is executing correctly. Step 2 is not, and I'm not sure about Step 3.

    Any help would be greatly appreciated!!!

    Code:
    Set conn2 = Server.CreateObject("ADODB.Connection")
    		conn2.Open "VA-14","sa",""
    		set rs3 = Server.CreateObject("ADODB.Recordset")
    		sql="SELECT IMV_MfgItemID AS Mfg FROM dbo.IMV WHERE (IMV_ItemID = N'"&rs.Fields.Item("XPARTNUMBER")&"')"
    		rs3.Open sql, conn2
    		'// First set everything to  not found
    		I14(1)="Not Found"
    		I14(2)="Not Found"
    		I14(3)="Not Found"
    		I14(4)="Not Found"
    		if rs3.EOF then
    			rs3.Close
    			conn2.Close	
    		else
    			rs3.Close
    			sql="SELECT IMA_ItemID AS AItem FROM dbo.IMA WHERE (IMA_ItemID = N'"&rs.Fields.Item("XPARTNUMBER")&"') OR (IMA_ItemID = N'"&rs3.Fields.Item("Mfg")&"')"
    			rs3.Open sql, conn2
    			if rs3.Fields.Item("AItem") = rs.Fields.Item("XPARTNUMBER") AND rs3.Fields.Item("QTY") > 0 then
    				rs3.Close
    				sql="SELECT IMA_OnHandQty AS QTY2, IMA_PrimaryLocation AS PriLoc2, IMA_LastCostAmt AS Cost2 FROM dbo.IMA WHERE IMA_ItemID = N'"&rs.Fields.Item("XPARTNUMBER")&"' AND IMA_OnHandQty>0"
    				rs3.Open sql, conn2
    				I14(1)="Not Needed"
    			elseif rs3.Fields.Item("AItem") = rs3.Fields.Item("Mfg") then
    				if rs3.Fields.Item("QTY") > 0 then
    					rs3.Close
    					sql="SELECT IMA_OnHandQty as QTY2, IMA_PrimaryLocation AS PriLoc2, IMA_LastCostAmt AS Cost2 FROM dbo.IMA WHERE IMA_ItemID = N'"&rs3.Fields.Item("Mfg")&"' AND IMA_OnHandQty>0"
    					rs3.Open sql, conn2
    					I14(1)=rs3.Fields.Item("Mfg")
    				else
    					rs3.Close
    					sql="SELECT IMA_OnHandQty as QTY2, IMA_PrimaryLocation AS PriLoc2, IMA_LastCostAmt AS Cost2 FROM dbo.IMA WHERE IMA_ItemID = N'"&rs3.Fields.Item("Mfg")&"' AND IMA_OnHandQty=0"
    					rs3.Open sql, conn2
    					I14(1)=rs3.Fields.Item("Mfg")				
    				end if
    			else
    				rs3.Close
    				sql="SELECT IMA_OnHandQty as QTY2, IMA_PrimaryLocation AS PriLoc2, IMA_LastCostAmt AS Cost2 FROM dbo.IMA WHERE IMA_ItemID = N'"&rs.Fields.Item("XPARTNUMBER")&"' AND IMA_OnHandQty=0"
    				rs3.Open sql, conn2				
    			end if
    		end if
    		I14(2)=rs3.Fields.Item("QTY2")
    		I14(3)=rs3.Fields.Item("PriLoc2")
    		I14(4)=rs3.Fields.Item("Cost2")
    		rs3.Close	
    		conn2.close
    Don't let others drive you insane when it's within walking distance.

  • #2
    New Coder
    Join Date
    Aug 2006
    Location
    Texas
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update

    I have revised my code as follows, but am now having trouble with recordset values in the first elseif statement. When I plug in rs6.Fields.Item("AItem2") for "s2" it reverts back to "Not Found". When "s2" is left, "s2" is what is written in the table. Any ideas why this could be happening?

    Here are some screen captures I got when I queried each database for the values that SHOULD be populated in one of the table rows:





    Code:
    		Set conn2 = Server.CreateObject("ADODB.Connection")
    		conn2.Open "VA-14","sa",""
    		set rs3 = Server.CreateObject("ADODB.Recordset")
    		sql5="SELECT IMV_ItemID AS VItem, IMV_MfgItemID AS Mfg FROM dbo.IMV WHERE IMV_ItemID = N'"&rs.Fields.Item("XPARTNUMBER")&"'"
    		rs3.Open sql5, conn2
    		
    		Set conn3 = Server.CreateObject("ADODB.Connection")
    		conn3.Open "VA-14","sa",""
    		set rs4 = Server.CreateObject("ADODB.Recordset")		
    		sql6="SELECT IMA_ItemID AS AItem, IMA_OnHandQty AS QTY FROM dbo.IMA WHERE IMA_ItemID = N'"&rs3.Fields.Item("VItem")&"'"
    		rs4.Open sql6, conn3
    		
    		Set conn4 = Server.CreateObject("ADODB.Connection")
    		conn4.Open "VA-14","sa",""		
    		set rs5 = Server.CreateObject("ADODB.Recordset")
    		sql7="SELECT IMA_ItemID AS AItem, IMA_OnHandQty AS QTY FROM dbo.IMA WHERE IMA_ItemID = N'"&rs3.Fields.Item("Mfg")&"'"
    		rs5.Open sql7, conn4
    		
    		'// First set everything to  not found
    		I14(1)="Not Found"
    		I14(2)="Not Found"
    		I14(3)="Not Found"
    		I14(4)="Not Found"
    		if rs3.EOF then
    			rs3.Close			
    			conn2.Close			
    		else
    			if rs4.EOF AND rs5.EOF then
    				rs4.Close
    				conn3.Close
    				rs5.Close
    				conn4.Close
    			else
    				if rs4.Fields.Item("AItem") = rs3.Fields.Item("VItem") AND rs4.Fields.Item("QTY") > 0 then
    					Set conn5 = Server.CreateObject("ADODB.Connection")
    					conn5.Open "VA-14","sa",""		
    					set rs6 = Server.CreateObject("ADODB.Recordset")
    					sql="SELECT IMA_ItemID AS AItem2, IMA_OnHandQty AS QTY2, IMA_PrimaryLocation AS PriLoc2, IMA_LastCostAmt AS Cost2 FROM dbo.IMA WHERE IMA_ItemID = N'"&rs3.Fields.Item("VItem")&"' AND IMA_OnHandQty>0"
    					rs6.Open sql, conn5
    					I14(1)=""
    					I14(2)=rs6.Fields.Item("QTY2")
    					I14(3)=rs6.Fields.Item("PriLoc2")
    					I14(4)=rs6.Fields.Item("Cost2")
    				elseif rs5.Fields.Item("AItem") = rs3.Fields.Item("Mfg") AND rs5.Fields.Item("QTY") > 0 then
    					Set conn5 = Server.CreateObject("ADODB.Connection")
    					conn5.Open "VA-14","sa",""		
    					set rs6 = Server.CreateObject("ADODB.Recordset")
    					sql="SELECT IMA_ItemID AS AItem2, IMA_OnHandQty AS QTY2, IMA_PrimaryLocation AS PriLoc2, IMA_LastCostAmt AS Cost2 FROM dbo.IMA WHERE IMA_ItemID = N'"&rs3.Fields.Item("Mfg")&"' AND IMA_OnHandQty>0"
    					rs6.Open sql, conn5
    					I14(1)="s2"
    					I14(2)="s2"
    					I14(3)="s2"
    					I14(4)="s2"
    				elseif rs5.Fields.Item("AItem") = rs3.Fields.Item("Mfg") AND rs5.Fields.Item("QTY") = 0 then
    					Set conn5 = Server.CreateObject("ADODB.Connection")
    					conn5.Open "VA-14","sa",""		
    					set rs6 = Server.CreateObject("ADODB.Recordset")
    					sql="SELECT IMA_ItemID AS AItem2, IMA_OnHandQty AS QTY2, IMA_PrimaryLocation AS PriLoc2, IMA_LastCostAmt AS Cost2 FROM dbo.IMA WHERE IMA_ItemID = N'"&rs3.Fields.Item("Mfg")&"' AND IMA_OnHandQty=0"
    					rs6.Open sql, conn5
    					I14(1)=rs6.Fields.Item("AItem2")
    					I14(2)=rs6.Fields.Item("QTY2")
    					I14(3)=rs6.Fields.Item("PriLoc2")
    					I14(4)=rs6.Fields.Item("Cost2")
    				else
    					Set conn5 = Server.CreateObject("ADODB.Connection")
    					conn5.Open "VA-14","sa",""		
    					set rs6 = Server.CreateObject("ADODB.Recordset")
    					sql="SELECT IMA_ItemID AS AItem2, IMA_OnHandQty AS QTY2, IMA_PrimaryLocation AS PriLoc2, IMA_LastCostAmt AS Cost2 FROM dbo.IMA WHERE IMA_ItemID = N'"&rs3.Fields.Item("VItem")&"' AND IMA_OnHandQty=0"
    					rs6.Open sql, conn5
    					I14(1)=rs6.Fields.Item("AItem2")
    					I14(2)=rs6.Fields.Item("QTY2")
    					I14(3)=rs6.Fields.Item("PriLoc2")
    					I14(4)=rs6.Fields.Item("Cost2")
    				end if
    			end if
    		end if
    Last edited by enfleshjsc; 02-09-2007 at 06:57 PM.
    Don't let others drive you insane when it's within walking distance.

  • #3
    New Coder
    Join Date
    Aug 2006
    Location
    Texas
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Problem solved. I just needed to reference another field in the database. Code was fine all along otherwise.
    Don't let others drive you insane when it's within walking distance.


  •  

    Posting Permissions

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