Multiple "If Then Else" in "Do While Not" loop not working, but they do singularly

Question

OK so here is what's pulling my hair out ! I have a page which reads records from a table in one DB, manipulates the data slightly, then writes it to another DB.

In the Do While Not EOF loop, I have a number of If Then Else statements to manipulate the data.

If I run the code in full, most of the If Then Else do not work, if I run the page with just each one in there, they work fine.

I have spent 8 hrs+ working on this and am still baffled

Dim rsndb, fieldstr1, fieldstr2, fieldstr3, fieldstr4, fieldstr5, fieldstr6, fieldstr7, fieldstr8, fieldstr9, fieldstr10, fieldstr11, fieldstr12, fieldstr13, fieldstr14, fieldstr15
recordstr = 0

'##### Empty temp table #####
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "DSN=website"
cSql = "DELETE FROM newtandltest"
Connection.Execute (cSql)
Connection.Close
Set Connection = Nothing

' Select Records From House Database
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DSN=newdatabase;UID=Intranet;Pwd=password;"
Set rsndb = Server.CreateObject("ADODB.Recordset")

strSQL="Select top 500 VendorTransaction.VendorNo As propvendor, Resort.ResortCode As rid, Resort.ResortName As resname, Locations.LocationName As proploc, Country.CountryCode As propcountry, Season.SeasonDesc As propsea, Property.SeasonDesc As propextrasea, Property.Size As psize, Property.Occupancy As occ, FloatingPoints.FloatingPointsName As TAOLF, Property.StartWeek As propsw, Property.EndWeek As propew, Property.Weeks As propweeks, Property.Points As proppoints, Property.WebPricePW As webprice, Property.Priority As priority, Property.OpenToOffer As offers, Property.PoAOffer As poa, Property.RentalPW As rentalpw, Property.WebDate As webdate"

strSQL = strSQL & " From Property Inner Join Resort On Property.ResortId = Resort.Id Inner Join Country On Resort.CountryId = Country.Id Inner Join Locations On Resort.LocationId = Locations.Id Inner Join FloatingPoints On Property.FloatPointId = FloatingPoints.Id Inner Join PropertyStatus On Property.StatusId = PropertyStatus.Id Inner Join PropertyType On Property.TypeId = PropertyType.Id Inner Join Season On Property.SeasonId = Season.Id Inner Join VendorTransaction On VendorTransaction.propertyId = Property.Id Where (Property.WebDate >= DateAdd(day, -187, GetDate()) And Property.StatusId = 4 and year(Property.WebDate) <> 9999)"

rsndb.Open strSQL, adoCon
Set adoCon1 = Server.CreateObject("ADODB.Connection")
adoCon1.Open "DSN=website"

Do While not rsndb.EOF
    '------------------------------------------------------------------------------------------------------------------
    fieldstr1 = rsndb("webdate")
    '------------------------------------------------------------------------------------------------------------------
    fieldstr2 = rsndb("propvendor")
    '------------------------------------------------------------------------------------------------------------------
    fieldstr3 = rsndb("propcountry")
    If rsndb("proploc") = "FLOR" Then
        fieldstr3 = rsndb("proploc")
    End If
    If rsndb("proploc") = "MADE" Then
        fieldstr3 = rsndb("proploc")
    End If
    If rsndb("propcountry") = "ESC" Then
        fieldstr3 = rsndb("propcountry") & left(rsndb("proploc"),1)
    End If
    '------------------------------------------------------------------------------------------------------------------
    resnamestr = replace(rsndb("resname"),chr(13)," ")
    resnamestr = replace(rsndb("resname"),chr(34),"")
    resnamestr = replace(rsndb("resname"),"'","")
    fieldstr4 = resnamestr
    '------------------------------------------------------------------------------------------------------------------
    fieldstr5 = rsndb("propsea")
    If len(rsndb("propextrasea")) > 3 Then
        fieldstr5 = rsndb("propextrasea")
    End If
    If rsndb("propcountry") = "POIN" Then
        fieldstr5 = ""
    End If
    '------------------------------------------------------------------------------------------------------------------
    fieldstr6 = ""
    '------------------------------------------------------------------------------------------------------------------
    fieldstr7 = rsndb("propweeks")
    If rsndb("propcountry") = "POIN" Then
        fieldstr7 = rsndb("proppoints")
    End If
    '------------------------------------------------------------------------------------------------------------------
    If rsndb("propsw") = rsndb("propew") Then
        fieldstr8 = rsndb("propsw")
    End If
    If rsndb("propcountry") = "POIN" Then
        fieldstr8 = "POINTS"
        fieldstr9 = "n/a"
    End If
    If rsndb("propsw") < rsndb("propew") Then
        fieldstr8 = rsndb("propsw") & "/" & rsndb("propew")
    End If
    If rsndb("TAOLF") = "F" Then
        fieldstr8 = "FLOATING"
    End If
    '------------------------------------------------------------------------------------------------------------------
    tempsize = rsndb("psize")
    Select Case tempsize
        Case 1
            fieldstr9 = "Studio" & " - " & rsndb("occ")
        Case 2
            fieldstr9 = "1 Bedroom" & " - " & rsndb("occ")
        Case 3
            fieldstr9 = "2 Bedroom" & " - " & rsndb("occ")
        Case 4
            fieldstr9 = "3 Bedroom" & " - " & rsndb("occ")
        Case 5
            fieldstr9 = "4 Bedroom" & " - " & rsndb("occ")
    End Select
    '------------------------------------------------------------------------------------------------------------------
    fieldstr10 = rsndb("rid")
    '------------------------------------------------------------------------------------------------------------------
    fieldstr11 = rsndb("rid")
    '------------------------------------------------------------------------------------------------------------------
    fieldstr12 = ""
    '------------------------------------------------------------------------------------------------------------------
    fieldstr13 = "N"
    '------------------------------------------------------------------------------------------------------------------
    fieldstr14 = rsndb("webprice")
    If rsndb("offers") = 1 Then
        fieldstr14 = "Offers"
    End If
    If rsndb("poa") = 1 Then
        fieldstr14 = "POA"
    End If
    '------------------------------------------------------------------------------------------------------------------
    fieldstr15 = rsndb("rentalpw")
    lCount = lCount + 1
    Set Connection1 = Server.CreateObject("ADODB.Connection")
    Connection1.Open "DSN=website"
    cSql = "INSERT INTO newtandltest(datefield, field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, price, bargainprice)"
    cSql = cSql & "VALUES('"&fieldstr1&"','"&fieldstr2&"','"&fieldstr3&"','"&fieldstr4&"','"&fieldstr5&"','"&fieldstr6&"','"&fieldstr7&"','"&fieldstr8&"','"&fieldstr9&"','"&fieldstr10&"','"&fieldstr11&"','"&fieldstr12&"','"&fieldstr13&"','"&fieldstr14&"','"&fieldstr15&"');"
    Connection1.Execute (cSql)
    Connection1.Close
    Set Connection1 = Nothing
    rsndb.MoveNext
Loop
rsndb.Close
adoCon.Close
Set rsndb = Nothing
Set adoCon = Nothing

Answer 1

Something u can improve in your code.

1) You have open connection before while loop so you do not need to open it again in While loop it should be remove 

Set Connection1 = Server.CreateObject("ADODB.Connection")
    Connection1.Open "DSN=website
While loop ...
WEND
Connection1.Close
    Set Connection1 = Nothing

2) check Null and blank before replacing with some value.

for e.g 
if(fld("resname") <> "" AND NOT ISNULL(fld("resname")))
replace(fld("resname"), chr(13), " ")
end if

- Note track loop with default value like response.write ("hello") and check in which row loop is breaking and by which cause

Answer 2

I am with Shadow Wizard. Tidy up your code, only use rsndb once for every key. And use a dictionary to store your fields with reasonable names. As it is now, it is not maintainable: How should someone know what fieldStr12 stands for?

There is one real bug I spotted: You do three times a replace on the original string, so effectively only the last replace will be visible.

Suggestion how to tidy your code:

Do While not rsndb.EOF

    Set fld = createObject("Scripting.Dictionary")
    fld.Add "webdate",      rsndb("webdate")
    fld.Add "propvendor",   rsndb("propvendor")
    fld.Add "propcountry",  rsndb("propcountry")

    ' etc...

    Select Case fld("proploc")
        Case "FLOR"     fld("propcountry") = fld("proploc")
        Case "MADE"     fld("propcountry") = fld("proploc")
    End case

    If fld("propcountry") = "ESC" Then 
        fld("propcountry") = fld("propcountry") & left(fld("proploc"),1)
    End If

    ' This part is not correct, it is only replacing the ' by an empty string
     'resnamestr = replace(rsndb("resname"),chr(13)," ")
     'resnamestr = replace(rsndb("resname"),chr(34),"")
     'resnamestr = replace(rsndb("resname"),"'","")
     'fieldstr4 = resnamestr

    fld("resname") = replace(fld("resname"), chr(13), " ")
    fld("resname") = replace(fld("resname"), chr(34), "")
    fld("resname") = replace(fld("resname"), "'", "")

    ' etc...

    Set Connection1 = Server.CreateObject("ADODB.Connection")
    Connection1.Open "DSN=website"
    cSql = "INSERT INTO newtandltest(datefield, field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, price, bargainprice)"

    ' the Items property of a dictionary is an array in the same order that you add them to the dictionary
    ' You could do the same with the Keys property.
    cSql = cSql & "VALUES('" & join(fld.Items, "','") & "');"
    Connection1.Execute (cSql)
    Connection1.Close
    Set Connection1 = Nothing
    rsndb.MoveNext
Loop

Answer 3

The only thing that I can see that might cause this is reading the same field more than once; with certain types of fields (e.g. Memo field of Access) it might become blank after the first read.

To fix such problem you need to read each field once, and store it into local variable:

Do While not rsndb.EOF
    curPropLoc = rsndb("proploc")
    curPropCountry = rsndb("propcountry")
    '...
    fieldstr3 = curPropCountry
    If curPropLoc = "FLOR" Then
        fieldstr3 = curPropLoc
    End If
    If curPropLoc = "MADE" Then
        fieldstr3 = curPropLoc
    End If
    '...keep using curPropLoc instead of rsndb("proploc") throughout the loop...
    '...keep using curPropCountry instead of rsndb("propcountry") throughout the loop...
    '......
    rsndb.MoveNext
Loop