Discuss "Help with If contains then" in the ASP Development forum.

Rebelle · #1 (**permalink**) 05-02-2008, 11:09 AM

Hi All,

I have a sql view where i pull all records the contains the word ready or repair.

I currently have them ordered by region and this is all good but i would like to have it look like so when it comes to separating by ones containing ready and repair:

Region
---Ready
-------(GC-ready)---tool---currcc
-------(GC-ready)---tool---currcc
-------(DU-ready)---tool---currcc
---Repair
-------(GC-repair)---tool---currcc
-------(AB-repair)---tool---currcc
Next Region
ETC.

Code:

<TR>
<TD bgcolor=#ffe4e1 align=right><%=rs("Loc")%> &nbsp;</TD>
<TD align=center><%=rs("Tools")%> &nbsp;</TD>
<TD align=center><%=rs("CurrCC")%> &nbsp;</TD>
</TR>

mehere · #2 (**permalink**) 05-02-2008, 11:25 AM

well ... this would all depend on how the data is in the database to see if it can be ordered that way ... so that it can be displayed. otherwise you may have to put the recordset into an array then look at using a disconnected recordset to order it. we really need more information on your table layout along with the data that is currently in the column where you're getting "ready" & "repair"

Rebelle · #3 (**permalink**) 05-02-2008, 12:02 PM

Thanks for the reply Mehere!

Here is the view i'm using:

Code:

SELECT     dbo.tblEQStatus.RegionID, dbo.tblEQStatus.ToolID, dbo.tblEQStatus.DistrictID, dbo.tblEQStatus.EQStatus, dbo.tblEQStatus.TrackingStatus, 
                      dbo.tblEQStatus.EQNumber, dbo.tblEQStatus.Loc, dbo.tblEQStatus.CurrCC, dbo.tblTools.Tools, dbo.tblRegion.RegionName, 
                      dbo.tblDistricts.District_Location
FROM         dbo.tblTools RIGHT OUTER JOIN
                      dbo.tblEQStatus INNER JOIN
                      dbo.tblDistricts INNER JOIN
                      dbo.tblRegion ON dbo.tblDistricts.RegionID = dbo.tblRegion.RegionID ON dbo.tblEQStatus.DistrictID = dbo.tblDistricts.DistrictID ON 
                      dbo.tblTools.ToolID = dbo.tblEQStatus.ToolID
WHERE     (dbo.tblEQStatus.Loc LIKE '%ready%') OR
                      (dbo.tblEQStatus.Loc LIKE '%repair%')

Right now on my results.asp page, I have something like this:
Region
---AB-Repair--Tools---EQNumber
---AB-Repair--Tools---EQNumber
---AB-Ready--Tools---EQNumber
---DU-Repair--Tools---EQNumber
---EH-Ready--Tools---EQNumber
Next Region
Etc.

The LOC field contains hyphens (AB-Repair)

I didn't know if there was a way to use an if statement with code below to say if LOC contains ready then show all the ready, then if repair show all the repair.

Code:

<TR>
<TD bgcolor=#ffe4e1 align=right><%=rs("Loc")%> &nbsp;</TD>
<TD align=center><%=rs("Tools")%> &nbsp;</TD>
<TD align=center><%=rs("CurrCC")%> &nbsp;</TD>
</TR>

mehere · #4 (**permalink**) 05-02-2008, 12:08 PM

if you can order it by the LOC field ... then you should be able to do it ... give me a minute to see

mehere · #5 (**permalink**) 05-02-2008, 12:16 PM

use an ORDER BY in your statement ... something like this

Code:

ORDER BY dbo.tblEQStatus.RegionID, SUBSTRING(dbo.tblEQStatus.Loc,CHARINDEX('-',dbo.tblEQStatus.Loc)+1,LEN(dbo.tblEQStatus.Loc))

this way all your "Ready" will be together, then all of your "Repair" in each region. Then it should display correctly.

Rebelle · #6 (**permalink**) 05-02-2008, 12:57 PM

Super!! That worked out the ordering situation much better!!!

But how can I slip in the static "Ready" group name with only making it show the ready, then the "Repair"?

mehere · #7 (**permalink**) 05-02-2008, 01:02 PM

while displaying the records ... check if it's ready or repair ... kinda like the way you're (i'm assuming) you're doing it for region ... something like this:

Code:

do while not rs.EOF
strLoc = rs("loc")
strType = MID(strLoc,inStr(strLoc,"-")+1,len(strLoc))
if strType <> prevType then
     response.Write(strType & "<br />")
end if
response.Write(the rest of the data)
prevType = strType
rs.MoveNext
loop

Rebelle · #8 (**permalink**) 05-02-2008, 01:05 PM

Ok, I think the MID thing is what I'm missing, I'll give it a try.

Thank you so much!

mehere · #9 (**permalink**) 05-02-2008, 01:14 PM

np ... let me know how it works out for you

Rebelle · #10 (**permalink**) 05-02-2008, 01:31 PM

Any clue why it doesn't like the MID?, getting the following.

Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'Mid'

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

The Following User Says Thank You to mehere For This Useful Post:
Rebelle (06-17-2008)

Discuss "Help with If contains then" in the ASP Development forum.

ASP Development - Learn coding practices and tips to get the best out of your Active Server Pages (ASP). The Classic ASP forum is for ASP/VBScript and ASP/JScript applications.