ado.net数据库访问问题，求解答！

[li_xi_quan]

分别对dataGridView中显示的数据做修改、删除、添加记录的操作，数据更新都是可以成功提交到MySQL服务器的，在MySQL服务器中可以查看到更新的记录，只是删除和修改一条记录并且update后，再重新从数据库fill到一个数据集并且用dataGridView显示，异常提示在位置0处找不到任何行，调试发现数据集中没有数据只有表的字段名，奇怪的是对dataGridView中显示的数据做添加记录操作后再update，然后再从数据库fill到数据集用同一个dataGridView显示是可以显示数据的，没有任何异常。我是首先从数据库fill一个表到数据集1，而绑定dataGridView的是数据集2，数据集2中的内容是从数据集一中复制读取过来的，主要用于实现分页功能的。
代码如下：
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
string tableName;
string databaseName1;
//string databaseName2;
string MyConnectionString;

//定义分页数据
int pageSize = 0; //每页显示行数
int nMax = 0; //总记录数
int pageCount = 0; //总页数＝总记录数/每页显示行数
int pageCurrent = 0; //当前页号
int nCurrent = 0; //当前记录行
int page1 = 0;

DataSet myds;// = new DataSet();
DataSet dtTemp = new DataSet();
DataTable tmpTable = new DataTable();//初始数据库表

MySqlConnection conn;
MySqlDataAdapter myadp;
MySqlCommandBuilder objCommandBuilder;

public Form1()
{
InitializeComponent();
}
public void showTablesData_Click(object sender, EventArgs e)//显示表数据
{
if (tableName == null)
{
MessageBox.Show("没有选中要修改的数据库表，不能显示要修改的表！");
}
else
{
GetTableContent(tableName);
InitDataSet();
this.currentPages.Value = 1;
}
}

private void GetTableContent(string tableName)
{
conn = new MySqlConnection(); // 创建mysql连接对象
MyConnectionString = "server = 192.168.0.22; uid = users; pwd = 85836262; charset = utf8 ;database =" + databaseName1;
conn.ConnectionString = MyConnectionString; // 配置连接
myds = new DataSet();
conn.Open(); // 打开连接
myadp = new MySqlDataAdapter("select * from " + tableName, conn);
//myds.Reset();
myadp.Fill(myds, tableName);//在 DataSet 中添加或刷新行以匹配使用 DataSet 和 DataTable 名称的数据源中的行。
conn.Close();
}

private void InitDataSet()
{
pageSize = 10; //设置页面行数
nMax = myds.Tables[tableName].Rows.Count; //Tables[0]改成了Tables[tableName]
pageCount = (nMax / pageSize); //计算出总页数
if ((nMax % pageSize) > 0) pageCount++;
pageCurrent = 1; //当前页数从1开始
nCurrent = 0; //当前记录数从0开始
this.totalPages.Text = pageCount.ToString(); //显示共几页
LoadData();
}

private void LoadData()
{
int nStartPos = 0; //当前页面开始记录行
int nEndPos = 0; //当前页面结束记录行
tmpTable = myds.Tables[tableName].Clone(); //Tables[0]改成了Tables[tableName]
if (pageCurrent == pageCount)
nEndPos = nMax;
else
nEndPos = pageSize * pageCurrent;
nStartPos = nCurrent;
page1 = pageCurrent;
dtTemp.Tables.Clear();
dtTemp.Tables.Add(tmpTable);
//从元数据源复制记录行
for (int i = nStartPos; i < nEndPos; i++)
{
tmpTable.ImportRow(myds.Tables[tableName].Rows[i]); //Tables[0]改成了Tables[tableName]
nCurrent++;
}
bdsInfo.DataSource = tmpTable;
bdnInfo_ItemClicked.BindingSource = bdsInfo;
dataGridView1.DataSource = bdsInfo;
}

private void saveChanges_Click(object sender, EventArgs e)//保存记录修改
{
if (dataGridView1.Rows.Count == 0)//如果dataGridView1的行数为0，说明其中尚未有数据(显示数据)，提示并且中断保存修改操作
{
MessageBox.Show("没有记录修改，不需要保存修改！");
}
else
{
if (MessageBox.Show("确定要从MySQL数据库中修改记录吗？", "警告！", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
{
conn = new MySqlConnection(); // 创建mysql连接对象
MyConnectionString = "server = 192.168.0.22; uid = users; pwd = 85836262; charset = utf8 ;database =" + databaseName1;
conn.ConnectionString = MyConnectionString; // 配置连接
conn.Open(); // 打开连接
myadp = new MySqlDataAdapter("select * from " + tableName, conn);
objCommandBuilder = new MySqlCommandBuilder(myadp);
myadp.Update(dtTemp, tableName);
dtTemp.AcceptChanges();
MessageBox.Show("数据已成功更新到MySQL数据库！");
conn.Close();
}
}
}

private void deleteRecord_Click(object sender, EventArgs e)//删除记录
{
if (dataGridView1.Rows.Count == 0)//如果dataGridView1的行数为0，说明其中尚未有数据(显示数据)，则提示并且中断刷新操作
{
MessageBox.Show("没有选中要删除的记录，不能进行删除操作！");
}
else
{
if (MessageBox.Show("确定要从MySQL数据库中删除选中记录吗？", "警告！", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
{
foreach (DataGridViewRow r in dataGridView1.SelectedRows)
{
if (!r.IsNewRow)
dataGridView1.Rows.Remove(r);
}
conn = new MySqlConnection(); // 创建mysql连接对象
MyConnectionString = "server = 192.168.0.22; uid = users; pwd = 85836262; charset = utf8 ;database =" + databaseName1;
conn.ConnectionString = MyConnectionString; // 配置连接
conn.Open(); // 打开连接
myadp = new MySqlDataAdapter("select * from " + tableName, conn);
objCommandBuilder = new MySqlCommandBuilder(myadp);
myadp.Update(dtTemp, tableName);
dtTemp.AcceptChanges();
MessageBox.Show("选中记录已成功从MySQL数据库中删除!");
conn.Close();
}
}
}

[li_xi_quan]

private void previousPage_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
//上一页
if (pageCurrent == 0)//如果当前页数为0，则提示操作无效，停止运行，防止异常发生
{
MessageBox.Show("没有数据显示，操作无效！");
}
else
{
pageCurrent--;
if (pageCurrent <= 0)//当前页号
{
MessageBox.Show("已经是第一页，请点击“下一页”查看！");
pageCurrent++;
return;
}
else
{
nCurrent = pageSize * (pageCurrent - 1);
}
LoadData();
if (this.currentPages.Value > 1)
{
--this.currentPages.Value;
}
}
}

private void nextPage_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
//下一页
if (pageCurrent == 0)//如果当前页数为0，则提示操作无效，停止运行，防止异常发生
{
MessageBox.Show("没有数据显示，操作无效！");
}
else
{
pageCurrent++;
if (pageCurrent > pageCount)//当前页号>页数
{
MessageBox.Show("已经是最后一页，请点击“上一页”查看！");
pageCurrent--;
return;
}
else
{
nCurrent = pageSize * (pageCurrent - 1);
}
LoadData();
if (this.currentPages.Value < this.currentPages.Maximum)
{
++this.currentPages.Value;
}
}
}

private void firstPage_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
//第一页
if (pageCurrent == 0)//如果当前页数为0，则提示操作无效，停止运行，防止异常发生
{
MessageBox.Show("没有数据显示，操作无效！");
}
else
{
pageCurrent = 1; //当前页数从1开始
nCurrent = 0; //当前记录数从0开始
LoadData();
this.currentPages.Value = 1;
}
}

private void lastPage_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
//最后页
pageCurrent = pageCount;
nCurrent = pageSize * (pageCurrent - 1);
if (pageCurrent == 0)//如果当前页数为0，则提示操作无效，停止运行，防止异常发生
{
MessageBox.Show("没有数据显示，操作无效！");
}
else
{
LoadData();
this.currentPages.Value = Convert.ToInt32(this.totalPages.Text);
}
}

private void currentPages_ValueChanged(object sender, EventArgs e)
{
//转到指定页
int page = Convert.ToInt32(this.currentPages.Value);
if (page > page1 && page <= pageCount)
{
int number = page - page1;
if (pageCurrent < pageCount)
{
pageCurrent = pageCurrent + number;
if (pageCurrent == pageCount)
{
nCurrent = pageSize * (pageCurrent - 1);

}
else
{
nCurrent = pageSize * (pageCurrent - 1);
}
LoadData();
}
}

if (0 < page && page < page1)
{
int number = page1 - page;
if (pageCurrent > 0)
{
pageCurrent = pageCurrent - number;
if (pageCurrent == 1)
{
nCurrent = pageSize * (pageCurrent - 1);
}
else
{
nCurrent = pageSize * (pageCurrent - 1);
}
LoadData();
}
}
}

private void saveAdd_Click(object sender, EventArgs e)//添加记录
{
if (dataGridView1.Rows.Count == 0)//如果dataGridView1的行数为0，说明其中尚未有数据(显示数据)，则提示并且中断刷新操作
{
MessageBox.Show("没有记录添加，不需要保存添加！");
}
else
{
if (MessageBox.Show("确定要添加记录到MySQL数据库吗？", "警告！", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
{
conn = new MySqlConnection(); // 创建mysql连接对象
MyConnectionString = "server = 192.168.0.22; uid = users; pwd = 85836262; charset = utf8 ;database = " + databaseName1;
conn.ConnectionString = MyConnectionString; // 配置连接
conn.Open(); // 打开连接
myadp = new MySqlDataAdapter("select * from " + tableName, conn);
objCommandBuilder = new MySqlCommandBuilder(myadp);
myadp.Update(dtTemp, tableName);
dtTemp.AcceptChanges();
MessageBox.Show("数据已成功添加到MySQL数据库！");
conn.Close();
}
}
}

private DataTable GetTableNames1(string sql)//GetTableNames1()方法，用于获取所有数据库名
{
MySqlConnection conn = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
DataTable getName = new DataTable();

//DataTable table = GetTableNames1("show databases");//调用GetTableNames1()获取所有数据库名
//databaseName2 = table.Rows[0][0].ToString();

conn = new MySqlConnection("Server=192.168.0.22;uid=users;pwd=85836262;charset = utf8 ;Database=xscj");////获取数据库名时，先要在某个数据库中建立一个登录验证的表
command = conn.CreateCommand();
command.CommandText = sql;
conn.Open();
reader = command.ExecuteReader();
getName.Load(reader);
conn.Close();
command = null;
reader.Close();
return getName;
}

private DataTable GetTableNames2(string sql)//GetTableNames2()方法，用于获取某个数据库中所有表名
{
MySqlConnection conn = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
DataTable getName = new DataTable();
conn = new MySqlConnection("Server=192.168.0.22;uid=users;pwd=85836262;charset = utf8 ;Database=" + databaseName1);
command = conn.CreateCommand();
command.CommandText = sql;
conn.Open();
reader = command.ExecuteReader();
getName.Load(reader);
conn.Close();
command = null;
reader.Close();
return getName;
}

[li_xi_quan]

private void getTableName_Click(object sender, EventArgs e)//获取MySQL服务器中某个数据库的所有表名，并且把所有表名在checkedListBox1中显示
{
if (databaseName1 == null)
{
MessageBox.Show("你还没有获取或者选中一个数据库名！");
}
else
{
checkedListBox1.Items.Clear();//当用户再次单击获取数据集按钮时，防止在checkedListBox1中重复显示，先清除之前checkedListBox1中的内容
DataTable table = GetTableNames2("show tables");//调用GetTableNames2()获取某个数据库中所有表名
for (int i = 0; i < table.Rows.Count; i++)
{
checkedListBox1.Items.Add(table.Rows[i][0].ToString());
}
}
}

private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
Application.Exit();
}

private void checkedListBox1_ItemCheck(object sender, ItemCheckEventArgs e)//控制checkedListBox1只能单选，不能复选
{
if (this.checkedListBox1.CheckedItems.Count > 0)
{
for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
{
if (i != e.Index)
{
this.checkedListBox1.SetItemCheckState(i, System.Windows.Forms.CheckState.Unchecked);
}
}
}
}

private void checkedListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
tableName = checkedListBox1.SelectedItem.ToString();
}

private void getDataBaseName_Click(object sender, EventArgs e)//获取MySQL服务器中所有的数据库名，并且把所有的数据库名在checkedListBox2中显示
{
checkedListBox2.Items.Clear();
DataTable table = GetTableNames1("show databases");//调用GetTableNames1()获取所有数据库名
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i][0].ToString() != "information_schema" && table.Rows[i][0].ToString() != "mysql"
&& table.Rows[i][0].ToString() != "performance_schema" && table.Rows[i][0].ToString() != "test"
&& table.Rows[i][0].ToString() != "work")
{
checkedListBox2.Items.Add(table.Rows[i][0].ToString());
}
else
continue;
}
}

private void checkedListBox2_ItemCheck(object sender, ItemCheckEventArgs e)//控制checkedListBox2只能单选，不能复选
{
if (this.checkedListBox2.CheckedItems.Count > 0)
{
for (int i = 0; i < this.checkedListBox2.Items.Count; i++)
{
if (i != e.Index)
{
this.checkedListBox2.SetItemCheckState(i, System.Windows.Forms.CheckState.Unchecked);
}
}
}
}

private void checkedListBox2_SelectedIndexChanged(object sender, EventArgs e)
{
databaseName1 = checkedListBox2.SelectedItem.ToString();
}
}
}

以上是程序的完整代码

[rucypli]

打印出sql语句

[li_xi_quan]

问题解决了，改用ODBC访问数据库，就没有问题了，呵呵。