updating database problem

nazra · July 20th, 2003, 09:46 PM

hi...
i had create a new page to update my database...went i run it,there is no error....the problem is my database is NOT update
please help me

here my coding for update
-------------------------------------

<html>
<body>

<body bgcolor="#FEF1D6">
<h2>Update Record</h2>

<%

dim data_source
dim sql_update
dim conn

data_source = myDSN
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/inetpub/wwwroot/staff/pracre.mdb"

icpra=Request.Form("IC")

if Request.form("NAME_PRACTICAL")="" then
  set rs=Server.CreateObject("ADODB.Recordset")
  rs.open "SELECT * FROM praktikal WHERE IC='" & icpra & "'",conn
  %>
  <form method="post" action="updpraktikal.asp">
  <table>

  <%for each x in rs.Fields%>

  <tr>
  <td><%=x.name%></td>
  <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
  <%next%>
  </tr>
  </table>
  <br /><br />
  <input type="submit" value="Update record">
  </form>
<%
else

sql_update="UPDATE praktikal SET "
  sql=sql & "NAME_PRACTICAL='" & Request.Form("namepra") & "',"
  sql=sql & "ADDRESS='" & Request.Form("address") & "',"
  sql=sql & "DATE_REQUIRED='" & Request.Form("date_required") & "',"
  sql=sql & "DUTIES='" & Request.Form("duties") & "',"
  sql=sql & "QUALIFICATION='" & Request.Form("qualification") & "',"
  sql=sql & "SKILL_REQUIRED='" & Request.Form("skill") & "',"
  sql=sql & "EXPERIENCE_REQUIRED='" & Request.Form("experience") & "',"
  sql=sql & "SPECIAL_INSTRUCTION='" & Request.Form("special") & "',"
  sql=sql & "DEPT_BRANCH='" & Request.Form("dept_branch") & "',"
  sql=sql & "STARTDATE='" & Request.Form("startdt") & "',"
  sql=sql & " WHERE IC='" & icpra & "'"

  on error resume next
  conn.Execute sql_update
  conn.close
  Set conn = Nothing

  if error<>0 then
    response.write("No update permissions!")
  else
    response.write("Record " & icpra & " was updated!")
conn.updatebatch
  end if
end if

%>
</body>
</html>

nazra

bluckcuck · July 21st, 2003, 09:48 AM

I would recommend removing the "on error resume next" and tell us what errors (if any) are happening.

We might then be able to help you figure it out.

Also, it looks to me like your form field names in your update SQL don't match your column names. Since you're building the form with all of inputs named the same as your column names, I would be expecting them to match.

In your code, the bolded items seem suspect

  sql_update="UPDATE praktikal SET "
  sql=sql & "NAME_PRACTICAL='" & Request.Form("namepra") & "',"
  sql=sql & "ADDRESS='" & Request.Form("address") & "',"
  sql=sql & "DATE_REQUIRED='" & Request.Form("date_required") & "',"
  sql=sql & "DUTIES='" & Request.Form("duties") & "',"
  sql=sql & "QUALIFICATION='" & Request.Form("qualification") & "',"
  sql=sql & "SKILL_REQUIRED='" & Request.Form("skill") & "',"
  sql=sql & "EXPERIENCE_REQUIRED='" & Request.Form("experience") & "',"
  sql=sql & "SPECIAL_INSTRUCTION='" & Request.Form("special") & "',"
  sql=sql & "DEPT_BRANCH='" & Request.Form("dept_branch") & "',"
  sql=sql & "STARTDATE='" & Request.Form("startdt") & "',"
  sql=sql & " WHERE IC='" & icpra & "'"

Bruce Luckcuck
Director, Applications & Support Services
Wiley Publishing, Inc.

nazra · July 23rd, 2003, 02:26 AM

hello bluckcuck...

i had removing 'an error resume next'
n below the error...
-----------

Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/staff/updpraktikal.asp, line 55

what should i do now?

nazra

stephanel · July 23rd, 2003, 09:28 AM

The problem seem to be that you have put an comma before your WHERE clause.
It should be like this:

Code:

 sql_update="UPDATE praktikal SET "
  sql=sql & "NAME_PRACTICAL='" & Request.Form("namepra") & "',"
  sql=sql & "ADDRESS='" & Request.Form("address") & "',"
  sql=sql & "DATE_REQUIRED='" & Request.Form("date_required") & "',"
  sql=sql & "DUTIES='" & Request.Form("duties") & "',"
  sql=sql & "QUALIFICATION='" & Request.Form("qualification") & "',"
  sql=sql & "SKILL_REQUIRED='" & Request.Form("skill") & "',"
  sql=sql & "EXPERIENCE_REQUIRED='" & Request.Form("experience") & "',"
  sql=sql & "SPECIAL_INSTRUCTION='" & Request.Form("special") & "',"
  sql=sql & "DEPT_BRANCH='" & Request.Form("dept_branch") & "',"
  sql=sql & "STARTDATE='" & Request.Form("startdt") & "'"
  sql=sql & " WHERE IC='" & icpra & "'"

StÃ©phane Lajoie

nazra · July 23rd, 2003, 08:25 PM

thanks stephane....

i had change my sql code...but its not work...
my database is still not update..

now i'll give u my full coding

-----------------------------------------------------------------------
'here my first page coding

<html>
<body>

<body bgcolor="#FEF1D6">
<%

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/inetpub/wwwroot/staff/pracre.mdb"
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM praktikal where IC = '"&request("icpra")&" ' " , conn

%>
<h2>List Database</h2>
<table border="1" width="100%" bgcolor="#FFFFCC" bordercolor="#C0C0C0" >
<tr>
<%
for each x in rs.Fields
  response.write("<th align='left' bgcolor='#0000FF' bordercolor='#C0C0C0' fontcolor='#FFFF00'>" & ucase(x.name) & "</th>")
next
%>
</tr>
<% do until rs.EOF %>
<tr>
<form method="post" action="updpraktikal.asp">
<%
for each x in rs.Fields
  if x.name="IC" then%>
    <td>
    <input type="submit" name="IC" value="<%=x.value%>">
    </td>
  <%else%>
    <td><%Response.Write(x.value)%></td>
  <%end if
next
%>

</form>
<%rs.MoveNext%>
</tr>

<%
loop
conn.close
%>

</table>
</body>
</html>

---------------------------------------------------------------------
' here my second page coding for update db

<html>
<body>

<body bgcolor="#FEF1D6">
<h2>Update Record</h2>

<%

dim data_source
dim sql_update
dim conn

data_source = myDSN
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/inetpub/wwwroot/staff/pracre.mdb"

icpra=Request.Form("IC")

if Request.form("NAME_PRACTICAL")="" then
  set rs=Server.CreateObject("ADODB.Recordset")
  rs.open "SELECT * FROM praktikal WHERE IC='" & icpra & "'",conn
  %>
  <form method="post" action="updpraktikal.asp">
  <table>

  <%for each x in rs.Fields%>

  <tr>
  <td><%=x.name%></td>
  <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
  <%next%>
  </tr>
  </table>
  <br /><br />
  <input type="submit" value="Update record">
  </form>
<%
else

sql_update="UPDATE praktikal SET "
  sql=sql & "NAME_PRACTICAL='" & Request.Form("name_practical") & "',"
  sql=sql & "ADDRESS='" & Request.Form("address") & "',"
  sql=sql & "DATE_REQUIRED='" & Request.Form("date_required") & "',"
  sql=sql & "DUTIES='" & Request.Form("duties") & "',"
  sql=sql & "QUALIFICATION='" & Request.Form("qualification") & "',"
  sql=sql & "SKILL_REQUIRED='" & Request.Form("skill_required") & "',"
  sql=sql & "EXPERIENCE_REQUIRED='" & Request.Form("experience_required") & "',"
  sql=sql & "SPECIAL_INSTRUCTION='" & Request.Form("special_instruction") & "',"
  sql=sql & "DEPT_BRANCH='" & Request.Form("dept_branch") & "',"
  sql=sql & "STARTDATE='" & Request.Form("startdate") & "'"
  sql=sql & " WHERE IC='" & icpra & "'"

  on error resume next
  conn.Execute sql_update
  conn.close
  Set conn = Nothing

  if error<>0 then
    response.write("No update permissions!")
  else
    response.write("Record " & icpra & " was updated!")
conn.updatebatch
  end if
end if

%>
</body>
</html>

-------------------------------------------------------------------

did anybody have an idea?
please help me to solve this problem.

thanks.

nazra

pgtips · July 24th, 2003, 03:25 AM

nazra, you are using two different variable names for building your sql statement. The first line uses: sql_update="UPDATE praktikal SET " but all the other lines use sql=sql & ...

Can you see the difference? At the moment you are doing conn.Execute sql_update, but because of your error in variable naming you are just asking it to do:
conn.Execute "UPDATE praktikal SET " which is not a valid SQL statement.

Its good practice to use Option Explicit and declare all of your variables with a Dim statement, it will save you much debugging hassle.

Finally, take out that On Error Resume Next while you are still developing the page - it's really not helping you at all.

rgds
Phil

nazra · July 24th, 2003, 03:42 AM

thanks pgtips....
for this moment i had change all my asp code to update my db...
but it still not work...

lastly i had made new asp coding to update my db....
its very simple....but it still not work....

what should i do now?

did anybody had an idea?

here my new coding for update..

-----------------------------------------------------------------
       <%

        response.Expires=-1
        set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    'sila ubah path ini mengikut kedudukan fail mydb.mdb tersebut
    conn.Open "C:\inetpub\wwwroot\staff\pracre.mdb"

%>
<html>
<head>
<title>Practical Student</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">

</style>
</head>

<body bgcolor="#FFFFFF" text="#000000">
<%
op = request.querystring("op")

if op = "addrecord" then
    txtnamepra = request.form("txtnamepra")
    txtic = request.form("txtic")
    txtaddress = request.form("txtaddress")
    txtdatere = request.form("txtdatere")
    txtduties = request.form("txtduties")
    txtqualif = request.form("txtqualif")
    txtskill = request.form("txtskill")
    txtexper = request.form("txtexper")
    txtage = request.form("txtage")
    txtspecial= request.form("txtspecial")
    txtbranch = request.form("txtbranch")
    txtstartdt = request.form("txtstartdt ")

    set rs=Server.CreateObject("ADODB.Recordset")
      sql = "INSERT INTO praktikal (NAME_PRACTICAL, IC, ADDRESS, DATE_REQUIRED, DUTIES, QUALIFICATION,SKILL_REQUIRED, EXPERIENCE_REQUIRED, AGE_RANGE, SPECIAL_INSTRUCTION, DEPT_BRANCH, STARTDATE) " & _
            "values ('" & txtnamepra & "','" & txtic & "','" & txtaddress & "','" & txtdatere & "','" & txtduties & "','" & txtqualif & "','" & _
            txtskill & "','" & txtexper & "','" & txtage & "','" & txtspecial & "','" & txtbranch & "','" & txtstartdt & "')"
    'sila buka comment ini untuk tujuan debug.
    'response.write sql
    'response.end

    rs.open sql, conn

    'setelah berjaya run sql command, pindahkan link kepada listing
    'response.redirect "updpraktikal.asp"
%><script language='javascript'>
document.location.href = 'updatepra.asp';
</script>
<%
    response.end

elseif op = "update" then
    IC = request.form("ic")
    txtnamepra = request.form("txtnamepra")
    txtic = request.form("txtic")
    txtaddress = request.form("txtaddress")
    txtdatere = request.form("txtdatere")
    txtduties = request.form("txtduties")
    txtqualif = request.form("txtqualif")
    txtskill = request.form("txtskill")
    txtexper = request.form("txtexper")
    txtage = request.form("txtage")
    txtspecial= request.form("txtspecial")
    txtbranch = request.form("txtbranch")
    txtstartdt = request.form("txtstartdt ")

    set rs=Server.CreateObject("ADODB.Recordset")
      sql = "UPDATE praktikal set NAME_PRACTICAL='" & txtnamepra & "', " & _
        "IC='" &txtic & "', " & _
        "ADDRESS='" & txtaddress & "', " & _
        "DATE_REQUIRED='" & txtdatere & "', " & _
        "DUTIES='" & txtduties & "', " & _
        "QUALIFICATION='" & txtqualif & "', " & _
        "SKILL_REQUIRED='" & txtskill & "', " & _
        "EXPERIENCE_REQUIRED='" & txtexper & "', " & _
        "AGE_RANGE='" & txtage & "', " & _
        "SPECIAL_INSTRUCTION='" & txtspecial & "', " & _
        "DEPT_BRANCH='" & txtbranch& "', " & _
        "STARTDATE='" & txtstartdt & "' where ID =" + id

    'sila buka comment ini untuk tujuan debug.
    'response.write sql
    'response.end

    'setelah berjaya run sql command, pindahkan link kepada listing
    'response.redirect "updpraktikal.asp"
%><script language='javascript'>
document.location.href = 'studcomfirm.html';
</script>
<%
    response.end

elseif op = "list" or op = "" then %>
<table width="1983" border="1" cellspacing="1" cellpadding="3" bordercolor="#808080">
  <tr bgcolor="#CCCCCC">
    <td colspan="13" width="1131" align="center">
      <div align="center"><b>PRACTICAL STUDENT</b></div>
    </td>
  </tr>
  <tr bgcolor="#00CCFF">
    <td width="31" align="center">NO.</td>
    <td width="195" align="center">NAME</td>
    <td width="177" align="center">IC</td>
    <td width="183" align="center">ADDRESS</td>
    <td width="107" align="center">DATE REQUIRED</td>
    <td width="147" align="center">DUTIES</td>
    <td width="171" align="center">QUALIFICATION</td>
    <td width="186" align="center">SKILL REQUIRED</td>
    <td width="186" align="center">EXPERIENCE REQUIRED</td>
    <td width="104" align="center">AGE RANGE</td>
    <td width="291" align="center">SPECIAL INSTRUCTION</td>
    <td width="186" align="center">DEPARTMENT/BRANCH</td>
    <td width="83" align="center">STARTING DATE</td>
  </tr>
<%

        set rs=Server.CreateObject("ADODB.Recordset")
        rs.open "SELECT * FROM praktikal where IC = '"&request("ic")&" ' " , conn

        i=0
    while not rs.eof

        %>
          <tr>
             <td width="31"><%=i+1%></td>
            <td width="195"><a href='updpraktikal.asp?id=<%=rs("id")%>&op=edit'>< %=rs("NAME_PRACTICAL")%></a></td>
            <td width="177"><%=rs("IC")%></td>
            <td width="183"><%=rs("ADDRESS")%></td>
            <td width="107"><%=rs("DATE_REQUIRED")%></td>
            <td width="147"><%=rs("DUTIES")%></td>
            <td width="171"><%=rs("QUALIFICATION")%></td>
            <td width="186"><%=rs("SKILL_REQUIRED")%></td>
            <td width="186"><%=rs("EXPERIENCE_REQUIRED")%></td>
            <td width="104"><%=rs("AGE_RANGE")%></td>
            <td width="291"><%=rs("SPECIAL_INSTRUCTION")%></td>
            <td width="186"><%=rs("DEPT_BRANCH")%></td>
            <td width="83"><%=rs("STARTDATE")%></td>
          </tr>
        <%
        rs.movenext
        i=i+1
    wend
    response.write "Record Num : " & i
%>
</table>
<br>
<%elseif op = "edit" or op = "new" then
    ID = request.querystring("id")
    if Id = "" then
        actionform = "updpraktikal.asp?op=addrecord"
    else
        actionform = "updpraktikal.asp?op=update"
        set rs=Server.CreateObject("ADODB.Recordset")
          rs.open "SELECT * FROM praktikal where ID =" + id, conn

        fld1 = rs("NAME_PRACTICAL")
        fld2 = rs("IC")
        fld3 = rs("ADDRESS")
        fld4 = rs("DATE_REQUIRED")
        fld5 = rs("DUTIES")
        fld6 = rs("QUALIFICATION")
        fld7 = rs("SKILL_REQUIRED")
        fld8 = rs("EXPERIENCE_REQUIRED")
        fld9 = rs("AGE_RANGE")
        fld10 = rs("SPECIAL_INSTRUCTION")
        fld11 = rs("DEPT_BRANCH")
        fld12 = rs("STARTDATE")
    end if

%>
<form name="myform" method="post" action="<%=actionform%>">
  <table width="100%" border="0" cellspacing="1" cellpadding="3">
    <tr bgcolor="#CCCCCC">
      <td colspan="2">
        <div align="center"><b>Add/Edit Record</b></div>
      </td>
  </tr>
    <tr valign="top">
      <td width="172">NAME </td>
      <td width="473">

        <input type="text" name="txtnamepra" value="<%=fld1%>" size="49">

      </td>
  </tr>
    <tr valign="top">
      <td width="172">IC</td>
      <td width="473">

        <input type="text" name="txtic" value="<%=fld2%>" size="49">

      </td>
  </tr>
    <tr valign="top">
      <td width="172">ADDRESS</td>
      <td width="473">

        <textarea name="txtaddress" cols="40" rows="5"><%=fld3%></textarea>

      </td>
  </tr>
    <tr valign="top">
      <td width="172">DATE REQUIRED</td>
      <td width="473">

        <input type="text" name="txtdatere" value="<%=fld4%>" size="49">

      </td>
  </tr>
    <tr valign="top">
      <td width="172">DUTIES</td>
      <td width="473">

        <input type="text" name="txtduties" value="<%=fld5%>" size="49">

      </td>
  </tr>
    <tr valign="top">
      <td width="172">QUALIFICATION</td>
      <td width="473">

        <input type="text" name="txtqualif" value="<%=fld6%>" size="49">

      </td>
  </tr>
    <tr valign="top">
      <td width="172">SKILL REQUIRED</td>
      <td width="473">

        <input type="text" name="txtskill" value="<%=fld7%>" size="49">

      </td>
  </tr>
    <tr valign="top">
      <td width="172">EXPERIENCE REQUIRED</td>
      <td width="473">

        <input type="text" name="txtexper" value="<%=fld8%>" size="49">

      </td>
  </tr>
  <tr valign="top">
      <td width="172">AGE RANGE</td>
      <td width="473">

        <input type="text" name="txtage" value="<%=fld9%>" size="49">

      </td>
  </tr>
    <tr valign="top">
      <td width="172">SPECIAL INSTRUCTION</td>
      <td width="473">

        <input type="text" name="txtspecial" value="<%=fld10%>" size="49">

      </td>
  </tr>
    <tr valign="top">
      <td width="172">DEPARTMENT/BRANCH</td>
      <td width="473">

        <input type="text" name="txtbranch" value="<%=fld11%>" size="49">

      </td>
  </tr>
    <tr valign="top">
      <td width="172">STARTING DATE</td>
      <td width="473">

        <input type="text" name="txtstartdt" value="<%=fld12%>" size="49">

      </td>
  </tr>
</table>
  <table width="100%" border="0" cellspacing="1" cellpadding="3">
    <tr>
      <td>
        <div align="center">
          <input type="hidden" name="id" value="<%=id%>">
          <input type="submit" name="btnsubmit" value="Submit">
          <input type="reset" name="btnreset" value="Reset">
          <input type="button" name="btncancel" value="Cancel" language="javascript" onclick="document.location.href='updpraktikal.asp' ">
        </div>
      </td>
    </tr>
  </table>
</form>
<%end if%>
</body>
</html>

nazra

pgtips · July 24th, 2003, 04:12 AM

what error do you get now?

You must help people to help you. Just saying "I've changed it and it still doesn't work" is not helpful at all.

nazra · July 24th, 2003, 08:45 PM

thanks for help pgtips....

when i run my coding...there is no error...it just said that your record was update at studcomfirm page...but when i see my database, the record that i just make is not in my database....

the problem is with my update statment ...but i still cant see what is the problem now...

please help me..

nazra

pgtips · July 25th, 2003, 04:30 AM

You say that your new page is "very simple" but it seems a lot more complicated to me.

One thing I notice is that you use "rs.open sql, conn" for your update - but there is no recordset created when an update is done. You need to use "conn.Execute ssql" instead.

Also, you've commented out the response.write ssql. You're not going to get anywhere until you can make sure that the sql statement you are building is correct. Put that
response.write ssql back in and post here the sql statement so we can see exactly what you are sending to the database.

It would also help if you could list the names and datatypes of the fields in the table you are trying to update.