Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ASP/SQL - this is probably easy!

    I have cobbled together a DSN-Less connection to an Access db in ASP and want it to display, in table format, all the records fitting a SQL criteria. I suppose a DO...WHILE...EOF is one way to go but I am not sure how to implement it. I've tried warpping the table with a DO loop but ASP seems to want the whole statement in one line, so i've put it at the beginning and the end and all i get is the first record.

    this should be easy but i'm not getting anywhere...

    here's the SQL string:

    SQLstring = "SELECT * FROM CRCG_Contacts WHERE CRCGType ='Children';"

    and here's the attempt to build a simple list (not yet getting to a table):

    <%
    ' Displaying First row of SQL result in browser window, if all rows should be displayed you can iterate through the results with a do while loop around the following three lines of code.
    Response.write(myRecordSet.Fields("CRCGName") & "")
    Response.write(myRecordSet.Fields("CRCGType") & "")
    Response.write(myRecordSet.Fields("County") & "")

    %>

    i grabbed this code from an ASP help site and it works just fine, just trying to implement the advice in the comment.

    for the record, I am on:

    windows 2000 SR-2, frontpage 2000, iis 5.0

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ASP/SQL - this is probably easy!

    I created a test page in ASP back in January. I pasted it into an .rtf and attached it to this post. In reading it now, I think it is a logical way to do this, but... since then I bought and ADO book and learned about the GetString method. GetString works something like this:

    strTable = myRS.GetString(,, ColumnDelimiter, RowDelimiter, SwapForNullValue
    strTable = myRS.GetString(,, "</TD><TD>", "</TD></TD><TR><TD>", "& nbsp")

    (That last one obviously would need to be written correctly.)

    This looks fabulously easy and would replace the entire While/Wend loop, except for the calculation of the running total. Maybe there's another way to do that. Hope this helps.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP/SQL - this is probably easy!

    thanks for the response but i figured out what to do in the meantime. it was pretty simple, as I thought; just needed to figure out how to work the code.

    here's my solution (it's generic enough for most applications):

    At the beginning of pulling the data into a table do this:

    <%Do WHILE Not myRecordSet.EOF%>
    ---- table block



  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP/SQL - this is probably easy!

    You'll find that ASP code runs faster if it doesn't have to switch back and forth between ASP and HTML. Staying in ASP and spitting your HTML through response.write "html code" speeds it up significantly.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP/SQL - this is probably easy!

    I second VBNerd.

    As far as getting user input, I'd suggest an initial HTML form with a text box they can key criteria into. That form's Submit button calls your ASP page, and you create your SQL WHERE clause using the Request.QueryString object.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Porthcawl, Mid Glamorgan, Wales
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP/SQL - this is probably easy!

    This looks like a useful tip, however, how does the Write method output a line like that below?

    <font color=red> Response.Write "<table border="0" cellspacing="0" cellpadding="0" width=780 style="color: #000000">"
    </font color=red>

    It obviously doesn't work in this form. Is there a way of 'escaping' the double quote?

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Porthcawl, Mid Glamorgan, Wales
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP/SQL - this is probably easy!

    It wasn't difficult to work it out myself. Just remove all the double quotes. They were originally inserted by Frontpage.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP/SQL - this is probably easy!

    I might try to get .asp to generate the .html table code, but for now that is not so important. it works as it is.

    as far as grabbing the user input, i have one form that i am developing at the moment that is more or less a 'search results' page, in that the form POSTs to itself to display the data from the db. what i have so far doesn't work.

    I set up a short form (one inbox and a POST to the form itself:

    <form BOTID="0" METHOD="POST" ACTION="kids_reg_ch_searchNEW.asp">
    <div align="center">
    <center>
    <table BORDER="0">
    <tr>
    <td>HHSC_Region</td>
    <td><input TYPE="TEXT" NAME="HHSC_Regions" VALUE="<%=Request("HHSC_Regions")%>" size="20"></td>
    </tr>
    </table>
    </center>
    </div>



    </form>

    And then I set up a connection to access and a SQLstring that I want to pull from whatever is inputted above:

    <%
    '-- Declare your variables
    Dim DataConnection, SQLstring, myRecordSet, DBFileName, DBmyRecordSetFileName, Value, HHSC_Regions
    ' Change the db1.mdb to <yourfilename>.mdb
    DBFileName = "WebUpdateLocalContacts.mdb"

    '-- Create dataconnection and recordset object and open database
    Set DataConnection = Server.CreateObject("ADODB.Connection")
    Set myRecordSet = Server.CreateObject("ADODB.Recordset")

    DataConnection.Open "DBQ=" & Server.Mappath(DBFileName) &";Driver={Microsoft Access Driver (*.mdb)};"

    '-- default SQL
    ' Change the SQL string to a SQL string for your DB
    SQLstring = "SELECT * FROM CRCG_Contacts WHERE CRCGType = 'Children' AND HHSC_Regions = 'HHSC_Regions';"
    'SQLstring = SQLstring & "HHSC_Regions = 'HHSC_Regions';"

    myRecordSet.Open SQLstring, DataConnection

    %>

    Now, possibly if I hard-coded the HHSC_Regions to be a number (1 thru 11) this would if a SELECT CASE based on the input was installed, that might work (it's been recommended in some tip sites) but I don't think that solution is particularly elegant . Again, I suspect the problem is relatively easy but i have been futzing with it for a while...

    I have other pages that will take an input on that page and then open the search pages with the specified criteria, as you suggested, but I don't see why you can't have the asp page post to itself, as a search results page does. And I can get this scenario to work using FP xsions, but I am trying to keep my site pure of FP xsions....

    Any ideas? I certainly appreciate your help!

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP/SQL - this is probably easy!

    A couple of thoughts.

    (1) You can get the page to be self-posting, but you must have a mechanism to check whether it's the first visit to the page, or if somebody clicked on the submit button and it's calling itself.
    -- Perhaps you can examine the contents of the text box from your form. If there is something in the text box, you've already done a self-call. If there isn't, either it's your first visit to the page or you didn't key anything into the text box before you clicked the Submit button.

    (2) Assume that you've performed a self-call and now you need to use the value you keyed in for your WHERE statement.
    -- You use the Request.Form collection to pull the data from controls on the calling form.

    (3) I don't see a Submit button on your form, but I assume it's there somewhere.

    Obviously I haven't tested the code below, so I can't guarantee it'll work, but you can try it out - just be sure to back up what you have now before you do!!

    '*****
    <%

    'Set this variable to the value keyed in the form
    Dim strRegion 'as String
    strRegion = Request.Form("HHSC_Regions")

    'Check to see if the form has been filled and is calling itself
    'by looking at the length of what was in the text field.
    If Len(Trim(strRegion)) > 0 Then
    'A region has already been entered and the form is calling itself

    '-- Declare your variables
    Dim DataConnection, SQLstring, myRecordSet, DBFileName, DBmyRecordSetFileName, Value, HHSC_Regions
    ' Change the db1.mdb to <yourfilename>.mdb
    DBFileName = "WebUpdateLocalContacts.mdb"

    '-- Create dataconnection and recordset object and open database
    Set DataConnection = Server.CreateObject("ADODB.Connection")
    Set myRecordSet = Server.CreateObject("ADODB.Recordset")

    DataConnection.Open "DBQ=" & Server.Mappath(DBFileName) &";Driver={Microsoft Access Driver (*.mdb)};"

    ' Change the SQL string to a SQL string for your DB
    'Set the region criteria equal to the contents of the
    'text box
    SQLstring = "SELECT * FROM CRCG_Contacts WHERE CRCGType = 'Children' " _
    & "AND HHSC_Regions = '" & strRegion & "';"

    myRecordSet.Open SQLstring, DataConnection
    'Here is where you write the results.
    'Then clean up after your connection and recordset.

    Else
    'No region was entered or it's the first visit to the page
    'so write something appropriate.
    Response.Write "No results yet"
    End If

    %>
    '*****
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP/SQL - this is probably easy!

    Cool! It is now working.

    The thing I needed was the syntax for referring to the value in the input box in the SQLstring line, which is

    SQLstring = "SELECT * FROM CRCG_Contacts WHERE HHSC_Regions = '" & HHSC_Regions & "';"
    with the single quote - double quote - ampersands bracketing the field name.

    I will probably not implement this as a form submitting to itself, since I needed to get user input to filter the Access db based on selections; I think what I'll do is assemble a 'Search the DB' page with various options (search by a CRCG Type, County or Region, etc.) that goes to a single Search Results page... currently there are 3 identical .asp pages with slight differences (hard-coded) in the SQLstring. I'd obviously be better off with one.

    That way I don't have to worry about point no. 1. as for point 3, i had at some point in my troubleshooting process deleted the submit button (don't ask me why, it was getting late) - since then i put one in and, with the bit of coding you favored me with, it works as needed.

    Also, since i am going to have several SQLstring criteria pulled together on a search page, I will need to use a wildcard character for some conditions which will expect filtering on the generic .asp page. I assume that would be the asterisk[*] in the form value?

    There are some othe issues that I will likely have to look into, but it's looking better than before.

    Thanks again!

Posting Permissions

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