I would imagine this is a super easy question but I am stumped. I receive data from vendor with a field called features. features includes data like "A354, A321, A345, A876". I then have another table has the full name for each feature that would have a field for ID and name and a records would look like this

ID Name
A354 Refriderator
A321 Oven
A345 A/C
A876 Central Heat

normally this is easy with a simply INNER JOIN but since I get the data with all features in one field seperated by a comma
A354, A321, A345, A876
that INNER JOIN doesn't work. How would I break these up so I can have it write out all the features by name on my asp page?

I am useing SQL2000, DMX, ASP, VBSCRIPT


** By the way, thanks for all the help, this site is by far the best at getting quick and correct help

Hi Bob

Can you please give a full example of the string you expect to get. And where is this string coming from, txt file etc.?

Digga

Sharing Knowledge Saves Valuable Time!!!

basically here is what I have
I have two tables

One with the property address and features

ResData (TableName)
PropID PropAdd PropFeatures
1 123 Main Street A123, C124, F125
2 154 Main Street A124, C124, F123
3 555 South Street A555, G432, T432


FeatureCodes (Tablename)
FeatureCodeID Label Value
1 A123 Bay Window
2 C124 new appliances
3 F123 French Doors
4 F125 New Dishwasher
etc, etc

what I have now is a page that does a simple
Select *
FROM ResData
WHERE (PropID='1')


this will then allow me to create a detailed paged for each property, what I need to do though is display the features as well

I want them to just appear in a paragraph like this

Applianes: Bay Window, New Appliances, New Dishwasher
if the record has those features in that PropFeatures Field

hope that helps and thanks again for the help

Hi Bob

Run this in query analyser, this gave me the results you're after.

<pre id=code><font face=courier size=2 id=code>DECLARE @myString VARCHAR(500), @strOut VARCHAR(500), @PropID INT
SET @PropID = 1 -- set you property ID here
SET @myString = (SELECT PropFeatures FROM resData WHERE PropID = @PropID)
SET QUOTED_IDENTIFIER OFF

SET @strOut = "SELECT [Value] FROM FeatureCodes WHERE Label IN ('" + REPLACE(@myString, ", ", "','" + "')"
SET @strOut = REPLACE(REPLACE(@strOut, CHAR(13),''), CHAR(10), '')

EXEC (@strOut)

SET QUOTED_IDENTIFIER ON </font id=code></pre id=code>

You'll need to sort the output on your web page to put the text into a paragraph.

Digga

Sharing Knowledge Saves Valuable Time!!!

Edited by - Digga the Wolf on 17 Sep 2004 13:07:36

sorry to be such an idiot but I am a bit confused. I put that code in the page and I get this

Microsoft VBScript compilation error '800a0408'

Invalid character

/PropSearchDetails.asp, line 339

DECLARE @myString VARCHAR(500), @strOut VARCHAR(500), @PropID INT

sorry to be such an idiot. By the way, I like your choice in Bikes, I dont ride street but alot on the dirt.

Hi Bob

Sorry my mistake!

The solution I provided is SQL, which could be run from a stored procedure but the code will need to be tweaked. What language do you want the code in?

The 7R has gone, traded it in for a ZZR1200, still got the 10R though. I had a go a motocross a few years ago, it was fantastic fun, felt like a real hero, I'd love to do it again.

Digga

Sharing Knowledge Saves Valuable Time!!!

sorry for the delay, I am pretty unfamiliar with the stored procedures, I was just hoping to write in and insert it into the ASP page.

I have a buddy who is a test rider for yamaha and I got to ride the R1 at Laguna Seca last week. it was awesome, that bike is a rocket and that course what amazing to not only see but to ride on. I think I looked like a fool in front of all the test guys becuase they passed me like I was standing still but it still was a good time

Hi Bob

I need to have a think about this so I'll get back to you later.

Digga

Sharing Knowledge Saves Valuable Time!!!

Hi Bob

Here's an entire test page, just change the second line to your connection and play around until you have it incorporated into your page:

<pre id=code><font face=courier size=2 id=code>&lt;%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%&gt;
&lt;!--#include file="Connections/Test.asp" --&gt;
&lt;%
Dim intTest
If Request.Form("txtID" &lt;&gt; "" Then
intTest = 1
Else
intTest = 0
End If
%&gt;
&lt;%
If intTest = 1 Then

Dim Recordset1__ID
Recordset1__ID = "0"
If (Request.Form("txtID" &lt;&gt; "" Then
Recordset1__ID = Request.Form("txtID"
End If
%&gt;
&lt;%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_Test_STRING
Recordset1.Source = "SELECT * FROM dbo.ResData WHERE PropID = " + Replace(Recordset1__ID, "'", "''" + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%&gt;
&lt;%
Dim myIN, myINArray, myCounter, myUpper, myOut
myIN = Replace(Recordset1.Fields.Item("PropFeatures".Value, ", ", "','",1,-1,1)
myOut = "'" & Replace(myIN, " ", "",1,-1,1) & "'"

Response.Write("&lt;strong&gt;Input from PropFeatures: &lt;/strong&gt;" & myIN & "&lt;br&gt;"
Response.Write("&lt;strong&gt;Output after manipulation: &lt;/strong&gt;" & myOut & "&lt;br&gt;"
%&gt;
&lt;%
Dim Recordset2__myRange
Recordset2__myRange = "0"
If (myOut &lt;&gt; "" Then
Recordset2__myRange = myOut
End If
%&gt;
&lt;%
Response.Write("The string below is the SQL that's being passed to the server:&lt;br&gt;"
Response.Write("SELECT * FROM dbo.FeatureCodes Where Label IN (" & Recordset2__myRange & ""
%&gt;
&lt;%
Dim Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset"
Recordset2.ActiveConnection = MM_Test_STRING
Recordset2.Source = "SELECT * FROM dbo.FeatureCodes Where Label IN (" & Recordset2__myRange & ""
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%&gt;
&lt;%
Dim strOut

While ((NOT Recordset2.BOF) AND (NOT Recordset2.EOF))
strOut = strOut & Recordset2.Fields.Item("Value".Value & ", "
Recordset2.MoveNext()
Wend

End If
%&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt;
&lt;/head&gt;

&lt;body&gt;
&lt;form action="" method="post" name="frmtest" id="frmtest"&gt;&lt;input name="txtID" type="text" value="Enter PropID"&gt;
&lt;label&gt;
&lt;input type="submit" name="Submit" value="Submit"&gt;
&lt;/label&gt;
&lt;/form&gt;
&lt;%
if intTest = 1 Then
%&gt;
&lt;table border="1"&gt;
&lt;tr&gt;
&lt;td&gt;PropID&lt;/td&gt;
&lt;td&gt;PropAdd&lt;/td&gt;
&lt;td&gt;PropFeatures&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;%=(Recordset1.Fields.Item("PropID".Value)%&gt;&lt;/td&gt;
&lt;td&gt;&lt;%=(Recordset1.Fields.Item("PropAdd".Value)%&gt;&lt;/td&gt;
&lt;td&gt;&lt;%=(Recordset1.Fields.Item("PropFeatures".Value)%&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;p&gt;&lt;strong&gt;Appliances: &lt;/strong&gt;&lt;%= strOut%&gt;&lt;/p&gt;
&lt;% End If %&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;%
If intTest = 1 Then
Recordset1.Close()
Set Recordset1 = Nothing

Recordset2.Close()
Set Recordset2 = Nothing
End If
%&gt;
</font id=code></pre id=code>

Digga

Sharing Knowledge Saves Valuable Time!!!

Hi Colin

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>I have a buddy who is a test rider for yamaha and I got to ride the R1 at Laguna Seca last week. it was awesome, that bike is a rocket and that course what amazing to not only see but to ride on. I think I looked like a fool in front of all the test guys becuase they passed me like I was standing still but it still was a good time
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Lucky lucky you!

Don't concern yourself about the 'fool' bit, it's up to them to go around you, just enjoy.

I'm supposed to racing next seasonas a friend needs another team rider, me thinks I need some practice.

Digga

Sharing Knowledge Saves Valuable Time!!!

that was soo cool of you to make that form, I tried it and I am SUPER CLOSE, I get this far

Input from PropFeatures: APL103,APL188,APL291,APL298,APL430,APL543
Output after manipulation: 'APL103,APL188,APL291,APL298,APL430,APL543'
The string below is the SQL that's being passed to the server:
SELECT * FROM dbo.FeatureCodes Where Value IN ('APL103,APL188,APL291,APL298,APL430,APL543')
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/test.asp, line 46



I have tried to take that SELECT statement and put it in a TEMP view in SQL and it just brings me back no results, in the ASP page it just gives me that error.

Hi Colin

I think this line is missing, if not let me have your asp page script:

myIN = Replace(Recordset1.Fields.Item("PropFeatures".Value, ", ", "','",1,-1,1)

The error is showing that the items in the IN list are not separated correctly, they should be enclosed with single quotes and separated with commas.

If fact, just had a thought, I bet the above line is in place but it's looking for ", " so replace the line above with:

myIN = Replace(Recordset1.Fields.Item("PropFeatures".Value, ",", "','",1,-1,1)

Digga

Sharing Knowledge Saves Valuable Time!!!

Edited by - Digga the Wolf on 27 Sep 2004 11:56:52

digga
you are the man. That worked perfectly, I should have picked up on that but I think my frustration level was too high at that point. It worked perfectly and looks awesome.

I can't thank you enough, if you were a yamaha rider, i would try to get you some yamaha gear. let me know if you do and I will see what I can get for ya

email =