...

View Full Version : Trouble with if elseif statements



enfleshjsc
02-08-2007, 09:25 PM
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!!!


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

enfleshjsc
02-09-2007, 04:51 PM
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:

http://www.enflesh.com/IMA.bmp

http://www.enflesh.com/IMV.bmp



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

enfleshjsc
02-09-2007, 06:09 PM
Problem solved. I just needed to reference another field in the database. Code was fine all along otherwise.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum