selecting recordset and writing field values

marketingmaggie · February 4th, 2004, 08:18 AM

I'm not really a programmer but I've got sucked into ASP. I've written 5 good searches on a database but the 6th is driving me mad - and it ought to be the simplest of them all! If anyone would like to comment on the code extract below, I'd be very grateful. The Form part just selects the name of a distributor and passes it to the ASP page. I then want to use that selection to Select one single record from the table "distdetails" in the database: "distributorname" is the matching Field. The RecToTable function works absolutely fine, but I get all the records displayed instead of just the one I'm trying to match from the Form.

Many thanks - I guess I just can't do logic!

Maggie

<%
Option Explicit
Dim strConnect
%>

<html>

Dim strSQL
Dim objRS, objConn

'Open a recordset

Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open "distdetails", strConnect, 0, 3, 0

If Request.Form("Distributor") <> " " Then
strSQL = "SELECT distributorname FROM distdetails WHERE DistributorName LIKE '%" & Request.Form("Distributor") & "%'"
End If

Response.Write RecToTable (objRS)

objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing

%>

<FORM NAME=DistiInfo ACTION="test5matchtodatabase.asp" METHOD="POST">

        <BR>
        Select the name of the <b> Distributor</b> whose details you want to find in the box below:<BR>

<p><BR>
<BR><select size=1 name=distributor>
  <OPTION selected></OPTION>
(and lots of options)
</select>

<BR><INPUT TYPE=SUBMIT VALUE="Find">&nbsp<INPUT TYPE=RESET VALUE="Clear">
</FORM>
</HTML>

pgtips · February 4th, 2004, 08:53 AM

Your problem is that you don't use the SQL you've built! Look again at this bit of your code:

Code:

Set objRS = Server.CreateObject ("ADODB.Recordset")
' ====================================================
' here you open the recordset against the whole table
' ====================================================
objRS.Open "distdetails", strConnect, 0, 3, 0

' ====================================================
' then you build your SQL
' ====================================================
If Request.Form("Distributor") <> " " Then
    strSQL = "SELECT distributorname FROM distdetails WHERE DistributorName LIKE '%" & Request.Form("Distributor") & "%'"
End If

' ====================================================
' but you don't use that SQL so objRS contains the whole table
' ====================================================
Response.Write RecToTable (objRS)

So, you need to open your recordset after you've built the SQL and pass the SQL as the first argument in place of the table name. Also, in the objRS.open line change the 3 (adLockOptimistic) to a 1 (adLockReadOnly) and change the final 0 to a 1 (adCmdText). So it should read:

Code:

objRS.Open strSQL, strConnect, 0, 1, 1

hth
Phil

marketingmaggie · February 4th, 2004, 10:39 AM

Phil,

Thanks for the advice, and I've changed the code order as below. However, the Form doesn't even come up for me to make a selection. Certainly the table now gets written (without error messages!) as I preview the page, but it displays all the distributor names (which are in one field) but none of the full records (ie the set of fields). I seem to be getting a single column of data when what I want is a single row.

I must be missing something else.

Maggie
<%
Option Explicit
Dim strConnect
%>

<html>
<head>
<title>Find Disti 5</title>
<base target="_self">
</head>
<body>
<table border="0" cellpadding="0" cellspacing="0" width="640">
    <tr>
      <td> </td>
<%
Dim strSQL
Dim objRS, objConn

If Request.Form("Distributor") <> " " Then
strSQL = "SELECT distributorname FROM distdetails WHERE DistributorName LIKE '%" & Request.Form("Distributor") & "%'"
End If

Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open strSQL, strConnect, 0, 1, 1

Response.Write RecToTable (objRS)

objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing

%>

<FORM NAME=DistiInfo ACTION="test5matchtodatabase.asp" METHOD="POST">

        <BR>
        Select the name of the <b> Distributor</b> whose details you want to find in the box below:<BR>

<p><BR>
<BR><select size=1 name=distributor>
  <OPTION selected></OPTION>
(lots of options)
</select>

<BR><INPUT TYPE=SUBMIT VALUE="Find">&nbsp<INPUT TYPE=RESET VALUE="Clear">
</FORM>

<p>   </p>
    </tr>
  </table>

</body>

</html>

pgtips · February 4th, 2004, 11:48 AM

Sorry, I don't understand exactly what the problem is now. I'm confused about a number of things, so maybe you could shed some light on what exactly you expect to happen?

Is the sequence of events like this:
1. user is presented with a form where the distributors are listed in a select box
2. user chooses a distributor and hits Submit
3. page shows details for the distributor chosen

The questions I have are:
a) are the pages in steps 1 and 3 the same asp page (i.e. does the page post to itself)
b) in step 3 should that page also re-show the select list of distributors so the user can view details of another distributor
c) in step 1) where does the list of distributors come from? is it another recordset? Maybe you should post the code for that too.

rgds
Phil

marketingmaggie · February 4th, 2004, 11:58 AM

Hi Phil.

To answer your questions:
Is the sequence of events like this:
1. user is presented with a form where the distributors are listed in a select box
2. user chooses a distributor and hits Submit
3. page shows details for the distributor chosen
Yes, this is exactly right. When it works.

The questions I have are:
a) are the pages in steps 1 and 3 the same asp page (i.e. does the page post to itself)
Yes it does: that's in the Form Action bit.
b) in step 3 should that page also re-show the select list of distributors so the user can view details of another distributor
Yes, it should. At the moment the HTML doesn't seem to execute at all.
c) in step 1) where does the list of distributors come from? is it another recordset? Maybe you should post the code for that too.
No, the list is one I have created as an Options set text list (out of the database), and looks like:

<select size=1 name=distributor>
<OPTION selected></OPTION>
<OPTION value='2001 Electronic Components Ltd'>2001 Electronic Components Ltd</OPTION>
<OPTION value='Abacus Group plc'>Abacus Group plc</OPTION>
<OPTION value='ACAL Electronics Limited'>ACAL Electronics Limited</OPTION>
<OPTION value='ACAL Radiatron'>ACAL Radiatron</OPTION> etc.
</SELECT>

thanks
Maggie

pgtips · February 11th, 2004, 10:17 AM

Hi Maggie,

Sorry for the delay in getting back to you.

I would make a small change to only build *and execute* the SQL if you have a value in Request.Form("Distributor").

Code:

<%
Dim strSQL
Dim objRS, objConn ' YOU DON'T USE objConn SO GET RID OF IT

If Len("" & Request.Form("Distributor")) > 0 Then
    strSQL = "SELECT distributorname FROM distdetails WHERE DistributorName LIKE '%" & Request.Form("Distributor") & "%'"
    Set objRS = Server.CreateObject ("ADODB.Recordset")
    objRS.Open strSQL, strConnect, 0, 1, 1

    Response.Write RecToTable (objRS)

    objRS.Close
    Set objRS = Nothing
End If
%>

Apart from that, it just looks like you are generating some duff HTML. It appears that your <FORM> tag begins in the middle of a <table></table> pair, but there's no containing <td> for the form (I may be wrong because you don't say what HTML your function RecToTable generates).

To solve this I would work backwards from the HTML that your ASP page is generating. Use "View > Source" in the browser to see the HTML. If you can't figure out why the form isn't showing then post the HTML here and we'll take a look. Once the HTML is fixed you can then apply the changes to your ASP page to generate the working HTML.

hth
Phil

DaveGerard · February 20th, 2004, 11:49 AM

You need to check your code as follows.

If Request.Form("Distributor") <> " " Then
strSQL = "SELECT distributorname FROM distdetails WHERE DistributorName LIKE '%" & Request.Form("Distributor") & "%'"
End If

>> " " has a space in it so the code is searching for any distributor name that does not equal a space. Try this...

Distributor = request("Distributor")
If Distributor <> "" Then
strSQL = "SELECT distributorname FROM distdetails WHERE DistributorName LIKE '%" & Distributor & "%'"
End If

I usually set a variable for my request that I use throughout the page rather than requesting it over and over. Also you don't need to use "Request.Form" when "Request" works just fine. One other thing I do is break down my select statement into multiple lines whereby I can comment out my criteria when testing.

Distributor = request("Distributor")
DistributorId = request("DistributorId")
If Distributor <> "" Then
strSQL = "SELECT distributorname"
strSQL = strSQL & " FROM distdetails"
strSQL = strSQL & " WHERE DistributorName LIKE '%" & Distributor & "%'"
'strSQL = strSQL & " AND DistributorId = " & DistributorId
End If

Not sure you needed that, but someone might appreciate it. Let me know if removing the space helped.

Dave

U.N.C.L.E. · February 20th, 2004, 03:00 PM

Your SQL only returns all DistributorName that match your search criteria. To retrieve all fields of the records you would need to either specify each field your want returned in the recordset or specify to return all fields:

strSQL = "SELECT * FROM distdetails WHERE DistributorName LIKE '%" & Request.Form("Distributor") & "%'"

Notice the "*"...

marketingmaggie · February 23rd, 2004, 06:49 AM

Guys,

You are terrific! Thank you all for your suggestions. I have corrected my HTML table to include a <td> </td> pair for the form (with blushes), used Phil's revised script and UNCLE's SELECT *, and it works fine. Dave, I haven't used the space because I no longer needed to, but will check that out separately.

I'm now going to post another question, so may hear from some of you again.

Maggie