从Access数据库删除按钮单击的特定记录
问题描述:
我有一个包含9个字符的字符串,例如LON052012(来自伦敦23.05.2012。 - day.month.year。)。我想从Access中的表中删除所有城市的所有记录,其中所有城市都以该字符串的前三个字母开始,并具有这些月份和年份(05.2007。)。从Access数据库删除按钮单击的特定记录
这是函数:
private void button2_Click(object sender, EventArgs e)
{
if (textBox1.Text != "")
{
try
{
string sTown, s1, s2;
sTown = textBox1.Text.Substring(0, 3);
s1 = textBox1.Text.Substring(3, 2);
s2 = textBox1.Text.Substring(5);
string sDelete = "DELETE * FROM Country WHERE Town LIKE @p1 and month(TownDate) = @p2 and year(TownDate) = @p3";
OleDbCommand komDelete = new OleDbCommand(sDelete, connection);
komDelete.Parameters.AddWithValue("@p1", sTown + "*");
komDelete.Parameters.AddWithValue("@p2", s1);
komDelete.Parameters.AddWithValue("@p3", s2);
connection.Open();
komDelete.ExecuteNonQuery();
MessageBox.Show("Deleted");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
connection.Close();
}
}
else
{
MessageBox.Show("TextBox is empty!");
}
}
这是表国家的样子(CountryID,镇,TownDate):
它总是说,该记录即使删除不是。
答
对于UPDATE,INSERT和DELETE语句,ExecuteNonQuery的返回值是受该命令影响的行数。
因此,修改代码为:
var n= komDelete.ExecuteNonQuery();
if (n >0)
{
MessageBox.Show("Deleted");
}
编辑
查询的问题是:
- 参数的数据类型S1,S2,他们是字符串,并且他们 应该作为整数传递。
- 使用*,它应该是%(接入使用 %,见我的评论)
所以,你必须修改代码为:
var p1 = sTown + "%"; // not *
komDelete.Parameters.AddWithValue("@p1", p1);
var p2 = int.Parse(s1); //convert to numeric data type
var p3 = int.Parse(s2);
komDelete.Parameters.AddWithValue("@p2", p2);
komDelete.Parameters.AddWithValue("@p3", p3);
var n= komDelete.ExecuteNonQuery();
if (n >0)
{
MessageBox.Show("Deleted");
}
我测试的代码访问2007年和2007年Office系统驱动程序:数据连接组件,它工作正常,并删除该行。
答
试试这个:
if (textBox1.Text != "")
{
//retrieve data
string sTown, sMonth, sYear;
sTown = textBox1.Text.Substring(0, 3);
sMonth = textBox1.Text.Substring(3, 2);
sYear = textBox1.Text.Substring(5);
//validate input
bool valid_town = new Regex(@"[a-z]", RegexOptions.IgnoreCase).IsMatch(sTown) && new Regex(@"[^0-9]").IsMatch(sTown) && sTown.Length ==3;
bool valid_month = new Regex(@"[0][1-9]|[1][0-2]").IsMatch(sMonth) && new Regex(@"[^a-z]", RegexOptions.IgnoreCase).IsMatch(sMonth) && sMonth.Length ==2;
int year = 0;
bool isNum = int.TryParse(sYear,out year);
bool valid_year = new Regex(@"[^a-z]", RegexOptions.IgnoreCase).IsMatch(sYear) && sYear.Length == 4 && (isNum ? year > 1980 && year < 2100 : false);
string newLine = System.Environment.NewLine;
//if input valid
if (valid_town && valid_month && valid_year)
{
//create sql statements
string sWhere = String.Format("WHERE Town LIKE {0} and month(TownDate) = {1] and year(TownDate) = {2}", sTown,sMonth,sYear);
string sCount = String.Format("SELECT COUNT(*) FROM Country {0}", sWhere);
string sDelete = String.Format("DELETE * FROM Country {0}",sWhere);
//counts; to be deleted, actually deleted
int initialCount = 0;
int deletedCount = 0;
try
{
//create connection//NOTE: Using 'Use' statement will handle opening and closing. I dont know where you created your initial 'connection' object but it could cause you problems by haven a connection that is being used in multiple methods.
using (OleDbCommand connection = new OleDbCommand(ConnectionString))
{
//get total rows to be affected
connection.CommandText = sCount;
initialCount = connection.ExecuteNonQuery();
//delete rows and get delete count
connection.CommandText = sDelete;
deletedCount = connection.ExecuteNonQuery();
//display message
MessageBox.Show(String.Format("Found {0} rows to delete. {1}Deleted {2} rows.", initialCount, newLine, deletedCount));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
connection.Close();
}
}
{
//else; input not valid
MessageBox.Show(String.Format("Failed validation of TextBox:{0}Country:{1}{2}Month{3}{4}Year{5}.", newLine, valid_town.ToString(), newLine, valid_month.ToString(), newLine, valid_year.ToString())); ;
}
}
else
{
MessageBox.Show("TextBox is empty!");
}
,你将需要添加此使用:
using System.Text.RegularExpressions;
我没有得到一个机会来创建示例项目自己试试吧......
我做到了,但它仍然不会删除表中的记录... – Nikola
您可以在传递参数(例如komDelete.CommandText)后检查(并发布)您的sql语句。 –
对于具有访问权限的OLE连接,使用%而不是*,查看https://*.com/a/17000335/3142139 –