buildquery.asp

asp与MYSQL的源码

<%
if len(request("run")) > 0 then
	%>
	<!--#include file="dsn.asp"-->
	<%

	sql = "select " & request("fields") & " from " & request("db") & "." & request("tb")
	
	if len(request("whereclause")) > 0 then
		sql = sql & " where " & request("whereClause")
	else
		set rsLookup = server.CreateObject("ADODB.Recordset")
		rsLookup.CursorLocation = 3
		rsLookup.Open "desc " & request("db") & "." & request("tb"), dsn, 2, 3
		
		doWhereClause = false
		
		for i = 1 to rsLookup.RecordCount 
		
			if len(request(rsLookup("field") & "_Value")) > 0 then
				if len(whereclause) > 0 then
					whereclause = whereclause & request(rsLookup("field") & "_Operator") & " " 
				end if
				whereclause = whereclause & rsLookup("field") & " " & request(rsLookup("field") & "_function") & " '" & request(rsLookup("field") & "_value") & "' "
				doWhereClause = true
			end if
			rsLookup.MoveNext 
		
		next
		if doWhereClause then
			sql = sql & " where " & whereClause
		end if
		set rsLookup = nothing
	end if
	
	if len(request("order_by")) > 0 then
		sql = sql & " order by " & request("order_by")
	end if
	if len(request("order_by")) > 0 and len(request("order_by_direction")) > 0 then
		sql = sql & " " & request("order_by_direction")
	end if
	
	Response.Redirect "runquery.asp?db=" & request("db") & "&tb=" & request("tb") & "&vQuery=" & server.URLEncode(sql)

end if

Response.Buffer = true
%>
<!--#include file="top.asp"-->
<TABLE WIDTH=750 bgcolor=#E7F1EC cellspacing=1 cellpadding=0 border=0>
	<TR>
		<TD width=200 valign=top><!--#include file="left.asp"--></TD>
		<TD width=550 valign=top>
			<TABLE WIDTH=100% cellpadding=5 cellspacing=0>
				<TR>
					<TD bgcolor="#339999" align=center width=550 class=celltitle><%=Request("db")%> -- <%=request("tb")%></TD>
				</TR>
				<TR>
					<TD class=cellsmallcontent>
						<table border="0" cellpadding=2 cellspacing=1 style="border:solid black 1pt;">
							<TR>
								<TD bgcolor=#339999 class=cellsmalltitle>Build SQL Query</TD>
							</TR>
							<FORM ACTION="buildquery.asp?run=y&db=<%=request("db")%>&tb=<%=request("tb")%>" method=post>
							<TR>
								<TD class=cellsmallcontent>
									Select Fields (at least one):<BR>
									<SELECT NAME=fields size=5 multiple>
									<%
									set rsTables = server.CreateObject("ADODB.Recordset")
									rsTables.CursorLocation = 3
									rsTables.Open "desc " & request("db") & "." & request("tb"), dsn, 2, 3
									for i = 1 to rsTables.RecordCount 
									%>
										<OPTION value='<%=rsTables(0)%>' selected><%=rsTables(0)%></oPTION>
									<%
										rsTables.movenext
									next
									%>
									</SELECT>
								</TD>									
							</TR>
							<TR>
								<TD class=cellsmallcontent>
									Add Search Conditions (Where Clause):<BR>
									<INPUT TYPE=TEXT NAME=WhereClause STYLE="Width:200px;">
								</TD>
							</TR>
							<TR>
								<TD class=cellsmallcontent>
									<I><B>Or</I></B> Build Search Conditions:<BR>
									<INPUT TYPE=HIDDEN NAME=NumFields value=<%=rsTables.recordcount%>>
									<TABLE cellpadding=0 width=400 cellspacing=0 border=0>
										<TR>
											<TD class=cellsmalltitle bgcolor=#339999>Field</TD>
											<TD class=cellsmalltitle bgcolor=#339999>Type</TD>
											<TD class=cellsmalltitle bgcolor=#339999>Operator</TD>
											<TD class=cellsmalltitle bgcolor=#339999>Function</TD>
											<TD class=cellsmalltitle bgcolor=#339999>Value</TD>
										</TR>
										<%
										rsTables.MoveFirst
										For i = 1 to rsTables.RecordCount 
										%>
										<TR>
											<TD class=cellsmallcontent<%if i mod 2 = 0 then%> bgcolor=white<%end if%>><%=rsTables("field")%></TD>
											<TD class=cellsmallcontent<%if i mod 2 = 0 then%> bgcolor=white<%end if%>><%=rsTables("Type")%></TD>
											
											<TD class=cellsmallcontent<%if i mod 2 = 0 then%> bgcolor=white<%end if%>>
											<%
											if i > 1 then
											%>
												<INPUT TYPE=RADIO NAME="<%=rsTables("field")%>_Operator" Value="And" checked>And <BR>
												<INPUT TYPE=RADIO NAME="<%=rsTables("field")%>_Operator" Value="Or">Or
											<%
											end if
											%>
											</TD>
											
											<TD<%if i mod 2 = 0 then%> bgcolor=white<%end if%>>
												<SELECT NAME="<%=rsTables("field")%>_Function">
												<%
												if	instr(lcase(rsTables("type")), "double") > 0 or _
													instr(lcase(rsTables("type")), "int") > 0 or _
													instr(lcase(rsTables("type")), "float") > 0 or _
													instr(lcase(rsTables("type")), "decimal") > 0 or _
													instr(lcase(rsTables("type")), "set") > 0 then
												%>
													<OPTION VALUE='='>=</OPTION>
													<OPTION VALUE='>'>></OPTION>
													<OPTION VALUE='>='>>=</OPTION>
													<OPTION VALUE='<'><</OPTION>
													<OPTION VALUE='<='><=</OPTION>
													<OPTION VALUE='!='>!=</OPTION>
												<%
												else
												%>
													<option value="LIKE">LIKE</OPTION>
													<OPTION VALUE="=">=</OPTION>
													<OPTION VALUE="!=">!=</OPTION>
												<%
												end if
												%>
												</SELECT>
											</TD>
											<TD<%if i mod 2 = 0 then%> bgcolor=white<%end if%>><INPUT TYPE=TEXT NAME="<%=rsTables("field")%>_value"></TD>
										</TR>
										<%
										rsTables.MoveNext
										next
										%>
									</TABLE>
								</TD>
							</TR>
							<TR>
								<TD class=cellsmallcontent>
									Display Order:<BR>
									<%
									rsTables.MoveFirst 
									%>
									<SELECT NAME=Order_by>
										<option value=''></OPTION>
									<%
									for i = 1 to rsTables.RecordCount 
									%>
										<OPTION VALUE='<%=rsTables(0)%>'><%=rsTables(0)%></OPTION>
									<%
										rsTables.MoveNext
									next
									set rsTables = nothing
									%>
									</SELECT>
									<INPUT TYPE=Radio Name=Order_By_Direction value='asc'> Ascending &nbsp;&nbsp; <INPUT TYPE=Radio Name=Order_By_Direction value='desc'> Descending
								</TD>
							</TR>
							<TR>
								<TD>
									<INPUT TYPE=SUBMIT VALUE="Run Query">
								</TD>
							</TR>
							</FORM>
						</TABLE>
					</TD>
				</TR>
			</TABLE>
		</TD>
	</TR>
</TABLE>
<!--#include file="bottom.asp"-->