A really Basic ASP to Access problem that has stumped me.

[AlunD]

Guys I'm sorry to bother you with this but I'm stuck.

I'm trying to do something that OUGHT to be easy and I've looked at numerous examples but got nowhere.

5 fields in an access database table the database is called IT_Status, Table is called tbl_Status ( connection proven and working)
ID ( auto number )
Application_Name ( text )
Status ( Text 1 Char, R, A , G )
Comment ( Text )
Time_stamp ( date and Time )

I want an ASP page to call them all, display them in a tabular layout ( this I can Do ) and allow edit , delete and add. The ID and Time stamp are to be hidden and the latter being auto pop from now() in the perfect world it would also pick up the users details from the browser..

Its been years since I last had to do this sort of thing and only doing it now because the IT department can't....... I've done the rest of the work just this page left.

I've been trying to customise an example from ASP101 samples.
Thanks in advance for any assistance.

[AlunD]

The code I'm using is below

<%
'*******************************************************
'*     ASP 101 Sample Code - http://www.asp101.com/    *
'*                                                     *
'*   This code is made available as a service to our   *
'*      visitors and is provided strictly for the      *
'*               purpose of illustration.              *
'*                                                     *
'*      http://www.asp101.com/samples/license.asp      *
'*                                                     *
'* Please direct all inquiries to [e-mail] *
'*******************************************************
%>
<!-- #include file="adovbs.inc" -->
<%
Dim CONN_STRING
CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= D:\IT_Status.mdb"
 
 
' The name of this file so all the links and forms will still
' work if you rename it.
Dim SCRIPT_NAME
SCRIPT_NAME = Request.ServerVariables("SCRIPT_NAME")
 
' I use this link a lot so I threw it into a "pseudo-const"
' so I don't have to keep typing it.
Dim BACK_TO_LIST_TEXT
BACK_TO_LIST_TEXT = "<p>Click <a href=""" & SCRIPT_NAME & """>" _
    & "here</a> to go back to record list.</p>"
 
 
'ADD - partly working
 
' Declare our standard variables.
Dim cnnDBEdit, rstDBEdit  ' ADO objects
 
Dim strSQL      ' To hold various SQL Strings
Dim iRecordId   ' Used to keep track of the record in play
 
' Choose what to do by looking at the action parameter
Select Case LCase(Trim(Request.QueryString("action")))
    Case "add"
        ' Select an empty RS
        strSQL = "SELECT * FROM tbl_Status WHERE id=0;"
 
        Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
        rstDBEdit.Open strSQL, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText
 
        ' Add our record and set it's values.  You could bounce
        ' into an edit mode here to let people enter the initial
        ' values, but for simplicity I just add the record with
        ' some default values.
        rstDBEdit.AddNew
 
        rstDBEdit.Fields("Application_Name").Value      = CStr("")
        rstDBEdit.Fields("Status").Value   = CStr("")
        rstDBEdit.Fields("Comment").Value   = CStr("")
 
 
        rstDBEdit.Update
 
        ' Get the id of the record just added. This might cause
        ' problems with some DB providers, but it works with
        ' the SQL Server our sample runs off.
        iRecordId = rstDBEdit.Fields("id").Value
 
        rstDBEdit.Close
        Set rstDBEdit = Nothing
 
        Response.Write("<p>Record Id #" & iRecordId & " added!</p>")
        Response.Write(BACK_TO_LIST_TEXT)
'DELETE - this works
 
    Case "delete"
        ' Get the id to delete
        iRecordId = Request.QueryString("id")
        If IsNumeric(iRecordId) Then
            iRecordId = CLng(iRecordId)
        Else
            iRecordId = 0
        End If
 
        strSQL = "DELETE FROM tbl_Status WHERE id=" & iRecordId & ";"
 
        Set cnnDBEdit = Server.CreateObject("ADODB.Connection")
        cnnDBEdit.Open CONN_STRING
 
        cnnDBEdit.Execute strSQL, adAffectAll, adExecuteNoRecords
 
        cnnDBEdit.Close
        Set cnnDBEdit = Nothing
 
 
        Response.Write("Record Id #" & iRecordId & " deleted!")
        Response.Write(BACK_TO_LIST_TEXT)
 
 
'EDIT        - partly works
 
    Case "edit"
        ' First of a 2 part process... build a form with the
        ' values from the db.
        iRecordId = Request.QueryString("id")
        If IsNumeric(iRecordId) Then
            iRecordId = CLng(iRecordId)
        Else
            iRecordId = 0
        End If
 
        strSQL = "SELECT * FROM tbl_Status WHERE id=" & iRecordId & ";"
 
        Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
        rstDBEdit.Open strSQL, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText
 
        If Not rstDBEdit.EOF Then
            %>
                    <form action="<%= SCRIPT_NAME %>?action=editsave" method="post">
                <input type="hidden" name="id" value="<%= rstDBEdit.Fields("id").Value %>" />
                <input type="text" name="Application_Name" value="<%= Server.HTMLEncode(rstDBEdit.Fields("Application_Name").Value) %>" /><br />
                <input type="text" name="Status" value="<%= Server.HTMLEncode(rstDBEdit.Fields("Status").Value) %>" style="height: 22px" /><br />
                <input type="text" name="Comment" value="<%= Server.HTMLEncode(rstDBEdit.Fields("Comment").Value) %>" /><br />
 
                <input type="submit" name="Update Database">
            </form>
            <%
        Else
            Response.Write "Record not found!"
        End If
 
        rstDBEdit.Close
        Set rstDBEdit = Nothing
 
        Response.Write(BACK_TO_LIST_TEXT)
    Case "editsave"
        ' Part 2 of 2: Here's where we save the values that the
        ' user entered back to the DB.  Again... no error
        ' handling or input checking so ' characters and invalid
        ' values will throw error messages.
        iRecordId = Request.Form("id")
        iRecordId = Replace(iRecordId, "'", "''")
 
        ' Date delimiter on this should be changed to # for Access
        strSQL = "UPDATE tbl_Status SET " _
            & "Application_Name = '" & CStr(Replace(Request.Form("Application_Name"), "'", "''")) & "', " _
            & "Status = " & CStr(Replace(Request.Form("Status"), "'", "''")) & ", " _
            & "Comment = " & CStr(Replace(Request.Form("Comment"), "'", "''")) & ", " _
            & "WHERE (id = " & iRecordId & ")"
 
        ' If something does throw an error, checking this is
        ' actually a valid command often helps debug.
        Response.Write strSQL
 
        Set cnnDBEdit = Server.CreateObject("ADODB.Connection")
        cnnDBEdit.Open CONN_STRING
 
        cnnDBEdit.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords
 
        cnnDBEdit.Close
        Set cnnDBEdit = Nothing
 
        Response.Write("<p>Record Id #" & iRecordId & " updated!</p>")
        Response.Write(BACK_TO_LIST_TEXT)
    Case Else ' view
        ' Our default action... just lists the records in the DB
        strSQL = "SELECT * FROM tbl_Status ORDER BY id;"
 
        Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
        rstDBEdit.Open strSQL, CONN_STRING, adOpenForwardOnly, adLockReadOnly, adCmdText
        %>
        <table border="0" cellspacing="2" cellpadding="2">
        <thead>
        <tr>
 
            <th>Application Name</th>
            <th>Status</th>
            <th>Comment</th>
            <th>Delete</th>
            <th>Edit</th>
        </tr>
        </thead>
        <tbody>
        <%
        Do While Not rstDBEdit.EOF
            %>
            <tr>
 
                <td><%= rstDBEdit.Fields("Application_Name").Value %></td>
                <td><%= rstDBEdit.Fields("Status").Value %></td>
                <td><%= rstDBEdit.Fields("Comment").Value %></td>
 
                <td><a href="<%= SCRIPT_NAME %>?action=delete&id=<%= rstDBEdit.Fields("id").Value %>">Delete</a></td>
                <td><a href="<%= SCRIPT_NAME %>?action=edit&id=<%= rstDBEdit.Fields("id").Value %>">Edit</a></td>
            </tr>
            <%
            rstDBEdit.MoveNext
        Loop
        %>
        </tbody>
        <tfoot>
        <tr>
            <td colspan="6" align="right"><a href="<%= SCRIPT_NAME %>?action=add">Add a new record</a></td>
        </tr>
        </tfoot>
        </table>
        <%
        rstDBEdit.Close
        Set rstDBEdit = Nothing
End Select
%>
 
<%
'********************************
' This is the end of the sample!
'********************************
 
' Feel free to skip this area. (Ignore the man behind the curtain!)
' To keep things manageable and working for other visitors, I'm
' checking to make sure you don't delete all the records and adding
' some to keep at least 2 records in the DB.
 
Dim cnnCleanUp, rstCleanUp, iRecordCount
 
Set cnnCleanUp = Server.CreateObject("ADODB.Connection")
cnnCleanUp.Open CONN_STRING
 
strSQL = "SELECT COUNT(*) FROM tbl_status;"
 
Set rstCleanUp = cnnCleanUp.Execute(strSQL, , adCmdText)
iRecordCount = rstCleanUp.Fields(0).Value
rstCleanUp.Close
Set rstCleanUp = Nothing
 
'Response.Write iRecordCount
If iRecordCount <= 2 Then
    strSQL = "INSERT INTO tbl_Status " _
        & "(Application_Name, Status,Comment) " _
        & "VALUES (" _
        & "'" & Application_Name & "', " _
           & "'" & Status & "', " _
        & "'" & Comment & ")"
 
 
    ' Add 2
    cnnCleanUp.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords
    cnnCleanUp.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords
End If
 
cnnCleanUp.Close
Set cnnCleanUp = Nothing
%>

[jhardman]

so which part is giving you trouble?

Jared

[AlunD]

oops sorry the edit ( starts line 98 ) and add ( starts line 32 ) sections.

It falls over when it tries to send updates / additions to the database.

[jhardman]

oops sorry the edit ( starts line 98 ) and add ( starts line 32 ) sections.

It falls over when it tries to send updates / additions to the database.

Just looking at the "add" function, the code is just fine. You say it is partly working? What is the trouble? What would you like it to do? Right now it just adds a new blank record to the db. If you want to add values, it needs to say something like this (assuming the user just entered values in fields marked "application_name", "status", and "comment"):

        rstDBEdit.AddNew
 
        rstDBEdit.Fields("Application_Name").Value      = request("application_name")
        rstDBEdit.Fields("Status").Value   = request("status")
        rstDBEdit.Fields("Comment").Value   = request("comment")
        rstDBEdit.Fields("Time_Stamp").Value = now()
 
        rstDBEdit.Update

I'm not sure I understand what you are asking, but does this answer your question?

Jared

[AlunD]

Jared

Thanks for bearing with me on this.

I've made the changes you suggested.

The errors I get are
Edit error

UPDATE tbl_Status SET Application_Name = 'Printers', Status = G, Comment = except Norwich nice, WHERE (id = 24)
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'except Norwich nice'.
/systems_statusalert/input3.asp, line 155

Add error

Microsoft JET Database Engine error '80040e21'
Field 'tbl_Status.Status' cannot be a zero-length string.
/systems_statusalert/input3.asp, line 59 

[AlunD]

Solved and thanks a series of blond moments at this end.