Need help in debugging

[sunnywink]

Hi all,

I am tasked put up an access database on intranet using ASP. However, I do not have access to our server and is unable to do debugging online.

I am new to asp and VBscript programming and would really appreciate help to check the following code. Here are the functions that are to be incorporated:
1. enable user to select a record on the database located on C: drive by query
2. allow Add and Edit function on the record selected
3. redirect user to a different page to do queries

There are 4 fields in the database: Car # (an Autonumber), Region (an option box item), VQA Raised (A checkbox item) and Customer ref (A text box item)

Here are the detailed codes:

<%
' Declare variables
Dim strURL 'ADO connection
Dim cnnSearch 'ADO connection
Dim rstSearch 'ADO recordset
Dim cnnDBEdit 'ADO object
Dim rstDBEdit 'ADO object
Dim strDBPath 'path to our Access database (*.mdb) file
Dim strSQL 'The SQL query we build
Dim strSearch 'The text being looked for
Dim iRecordID 'keep track of record in play

Dim BACK_TO_LIST_TEXT
BACK_TO_LIST_TEXT = &quot;<p>Click <a href=&quot;&quot;&quot; &strURL & &quot;&quot;&quot;>&quot;_
& &quot;here</a> to go back to record list.</p>&quot;

'FIRST PART; ENABLE USER TO CHOOSE A RECORD FROM DATABASE

'Retrieve URL of this page from server variables so all links and forms will still work if we rename it
strURL = Request.ServerVariables(&quot;URL&quot

'Retrive term searched for by using query strings
strSearch = Request.Querystring(&quot;Search&quot
strSearch = Replace(strSearch,&quot; ' &quot;, &quot; ' ' &quot
%>


'Confirm if search value entered, else show search form and quit
<p>Search our database by CAR#. (% returns all)</p>
<form action = &quot;<%=strURL%>&quot;method=&quot;get&quot;>
<input name = &quot;search&quot; value=&quot;<%=strSearch%>&quot; />
<input type = &quot;submit&quot; />
</form>
<p>(Try Eg. '1','10' etc)</p>


<%
If strSearch<> &quot;&quot; Then

'Open connection to Access database, DSN-less connection
Set cnnSearch=Server.CreateObject(&quot;ADODB.connection&quot
cnnSearch.provider=&quot;Microsoft.Jet.OLEDB.4.0&quot;
cnnSearch.open&quot;C:\NoSupt\Practice_Access\Car2003&quot;

'Build our query based on input
strSQL = &quot;SELECT * &quot;_
& &quot;FROM CAR_Table&quot;_
& &quot;WHERE Car # LIKE'%&quot; & Replace(strSearch,&quot; ' &quot;,&quot; ' ' &quot&&quot;%'&quot;_
& &quot;ORDER BY Car #;&quot;


'Execute our query using connection object. It automatically create and returns a recordset # we store in our viarable.
Set rstSearch = cnnSearch.Execute(strSQL)

'Display table of data in the recordset. Loop through recordset displaying fields from table and use.
'MoveNext function to increment next record. We stop when we reach EOF.
%>


<table border=&quot;1&quot;>
<thead>
<tr>
<td colspan=&quot;6&quot; align=&quot;right&quot;><a href=&quot;<%= strURL%>?action=add&quot;>Add a new record</a></td>
</tr>
<tr>
<th>ASPAS Ref.</th>
<th>Region</th>
<th>VQA Raised</th>
<th>Customer Ref.</th>
<th>Edit</th>
</tr>
</thead>

<tbody>
<%
Do While Not rstSearch.EOF
%>


<tr>
<td><%=rstSearch.Fields(Car #).Value%></td>
<td><%=rstSearch.Fields(Region).Value%></td>
<td><%=rstSearch.Fields(VQA Raised).Value%></td>
<td><%=rstSearch.Fields(Customer Ref).Value%></td>
<td><a href=&quot;<%=strURL%>?action=edit&id=<%rstDBEdit.Fields(&quot;Car #&quot.Value%>&quot;>Edit</a></td>
</tr>


<%
rstSearch.MoveNext
Loop
%>
</tbody>
</table>

<%
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>



'2ND PART: PUT IN THE ADD AND EDIT FUNCTIONS WHICH ENABLE USER TO CLICK ON AND ENTER FORM

'incoporate Add and Edit functions
'Choose what to do by looking at action parameter
Select Case LCase(Trim(Request.Querystring(&quot;action&quot))


Case &quot;add&quot;

'Select an empty RS
strSQL = &quot;SELECT * From CAR_Table WHERE Car # = 0;&quot;
Set rstDBEdit = Server.CreateObject(&quot;ADODB.Recordset&quot
rst DBEdit.Open strSQL, cnnSearch, adOpenKeyset, adLockOptimistic, adCmdText

'Add new record
rstDBEdit.AddNew
'Get the id of the record just added
iRecordId = rstDBEdit.Fields(&quot;Car #&quot.Value



Case &quot;edit&quot;

iRecordId=Request.QueryString(&quot;Car #&quot
If IsNumeric(iRecordId)Then
iRecordId=CLng(iRecordId)
Else
iRecordId=0
End If
strSQL = &quot;SELECT * FROM CAR_Table WHERE Car # = &quot; & iRecordId & &quot;;&quot;
Set rstDBEdit = Server.CreateObject(&quot;ADODB.Recordset&quot
rst DBEdit.Open strSQL, cnnSearch, adOpenKeyset, adLockOptimistic, adCmdText


'SHOW THE FORM FOR USER TO EDIT WITH VALUES PREVIOUSLY ENTERED

<%
If Not rstDBEdit.EOF Then
%>
<form action&quot;<%=strURL%>?action=editsave&quot; method=&quot;post&quot;>
<input type=&quot;hidden&quot; name=&quot;ASPAS Ref.&quot; value=&quot;<%=rstDBEdit.Fields(&quot;Car #&quot.Value%>&quot;/>
Region:
<select name=&quot;Region&quot;>
<option value=&quot;Singapore&quot;>Singapore
<option value=&quot;Malaysia&quot;>Malaysia
<option value=&quot;Taiwan&quot;>Taiwan
<option value=&quot;China&quot;>China
<option value=&quot;Hong Kong&quot;>Hong Kong
<option value=&quot;ROA&quot;>ROA
<option value=&quot;Korea&quot;>Korea
<option value=&quot;Japan&quot;>Japan
</select>
<br/>

VQA Raised:
<input type=&quot;checkbox&quot; name=&quot;VQA Raised&quot;>
<br/>

Customer Ref:
<input type=&quot;text&quot; name=&quot;Customer Ref&quot; value=&quot;<%= Server.HTMLEncode(rstDBEdit.Fields(&quot;Customer Ref&quot.Value)%>&quot;/><br/>
<input type=&quot;submit&quot; name=&quot;Update Database&quot;>
</form>

<%
Else
Resonse.Write&quot;Record not found!&quot;
End If

rstDBEdit.Close
Set rstDBEdit = Nothing

Response.Write(BACK_TO_LIST_TEXT)


'save values entered back to access database
Case &quot;editsave&quot;

'Save values entered by user back to DB
iRecordId = Request.Form(&quot;Car #&quot
iRecordId = Replace(iRecordId, &quot; ' &quot;, &quot; ' ' &quot

'change date delimiter on this to # for Access
strSQL = &quot;UPDATE CAR_Table SET&quot;_
& &quot;Region = '&quot; & CStr(Replace(Request.Form(&quot;Region&quot, &quot; ' &quot;, &quot; ' ' &quot)_
& &quot;VQA Raised='&quot; & CStr(Replace(Request.Form(&quot;VQA Raised&quot, &quot; ' &quot;, &quot; ' ' &quot)_
& &quot;Customer Ref='&quot; & CStr(Replace(Request.Form(&quot;Customer Ref&quot, &quot; ' &quot;, &quot; ' ' &quot)_
& &quot;WHERE (Car # = &quot; & iRecordId & &quot&quot;

Set cnnDBEdit = Server.CreateObject(&quot;ADODB.Connection&quot
cnnDBEdit.Open cnnSearch

cnnDBEdit.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords

cnnDBEdit.Close
Set cnnDBEdit = Nothing

Response.Write(&quot;<p>Car #&quot; & iRecordId & &quot; has been updated</p>&quot
Response.Write(BACK_TO_LIST_TEXT)


'view the end results, lists the records in the DB in a table with Add and Edit functions
Case Else
strSQL = &quot;SELECT * FROM CAR_Table ORDER BY Car #;&quot;

Set rstDBEdit = Server.CreateObject(&quot;ADODB.Recordset&quot
rstDBEdit.Open strSQL, cnnSearch, adOpenForwardOnly, adLockReadOnly, adCmdT
%>

<table border=&quot;1&quot;>
<thead>
<tr>
<td colspan=&quot;6&quot; align=&quot;right&quot;><a href=&quot;<%= strURL%>?action=add&quot;>Add a new record</a></td>
</tr>
<tr>
<th>ASPAS Ref.</th>
<th>Region</th>
<th>VQA Raised</th>
<th>Customer Ref.</th>
<th>Edit</th>
</tr>
</thead>

<tbody>
<%
Do While Not rstDBEdit.EOF
%>


<tr>
<td><%=rstDBEdit.Fields(Car #).Value%></td>
<td><%=rstDBEdit.Fields(Region).Value%></td>
<td><%=rstDBEdit.Fields(VQA Raised).Value%></td>
<td><%=rstDBEdit.Fields(Customer Ref).Value%></td>
<td><a href=&quot;<%=strURL%>?action=edit&id=<%rstDBEdit.Fields(&quot;Car #&quot.Value%>&quot;>Edit</a></td>
</tr>

<%
rstSearch.MoveNext
Loop
%>
</tbody>
</table>

<%
rstDBEdit.Close
Set rstDBEdit=Nothing
End Select
%>



'QUERYING, REDIRECT USER TO A DIFFERENT URL TO EXECUTE QUERIES
<%
If Request.Form(&quot;Select&quot<>&quot;&quot; Then
Response.Redirect(Request.Form(&quot;Select&quot)
End If
%>

'insert checkbox containing prompt to redirect the user to queries_redirect.asp
<form action=&quot;queries_redirect.asp&quot; method=&quot;post&quot;>
QUERIES<br>
<input type= &quot;checkbox&quot; name = &quot;Select&quot; value= &quot;queries_redirect.asp&quot;>
<input type=&quot;submit&quot; value=&quot;Go&quot;>
</form>


Many thanks in advance.