675,865 reviews,
402 today

Tekk · 08-26-2003, 11:27 PM

Well my stupid teacher didn't really explain ASP at ALL and he expects us to put it in our project soooooo Im hitting a bit of a wall here. I've tried to figure it out but its not as self-explanatory as Java was

Heres the sample code he gave us that we are supposed to edit slightly and get our result

Code:

<%@ Language=VBScript%>
< t = Request.Form("n") >
<HTML>
<HEAD>
</HEAD>
<BODY>
<% If t = "" Then %>
   <H1> Search for Customer Information</H1><P>

   <FORM ACTION = "search.asp" METHOD = "post">
      <INPUT TYPE = "TEXT" NAME = "n"><BR>
      <INPUT TYPE = "SUBMIT" VALUE = "Search">
   </FORM><BR><BR>
<% Else %>
   <H1>Information</H1>


<%
Dim objRec

   strConnect = "Driver={Microsoft Access Driver (*.MDB)};DBQ=" & Server.MapPath("customerDB.mdb")

   Set objRec = Server.CreateObject("ADODB.Recordset")
   objRec.Open "SELECT PartNumber, UnitsOnHand FROM Part WHERE PartNumber = '" & t &"'", strConnect
  
   Do While Not objRec.EOF
      countup = countup + 1
      objRec.MoveNext
   Loop

   If countup > 0 Then
      Response.Write "<B>There have been " & countup & " hits</B><P>"
      objRec.MoveFirst
      Response.Write "Customer Number || Last || First"
      Do while Not objRec.EOF
         Response.Write "<BR><B>" & objRec("CustomerNumber") & " || " & objRec("Last") & " || " & objRec("First")
         objRec.MoveNext
      Loop

   Else
      Response.Write "Sorry, no records found!<P>"
   End If
   
   objRec.Close
   Set objRec = Nothing
%>
<% End If %>
</BODY>
</HTML>

So I created the customerDB.mdb in Access and the primary key (what Im gonna search for in the search bar) is the CustomerNumber. I think everything is ok except I dont really know what to do with this section specifically

Code:

   Set objRec = Server.CreateObject("ADODB.Recordset")
   objRec.Open "SELECT PartNumber, UnitsOnHand FROM Part WHERE PartNumber = '" & t &"'", strConnect

What does this block of code do cause PartNumber and UnitsOnHand and such are from his database example so I need these to relate to my customerDB.

Can I test the ASP code on my home computer or do I need to have special software running (like a server prolly would) to be able to test this asp page. cause when I try to check it on my computer it is all messed up

So ANY help would be greatly appreciated!!!

vass0922 · 08-26-2003, 11:50 PM

1. You will require IIS (or maybe personal web server, but I wouldn't want that POS on my box

) to run this stuff.. with some edits you could run it as a vbscript but as a n00bie we'll just leave that alone

Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open "SELECT PartNumber, UnitsOnHand FROM Part WHERE PartNumber = '" & t &"'", strConnect

This code would be the same as

Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.ConnectionString = strConnect
objRec.Open "SELECT PartNumber, UnitsOnHand FROM Part WHERE PartNumber = '" & t &"'"

't' is the data you're getting from your form. Its populated from teh statement at the top

< t = Request.Form("n") >

There is a control called 'n' (bad name btw) .. this is named in the part that is like
<INPUT TYPE = "TEXT" NAME = "n"><BR>

So take the value of the text box called 'n' and place it into the variable 't' (another bad name

but assuming thats the teachers lazy coding )

The SQL does the actual work of pulling it from the DB
"SELECT PartNumber, UnitsOnHand FROM Part WHERE PartNumber = '" & t &"'",

Says get all the data from this table where the partnumber is <value from the form>

Set objRec = Server.CreateObject("ADODB.Recordset")
creates an instance of ADODB.Recordset which allows you to connect to an ODBC database.

strConnect at the end of your OPEN method tells the OPEN method where to look for your database

If you need that SQL to relate to YOUR DB then you need to get the necessary column names from your DB and put it in the sql

The format is
SELECT Column1, Column2... FROM TableName WHERE Column3 = "Some value"
OR You can put SELECT * FROM Tablename
That will get you ALL of the columns

That help?

If not let me know, maybe I can specify something more clearly

Agent_Embryo · 08-26-2003, 11:56 PM

Ok. To set it up and test it at home you need to have eighter Windows 2000 or XP with IIS installed. It's not that hard to set up.

Code:

strConnect = "Driver={Microsoft Access Driver (*.MDB)};DBQ=" & Server.MapPath("customerDB.mdb")

This is your connection string. It tells you where and how to connect to your database.

Code:

Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open "SELECT PartNumber, UnitsOnHand FROM Part WHERE PartNumber = '" & t &"'", strConnect

What this does is it connects to the database using the strConnect(connection string), then it executes an SQL Request on the database, and then it returns the records found to the recordset(objRec). PartNumber and UnitsOnHand are database tables containing the data.

Edit:

Vass beat me to it and probably explained better.

Tekk · 08-27-2003, 12:14 AM

Ok...can someone test this out for me?

Here's my db file:
http://home.earthlink.net/~wolfman32...customerDB.mdb

Here's my search.asp file:
http://home.earthlink.net/~wolfman32x/CIS311/search.asp

Agent_Embryo · 08-27-2003, 12:23 AM

Not working. Looking into why it's not working.

Tekk · 08-27-2003, 12:34 AM

thanks ae

Agent_Embryo · 08-27-2003, 12:35 AM

Ok here's what I had to do to make it work:

First off - the database. NEVER name tables with spaces in the name. It just won't work! I changed it from Customer Table to CustomerTable. I also modified the SQL code to match the new table name.

Second - I had to use a different connection string. I can't say that the one you use is wrong, but it didn't work on my configuration. I used a connection string that works for me.

Third - I changed < t = Request.Form("n") > into

<%
Dim t
t = Request.Form("n")
%>

So that the server will interpret the ASP code and not just print it to the page.

That's about it.

Good luck.

vass0922 · 08-27-2003, 12:36 AM

I suck at ASP so don't shoot me

This is the version I have

Code:

<%@ Language=VBScript%>

<HTML>
<HEAD>
</HEAD>
<BODY>

<% If Request.Form("n") = "" Then %>
   <H1> Search for Customer Information</H1><P>

   <FORM ACTION = "search.asp" METHOD = "post">
      <INPUT TYPE = "TEXT" NAME = "n"><BR>
      <INPUT TYPE = "SUBMIT" VALUE = "Search">

   </FORM><BR><BR>
<% Else 

Dim objRec
   t = Request.Form("n")
   strConnect = "Driver={Microsoft Access Driver (*.MDB)};DBQ=" & Server.MapPath("customerDB.mdb")

   Set objRec = Server.CreateObject("ADODB.Recordset")
   objRec.Open "SELECT * FROM Customer WHERE CustomerNumber = '" & t &"'", strConnect
  
   Do While Not objRec.EOF
      countup = countup + 1
      objRec.MoveNext
   Loop

   If countup > 0 Then
      Response.Write "<H1>Information</H1>"
      Response.Write "<B>There have been " & countup & " hits</B><P>"
      objRec.MoveFirst
      Response.Write "Customer Number || Last || First || Address || City || State || Zip || Balance"
      Do while Not objRec.EOF
         Response.Write "<BR><B>" & objRec("CustomerNumber") & " || " & objRec("Last") & " || " & objRec("First")
         objRec.MoveNext
      Loop

   Else
      Response.Write "Sorry, no records found!<P>"
   End If
   
   objRec.Close
   Set objRec = Nothing
%>
<% End If %>
</BODY>
</HTML>

Note: IMPORTANT
You have in your SQL that the table name is 'Customer' but in your database you have the table as "Customer Name" ... easiest thing to do would be to change the table name in Access to 'Customer' that way you don't have to deal with spaces in the table name in the SQL.

It seems to work.. but its ugly, it should be put into tables as HTML does not show whitespace.

Agent_Embryo · 08-27-2003, 12:42 AM

Oh and, to display the records in a nice layout, use tables in the "Do while Not objRec.EOF" loop.(Assuming you knwo HTML) have the <table> tag before the loop, put the captions to each column like this:

Code:

<TABLE>
<TR><TD>First</TD><TD>Last</TD></TR>
Do while Not objRec.EOF
Response.Write "<TR><TD>" &objRec("First") &"</TD><TD>" &objRec("Last") &"</TD></TR>"
Loop
</TABLE>

That will give you a professional look.

Tekk · 08-27-2003, 01:43 AM

thanks guys...finally got it working...and learned a little about the coding!!! hehe. I'll spruce up the display tomorrow, its late and I was suposed to be studying for a business law final that i have tomorrow but alas...I did not. Its too late to start now anyway, need sleepy.

Thanks again.

675,865 reviews, 402 today

675,865 reviews,
402 today