|
rrnml -> RE: concat (9/21/2006 14:51:28)
|
Looks like it is coming from the source. When looking at all the other fields I am using, if there is a space, then it puts a CR in there. For example, say the field is city and in this field is the city Santa Fe, it will display like this....
Santa
Fe
Weird but not a big deal. I just won't put dashes in there. The rest looks fine. However, I do of course have another problem. It's a little complicated to explain so I'll give you the code and the error I'm getting and let you take a look. Then I can explain the best I can from there. Thanks.
Here's the code...
<%
Dim con
Dim rst
Dim rst2
Dim strSQL
Dim strSQL2
Dim strParm4
Dim strParm5
Dim strParm99
Dim strITVM
Dim savRep
Dim savNam
Dim savAddr
Dim savCity
Dim savRank
Dim savWeek
Dim savDay
Dim savStop
Dim savCont
Dim savPhon
Const adOpenKeyset = 0
Const adLockOptimistic = 1
'Create the objects
Set con = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")
Set rst2 = Server.CreateObject("ADODB.Recordset")
strParm4 = Request.Form("sclsr")
strParm5 = Mid(strParm4,1,3)
' Open the connection to the AS400 Database
con.Open "pri", "user", "user1"
con.CommandTimeout = 0
' Create the SQL Query String
If Request.Form("sclsr") = "" Then
' 02/19/2003 - Security Agents are assigned LSR Numbers from 700-720.
' Do not allow show the Agents sign-ons.
strSQL = " select nmpdta.arrtlml.rmrtno, nmpdta.arrtlml.rmrtnm, nmpdta.arrtlml.rmdlad, nmpdta.seapldl1.adrslt,nmpdta.arrtlml.rmrpno, " & _
" nmpdta.arrtlml.rmdlct, nmpdta.arrtlml.rmctnm, nmpdta.arrtlml.rmvsin, nmpdta.arrtlml.rmstno, nmpdta.arrtlml.rmdldy, " & _
" nmpdta.arrtlml.rmdlp1, nmpdta.arrtlml.rmdlp2, nmpdta.arrtlml.rmdlp3 " & _
" from nmpdta.arrtlml Left Join nmpdta.seapldl1 on (rmrtno = adrtno) and (adclr# = 999) " & _
" where (rmrpno) not between 700 and 720) " & _
" and rmlcst = 'A' " & _
"Order by rmrpno, rmrtno"
Else
strSQL = " select nmpdta.arrtlml.rmrtno, nmpdta.arrtlml.rmrtnm, nmpdta.arrtlml.rmdlad, nmpdta.seapldl1.adrslt,nmpdta.arrtlml.rmrpno, " & _
" nmpdta.arrtlml.rmdlct, nmpdta.arrtlml.rmctnm, nmpdta.arrtlml.rmstno, nmpdta.arrtlml.rmvsin, nmpdta.arrtlml.rmdldy, " & _
" nmpdta.arrtlml.rmdlp1, nmpdta.arrtlml.rmdlp2, nmpdta.arrtlml.rmdlp3 " & _
" from nmpdta.arrtlml Left Join nmpdta.seapldl1 on (rmrtno = adrtno) and (adclr# = 999) " & _
" where (nmpdta.arrtlml.rmrpno = " & strParm5 & ") " &_
" and rmlcst = 'A' " & _
"Order by rmrpno, rmrtno"
End If
'Response.write strSql
server.ScriptTimeOut = 600
rst.Open strSQL, con, adOpenKeyset, adLockOptimistic
' Only display records if there are records in the recordset
If NOT rst.EOF then
'Display the header
%>
<p align="center"><strong>LSR Contacts</strong></p>
<p align="center">LSR Number & Name</p>
<p align="center"><%=Request.Form("sclsr")%> </p>
<div align="center" style="width: 910; height: 232"><div align="center"><center>
<table BORDER="1" height="181" width="922">
<tr>
<td align="center" height="51" width="54"><b>LSR</b></td>
<td align="center" height="51" width="55"><b>Retailer </b></td>
<td align="center" height="51" width="157"><b>Name</b></td>
<td align="center" height="51" width="112"><b>Address</b></td>
<td align="center" height="51" width="30"><b>City</b></td>
<td align="center" height="51" width="73"><b>ITVM</b></td>
<td align="center" height="51" width="47"><b>I R</b></td>
<td align="center" height="51" width="43"><b>Week</b></td>
<td align="center" height="51" width="30"><b>Day </b></td>
<td align="center" height="51" width="36"><b>Stop</b></td>
<td align="center" height="51" width="63"><b>Contact</b></td>
<td align="center" height="51" width="146"><b>Phone #</b></td>
</tr>
<center>
<%
'Loop through the recordset until there are no more records
savRtl = CDbl(rst("rmrtno"))
savRep = CDbl(rst("rmrpno"))
savNam = rst("rmrtnm")
savAddr = rst("rmdlad")
savCity = rst("rmdlct")
savRank = rst("adrslt")
savWeek = rst("rmvsin")
savDay = rst("rmdldy")
savStop = rst("rmstno")
savCont = rst("rmctnm")
savPhon = rst("rmdlp1") & rst("rmdlp2") & rst("rmdlp3")
Do While Not rst.EOF
strParm99 = CDbl(rst("rmrtno"))
strSQL2 = "select adrslt " & _
" from seapldl1 " & _
" where (adrtno = " & strParm99 & ") " & _
" and (adapty = 'I') and (adclr# = 62) "
If rst2("adrslt") = " " or "NO" Then
strITVM = "N"
Else
strITVM = "Y"
End If
rst2.Open strSQL2, con, adOpenKeyset, adLockOptimistic
%>
<tr>
<td width="54" height="29"><%=(savRep) %></td>
<td width="55" height="29"><%=(savRtl)%></td>
<td align="center" height="17" width="157"><%=(savNam) %></td>
<td align="center" height="17" width="112"><%=(savAddr) %></td>
<td align="center" height="17" width="30"><%=(savCity) %></td>
<td align="center" height="17" width="73"><%=(strITVM) %></td>
<td align="center" height="17" width="47"><%=(savRank) %></td>
<td align="center" height="17" width="43"><%=(savWeek) %></td>
<td align="center" height="17" width="30"><%=(savDay) %></td>
<td align="center" height="17" width="36"><%=(savStop) %></td>
<td align="center" height="17" width="63"><%=(savCont) %></td>
<td align="center" height="17" width="146"><%=(savPhon) %></td>
</tr>
<%
savRtl = CDbl(rst("rmrtno"))
savRep = CDbl(rst("rmrpno"))
savNam = rst("rmrtnm")
savAddr = rst("rmdlad")
savCity = rst("rmdlct")
savRank = rst("adrslt")
savWeek = rst("rmvsin")
savDay = rst("rmdldy")
savStop = rst("rmstno")
savCont = rst("rmctnm")
savPhon = rst("rmdlp1") & rst("rmdlp2") & rst("rmdlp3")
'If you forget this next statement, you will be stuck in a loop
rst.MoveNext
Loop
Else
%>
<b>
<tr>
<td align="center" height="23" width="54">No records </b><%
End If
End If
rst.Close
rst2.Close
con.Close
%>
And here's the error.....The part that is highlighted in the code is where it points to.
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/Queries2/LSRContacts.asp, line 147
|
|
|
|
