Incorrect integer value '' for column… Mysql

January 16, 2022

I would like to allow null values to be submitted for this column but whenever it is left empty I get this notification. I checked my table and confirmed the column allows null values. I’m still new to sql and learning .

HTML

<div id="Temp">
                <label for="Temperature">Temperature</label>
                <input id="Temperature" type="number" name="Temperature">
            </div>
            <div id="Vol">
                <label for="Volume">Volume</label>
                <input id="Volume" type="number" name="Volume">
            </div>
            <input type="submit" value="OK" />

app.post("/device-added", function (req, res) {
        let sqlquery = "INSERT INTO devices (Name, Type, Room, Power, Status, Temperature, Volume) VALUES (?,?,?,?,?,?,?)";
        let newrecord = [req.body.Name, req.body.Type, req.body.Room, req.body.Power, req.body.Status, req.body.Temperature, req.body.Volume];
        // execute sql query
        db.query(sqlquery, newrecord, (err, result) => {
            if (err) {
                return console.error(err.message);
            }
        });
        res.render("device-added.html");
    });
}

If I insert a number in temperature it will give me the error for volume and vice versa

>Solution :

Can you try this.

let newrecord = [req.body.Name, req.body.Type, req.body.Room, req.body.Power, req.body.Status, req.body.Temperature || null, req.body.Volume];

In your code temperature us being passed as an empty string( i.e ""), and thus you get this error as according to your table structure it will only allow integers or null