Understanding SqlException: Incorrect Syntax Near ’nvarchar'

In this article, we will delve into the cause of a SqlException that occurs when attempting to insert data into a SQL database using ADO.NET. The error message “Incorrect syntax near ’nvarchar’” indicates that there is an issue with the column names or parameter values used in the SQL query.

What are SqlExceptions?

A SqlException is an exception thrown by ADO.NET when it encounters a problem while executing a SQL query. It provides detailed information about the error, including the severity level of the error and the specific error message.

Understanding the Error Message

The error message “Incorrect syntax near ’nvarchar’” suggests that there is a problem with the column name or parameter value containing the text ’nvarchar’. In SQL Server, column names must follow a specific naming convention, which does not include whitespace characters like spaces or newlines. Similarly, parameter values should be enclosed in single or double quotes, depending on the data type.

The Issue with Column Names

In your code, you are using @Employee Name and @Department as column names in the SQL query. Notice that there is a space between ‘Name’ and ‘@’. This will cause an error because SQL Server does not allow whitespace characters in column names.

To fix this issue, you should remove the spaces from the column names. Here’s an updated version of the SQL query:

cmd.Parameters.AddWithValue("@EmployeeName", txtName.Text);
cmd.Parameters.AddWithValue("@Department", txtDept.Text);

The Issue with Parameter Values

In your code, you are using @Loan Amount and @Years To Pay as parameter values. Notice that there is a space between ‘To’ and the parameter value. This will cause an error because SQL Server does not allow whitespace characters in parameter values.

To fix this issue, you should remove the spaces from the parameter values. However, if you want to include spaces in your data (e.g., “Years To Pay”), you need to enclose the value in single or double quotes:

cmd.Parameters.AddWithValue("@LoanAmount", "'" + txtAmount.Text + "'");
cmd.Parameters.AddWithValue("@YearsToPay", "'" + txtYears.Text + "'");

Setting Identity Specification

Another possible cause of this error is that the Id parameter has an identity specification set to False. When you insert data into a table with an Identity column, ADO.NET will automatically generate a unique value for the column. If you set the identity specification to True, you need to specify the value manually.

To fix this issue, you should check the identity specification of the Id parameter and set it to True if necessary:

con.Open();
cmd = new SqlCommand("INSERT INTO LoanRecord (EmployeeName, EmployeeNumber, Department, LoanAmount, YearsToPay, MonthlyPayment, TotalPayment) VALUES (@EmployeeName, @EmployeeNumber, @Department, @LoanAmount, @YearsToPay, @MonthlyPayment, @TotalPayment)", con);
cmd.Parameters.AddWithValue("@EmployeeName", txtName.Text);
cmd.Parameters.AddWithValue("@EmployeeNumber", txtEmpno.Text);
cmd.Parameters.AddWithValue("@Department", txtDept.Text);
cmd.Parameters.AddWithValue("@LoanAmount", "'" + txtAmount.Text + "'");
cmd.Parameters.AddWithValue("@YearsToPay", "'" + txtYears.Text + "'");
cmd.ExecuteNonQuery();

Conclusion

The SqlException “Incorrect syntax near ’nvarchar’” is caused by using whitespace characters in column names or parameter values. To fix this issue, you should remove spaces from column names and parameter values, and enclose values with quotes if necessary. Additionally, you need to check the identity specification of the Id parameter and set it to True if necessary.

Example Use Case

Here’s an example of how you can modify your code to use non-whitespace column names and parameter values:

con.Open();
cmd = new SqlCommand("INSERT INTO LoanRecord (EmployeeName, EmployeeNumber, Department, LoanAmount, YearsToPay, MonthlyPayment, TotalPayment) VALUES (@EmployeeName, @EmployeeNumber, @Department, @LoanAmount, @YearsToPay, @MonthlyPayment, @TotalPayment)", con);
cmd.Parameters.AddWithValue("@EmployeeName", txtName.Text);
cmd.Parameters.AddWithValue("@EmployeeNumber", txtEmpno.Text);
cmd.Parameters.AddWithValue("@Department", txtDept.Text);
cmd.Parameters.AddWithValue("@LoanAmount", "'" + txtAmount.Text + "'");
cmd.Parameters.AddWithValue("@YearsToPay", "'" + txtYears.Text + "'");
cmd.ExecuteNonQuery();

This code will insert the data into the LoanRecord table without any errors.

Last modified on 2023-10-02