这是个简单的读写数据库的程序.编译没有问题.但是更新和新建的数据无法写入数据库.请大家指教
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
namespace _0070620_Data
{
public partial class Form1 : Form
{
OleDbConnection OleDbConnection1;
OleDbCommand OleDbCommand1;
OleDbDataAdapter OleDbDataAdapter1;
private bool bNewRecord = false; //表示是否处在插入新记录的状态
private void getCustomerID()
{//获取客户的ID
OleDbDataReader sdr;
OleDbConnection1.Open();
//执行SQL语句并返回
sdr = OleDbCommand1.ExecuteReader(CommandBehavior.CloseConnection);
cbxID.Items.Clear();
while (sdr.Read())
{
cbxID.Items.Add(sdr.GetValue(0));
}
sdr.Close();
cbxID.SelectedIndex = 0;
}
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“shiliDataSet.客户”中。您可以根据需要移动或移除它。
this.客户TableAdapter.Fill(this.shiliDataSet.客户);
String conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=shili.mdb";
String sSQL = "Select * From 客户";
//创建一个数据库连接对象
OleDbConnection1 = new OleDbConnection(conn);
OleDbCommand1 = new OleDbCommand(sSQL, OleDbConnection1);
OleDbCommand1.CommandText = "Select 客户ID From 客户 Order By 客户ID";
OleDbDataAdapter1 = new OleDbDataAdapter(sSQL,OleDbConnection1);
//创建一个DataSet对象
DataSet DataSet1 = new DataSet();
OleDbDataAdapter1.Fill(DataSet1, "客户");
getCustomerID();
}
private void cbxID_SelectedIndexChanged(object sender, EventArgs e)
{
//创建SQL命令对象
OleDbCommand Olecmd = new OleDbCommand("Select * From 客户 where 客户ID=@客户ID", OleDbConnection1);
//设置参数
Olecmd.Parameters.AddWithValue("@客户ID",cbxID.Text);
OleDbDataReader sdr;
OleDbConnection1.Open();
sdr=Olecmd.ExecuteReader();
if (sdr.Read())
{
textBox2.Text = sdr.GetString(1);
textBox3.Text = sdr.GetString(2);
textBox4.Text = sdr.GetString(3);
textBox5.Text = sdr.GetString(5);
textBox6.Text = sdr["电话"].ToString(); //电话
textBox7.Text = sdr.GetString(8);
textBox8.Text = sdr.GetString(9);
textBox9.Text = sdr.GetString(4);
textBox10.Text = sdr["邮政编码"].ToString(); //邮政编码
textBox11.Text = sdr["传真"].ToString();//传真
}
sdr.Close();
OleDbConnection1.Close();
}
private void toolStripButton1_Click(object sender, EventArgs e)
{//前一条记录
if (cbxID.SelectedIndex > 0)
cbxID.SelectedIndex--;
else { MessageBox.Show("这是第一条记录", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
}
private void toolStripButton2_Click(object sender, EventArgs e)
{//后一条记录
if (cbxID.SelectedIndex < cbxID.Items.Count-1)
cbxID.SelectedIndex++;
else { MessageBox.Show("这是最后一条记录", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
}
private void toolStripButton3_Click(object sender, EventArgs e)
{//转到第一条记录
cbxID.SelectedIndex = 0;
}
private void toolStripButton4_Click(object sender, EventArgs e)
{//转到最后一条记录
cbxID.SelectedIndex = cbxID.Items.Count-1;
}
private void toolStripButton5_Click(object sender, EventArgs e)
{//新建记录
textBox2.Text ="";
textBox3.Text ="";
textBox4.Text ="";
textBox5.Text ="";
textBox6.Text ="";
textBox7.Text ="";
textBox8.Text ="";
textBox9.Text ="";
textBox10.Text ="";
textBox11.Text ="";
cbxID.DropDownStyle = ComboBoxStyle.DropDown;
cbxID.Text = "";
bNewRecord = false;
}
private void toolStripButton6_Click(object sender, EventArgs e)
{//保存记录
String OleDbStatement;
//根据是否正在添加新记录来建立适当的SQl语句
if (bNewRecord == false)
{
OleDbStatement = "Insert Into 客户 values(";
OleDbStatement += "'" + cbxID.Text + "',";
OleDbStatement += "'" + textBox2.Text + "',";
OleDbStatement += "'" + textBox3.Text + "',";
OleDbStatement += "'" + textBox4.Text + "',";
OleDbStatement += "'" + textBox9.Text + "',";
OleDbStatement += "'" + textBox5.Text + "',";
OleDbStatement += "'" + textBox10.Text + "',";
OleDbStatement += "'" + textBox6.Text + "',";
OleDbStatement += "'" + textBox7.Text + "',";
OleDbStatement += "'" + textBox8.Text + "',";
OleDbStatement += "'" + textBox11.Text + "')";
}
else
{
OleDbStatement = "Update 客户 Set";
OleDbStatement += "客户ID='" + cbxID.Text + "',";
OleDbStatement += "公司名称='" + textBox2.Text + "',";
OleDbStatement += "联系人姓名='" + textBox3.Text + "',";
OleDbStatement += "联系人头衔='" + textBox4.Text + "',";
OleDbStatement += "联系地址='" + textBox9.Text + "',";
OleDbStatement += "城市='" + textBox5.Text + "',";
OleDbStatement += "政编邮码='" + textBox10.Text + "',";
OleDbStatement += "电话='" + textBox6.Text + "',";
OleDbStatement += "地区='" + textBox7.Text + "',";
OleDbStatement += "国家='" + textBox8.Text + "',";
OleDbStatement += "传真='" + textBox11.Text + "'";
OleDbStatement += "where 客户ID='" + cbxID.Text + "'";
}
OleDbCommand OleDbcmd = new OleDbCommand(OleDbStatement, OleDbConnection1);
try
{
OleDbConnection1.Open();
int rowAffected = OleDbcmd.ExecuteNonQuery();
if (rowAffected == 1) //如果受影响的行数为1行
{
cbxID.Items.Add(cbxID.Text);
}
}
catch (Exception Err)
{
MessageBox.Show("更新错误:" + Err.Message, "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
OleDbConnection1.Close();
}
if (bNewRecord ==false)
{
cbxID.DropDownStyle = ComboBoxStyle.DropDownList;
bNewRecord = true;
cbxID.SelectedIndex = cbxID.Items.Count - 1;
}
}
}
}
