<%@ language=VBScript %>
<% option explicit %>
<!-- #INCLUDE FILE = "ASPADO.inc" -->

<%

'Multipurpose Form to Add, Edit and Update records
Const cDSN = "Driver=SQL Server;Server=130.66.76.169;Database=Pubs;UID=student;pwd=student"

Dim RA 'Records Affected 1 is good, 0 is BAD.
Dim CN 'Connection
Dim rs 'Recordset (For Select queries)
Dim AUID 'Author ID
dim ACTION 'What to do
Dim mySQL 'SQL Statement

'Get Authors ID if we went here by accident, redirect them to the authors page
AUID = trim(Request.QueryString("AUID"))
if len(AUID) = 0 then
    'Redirect method must be used before <html> element unless .buffer property used first
    Response.Redirect("AspADO1.Asp")
end if

ACTION = ucase(trim(Request.QueryString("ACTION")))

%>

<html>
<head>
<title>ASP/ADO Editing/Updating and Deleting Records</title>
</head>
<body bgcolor="#FFFFFF" text="#000000">

<%

Select case ACTION
    case "DELETE"
        'Create and open a connection object
        Set CN = Server.CreateObject("ADODB.Connection")
        CN.Open cDSN

        'Got to rid ourselves of there title/author relationship 1st
        mySQL = "Delete from TitleAuthor where (AU_ID = '" & AUID & "')"
        CN.Execute mySQL, RA
        Response.Write("<p>All of their author/titles removed.</p>")
        mySQL = "Delete from Authors where (AU_ID = '" & AUID & "')"
        CN.Execute mySQL, RA

        if RA = 1 then
            Response.Write("<p>Record deleted. Author ID: " & AUID & "</p>")
        else
            Response.Write("<p>Record NOT deleted, Contact support! Author ID: " & AUID & "</p>")
        end if

        CN.Close

    case "NEW"

        'Create a form to edit in
        response.write("<form action='AspADO2.asp?AUID=" & AUID & "&ACTION=SAVENEW' method='POST'>")
        response.write("<p>Author ID: (Social Security #)<input type='text' name='AUID'><br>")
        response.write("<p>First Name<input type='text' name='First'><br>")
        response.write("Last Name<input type='text' name='Last'></p>")
        response.write("<input type='submit' name='Submit'>")
        Response.Write("</form>")

    case "SAVENEW" 'For adding a new record

        AUID = trim(Request.Form("AUID"))
        if len(AUID) <> 11 then
            response.write("Author ID must be in the form ###-##-####, record not added.")
        else
            'Create and open a connection object
            Set CN = Server.CreateObject("ADODB.Connection")
            CN.Open cDSN
            mySQL = "Insert Into Authors (AU_ID, AU_FNAME, AU_LNAME, CONTRACT) Values ('" & AUID & "','" & Request.Form("FIRST") & "','" & Request.Form("LAST") & "',0)"
            CN.Execute mySQL,RA
            if RA = 1 then
                Response.Write("<p>Record Added.</p>")
            else
                Response.Write("<p>Record NOT deleted, Contact support! </p>")
            end if
            CN.Close
        end if

    case "SAVE" 'For saving an existing one

        'Create and open a connection object
        Set CN = Server.CreateObject("ADODB.Connection")
        CN.Open cDSN
        mySQL = "Update Authors Set AU_FNAME = '" & Request.Form("FIRST") & "', AU_LNAME = '" & Request.Form("LAST") & "' where (AU_ID = '" & AUID & "')"
        CN.Execute mySQL,RA
        if RA = 1 then
            Response.Write("<p>Record Saved. Author ID: " & AUID & "</p>")
        else
            Response.Write("<p>Record NOT Saved, Contact support! Author ID: " & AUID & "</p>")
        end if
        CN.Close

     case else

        Response.write("<h2>Edit Author</h2>")

        'Create a recordset
        Set rs = Server.CreateObject("ADODB.Recordset")

        'Specify the information we want
        mySQL = "Select au_id, au_lname, au_fname From Authors Where AU_ID = '" & AUID & "'"

        'Open a firehose cursor (connection created with recordset)
        rs.ActiveConnection = cDSN
        rs.CursorType = adOpenForwardOnly
        rs.LockType = adLockReadOnly
        rs.CursorLocation = adUseServer
        rs.Source = mySQL
        rs.Open

        'Create a form to edit in

        Response.write("<form action='AspADO2.asp?AUID=" & AUID & "&ACTION=SAVE' method='POST'>")
        Response.write("<p>Author ID: " & AUID & "</p>")
        Response.write("<p>First Name <input type='text' name='First' Value='" & rs.fields("au_fname").value & "'><br>")
        Response.write("Last Name <input type='text' name='Last' Value='" & rs.fields("au_lname").value & "'></p>")
        Response.write("<input type='submit' name='Submit'>")
        Response.Write("</form>")
        Response.write("<p><a href='AspAdo2.asp?AUID=" & AUID & "&ACTION=DELETE'>Delete This Record.</a>")

        'Don't forget to close and dispose your recordset objects
        rs.Close

end select

Response.write("<hr><p><a href='AspAdo1.asp'>Return to list of authors</a></p>")
set rs = Nothing
set CN = Nothing

%>
</body>
</html>