ASP/SQL – this is probably easy!

Topic

New Reply

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):

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

Reply | Quote

Replies

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(,, “

“, “ “, “& 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.

Reply | Quote

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:

—- table block

Reply | Quote

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.

Reply | Quote

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

Response.Write “

”

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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:

HHSC_Region

<input TYPE="TEXT" NAME="HHSC_Regions" VALUE="” size=”20″>

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 .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!

Reply | Quote

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!!

‘*****
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 .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

%>
‘*****

Reply | Quote

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!

Reply | Quote