系统数据中发生'System.Data.SqlClient.SqlException'类型的异常.dll 'b' 附近的语法不正确

Question

我在运行代码时收到此错误消息。它说"System.Data.SqlClient.SqlException"类型的异常发生在System.Data中.dll但未在用户代码中处理 其他信息：'b'附近的语法不正确。但我不知道如何解决。实际上，我引用了某人的代码，它与他们的代码相同，但仍然出现错误。

这是我的代码：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
namespace myCookbook
{
public partial class Form1 : Form
{
SqlConnection connection;
string connectionString;
public Form1()
{
InitializeComponent();
connectionString = ConfigurationManager.ConnectionStrings["myCookbook.Properties.Settings.cookBookDatabaseconnectionString"].ConnectionString;
}
private void Form1_Load(object sender, EventArgs e)
{
InsertResipi();
}
private void InsertResipi()
{
using (connection = new SqlConnection(connectionString))
using (SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT * FROM Recipe", connection))
{
DataTable RecipeTable = new DataTable();
myAdapter.Fill(RecipeTable);
listRecipes.DisplayMember = "ResepiName";
listRecipes.ValueMember = "Id";
listRecipes.DataSource = RecipeTable;
}
}
private void InsertIngredient()
{
string myQuery = " SELECT a.IngredientName FROM Ingredient a" +
"INNER JOIN ResipiIngredient b ON a.Id = b.IngredientId" +
"WHERE b.ResipiId = @ResipiId";
using (connection = new SqlConnection(connectionString))
using (SqlCommand myCommand  = new SqlCommand(myQuery, connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(myCommand))
{
myCommand.Parameters.AddWithValue("@ResipiId", listRecipes.SelectedValue);
DataTable IngredientTable = new DataTable();
adapter.Fill(IngredientTable); // the error message highlight this line
listIngredient.DisplayMember = "IngredientName";
listIngredient.ValueMember = "Id";
listIngredient.DataSource = IngredientTable;
}
}
private void listRecipes_SelectedIndexChanged(object sender, EventArgs e)
{
// MessageBox.Show(listRecipes.SelectedValue.ToString()); //show id
InsertIngredient();
}
}
}

这是我的参考资料

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
namespace databasecookbook
{
public partial class Form1 : Form
{
string connectiondenganawak;
SqlConnection connectionhati;
public Form1()
{
InitializeComponent();
connectiondenganawak =     ConfigurationManager.ConnectionStrings
["databasecookbook.Properties.Settings.cookbookConnectionString"].
ConnectionString;
}
private void Form1_Load(object sender, EventArgs e)
{
isirecipe();
}
void isirecipe()
{
using (connectionhati = new SqlConnection(connectiondenganawak))
using (SqlDataAdapter adapterawak = new SqlDataAdapter("SELECT * FROM Recipe", connectionhati))
{
DataTable RecipeTable = new DataTable();
adapterawak.Fill(RecipeTable);
listBoxRecipe.DisplayMember = "Name";
listBoxRecipe.ValueMember = "ID";
listBoxRecipe.DataSource = RecipeTable;
}
}
void isiingredient()
{
string querycinta = " SELECT a.Name FROM Ingredient a" +
"INNER JOIN RecipeIngredient b ON a.Id = b.IngredientID" +
"WHERE b.RecipeID = @RecipeID";
using (connectionhati = new SqlConnection(connectiondenganawak))
using (SqlCommand commandawak =new SqlCommand(querycinta,connectionhati))
using (SqlDataAdapter adapterawak = new SqlDataAdapter(commandawak))
{
commandawak.Parameters.AddWithValue("@RecipeID", listBoxRecipe.SelectedValue);
DataTable IngredientTable = new DataTable();
adapterawak.Fill(IngredientTable);
listBoxIngredients.DisplayMember = "Name";
listBoxIngredients.ValueMember = "ID";
listBoxIngredients.DataSource = IngredientTable;
}
}
private void listBoxRecipe_SelectedIndexChanged(object sender, EventArgs e)
{
isiingredient();
}
}
}

Answer 1

你得到的查询字符串在换行符周围缺少空格：当你连接时，比如说，" SELECT a.Name FROM Ingredient a" + "INNER JOIN RecipeIngredient b ON a.Id = b.IngredientID"，a和INNER JOIN之间没有空格，所以查询语法不正确。

C# 通过逐字字符串文本(即以@开头的文本)为问题提供了内置解决方案。这些文本可以包含换行符：

string myQuery =@"
SELECT a.IngredientName FROM Ingredient a
INNER JOIN ResipiIngredient b ON a.Id = b.IngredientId
WHERE b.ResipiId = @ResipiId
";

当您将此查询传递给 SQL Server 时，它具有有效的语法，并且在屏幕上也能很好地阅读。