Computers & ProgrammingADOBackend Development

ADO Updating Records in a Database

Updating database records is a very common process that any typical ASP web application would include. In this tutorial, we will take a look at some sample ADO/ASP codes that can be used to update records in the database.

SQL UPDATE Statement

Structured Query Language also known as ‘SQL‘ is the standard language used with most modern database applications. SQL provides us with a mechanism to select, insert, update and modify records in a database.

In this summary, we are going to focus on the SQL UPDATE statement. For more in-depth information about SQL, please visit the SQL section of this site. There are dozens of tutorials to help you better understand the SQL language.

Syntax

UPDATE tableName SET fieldName='value' WHERE [fieldName] = 'value'

Examples

In this tutorial, we will take a look at two ASP pages. The first example is an ASP page that a user visits to display the records from a database. A user can click on the button for the employee record that they wish to update. Here is the HTML code for the first page.

<!DOCTYPE html>
<html>
<head>
    <title>Employees</title>
    <style type="text/css">
        table {
            border:1px solid #ABABAB;
            border-collapse:collapse;
            color:#4F4F4F;
        }
        th {
            border:1px solid #ABABAB;
            background-color:#5F5F5F;
            color:#FFFFFF;padding:5px;
        }
        tr {
            border:1px solid #ABABAB;
        }
        td {
            border:1px solid #ABABAB;
            padding:5px;
        }
        .btn {
            width:25px;
            border:1px solid gray;
        }
    </style>
</head>
<body>
    <%
    Dim oConn, oRs, ds
    ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
    Set oConn=Server.CreateObject("ADODB.Connection")
    OConn.Open ds
    Set oRs=Server.CreateObject("ADODB.Recordset")
    oRs.open "SELECT * FROM employees",oConn
    %>
    <h1>Employees</h1>
    <table>
        <tr>
            <%
            for each x in oRs.Fields
                Response.Write("<th>" & UCase(x.name) & "</th>")
            next
            %>
        </tr>
        <% do until oRs.EOF %>
        <tr>
            <form method="post" action="employeeUpdate.asp">
                <%
                for each x in oRs.Fields
                    if lcase(x.name)="id" then%>
                        <td>
                            <input type="submit" name="id" class="btn" value="<%=x.value%>">
                        </td>
                    <%else%>
                        <td><%Response.Write(x.value)%></td>
                    <%end if
                next
                %>
            </form>
            <%oRs.MoveNext%>
        </tr>
        <%
        loop
        oConn.close
        %>
    </table>
</body>
</html>
a6b5ba5792594cf68ad3b9913fbb1547

When the user clicks on the button in the “ID” field, the user will be taken to a page called employeeUpdate.asp. The following example includes the ASP/ADO code that is needed to present the user with the required input elements so that the information about the specific record can be updated in the database record.

<!DOCTYPE html>
<html>
<head>
    <title>Update</title>
    <style type="text/css">
        table {
            border:1px solid #ABABAB;
            border-collapse:collapse;
            color:#4F4F4F;
        }
        tr {
            border:1px solid #ABABAB;
        }
        td {
            border:1px solid #ABABAB;
            padding:5px;
        }
        .btn {
            border:1px solid gray;
        }
    </style>
</head>
<body>
    <%
    Dim oConn, oRs, empID, sql
    ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
    Set oConn=Server.CreateObject("ADODB.Connection")
    OConn.Open ds
    empID = Request.Form("id")

    If Request.Form("empName")="" then
        Set oRs=Server.CreateObject("ADODB.Recordset")
        oRs.open "SELECT * FROM employees WHERE id ='" & empID & "'",oConn
        %>
        <form method="post" action="employeeUpdate.asp">
            <table>
                <%for each x in oRs.Fields%>
                    <tr>
                        <td><%=x.name%></td>
                        <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
                    </tr>
                <%next%>    
            </table>
            <br /><br />
            <input type="submit" class="btn" value="Update record">
        </form>
    <%
    Else
        sql="UPDATE employees SET "
        sql=sql & "empName ='" & Request.Form("empName") & "',"
        sql=sql & "empTitle ='" & Request.Form("empTitle") & "'"
        sql=sql & " WHERE id ='" & empID & "'"
        on error resume next
        oConn.Execute sql
        If err<>0 then
            Response.Write("Error updating Record!")
        Else
            Response.Write("Record " & empID & " was updated!")
        End If
    End if
    oConn.close
    %>
</body>
</html>

Leave a Comment

Your email address will not be published.

Scroll to Top