database file locations: url

Anybody know how to resolve this sophiscated program.

Now the URL is : http://internal.quickentextiles.com.hk/sampleroom/byweight.asp?searchtype='Alph'

If I input "10" at the by weight and "white" at the by color. After I input
this two criteria on the textbox. I suppose to have a button "criteria search".
I press this criteria search button. It should query the result with 10 weight and with the white color criteria searching result.
However, I wrote the program below but unable to implement the above
process. Anyone know how it work out and the problem of the syntax.
The criteria search program is as follows:

<%@. Language="VBSCRIPT" %>
<% Option Explicit %>
<% Server.ScriptTimeout = 300 %>
<% response.buffer = true %>


<%
Const Field1 = "productname"
Const Field2 = "tradename"
Const Field3 = "weightname"
Const Field4 = "colorname"
Const Field5 = "twillname"
Const Field6 = "supplierID"
Const Field7 = "suppliername"

Const thisURL= "byall.asp"
Const PageSize = 25
%>

<HTML><HEAD><TITLE>Sample Room Display</TITLE>
<META content="text/html; charset=big5" http-equiv=Content-Type>
<STYLE type=text/css>A:hover {
COLOR: #ff3300
}
</STYLE>

<META content="Microsoft FrontPage 5.0" name=GENERATOR></HEAD>
<BODY aLink=#000099 bgColor=#ffffff leftMargin=0 link=#000099 text=black
topMargin=0 vLink=#000099 marginheight="0" marginwidth="0">



<%
'------------------
'Sub Funcion : PageView
'Desc : Display SQL result as pages
'Param
' oRecordSet : object for output recordset object
' nPageNum : current pagenum
' nPageSize : item numbers in one page
' sQueryURL : query process url
'------------------
Sub PageView(oRecordSet , nPageNum,nPageSize, sQueryURL)
Dim nPageCount,i,j

oRecordSet.PageSize = nPageSize
nPageCount = oRecordSet.PageCount
IF (nPageCount < 1) THEN
Exit Sub 'The query result is empty
End IF

oRecordSet.AbsolutePage = nPageNum

For i=1 to oRecordSet.PageSize
Response.Write "<TR align=left vAlign=top>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field1) & "</TD>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field2) & "</TD>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field3) & "</TD>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field4) & "</TD>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field5) & "</TD>"
Response.Write "<TD width=300>"
Response.Write "<a href="http://links.10026.com/?link="displaydetail.asp?Searchby=Products&SID=" & oRecordSet.fields(Field6) & """>"& oRecordSet.fields(Field7) & "</a>"

Response.Write "</TD>"
Response.Write "</TR>"
oRecordSet.MoveNext
IF oRecordSet.EOF THEN Exit For
Next

Response.Write ("<TR><td colspan=3> ")
IF (nPageCount > 1) THEN
IF (nPageNum = 1) THEN
Response.Write "<A href="http://links.10026.com/?link= & sQueryURL & "&PageNum=2>Next Page</A>"
ELSEIF (nPageCount = nPageNum) THEN
Response.Write "<A href="http://links.10026.com/?link= & sQueryURL & "&PageNum=" &(nPageNum-1) & ">Prev Page</A>"
ELSE
Response.Write "<A href="http://links.10026.com/?link= & sQueryURL & "&PageNum=" &(nPageNum-1) & ">Prev Page</A>"
Response.Write " "
Response.Write "<A href="http://links.10026.com/?link= & sQueryURL & "&PageNum=" &(nPageNum+1) & ">Next Page</A>"
END IF
END IF
Response.Write ("</td></tr>")

End Sub

Dim DbConn
Dim searchtradenameAZ
Dim gettype
Dim getparam
Dim inparam
Dim searchtradenameAZSQL
Dim sURL

Application.LOCK
'Create connection
Set DbConn = Server.CreateObject ("ADODB.Connection")
DbConn.Connectiontimeout=3
DbConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("sroomsearch.mdb") & ";user=nil;password=nil"
Set searchtradenameAZ = Server.CreateObject ("ADODB.Recordset")

gettype = trim(request("searchtype"))
getparam = trim(request("searchAlpha"))

' replace quotes
IF ( gettype = "'Alph'") THEN
inparam = replace(getparam,"'","") & "%"
ELSE
inparam = "%" & replace(getparam,"'","") & "%"
END IF

searchtradenameAZSQL = "select products.name as ProductName, suppliers.name as SupplierName,
weight.name as WeightName, color.name as ColorName, twill.name as twillName, " & _
" tradename.name as TradeName, suppliers.ID as SupplierID " & _
" from products, tradename, suppliers, weight, color, twill, pointers where " & _
" products.name = suppliers.name and color.name = weight.name " & _
" and (color.name IN ('indigo','Dark Coffee','Blue-Green','Blue-Black','Blue-Yellow','white')) "
& _
" and (weight.name IN (5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10,10.5,11,11.5,12, 12.5,13,13.5,14,14.5,15)
"

Dim bCriteriaMatched
bCriteriaMatched = False

If Request.Form("tradename") <> "" Then
bCriteriaMatched = True

searchtradenameAZSQL = searchtradenameAZSQL & " tradename.name like '"& Request.Form("tradename") &"' "
End If

If Request.Form("color") <> "" Then
If bCriteriaMatched Then
searchtradenameAZSQL = searchtradenameAZSQL & " AND "
End If

bCriteriaMatched = True

searchtradenameAZSQL = searchtradenameAZSQL & " color.name like '"& Request.Form("color") &"' "
End If

If Request.Form("twill") <> "" Then
If bCriteriaMatched Then
searchtradenameAZSQL = searchtradenameAZSQL & " AND "
End If

bCriteriaMatched = True

searchtradenameAZSQL = searchtradenameAZSQL & " twill.name like '"& Request.Form("twill") &"' "
End If

searchtradenameAZSQL = searchtradenameAZSQL & " order by weight.name, tradename.name, color.name,
twill.name, products.name;"

Set searchtradenameAZ = Server.CreateObject("ADODB.Recordset")
searchtradenameAZ.Open searchtradenameAZSQL, DbConn, adOpenStatic
%>

thanks!
maniaYou are joining 7 tables and you only have 2 predicate clauses that match rows on any of these tables. You are likely to recieve a lot more rows than you need.

Does the new report control for vs 2005 utilize SOAP or the URL method
to access the report server?
I need to utilize a pure SOAP based solution to get reports from the
report server. Just curious if the new solution is used that way or if
rolling my own is the only option.
ThanksThe new controls use soap (web services). They are a much more complete
solution that the old way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vivaldi" <ZachMattson@.gmail.com> wrote in message
news:1131634098.219113.321000@.g49g2000cwa.googlegroups.com...
> Does the new report control for vs 2005 utilize SOAP or the URL method
> to access the report server?
> I need to utilize a pure SOAP based solution to get reports from the
> report server. Just curious if the new solution is used that way or if
> rolling my own is the only option.
> Thanks
>

Wednesday, March 21, 2012

new sql searching

Monday, March 19, 2012

New Reporting Control

database file locations

Blog Archive

About Me