LearnAsp.com - ASP ASP.net Free Lessons

Search Search

#1 worldwide
FREE Coding Lessons
since 1996

THE BEST WAY to learn ASP & Asp.net!

Advertise Here!
click for details

Credits

Host:
DiscountASP.net

Server Admin:
The "Team"

Contact Info.
Charles M. Carroll

my Blog

[prev. Lesson] ADO: Show Table,1 param
[next Lesson] DB: Troubleshooting Part 1

Database -- Update Record

This page demonstrates the capabilities to update an existing record in a database with a SQL statement.

It is called like this:
filename=/learn/test/dbupdate.asp

<Test Script Below>


<TITLE>dbupdate.asp</TITLE>
<body bgcolor="#FFFFFF">
<HTML>
<%
on error resume next
auname=request.querystring("name")
auyear=request.querystring("year")
auID=request.querystring("ID")
Set Conn = Server.CreateObject("ADODB.Connection")
conn.open "DSN=Student;uid=student;pwd=magic"
SQLstmt = "UPDATE authors "
SQLStmt = SQLstmt & "SET Author='" & auname & "',"
SQLstmt = SQLstmt & "year_born=" & auyear 
SQLStmt = SQLStmt & " WHERE AU_ID=" & auid 
Set RS = Conn.Execute(SQLStmt)
If err.number>0 then
   response.write "VBScript Errors Occured:" & "<P>"
   response.write "Error Number=" & err.number & "<P>"
   response.write "Error Descr.=" & err.description & "<P>"
   response.write "Help Context=" & err.helpcontext & "<P>" 
   response.write "Help Path=" & err.helppath & "<P>"
   response.write "Native Error=" & err.nativeerror & "<P>"
   response.write "Source=" & err.source & "<P>"
   response.write "SQLState=" & err.sqlstate & "<P>"
else
   response.write "No problems occured!" & "<P>"
end if
IF conn.errors.count> 0 then
   response.write "Database Errors Occured" & "<P>"
for counter= 0 to conn.errors.count
   response.write "Error #" & conn.errors(counter).number & "<P>"
   response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
next
else
   response.write SQLstmt
   response.write "Everything Went Fine. Author is updated now!" & "<P>"
end if
set rstemp=nothing
Conn.Close
set conntemp=nothing
%>
</BODY>
</HTML>

To double check it is in the database try:
filename=/learn/test/db1parm.asp

<Test Script Below>


<TITLE>db1parm.asp</TITLE>
<body bgcolor="#FFFFFF">
<% 
' My ASP program that talks to a database
set conntemp=server.createobject("adodb.connection")
conntemp.open "DSN=Student;uid=student;pwd=magic"
p1=request.querystring("ID")
temp="select * from authors where AU_ID=" & p1
set rstemp=conntemp.execute(temp)
howmanyfields=rstemp.fields.count -1
%>
<table border=1>
<tr>
<% 'Put Headings On The Table of Field Names
for i=0 to howmanyfields %>
    <td><b><%=rstemp(i).name %></B></TD>
<% next %>
</tr>
<% ' Now lets grab all the records
do while not rstemp.eof %>
<tr>
<% for i = 0 to howmanyfields%>
    <td valign=top><% = rstemp(i) %></td>
<% next %>
</tr>
<% rstemp.movenext
loop
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing%>
</table>
</body>
</html>

Now try:
filename=/learn/test/dbupdate.asp

<Test Script Below>


<TITLE>dbupdate.asp</TITLE>
<body bgcolor="#FFFFFF">
<HTML>
<%
on error resume next
auname=request.querystring("name")
auyear=request.querystring("year")
auID=request.querystring("ID")
Set Conn = Server.CreateObject("ADODB.Connection")
conn.open "DSN=Student;uid=student;pwd=magic"
SQLstmt = "UPDATE authors "
SQLStmt = SQLstmt & "SET Author='" & auname & "',"
SQLstmt = SQLstmt & "year_born=" & auyear 
SQLStmt = SQLStmt & " WHERE AU_ID=" & auid 
Set RS = Conn.Execute(SQLStmt)
If err.number>0 then
   response.write "VBScript Errors Occured:" & "<P>"
   response.write "Error Number=" & err.number & "<P>"
   response.write "Error Descr.=" & err.description & "<P>"
   response.write "Help Context=" & err.helpcontext & "<P>" 
   response.write "Help Path=" & err.helppath & "<P>"
   response.write "Native Error=" & err.nativeerror & "<P>"
   response.write "Source=" & err.source & "<P>"
   response.write "SQLState=" & err.sqlstate & "<P>"
else
   response.write "No problems occured!" & "<P>"
end if
IF conn.errors.count> 0 then
   response.write "Database Errors Occured" & "<P>"
for counter= 0 to conn.errors.count
   response.write "Error #" & conn.errors(counter).number & "<P>"
   response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
next
else
   response.write SQLstmt
   response.write "Everything Went Fine. Author is updated now!" & "<P>"
end if
set rstemp=nothing
Conn.Close
set conntemp=nothing
%>
</BODY>
</HTML>

to change the "NewPerson" Birthday to 1964 again and
filename=/learn/test/dbupdate.asp

<Test Script Below>


<TITLE>dbupdate.asp</TITLE>
<body bgcolor="#FFFFFF">
<HTML>
<%
on error resume next
auname=request.querystring("name")
auyear=request.querystring("year")
auID=request.querystring("ID")
Set Conn = Server.CreateObject("ADODB.Connection")
conn.open "DSN=Student;uid=student;pwd=magic"
SQLstmt = "UPDATE authors "
SQLStmt = SQLstmt & "SET Author='" & auname & "',"
SQLstmt = SQLstmt & "year_born=" & auyear 
SQLStmt = SQLStmt & " WHERE AU_ID=" & auid 
Set RS = Conn.Execute(SQLStmt)
If err.number>0 then
   response.write "VBScript Errors Occured:" & "<P>"
   response.write "Error Number=" & err.number & "<P>"
   response.write "Error Descr.=" & err.description & "<P>"
   response.write "Help Context=" & err.helpcontext & "<P>" 
   response.write "Help Path=" & err.helppath & "<P>"
   response.write "Native Error=" & err.nativeerror & "<P>"
   response.write "Source=" & err.source & "<P>"
   response.write "SQLState=" & err.sqlstate & "<P>"
else
   response.write "No problems occured!" & "<P>"
end if
IF conn.errors.count> 0 then
   response.write "Database Errors Occured" & "<P>"
for counter= 0 to conn.errors.count
   response.write "Error #" & conn.errors(counter).number & "<P>"
   response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
next
else
   response.write SQLstmt
   response.write "Everything Went Fine. Author is updated now!" & "<P>"
end if
set rstemp=nothing
Conn.Close
set conntemp=nothing
%>
</BODY>
</HTML>

The script is:
filename=/learn/test/dbupdate.asp

<Test Script Below>


<TITLE>dbupdate.asp</TITLE>
<body bgcolor="#FFFFFF">
<HTML>
<%
on error resume next
auname=request.querystring("name")
auyear=request.querystring("year")
auID=request.querystring("ID")
Set Conn = Server.CreateObject("ADODB.Connection")
conn.open "DSN=Student;uid=student;pwd=magic"
SQLstmt = "UPDATE authors "
SQLStmt = SQLstmt & "SET Author='" & auname & "',"
SQLstmt = SQLstmt & "year_born=" & auyear 
SQLStmt = SQLStmt & " WHERE AU_ID=" & auid 
Set RS = Conn.Execute(SQLStmt)
If err.number>0 then
   response.write "VBScript Errors Occured:" & "<P>"
   response.write "Error Number=" & err.number & "<P>"
   response.write "Error Descr.=" & err.description & "<P>"
   response.write "Help Context=" & err.helpcontext & "<P>" 
   response.write "Help Path=" & err.helppath & "<P>"
   response.write "Native Error=" & err.nativeerror & "<P>"
   response.write "Source=" & err.source & "<P>"
   response.write "SQLState=" & err.sqlstate & "<P>"
else
   response.write "No problems occured!" & "<P>"
end if
IF conn.errors.count> 0 then
   response.write "Database Errors Occured" & "<P>"
for counter= 0 to conn.errors.count
   response.write "Error #" & conn.errors(counter).number & "<P>"
   response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
next
else
   response.write SQLstmt
   response.write "Everything Went Fine. Author is updated now!" & "<P>"
end if
set rstemp=nothing
Conn.Close
set conntemp=nothing
%>
</BODY>
</HTML>


	There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.