UPDATE sql name undefined

Adil Ali
Adil Ali used Ask the Experts™
on
Snapshot_19-05-01_18-37-48.png

So I have an sql errror when whenever I update my data

const express = require('express');
const app = express();
const mysql = require('mysql');
const bodyparser = require('body-parser');
app.use(bodyparser.urlencoded({ extended: true }));
app.use(bodyparser.json());
const mysqlConnection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'Adil@123',
  database: 'movies'

});
mysqlConnection.connect(err=>{
  if (err) {
    console.log('It was not successful \n Error:' + JSON.stringify(err,undefined,2));
  } else {
    console.log('Its a success');
  }
  });
 //  Collecting all the movies from the movielist
  app.get('/movielist',(req,res)=> {
    mysqlConnection.query("SELECT * FROM movielist", (err, rows,fields)=> {
      if (!err) {
        res.send(rows);
      } else {
        console.log(err);
      }
    });
  });
 // Finding a movie based on the `idmovielist` number
   app.get('/movielist',(req,res) => {
    mysqlConnection.query("SELECT * FROM movielist WHERE idmovielist = ?",[req.params.id],(err, rows,fields) =>{
    if (!err) {
      res.send(rows);
    } else {
    console.log(err);
    }
    });
  });

  // Delting a movie
   app.delete('/movielist/:id',(req,res) => {
    mysqlConnection.query("DELETE FROM movielist WHERE idmovielist = ?",[req.params.id],(err,rows,fields) =>{
      if (!err) {
        res.send("Movie is deleted");
      } else {
      console.log(err);
    }
    });
  });
  // Inserting a movie
  app.post('/movielist/addMovie',(req, res) => {
    //console.log("movielist/addMovie : ",req.body);
   mysqlConnection.query("INSERT INTO movielist (`idmovielist`,`name`,`thumnail_path`,`description`,`language_released`,`year_released`) VALUES ('"+req.body.idmovielist+"', '"+req.body.name+"','"+req.body.thumnail_path+"', '"+req.body.description+"', '"+req.body.year_released+"', '"+req.body.language_released+"')",
   (err,rows) => {
     if (!err) {
       res.send("Movie is added");
     } else {
       console.log(err);
     }
  });
});

app.put('/movielist/updateMovie/:id',(req,res) =>{
  let update = req.body;
  console.log(update.name);
  mysqlConnection.query("UPDATE movielist SET name = ?, thumnail_path = ?, description = ?, year_released = ?, language_released = ? WHERE idmovielist = ?",
  [update.name,update.thumnail_path,update.description,update.year_released,update.language_released,req.params.id], function (err, results) {
    if (!err) {
      res.send("Movie list is updated");
    } else {
      console.log(err);
    }
  });
});

// localhost:3000
app.listen(3000,() => {
  console.log('We got it running');
});
module.exports = app;

above is my node js and below is my jquerry

$(function() {
  $.ajax({
      method:"GET",
      url: "http://localhost:3000/movielist",
      dataType: "json",
      success: function (response) {
        $.each(response, function(i, movie) {
        const rowText = "<tr>" +
            "<td>" + movie.idmovielist + "</td>" +
            "<td>" + movie.name + "</td>" +
            "<td>" + movie.thumbnail_path + "</td>" +
            "<td>" + movie.description + "</td>" +
            "<td>" + movie.year_released + "</td>" +
            "<td>" + movie.language_released + "</td>" +
            "<td>" + "<button button id = \"deleteMovie\" type=\"button\" class=\"btn btn-danger\" data-toggle=\"modal\" data-target=\"#exampleModal2\">Delete</button>" + "</td>" +
            "<td>" + "<button button id = \"editMovie\" type=\"button\"  class=\"btn btn-danger\" data-toggle=\"modal\" data-target=\"#exampleModal2\">Edit</button>" + "</td>";
          $("#movies").append(rowText);
        });

renderMovieList('movies');
  function renderMovieList(){
  $.ajax({
    method:"GET",
    url: "http://localhost:3000/movielist",
    dataType: "json",
    success: function (response) {
      $('#movies').empty();
    $.each(response, function(i, movie) {
    const rowText = "<tr>" +
      "<td>" + movie.idmovielist + "</td>" +
      "<td>" + movie.name + "</td>" +
      "<td>" + movie.thumbnail_path + "</td>" +
      "<td>" + movie.description + "</td>" +
      "<td>" + movie.year_released + "</td>" +
      "<td>" + movie.language_released + "</td>" +
      "<td>" + "<button button id = \"deleteMovie\" type=\"button\" class=\"btn btn-danger\" data-toggle=\"modal\" data-target=\"#exampleModal2\">Delete</button>" + "</td>" +
      "<td>" + "<button button id = \"editMovie\" type=\"button\"  class=\"btn btn-danger\" data-toggle=\"modal\" data-target=\"#exampleModal2\">Edit</button>" + "</td>";
      $("#movies").append(rowText);
    });
    }
  });
}
  $("#movieAdded").click(function(a) {
    a.preventDefault();

    let mydata = {
      idmovielist: $($("#newForm")[0].intNum).val(),
      name: $($("#newForm")[0].name).val(),
      thumnail_path: $($("#newForm")[0].thumnail_path).val(),
      description: $($("#newForm")[0].description).val(),
      year_released: $($("#newForm")[0].year_released).val(),
      language_released: $($("#newForm")[0].language_released).val(),
    }

    displayMovie(mydata);

    $("#newForm").trigger("reset");
    $("#newForm").toggle();
  });

  $("#updateMovie").on("click", function(a) {
    a.preventDefault();

    let data = {
      idmovielist: $($("#updateForm")[0].intNum).val(),
      name: $($("#updateForm")[0].name).val(),
      thumnail_path: $($("#updateForm")[0].thumnail_path).val(),
      description: $($("#updateForm")[0].description).val(),
      year_released: $($("#updateForm")[0].year_released).val(),
      language_released: $($("#updateForm")[0].language_released).val(),
    }

    putMovie($($("#updateForm")[0].movieId).val(), data);

    $("#updateForm").trigger("reset");
    $("#updateForm").toggle();
});

function getOneMovie(id) {
  $.ajax({
    url: "http://localhost:3000/movielist" + id,
    method: 'GET',
    dataType: 'json',
    success: function(data) {
      $($("#updateForm")[0].movieId).val(data._id);
      $($("#updateForm")[0].intNum).val(data.intNum);
      $($("#updateForm")[0].name).val(data.name);
      $($("#updateForm")[0].thumnail_path).val(data.thumnail_path);
      $($("#updateForm")[0].description).val(data.description);
      $($("#updateForm")[0].year_released).val(data.year_released);
      $($("#updateForm")[0].language_released).val(data.language_released);
      $("#updateForm").show();
    }
  });
}

function displayMovie(mydata) {
  $.ajax({
    method: "POST",
    url: "http://localhost:3000/movielist/addMovie",
    dataType: "json",
    data: mydata,
    success: function(data) {
       console.log(data);
      renderMovieList();
}
});
}

function loadButtons() {
              $(".editMovie").click(function (a) {
                  getOneMovie($($(this)[0]).data("movieId"));
                  a.preventDefault();
              });

              $(".deleteMovie").click(function (a) {
                  deleteMovie($($(this)[0]).data("movieId"));
                  a.preventDefault();
              });
          }

loadButtons();
function putMovie(data) {
  $.ajax({
    url: "http://localhost:3000/movielist/updateMovie/2",
    method: 'PUT',
    dataType: 'json',
    data: data,
    success: function(data) {
      console.log(data);
      getOneMovie();
    }
  });
}



function deleteMovie(id) {
  $.ajax({
    url: "http://localhost:3000/movielist/" + id,
    method: 'DELETE',
    dataType: 'json',
    success: function(data) {
      console.log(data);
    }
  });
}

}
})

});

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
it mentioned column "Name" cannot be null, it means you must provide a value for it.

so SET name = null is not valid

Author

Commented:
Would I also have to do that to the other column names as well such as thumnail_path= null is not valid

Author

Commented:
I am still getting that error  'Column \'name\' cannot be null',

app.put('/movielist/updateMovie/:id',(req,res) =>{
  let update = req.body;
  console.log(update.name);
  mysqlConnection.query("UPDATE movielist SET name =  null is not valid, language_released = ? WHERE idmovielist = ?",
  [update.name,update.language_released,req.params.id], function (err, results) {
    if (!err) {
      res.send("Movie list is updated");
    } else {
      console.log(err);
    }
  });
});
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ryan ChongSoftware Team Lead

Commented:
this part is wrong:

mysqlConnection.query("UPDATE movielist SET name =  null is not valid, language_released = ?

what I mean is you need to set name field to a non-NULL value, like:
UPDATE movielist SET name =  '', language_released = ?

Open in new window

or
UPDATE movielist SET name =  'whatever default value', language_released = ?

Open in new window


or if the fact this value is nullable, then pls make necessary adjustment at DB Table to change the field to allow nullable values.
johnsoneSenior Oracle DBA

Commented:
To me, it looks more like an application issue.  In the posted screen shot, the update screen is totally blank.  I would think the application should be checking to see if there is actually an update before trying to perform one.

However, the description of the error is correct.  You are trying to put NULL into a field that doesn't allow NULL.

Author

Commented:
@Ryan Chong   app.put('/movielist/updateMovie/:id',(req,res) =>{
  let update = req.body;
  mysqlConnection.query("UPDATE movielist SET name = '"+update.name+"', thumnail_path = '"+update.thumnail_path+"', description = '"+update.description+"', year_released = '"+update.year_released+"', language_released = '"+update.language_released+"' WHERE idmovielist = '"+update.idmovielist+"'",
   function (err, results) {
    if (!err) {
      res.send("Movie list is updated");
    } else {
      console.log(err);
    }
  });
}); I did it this way is it right

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial