3

I'm a beginner to ExpressJS, I want to be able to add records to the "site" table. However, when I run the following code, it says: 

Error: ER_BAD_FIELD_ERROR: Unknown column 'BeastMode' in 'field list'.

"BeastMode" is an entry I made to the shortName field.

A little context: I'm not supposed to use ORM. I have to use raw sql queries to add to MYSQL database.I'm using 'mysql'package for Nodejs to connect to the database.

 var squery = "INSERT INTO SITE (shortName,addressLine1,addressLine2,city,state,zipcode,phoneNumber) VALUES "+
     "("+
      req.body.shortName+", "+
      req.body.addressLine1+", "+
      req.body.addressLine2+", "+
      req.body.city+", "+
      req.body.state+", " +
      req.body.zipcode+", " +
      req.body.phoneNumber+" );"    

     console.log(req.body);

     dbconnector.query(squery,function(err,rows,fields){
         if(!err){
             res.send("Record Added Successfully: "+req.body);
         }else{
             res.send("Error: "+ err);
         }
     });

    });

Also, here is my dbconnect.js file:

var mysql = require('mysql');

dbconnect = mysql.createConnection({
 host: "localhost",
 user: "root",
 password: "",
 database:"rsacs"
});

module.exports = dbconnect

Here is my HTML:

<!DOCTYPE html>
<html lang="en">
<head>
   <% include head %>
</head>
<body class="container">

<header>
   <% include header %>
</header>

<main>
   <div>
       <h1><%=title%></h1>
        <form method="post" action="/site/create" >
         <div class="form-group">   
            <label for="shortName">Shortname</label>
             <input type="text" class="form-control"  placeholder="Shortname"  name="shortName"><br>

             <label for="Address Line 1"> Address Line 1:</label>
             <input type="text" class="form-control"  placeholder="Address Line 1" name="addressLine1"><br>

             <label for="Address Line 2"> Address Line 2:</label>
             <input type="text"  class="form-control"  placeholder="Address Line 2" name="addressLine2"><br>

             <label for="City">City:</label>
             <input type="text"  class="form-control"  placeholder="City" name="city"><br>

             <label for="State">State:</label>
             <input type="text"  class="form-control"  placeholder="State" name="state"><br>

             <label for="Zipcode">Zipcode:</label>
             <input type="text"  class="form-control"  placeholder="Zipcode" name="zipcode"><br>

             <label for="PhoneNumber">Phone Number:</label>
             <input type="text"  class="form-control"  placeholder="PhoneNumber" name="phoneNumber"><br>

             <button type="submit" class="btn btn-primary">Submit</button>
         </div>                                                            
       </form>
   </div>
</main>

<footer>
   <% include footer %>
</footer>

</body>
</html>

Here is my Site table structure

Priyesh Goswami
  • 31
  • 2
  • Please see this answer. https://stackoverflow.com/questions/22648667/nodejs-mysql-insert-issue?lq=1&utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Fahad Subzwari Apr 09 '18 at 02:46
  • I'd advise you to `console.log(squery)` and copy it from the logs and paste it into the `mysql console`. That will more clearly describe the error so that you could fix it. And also please update your post with what does `console.log(squery)` log and also error in the mysql console using the copied query. – vibhor1997a Apr 09 '18 at 05:08

2 Answers2

1

To echo @AnshumanJaiswal's solution, you're probably encountering an escape character problem.

The solution I'm going to propose, though, is different. The mysql nodejs driver supports prepared queries. As such, the most robust way to sort your query is:

var squery = "INSERT INTO SITE (shortName,addressLine1,addressLine2,city,state,zipcode,phoneNumber) VALUES (?,?,?,?,?,?,?);
var objs =  [req.body.shortName,req.body.addressLine1,req.body.addressLine2,req.body.city,req.body.state,req.body.zipcode,req.body.phoneNumber]
sql = mysql.format(squery, objs);
// now you have a properly-escaped SQL query which you can execute as usual:
connection.query(squery, objs, function (error, results, fields) {if (error) throw error;});

Let me know if this doesn't sort your problem.

hd1
  • 33,938
  • 5
  • 80
  • 91
0

the values are string and you are not passing them as string. There are two possible ways:

Solution 1.

add `` to your string values like:

var squery = "INSERT INTO SITE (shortName,addressLine1,addressLine2,city,state,zipcode,phoneNumber) VALUES "+
     "('"+
      req.body.shortName+"', '"+
      req.body.addressLine1+"', '"+
      req.body.addressLine2+"', '"+
      req.body.city+"', '"+
      req.body.state+"', '" +
      req.body.zipcode+"', " +
      req.body.phoneNumber+" );"    
     ...

Solution 2.

make an object from body data as:

var data = {
    shortName: req.body.shortName,
    addressLine1: req.body.addressLine1,
    addressLine1: req.body.addressLine2,
    city: req.body.city,
    state: req.body.state,
    zipcode: req.body.zipcode,
    phoneNumber: req.body.phoneNumber
};
var squery = "INSERT INTO SITE SET ?";
dbconnector.query(squery, data, function(err,rows,fields){
     if(!err){
         console.log(rows);
         res.send("Record Added Successfully.");
     }else{
         res.send("Error: "+ err);
     }
 });
Anshuman Jaiswal
  • 5,352
  • 1
  • 29
  • 46