Discussion Board - How to delete the discussion?

Step 1 – We need to list out all the discussions in a table:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "Discuss",conn,1,3

do while not rs.eof

response.write("

" & _

"

" & _

"

" & _

"

" & _

"

")

rs.movenext

loop

rs.close

set rs = nothing

set conn = nothing

%>

User	Title	Post Date
" & rs.fields("User") & "	" & rs.fields("Title") & "	" & rs.fields("PostDate") & "

Step 2 – we need to let user to select which record that they need to delete:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "Discuss",conn,1,3

do while not rs.eof

response.write("

" & _

"

" & _

"

" & _

"

" & _

"

")

rs.movenext

loop

rs.close

set rs = nothing

set conn = nothing

%>

User	Title	Post Date
" & rs.fields("User") & "	" & rs.fields("Title") & "	" & rs.fields("PostDate") & "

Step 3 – We need to use the ASP script to delete the record in database:

<%

if request.form("SubmitButton")="Delete" then ‘When the user click the Submit button

if request.form("C1").count then ‘Is the user has select any record

for i=1 to request.form("C1").count ‘If Yes, use the looping to get each ID

OrderID = request.form("C1")(i)

strSQL="DELETE FROM Discuss WHERE ID="&OrderID ‘Generate the SQL

conn.execute strSQL ‘Delete the record

next

end if

end if

%>

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "Discuss",conn,1,3

do while not rs.eof

response.write("

" & _

"

" & _

"

" & _

"

" & _

"

")

rs.movenext

loop

rs.close

set rs = nothing

set conn = nothing

%>

User	Title	Post Date
" & rs.fields("User") & "	" & rs.fields("Title") & "	" & rs.fields("PostDate") & "

Copyright © 2001-02 ISC Software. All rights reserved. All other product and brand names are registered properties of their respective owners.

Discussion Board - How to show the details of the discussion?

When we get the ID, How can we get the information in database?

Step 1 – We need to use Request.QueryString function to get the ID that the user selected:

intID=request.querystring("id")

Step 2 – Use the ID to generate a SQL command to get the record:

strSQL= "SELECT * FROM Discuss WHERE ID=" & intID

Step 3 – use the SQL to open a RecordSet:

<%

intID=request.querystring("id")

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL= "SELECT * FROM Discuss WHERE ID=" & intID

rs.open strSQL,conn,1,3

%>

Step 4 – Now you can get the data:

<%

intID=request.querystring("id")

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL= "SELECT * FROM Discuss WHERE ID=" & intID

rs.open strSQL,conn,1,3

If NOt rs.EOF then

strUser = rs.fields("User")

strTitle = rs.fields("Title")

strContent = rs.fields("Content")

strPostDate = rs.fields("PostDate")

end if

%>

Step 5– Display the Data:

<%

intID=request.querystring("id")

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL= "SELECT * FROM Discuss WHERE ID=" & intID

rs.open strSQL,conn,1,3

If NOt rs.EOF then

strUser = rs.fields("User")

strTitle = rs.fields("Title")

strContent = rs.fields("Content")

strPostDate = rs.fields("PostDate")

end if

%>

User	<%=strUser%>
Post Date	<%=strPostDate%>
Title	<%=strTitle%>
Message	<%=strContent%>

Discussion Board - How to create an index of the discussion?

Step 1 – We need to list out all the discussions in a table:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "Discuss",conn,1,3

do while not rs.eof

response.write("

" & _

"

" & _

"

" & _

"

" & _

"

")

rs.movenext

loop

rs.close

set rs = nothing

set conn = nothing

%>

User	Title	Post Date
" & rs.fields("User") & "	" & rs.fields("Title") & "	" & rs.fields("PostDate") & "

Step 2 – We need to create a link that will show the details of the discussion.

In the HTML code, we need to create a hyperlink. So we need to use this command:

Now, the problem is, you don’t know How many links you need to create. So you need to use the ASP script!

First, we can use the Looping to create all the links:

<% For I = 1 to 10 %>

<%Next%>

But, this example only can generate 10 links to show the same file! So How can we let the user to select the different discussion titles and show the contents?

First, we understand that each discussion has its own ID, so we can use the ID to identify it.

Now, we can try to create a link like this:

‘1 is the ID of the Discuss

To create a dynamic one:

<%=rs.fields("ID")%>’> ‘We can get the ID from the database

The complete example is shown below:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "Discuss",conn,1,3

do while not rs.eof

response.write("

" & _

"

" & _

"

" & _

"

" & _

"

")

rs.movenext

loop

rs.close

set rs = nothing

set conn = nothing

%>

User	Title	Post Date
" & rs.fields("User") & "	" & rs.fields("Title") & "	" & rs.fields("PostDate") & "

Discussion Board - How to save the ASP form?

We need to get the information from the form that is posted by the user. We can save it to the database:

Step 1 – How to get the information from the form?

ASP provided the Request.Form to get the content of the form.

How to do it?

<%

strUser = Request.Form("user")

strTitle = Request Form("title")

strContent = Request.Form("content")

%>

Step 2 – Create the ADO Connection and Recordset:

The following step allows you to create the ADO Connection and Recordset and use it to save the information to the database:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

%>

Step 3 – Open the table and add the record:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.Open "Discuss", conn, 1,3

rs.AddNew

rs.Fields("User") = Request.Form("user")

rs.Fields("Title") = Request.Form("title")

rs.Fields("Content") = Request.Form("content")

rs.Fields("PostDate") = Now()

rs.Update

rs.Close

Set rs = nothing

Set conn = nothing

%>

The finally script is:

<%@LANGUAGE="VBScript"%>

<%

'First We get the value from the submit form

strUser = Request.form("user")

strTitle= Request.form("title")

strContent = Request.form("content")

%>

Post Your Message

---

<%

If strUser="" or strTitle="" or strContent="" Then

%>

User	>
Title	>
Message	<%=strContent%>

<%

Else

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.Open "Discuss", conn, 1,3

rs.AddNew

rs.Fields("User") = Request.Form("user")

rs.Fields("Title") = Request.Form("title")

rs.Fields("Content") = Request.Form("content")

rs.Fields("PostDate") = Now()

rs.Update

rs.Close

Set rs = nothing

Set conn = nothing

End If

%>

Discussion Board - How to avoid the user from sending an incomplete form?

When we write a script, we need to make sure that the user had completed the form.

So How can we avoid the user from sending an incomplete form?

Below are the ASP scripts and descriptions:

<%@LANGUAGE="VBScript"%>

<%

'First we get the value from the submit form

strUser = Request.form("user")

strTitle= Request.form("title")

strContent = Request.form("content")

%>

Post Your Message

---

<%

‘Now, we check any variable is empty, if any variable is empty, which means the form is incomplete! We need to display the form again.

‘Another case is when we start a new discussion, all variable will be empty

If strUser="" or strTitle="" or strContent="" Then

%>

User	>
Title	>
Message	<%=strContent%>

<%

Else

%>

We will save this message.

<%

End If

%>

When the user had not completed the form, the system will return a same form to the user after the user clicked on the Post button:

When the user had completed the form, this message will be displayed:

Discussion Board - How to post the first Discussion?

First, we need to create a form to let user post the discussion to the web server:

<%@LANGUAGE="VBScript"%>

Post Your Message

---

User
Title
Message

When you submit this form, the destination will be the same file if we set the Action parameter to the same file name:

>

Set the input type to Submit but not the Button. Only when the input type is Submit the form, it will then send the Input value to web server:

Discussion Board - How to create an Access database?

We need to create an Access database to be used in the Discussion Board:

Step 1 – Create mdb file call db1.mdb

Step 2 – Create a table in the database called Discuss

Step 3 – Insert the fields in the table as shown in the following table:

Field Name	Data Type
ID	AutoNumber
User	Text
Post Date	Date/Time
Title	Text
Content	Memo

How to solve the LockType problem in ASP scripts?

Below is the ASP scripts which contain LockType problem:

<%

const adOpenReadOnly = 1

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.Open "Stock",conn,1,adOpenReadOnly ‘The error will occur here

rs.addNew

rs.fields("ProductName") = "Music"

rs.fields("Price") = 12.30

rs.update

rs.Close

Set rs = Nothing

%>

The problem in this script is the LockType is set to the adOpenReadOnly.

In this kind of Lock type, we can make any modification or update the information in the recordset. So change the LockType will solve the problem.

What will cause error Data-Type-Mismatch while using this SQL?

If the following SQL command is used in the ASP scripts:

strSQL= "SELECT * FROM Discuss WHERE ID='" & intID & "'"

We will get this error message:

Actually, this is a correct SQL. But why we still have this problem?

Let us see what is the difference in between the following SQL example:

SELECT * FROM Discuss WHERE ID='1'

And

SELECT * FROM Discuss WHERE ID=1

The first Command, the ID is a string; in the following command, the ID is an integer.

Now you know what is wrong in that example.

In the database, if the ID is integer:

We use this SQL to get the information

SELECT * FROM Discuss WHERE ID='1'

So the "type mismatch" error will come up.

What will happen if I save the data in different datatype?

This will cause an error when we use a different format of datatype.

For example:

The "price" data type is Currency. When you perform "Add" function by trying to add the word "One" to these fields, the system will not allow you to do it:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.Open "Stock",conn,1,3

rs.addNew

rs.fields("ProductName") = "Music"

rs.fields("Price") = "One sen"

rs.update

rs.Close

Set rs = Nothing

%>

The error message as below will be shown:

So, please do not use a different or inappropriate format of "datatype".

What can I do if this error Fetching-Backward occurred?

Error found in this ASP scripts:

<%

Const adOpenForwardOnly = 0

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.Open "Member",conn,adOpenForwardOnly

if Not rs.EOF then

rs.movelast ‘The error will occur here

End if

rs.Close

Set rs = Nothing

%>

The problem of this script was that: we set the CursorType to adOpenForwardOnly. We can use the MoveFirst and MoveNext in any kind of CursorType.

However, the MovePrevious and MoveLast cannot be used when the CursorType is adOpenForwardOnly.

What can I do if error Cannot-Find-Input-Table occurred?

Field name typo error happened in the ASP codes below:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL ="SELECT MemberName FROM Members;" ‘The Error is occur here.

rs.Open strSQL,conn,1,3

..

..

rs.Close

Set rs = Nothing

%>

When this error occured, which means you used an invalid table name, or the table name is not in the database, you will need to correct that specific ASP file to avoid the error message as shown at the diagram above.

What can I do if the error Too-Few-Parameters occurred?

The error of wrong field name normally happened in this type of ASP scripts:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL ="SELECT MembersName FROM Member;" 'The error occured&ldots;

rs.Open strSQL,conn,1,3

If Not rs.EOF Then

Response.Write("The first record is " & rs.fields(0) & "
")

End If

rs.Close

Set rs = Nothing

%>

This kind of error occurred frequently while you used a wrong field name to call the value in the table.

For example:

When your table contains a field name call "MemberName", but you mistype "MembersName", errors will occured.

Another mistake will be made, such as, you used the field name that was never used in the table.

When you see the error message as shown at the diagram above, you will need to correct the ASP codes to avoid the error message.

How to transfer the table (in a recordset) into the array?

We can transfer the table (in a recordset) into the array.

The ASP codes are written and described as below:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "Member",conn,1,3 'Open a Recordset that can perform batch processing

MyArray = rs.getrows

rs.Close

Set rs = Nothing

response.write(MyArray(1,2))

'The first number identify the column(field) in the table and the second identify each record in that column.

%>

How to execute batch updates with ADO?

Normally, when we process a task, we need to update to the database. For example:

<%

rs.AddNew

rs.fields("MemberName") = "C.K."

rs.fields("Location") = "SG"

rs.Update

%>

But, can we perform few tasks, and update only one time?

The ASP codes are written and described as below:

<%

const adLockBatchOptimistic=4 'Use only when doing the batch processing.

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "Member",conn,1,adLockBatchOptimistic 'Open a Recordset that can perform batch processing

rs.AddNew

rs.Fields("MemberName")="Alvin"

rs.Fields("Location")="UK"

rs.AddNew 'Add another record again

rs.Fields("MemberName")="Eric"

rs.Fields("Location")="UK"

rs.UpdateBatch 'Save all new data.

rs.Close

Set rs = Nothing

%>

If you want to cancel the process, you can use the CancelBatch to cancel it.

How to get the first record by using SQL?

Sometimes, we only need to use the first record. However, normally what we do is to query all records and use the first one only.

Now we can use the SQL command to query the first record only:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL ="SELECT First(Price) As FirstRecortd FROM Stock;" 'This SQL we only return a single records (first record)

rs.Open strSQL,conn,1,3

If Not rs.EOF Then

Response.Write("The first record is " & rs.fields("FirstRecortd") & "
")

End If

rs.Close

Set rs = Nothing

%>

Or, we can get the last record:

SELECT Last(Price) As FirstRecortd FROM Stock;

How to get the Maximum value in the same column?

You can also get the maximum value in the same column of the table.

The ASP codes are written and described as below:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL ="SELECT Max(Price) As MinPrice FROM Stock;" 'Select a single record that is highest price in the stock.

rs.Open strSQL,conn,1,3

If Not rs.EOF Then

Response.Write("The highest price in the Stock is " & rs.fields("MinPrice") & "
")

End If

rs.Close

Set rs = Nothing

%>

How to get the minimum value in the same column?

You can get the minimum value in the same column on the table.

The ASP codes are written and described as below:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL ="SELECT Min(Price) As MinPrice FROM Stock;" 'Select a single record that is lowest price in the stock.

rs.Open strSQL,conn,1,3

If Not rs.EOF Then

Response.Write("The lowest price in the Stock is " & rs.fields("MinPrice") & "
")

End If

rs.Close

Set rs = Nothing

%>

How to count the total record queried by SQL?

Normally, we can use the RecordCount to get the total record in a Recordset. But the problem is that you only need the total record and do not need to query all records to the Recordset.

In this case, Count command in the SQL can help you:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strMember="Jack"

strSQL ="SELECT Count(*) As CountItem FROM Sales WHERE Buyer='" & strMember & "';" 'Select all sales transaction that make by a member; and count How many thing that the member purchase.

rs.Open strSQL,conn,1,3

If Not rs.EOF Then

Response.Write("The total thing that purchase by the member is " & rs.fields("CountItem") & "
")

End If

rs.Close

Set rs = Nothing

%>

How to get the total amount in fields?

When we need to get the total amount, normally we need to use the for..loop to add it one by one.

For example:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strMember="Jack"

strSQL ="SELECT * FROM Sales WHERE Buyer='" & strMember & "';" 'Select all sales transaction that make by a member.

rs.Open strSQL,conn,1,3

Do while Not rs.eof

vntTotal = vntTotal + rs.fields("Price")

rs.MoveNext

Loop

rs.Close

Set rs = Nothing

Response.Write("The total payment is " & vntTotal & "
")

%>

Or, we use the SQL command to return to the total of value:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strMember="Jack"

strSQL ="SELECT Sum(*) As TotalAmount FROM Sales WHERE Buyer='" & strMember & "';" 'Select all sales transaction that make by a member; and sum the price that the member need to pay.

rs.Open strSQL,conn,1,3

If Not rs.EOF Then

Response.Write("The total payment is " & rs.fields("TotalAmount") & "
")

End If

rs.Close

Set rs = Nothing

%>

How to select the top 10 records by using SQL?

When using SQL to get a set of record which you only need the first few records; we can use TOP command in the SQL to get few records at the top:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL = "SELECT Top 2 * FROM Member" 'Generate a SQL command to get the top 2 records.

rs.Open strSQL,conn,1,3 'Use the SQL to generate the Recordset

Do while Not rs.eof

Response.Write(rs("MemberName") & "
")

rs.movenext

Loop

rs.Close

Set rs = Nothing

%>

We can also select the percentage of the record that is needed to show.

For example:

SELECT Top 10 Percent * FROM Stocks Order By Price DESC

* Query the top 10 percent record that the price is high.

How to sort the record by using SQL command?

We can sort the record by using SQL command.

The ASP codes are written and described as below:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL = "SELECT * FROM Member ORDER BY Location" 'Generate a SQL command to sort the Location fields in Ascending order.

rs.Open strSQL,conn,1,3 'Use the SQL to generate the Recordset

If rs.EOF Then 'If Eof is True; that means is empty recordset

%>

<%

Else

Do while Not rs.eof

%>

<%

rs.movenext

Loop

End If

rs.Close

Set rs = Nothing

%>

No member...
<%=rs("MemID")%>	<%=rs("MemberName")%>	<%=rs("Location")%>

If you need to perform the descending order, you can add a word "DESC" in the back of SQL command, such as:

SELECT * FROM Member ORDER BY Location DESC

How to clear the Sort Properties?

We can clear the "Sort" properties.

The ASP codes are written and described as below:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.CursorLocation = 3 'Set the CursorLocation = adUseClient

rs.Open "Member",conn,1,3

%>

<%

rs.Sort = "Location ASC" 'Sorting the Location fields in Ascending

If rs.EOF Then 'If Eof is True; that means is not record

%>

<%

Else

Do while Not rs.eof

%>

<%

rs.movenext

Loop

End If

%>

No member...
<%=rs("MemID")%>	<%=rs("MemberName")%>	<%=rs("Location")%>

<%

rs.Sort = "" 'Now we clear the Sort setup

If rs.EOF Then

%>

<%

Else

Do while Not rs.eof

%>

<%

rs.movenext

Loop

End If

rs.Close

Set rs = Nothing

%>

No member...
<%=rs("MemID")%>	<%=rs("MemberName")%>	<%=rs("Location")%>