Can't update database using sql statement

[scriptblur]

Hi guys.... can anyone please kindly help me???

i got a problem: the problem is that i can't update my database
(microsoft access) using a sql statement.
Can someone help me to see what wrong with this statement?

Thank a lot...

<% @ Language=VBScript %>
<% Option Explicit %>

<%

Dim conn,rs,sql
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("Login.mdb"))

set rs=Server.CreateObject("ADODB.Recordset")


sql = "UPDATE TheZone SET School='"&Request.Form("departments")&"', Purpose='"&Request.Form("message")&"' WHERE Week='"&Request.Form("weeks")&"' AND Day='"&Request.Form("days")&"' AND Zones='"&Request.Form("location")&"'"


rs.Open sql, conn

Response.Redirect"approved_page.asp"

%>:confused:

[glenngv]

change the line:

rs.Open sql, conn

to:

conn.execute(sql)

[BigDaddy]

Actually, you probably don't need the "set rs = ". You're not creating a recordset--you're doing an update.

[aCcodeMonkey]

scriptblur,

All you need to do is to execute the sql statement instead of "opening" a recordset.

Change: rs.Open sql, conn
To: conn.execute(sql)

The change to the code below also allows for basic error handling

<% @ Language=VBScript %>
<% Option Explicit %>
<%
Dim conn,rs,sql,ErrText
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("Login.mdb"))

On Error Resume Next

sql = "UPDATE TheZone SET School='"&Request.Form("departments")&"', Purpose='"&Request.Form("message")&"' WHERE Week='"&Request.Form("weeks")&"' AND Day='"&Request.Form("days")&"' AND Zones='"&Request.Form("location")&"'"

conn.execute(sql)

' if update fails perform error handling else go to confirmation page

if conn.errors.count > 0 then
' display popup error message & return to form page
ErrText="The following error occured updating the database:\n\n " & _
Err.Number & "\n " & Err.Description
%>
<script language="javascript">
var varErrMsg='<%=ErrText %>';
alert(varErrMsg)
top.history.go(-1)
</script>
<%
conn.close
set conn = nothing
else
conn.close
set conn = nothing
Response.Redirect"approved_page.asp"
end if

%>

Dale Mitchell (acodemonkey@hotmail.com)
MindCrafter Web Designs (http://members.aol.com/mndcrftr)

[scriptblur]

Hi guys... thank for your helps... it works.....:thumbsup: