concat

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

concat

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> concat

Page: [1]

rrnml

Posts: 197
Joined: 9/20/2004
Status: offline

concat - 9/19/2006 18:11:41

I am trying to concatenate the following three fields in bold. Can anyone tell me how to do that? I tried a couple of things that didn't work. Thanks!

strSQL = " select distinct(nmpdta.arrtlml.rmrtno), nmpdta.arrtlml.rmrtnm, nmpdta.arrtlml.rmdlad, nmpdta.seapldl1.adrslt, " & _
	     " nmpdta.arrtlml.rmdlct, nmpdta.arrtlml.rmctnm, nmpdta.arrtlml.rmvsin, nmpdta.arrtlml.rmstno, nmpdta.arrtlml.rmdldy, " & _ 
	     " nmpdta.arrtlml.rmmlp1, nmpdta.arrtlml.rmmlp2, nmpdta.arrtlml.rmmlp3, nmpdta.arrtlml.rmrpno " & _  
         " 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"


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: concat - 9/20/2006 1:15:46 quote: am trying to concatenate the following three fields in bold. Do you mean you're trying to put them together for display, write them back to the database as a single text, or something else? Not really sure what you are trying to do. If you had real values of "One", "Two", and "Three" in those fields, what would the desired result be? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to rrnml)


rrnml Posts: 197 Joined: 9/20/2004 Status: offline	RE: concat - 9/20/2006 11:33:48 Yes it is three fields of a phone number. For example, 505 758 and 2361. I would like the result to be 505-758-2361. Is this possible? (in reply to rrnml)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: concat - 9/20/2006 11:54:28 quote: nmpdta.arrtlml.rmmlp1, nmpdta.arrtlml.rmmlp2, nmpdta.arrtlml.rmmlp3, How 'bout this? ... nmpdta.arrtlml.rmmlp1 & "-" & nmpdta.arrtlml.rmmlp2 & " - " & nmpdta.arrtlml.rmmlp3 AS myPhoneNumber, ... That help any? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to rrnml)


rrnml Posts: 197 Joined: 9/20/2004 Status: offline	RE: concat - 9/20/2006 11:59:18 When I use that I get this error.... Microsoft VBScript runtime error '800a000d' Type mismatch: '[string: " nmpdta.arrtlml.rmml"]' /Queries2/LSRContacts.asp, line 71 (in reply to rrnml)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: concat - 9/20/2006 12:38:15 quote: nmpdta.arrtlml.rmmlp1 & "-" & nmpdta.arrtlml.rmmlp2 & " - " & nmpdta.arrtlml.rmmlp3 AS myPhoneNumber Ooops. Probably needs apostrophes instead of quotes: nmpdta.arrtlml.rmmlp1 & '-' & nmpdta.arrtlml.rmmlp2 & '-' & nmpdta.arrtlml.rmmlp3 AS myPhoneNumber That one any better? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to rrnml)


rrnml Posts: 197 Joined: 9/20/2004 Status: offline	RE: concat - 9/20/2006 12:42:18 And now I'm getting this error.... Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token & was not valid. Valid tokens: + - AS <IDENTIFIER>. /Queries2/LSRContacts.asp, line 85 (in reply to rdouglass)

rdouglass

Posts: 9280
From: Biddeford, ME USA
Status: offline

RE: concat - 9/20/2006 13:30:31

quote:

mpdta.arrtlml.rmmlp1 & '-' & nmpdta.arrtlml.rmmlp2 & '-' & nmpdta.arrtlml.rmmlp3 AS myPhoneNumber

maybe it needs parens but mine didn't. Here is an exact script that I used for a test to check the syntax.

<%
	mySQL = "SELECT BlogAdKeyword1 & '-' & BlogAdKeyword2 & '-' & BlogAdKeyword3 AS Expr1,BlogAdID FROM tblBlogAdMaster"

	set conntemp=server.createobject("adodb.connection")
	conntemp.open myDSN
	response.write(mySQL)
	set rstemp=conntemp.execute(mySQL)
	IF  rstemp.eof THEN
	 	'response.write "Currently there are no offers."
	ELSE
		k101Array=rstemp.getrows
	END IF
	conntemp.close
	set conntemp=nothing
%>

I'm doing the same thing as I posted and this does work for me.

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to rrnml)

rrnml

Posts: 197
Joined: 9/20/2004
Status: offline

RE: concat - 9/20/2006 15:32:44

So I couldn't get that to work so instead I did this...

<%
'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
If savRtl <> CDbl(rst("rmrtno")) Then
%>

  <tr>
    <td width="54" height="29"><%=(savRep) %></td>
    <td width="55" height="29"><%=(savRtl)%></td>
    <td align="center" height="17" width="70"><%=(savNam) %></td>
    <td align="center" height="17" width="82"><%=(savAddr) %></td>
    <td align="center" height="17" width="60"><%=(savCity) %></td>
    <td align="center" height="17" width="73"></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="60"><%=(savCont) %></td>
    <td align="center" height="17" width="114"><%=(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")

End If

So now the result I get is 5057582631 with no dashes. I can't seem to figure out how to get those in there. Any ideas?

(in reply to rdouglass)


rrnml Posts: 197 Joined: 9/20/2004 Status: offline	RE: concat - 9/20/2006 15:36:11 Ok so I guess I did figure it out! For savPhon variable I tried this and it worked..... savPhon = rst("rmdlp1") & "-" & rst("rmdlp2") & "-" & rst("rmdlp3") Thanks! (in reply to rrnml)


rrnml Posts: 197 Joined: 9/20/2004 Status: offline	RE: concat - 9/20/2006 15:43:52 Ok so I was wrong. :) The result ends up as 505- 758- 2361 How can I get it to be 505-758-2361? This is driving me nuts! (in reply to rrnml)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: concat - 9/20/2006 16:21:17 quote: 505- 758- 2361 There must be CR's or something in your fields to do stuff like that. How 'bout this: avPhon = replace(replace(rst("rmdlp1") & "-" & rst("rmdlp2") & "-" & rst("rmdlp3"),CHR(013),""),"<br>","") That help any? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to rrnml)


rrnml Posts: 197 Joined: 9/20/2004 Status: offline	RE: concat - 9/20/2006 19:29:39 I still get the same result. (in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: concat - 9/20/2006 19:39:24 What do you get when you write them without combining them? Can you View Source to see what's in there? Or how 'bout a URL? I can tell you the <CR>'s are not coming from my code. Maybe they're in your field data. Again I suggest viewing the source. _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to rrnml)

rrnml

Posts: 197
Joined: 9/20/2004
Status: offline

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

(in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: concat - 9/21/2006 15:42:23 quote: If rst2("adrslt") = " " or "NO" Then Did you try it this way? If (trim(rst2("adrslt")&"") = "") or (trim(rst2("adrslt")&"") = "NO") Then Also be sure there really is a column "adrslt" in the query results. _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to rrnml)


rrnml Posts: 197 Joined: 9/20/2004 Status: offline	RE: concat - 9/21/2006 15:56:21 I was really hoping that would work. This file is just driving me crazy with all it's little quirks! Anyway, that didn't seem to work. Any other ideas? I can tell you that the file does contain the field adrslt which contains data like YES, NO, or it's blank. So when the adclr# = 62 the adrslt = YES, NO or blank. (in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: concat - 9/21/2006 16:15:35 quote: If rst2("adrslt") = " " or "NO" Then Lookig at it again, have you actually opened that recordset? It looks like you don't open it until 2-3 lines down.... _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to rrnml)

rrnml

Posts: 197
Joined: 9/20/2004
Status: offline

RE: concat - 9/21/2006 18:44:17

Thanks for that tip. I didn't even realize that. So I changed it a bit and now it works. Thanks for your help!

<%
'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
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")

strParm99 = CDbl(rst("rmrtno"))

strSQL2 = "select nmpdta.seapldl1.adrslt " & _
         " from nmpdta.seapldl1 " & _ 
         " where (nmpdta.seapldl1.adrtno = " & strParm99 & ") " & _
         " and (nmpdta.seapldl1.adclr# = 62) " & _
         " and (adapty = 'I') "
          
Set rst2 = Server.CreateObject("ADODB.Recordset")
rst2.Open strSQL2, con, adOpenKeyset, adLockOptimistic 

         
'response.write strSQL2
	strITVM = "N"
If NOT rst2.EOF then 
Do While Not rst2.EOF

If (trim(rst2("adrslt")&"") = "")  or (trim(rst2("adrslt")&"") = "NO") Then
		strITVM = "N"
	Else
		strITVM = "Y"
End If	
	


rst2.MoveNext

Loop

Else
%>
<%
End If


%>

  <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>
<%
'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
%>

(in reply to rdouglass)