SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How can I build a Dynamic Query?

    I am trying to build a page that checks the query string as the page is loaded. On the previous page, the person can select either an author or a title, thus producing the url article.asp?author=daniel or article.asp?title=ASP Tutorial.

    How can I dynamically create my SQL Statement so I dont have to repeat code?

    <%

    response.buffer=true
    author = Request.QueryString("author")
    title = Request.QueryString("title")

    if author <> "" then

    msql="SELECT * FROM AgentArticles WHERE Name = '" & author & "'"
    set rs = server.CreateObject("adodb.recordset")
    rs.Open msql,Application("ConnectionString"),adOpenStatic

    else if title <> "" then

    msql="SELECT * FROM AgentArticles WHERE Title = '" & title & "'"
    set rs = server.CreateObject("adodb.recordset")
    rs.Open msql,Application("ConnectionString"),adOpenStatic

    end if
    end if

    %>

  2. #2
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your If statements you could build up the SQL string, and not execute it until the end. Depending on what you want to do, you could just build up the WHERE clause inside the If to avoid more repetition. So it might be something like:

    Code:
    <% 
    
    If author <> "" Then 
       sWhereClause = " WHERE Name = '" & author & "'"
    ElseIf title <> "" Then
       sWhereClause = " WHERE Title = '" & title & "'"
    End If
    
    msql = "SELECT * FROM AgentArticles" & sWhereClause
    set rs = server.CreateObject("adodb.recordset") 
    rs.Open msql,Application("ConnectionString"),adOpenStatic 
    
    %>
    Obviously you could change the structure of the If statements as needed - you may allow both author AND title to be queried on for example, or you may allow partial searches using LIKE. But the general idea is to build up your SQL string in the If's, so you only need to write the rest once.

  3. #3
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •