嵌套选择问题
本文关键字:问题 选择 嵌套 | 更新日期: 2023-09-27 18:35:04
我在使用SqlServerCe时遇到了一个小问题。当使用以下 sqlString 执行 ExecuteNonQuery() 时;命令我收到以下错误。
string sqlString = "INSERT INTO Images (UID, Name) " +
"VALUES ((SELECT ID FROM Contributors WHERE ID = @UID), @Name);";
给出此错误:
There was an error parsing the query. [ Token line number = 1,Token line offset = 41,Token in error = SELECT ]
有什么建议吗?
方法
public static void InsertImage(Contributor contObj, string ImageName)
{
string sqlString = "INSERT INTO Images (UID, Name) " +
"VALUES ((SELECT ID FROM [Contributors] WHERE ID = @UID), @Name);";
using (SqlCeConnection sqlConnection =
new SqlCeConnection(WebConfigurationManager.ConnectionStrings["DefaultSQL"].ConnectionString))
{
SqlCeCommand sqlCommand = new SqlCeCommand(sqlString, sqlConnection);
sqlCommand.Parameters.AddWithValue("@UID", contObj.ID);
sqlCommand.Parameters.AddWithValue("@Name", ImageName);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
为什么在这里使用嵌套选择?既然@UID
已经引用了您选择的 ID 列,您不能只做:
INSERT INTO Images (UID, Name)
VALUES (@UID, @Name)
如果你真的必须使用选择,那么看看蓝脚的答案。他提供您指定的参数(@Name
)作为要插入的值之一,所以没问题。
你会想要使用INSERT INTO...SELECT..FROM
string sqlString = "INSERT INTO Images (UID, Name) " +
"SELECT ID, @Name FROM Contributors WHERE ID = @UID;";
如果您已经知道 @ID
的值,则可以使用 INSERT INTO... VALUES...
查询
INSERT INTO Images (UID, Name)
VALUES (@UID, @Name)
编辑,这是带有更新查询的代码
public static void InsertImage(Contributor contObj, string ImageName)
{
string sqlString = "INSERT INTO Images (UID, Name) " +
"VALUES (@UID, @Name)";
using (SqlCeConnection sqlConnection =
new SqlCeConnection(WebConfigurationManager.ConnectionStrings["DefaultSQL"].ConnectionString))
{
SqlCeCommand sqlCommand = new SqlCeCommand(sqlString, sqlConnection);
sqlCommand.Parameters.AddWithValue("@UID", contObj.ID);
sqlCommand.Parameters.AddWithValue("@Name", ImageName);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
你可以这样做:
string sqlString = @"INSERT INTO Images(UID, Name)
SELECT ID, @Name
FROM Contributors
WHERE ID = @UID"