I have an error doing either a DELETE
or INSERT
using a C# SqlCommand
; database is SQL server.
The SQL statement is
DELETE FROM [table_name]
WHERE id = 'some_id';
which is pretty simple.
id
is a column name with datatype varchar(15)
that can be NULL; some_id
has 15 characters.
First, to verify that only 1 row with that ID
exists, this SELECT
query was made that returns no errors
SELECT *
FROM [table_name]
WHERE id = 'some_id';
returns 1 row
The error on the DELETE
is
System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
This error can happen when more than 1 row satisfy the criteria of the query; clearly not the case here
What is puzzling is that doing the same DELETE
query using the C ODBC API there is no error; so it seems the problem is in the C# code
C# code that calls DELETE
:
public int execute(string sql)
{
SqlConnection conn = new SqlConnection(conn_str);
try
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return -1;
}
return 0;
}
C# code that calls SELECT
:
public List<List<string>> select(string sql)
{
Stopwatch sw = new Stopwatch();
sw.Start();
List<List<string>> rows = null;
SqlConnection conn = new SqlConnection(conn_str);
try
{
conn.Open();
SqlDataReader reader = null;
SqlCommand command = new SqlCommand(sql, conn);
reader = command.ExecuteReader();
int nbr_cols = reader.FieldCount;
int nbr_rows = 0;
rows = new List<List<string>>();
while (reader.Read())
{
nbr_rows++;
List<string> col = new List<string>();
for (int idx_col = 0; idx_col < nbr_cols; idx_col++)
{
string str = reader[idx_col].ToString();
col.Add(str);
}
rows.Add(col);
}
reader.Close();
conn.Close();
sw.Stop();
Console.WriteLine("Time to get {0} rows: {1} msec", nbr_rows, sw.ElapsedMilliseconds);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return rows;
}
C++ DELETE code with no error
std::string sql = "DELETE FROM [some_table] WHERE ID='some_id';";
if (query.exec_direct(sql) < 0)
{
std::cout << sql;
}
where the function exec_direct
is here
https://github.com/pedro-vicente/lib_odbc/blob/master/odbc.cc
thanks
EDIT
adding link to DELETE documentation
https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-2017
User contributions licensed under CC BY-SA 3.0