Angular5+Node.js+SQL Server：前端POST报错Invalid column name 'NaN'，Postman正常

Hey there, let's break down why this is happening and how to fix it quickly!

First, since your Postman tests work perfectly, we can rule out issues with your Node.js backend setup or SQL Server connection. The problem almost certainly lies in how your Angular app is formatting the data it sends, and how your backend is handling that data when building SQL queries.

Common Causes & Solutions #

1. You're Sending NaN Values from Angular #

SQL Server has no idea what NaN means, and if your backend is directly inserting that value into a SQL query, it'll interpret NaN as a column name instead of a value (hence the "Invalid column name 'NaN'" error).

• Check your frontend data first: Add a console.log in your Angular service right before sending the request to spot any NaN values:

  // In your Angular service
  submitData(formData: YourDataModel) {
    console.log('Data being sent:', formData); // Look for NaN in the output
    return this.http.post('/api/your-endpoint', formData);
  }
  

• Fix the NaN values: Before sending, validate and replace any NaN or undefined numeric fields. For example:

  // Clean up numeric fields
  if (isNaN(formData.price) || formData.price === undefined) {
    formData.price = null; // Or set a default value like 0, based on your business logic
  }
  

  If you're using Angular Reactive Forms, add validators to prevent invalid numeric inputs upfront:

  this.myForm = this.formBuilder.group({
    price: [0, [Validators.required, Validators.min(0)]]
  });
  

2. Your Backend is Building SQL Queries Incorrectly #

If you're directly concatenating values into your SQL string (instead of using parameterized queries), a NaN value from the frontend will break the query syntax.

• Bad practice (causes the error):

  // Node.js backend - Avoid this!
  const sqlQuery = `INSERT INTO YourTable (price) VALUES (${req.body.price})`;
  // If req.body.price is NaN, this becomes: INSERT INTO YourTable (price) VALUES (NaN)
  // SQL Server thinks "NaN" is a column name, hence the error
  

• Fix with parameterized queries (recommended):
  Use parameterized queries (supported by libraries like mssql) to safely handle values, including null:

  const sql = require('mssql');
  
  async function addData(req, res) {
    const pool = await sql.connect(config);
    const request = pool.request();
    
    // Pass values as parameters - automatically handles null/valid numbers
    request.input('price', sql.Int, req.body.price || null);
    
    const result = await request.query(`INSERT INTO YourTable (price) VALUES (@price)`);
    res.send(result);
  }
  

  Parameterized queries not only fix this NaN issue but also protect against SQL injection.

3. Double-Check Content-Type Matching #

While less likely, ensure your Angular request is sending data in the format your backend expects. Angular's HttpClient defaults to application/json, which should match if your backend uses body-parser with app.use(bodyParser.json()). If Postman was using form-data instead, that might explain the discrepancy—but since Postman works, this is probably not the issue.

Final Summary #

The root cause is that NaN values from your Angular app are ending up in your SQL query, which SQL Server misinterprets as a column name. Fix this by:

• Cleaning up NaN/undefined values in Angular before sending the request
• Using parameterized queries in your Node.js backend to safely handle all input values

内容的提问来源于stack exchange，提问作者Leonardo Assunção