使用 NetBeans 在 MS Access 数据库中不会发生插入删除更新

debugcn 发表于 Dev

高拉夫·吉拉卡

在学习了一点 Servlets 之后，我尝试制作一个简单的学生信息系统。该index.html码是

<html>
    <body bgcolor="AliceBlue">
        <h1 style="text-align: center">Student Information System</h1>
        <form method="get" action="MIS">
         First Name <input type="text" name="FName"/><br><br>
         Last Name <input type="text" name="LName"/><br><br>
         ID <input type="number" name="ID"/><br><br>
         Age <input type="number" name="Age"><br><br>
         <input type="submit" name="Insert" value="Insert">
         <input type="submit" name="Delete" value="Delete">
         <input type="submit" name="Update" value="Update">
        </form>
    </body>
</html>

在这里，我使用三个按钮插入、更新和删除来对数据库执行相应的操作。

这是MIS.java代码

 /*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Gaurav
 */
@WebServlet(urlPatterns = {"/MIS"})
public class MIS extends HttpServlet {

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
     * methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        try (PrintWriter out = response.getWriter()) {
            /* TODO output your page here. You may use following sample code. */
            out.println("<!DOCTYPE html>");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet MIS</title>");            
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet MIS at " + request.getContextPath() + "</h1>");
            out.println("</body>");
            out.println("</html>");
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html");
       PrintWriter pr = response.getWriter();



       try
       {
           Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
           Connection con=DriverManager.getConnection("jdbc:ucanaccess://F://Subjects//WT Netbeans//WT P5//src//studentmis.accdb","","");
           String FirstName = request.getParameter("FName");
           String LastName = request.getParameter("LName");
           String Insert = request.getParameter("Insert");
           String Update = request.getParameter("Update");
           String Delete = request.getParameter("Delete");
           int ID = Integer.parseInt(request.getParameter("ID"));
           int Age = Integer.parseInt(request.getParameter("Age"));
           String SqlInsert="INSERT INTO student_info(Firstname,Lastname,ID,Age)VALUES(?,?,?,?)";
           String SqlUpdate="UPDATE student_info SET Lastname=? ID=? Age=? WHERE Firstname=?";
           String SqlDelete="DELETE FROM student_info WHERE Firstname=?";
           if(Insert!=null)
           {
               PreparedStatement InsertStatement = con.prepareStatement(SqlInsert);
               InsertStatement.setString(1,FirstName);
               InsertStatement.setString(2,LastName);
               InsertStatement.setInt(3,ID);
               InsertStatement.setInt(4,Age);
               int RowsInserted = InsertStatement.executeUpdate();
               if(RowsInserted > 0)
                pr.println("New User Added Successfully");

           }

           else if(Update!=null)
           {
               PreparedStatement UpdateStatement = con.prepareStatement(SqlUpdate);
               UpdateStatement.setString(1,FirstName);
               UpdateStatement.setString(2,LastName);
               UpdateStatement.setInt(3,ID);
               UpdateStatement.setInt(4,Age);
               int RowsUpdated = UpdateStatement.executeUpdate();
               if(RowsUpdated > 0)
                pr.println("User Updated Successfully");

           }

           else if(Delete!=null)
           {
               PreparedStatement DeleteStatement = con.prepareStatement(SqlDelete);
               DeleteStatement.setString(1,FirstName);
               int RowsDeleted = DeleteStatement.executeUpdate();
               if(RowsDeleted > 0)
                pr.println("User Updated Successfully");
           }

       } 


       catch (ClassNotFoundException | SQLException ex) 
       {
           pr.println(ex);
       }

        //processRequest(request, response);
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    processRequest(request, response);
}

/**
 * Returns a short description of the servlet.
 *
 * @return a String containing servlet description
 */
@Override
public String getServletInfo() {
    return "Short description";
}// </editor-fold>

}

运行项目后，如果我按下插入按钮（输入信息后），它会出错

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 user lacks privilege or object not found: FIRSTNAME

如果我按下删除按钮（输入信息后）它会出错

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 user lacks privilege or object not found: FIRSTNAME

如果我按下更新按钮（输入信息后）它会出错

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 user lacks privilege or object not found: LASTNAME

这是我的 MS Access 数据库studentmis.accdb包含的内容：

数据库描述

Please help me find the problem in this code. If it's database authentication problem, then I assure that I didn't create a username and password for this database. Also, I assure that I have imported all the necessary .jar files for using Ucanaccess driver.

Ok now the other errors are resolved. Now I'm getting this errornet.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 unexpected token: ID when i try to click the update button. Modified MIS.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Gaurav
 */
@WebServlet(urlPatterns = {"/MIS"})
public class MIS extends HttpServlet {

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
     * methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        try (PrintWriter out = response.getWriter()) {
            /* TODO output your page here. You may use following sample code. */
            out.println("<!DOCTYPE html>");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet MIS</title>");            
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet MIS at " + request.getContextPath() + "</h1>");
            out.println("</body>");
            out.println("</html>");
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html");
       PrintWriter pr = response.getWriter();



       try
       {
           Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
           Connection con=DriverManager.getConnection("jdbc:ucanaccess://F://Subjects//WT Netbeans//WT P5//src//studentmis.accdb");
           String FirstName = request.getParameter("FName");
           String LastName = request.getParameter("LName");
           String Insert = request.getParameter("Insert");
           String Update = request.getParameter("Update");
           String Delete = request.getParameter("Delete");
           int ID = Integer.parseInt(request.getParameter("ID"));
           int Age = Integer.parseInt(request.getParameter("Age"));
           String SqlInsert="INSERT INTO student_info(Firstname,Lastname,ID,Age)VALUES(?,?,?,?)";
           String SqlUpdate="UPDATE student_info SET Lastname=? ID=? Age=? WHERE Firstname=?";
           String SqlDelete="DELETE FROM student_info WHERE Firstname=?";
           if(Insert!=null)
           {
               PreparedStatement InsertStatement = con.prepareStatement(SqlInsert);
               InsertStatement.setString(1,FirstName);
               InsertStatement.setString(2,LastName);
               InsertStatement.setInt(3,ID);
               InsertStatement.setInt(4,Age);
               int RowsInserted = InsertStatement.executeUpdate();
               if(RowsInserted > 0)
                pr.println("New User Added Successfully");

           }

           else if(Update!=null)
           {
               PreparedStatement UpdateStatement = con.prepareStatement(SqlUpdate);
               UpdateStatement.setString(1, LastName);
               UpdateStatement.setInt(2,ID);
               UpdateStatement.setInt(3,Age);
               UpdateStatement.setString(4,FirstName);
               int RowsUpdated = UpdateStatement.executeUpdate();
               if(RowsUpdated > 0)
                pr.println("User Updated Successfully");

           }

           else if(Delete!=null)
           {
               PreparedStatement DeleteStatement = con.prepareStatement(SqlDelete);
               DeleteStatement.setString(1,FirstName);
               int RowsDeleted = DeleteStatement.executeUpdate();
               if(RowsDeleted > 0)
                pr.println("User Deleted Successfully");
           }

       } 


       catch (ClassNotFoundException | SQLException ex) 
       {
           pr.println(ex);
       }

        //processRequest(request, response);
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}

YCF_L

You get this error because the names of your fields are not correct, the name of your fields are :

|  Field1   |   Field1   |  Field3   |   Field4   |

and not :

|  Firstname |   Lastname   |  ID   |   Age   |

In fact Firstname, Lastname, ID, Age is values and not names of your columns, so correct the name of your fields and every thing will be ok.

EDIT

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 unexpected token: ID

This is logic because you set your attributes in the wrong position :

UPDATE student_info SET Lastname=? ID=? Age=? WHERE Firstname=?
//--------------------------1----^--2-^--3--^----------4------^

UpdateStatement.setString(1,FirstName);//<<-----this should be Lastname
UpdateStatement.setString(2,LastName);//<<-----this should be ID
UpdateStatement.setInt(3,ID);//<<-----this should be Age
UpdateStatement.setInt(4,Age);//<<-----this should be Firstname

So instead use this :

UpdateStatement.setString(1, LastName);
UpdateStatement.setInt(2, ID);
UpdateStatement.setInt(3, Age);
UpdateStatement.setString(4, FirstName);

For good practice

不要在开始时用大写字母命名你的变量 UpdateStatement

编辑

您错过了您的字段之间的逗号，UPDATE 的正确语法是：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

所以在你的查询中：

UPDATE student_info SET Lastname=? ID=? Age=? WHERE Firstname=?
//--------------------------------^----^--miss comma

而是使用：

UPDATE student_info SET Lastname=?, ID=?, Age=? WHERE Firstname=?

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-07-9

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章

使用 NetBeans 在 MS Access 数据库中不会发生插入删除更新

使用 NetBeans 在 MS Access 数据库中不会发生插入删除更新

如何使用Java NetBeans更新MS Access表中的特定行？

使用Python将数据插入MS Access数据库

使用C＃，如何使用循环在MS Access数据库中插入递增数字？

通过单击按钮使用C＃从MS Access数据库中删除数据

通过单击按钮使用C＃从MS Access数据库中删除数据

使用datagridView时，INSERT命令会发生异常，而删除和更新在使用Access数据库的C＃中可以正常工作

使用Java数据库连接从MS Access表中删除指定的记录

使用SQL将数据插入MS Access数据库表

插入到ms Access数据库

如何使用VBA创建空白的MS Access数据库？

如何使用VBA创建空白的MS Access数据库？

使用Devexpress编辑远程MS Access数据库

使用vb.net将MS Access行插入数据库

如何在MS Access中使用VBA将值插入数据库表

MS Access数据库使用VBA和SQL查询在列上插入值

使用 ADODB 命令真正删除 MS Access 数据库的条目

如何使用update和sum（）函数更新MS Access数据库表？

使用Visual Basic 2010更新MS Access数据库时出错

使用 MS Access 数据库的更新语句中的语法错误

使用MS Access插入C＃语句

使用MS Access插入SQL Server

使用PHP将记录插入MS Access

使用MS Access插入SQL Server

在MS Access中访问数据库时更新查询

使用C＃在MS Access数据库中自动增加ID

我可以在MS Access中的SQL Sever数据库上使用我的视图吗？

关于动态使用C＃代码在MS ACCESS中创建数据库

尝试使用NetBeans和mysql数据库在数据库中插入数据

使用 VB.Net 将数据绑定文本框中的 string.empty 值更新到 MS Access 数据库中