高手救命啊！我用adapter的update方法来实现DataGrid中的数据修改，测试时用sqlclient正常，换成oledb就不行了。我百思不得其解……

Question

//这是用sqlclient的，可正常运行
using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Drawing; 
using System.Windows.Forms; public class DataGridSample:Form
{ 
DataGrid myGrid; 
   
SqlConnection con; 
SqlDataAdapter adapter; 
DataSet ds; 
Button ok, cancel; 
        
SqlParameter workParam = null; 
        
// apply to the columns in the table 
string query = "select CardNo,CardType,CardAmount, CardHolderName from CardTest"; 
        
// change the Server ,uid, pwd and database accordingly 
string url = "server=localhost;uid=sa;pwd=sa ;database=myData";  static void Main()
{ 
Application.Run(new DataGridSample()); 
}  public DataGridSample()
{ 
InitializeComponent(); 
}  public void InitializeComponent()
{ 
this.ClientSize = new System.Drawing.Size(550, 450); 
myGrid = new DataGrid(); 
myGrid.Location = new Point (10,10); 
myGrid.Size = new Size(500, 350); 
this.Text = "C# DataGrid with DataSet - Example"; 
this.Controls.Add(myGrid); 
        
ok = new Button(); 
ok.Location = new Point(10, 375); 
ok.Size = new Size(70, 30); 
ok.TabIndex = 1; 
ok.Text = "OK"; 
this.Controls.Add(ok);  
ok.Click += new System.EventHandler(button_Click);  cancel = new Button(); 
cancel.Location = new Point(95, 375); 
cancel.Size = new Size(70, 30); 
cancel.TabIndex = 1; 
cancel.Text = "Cancel"; 
this.Controls.Add(cancel);              
cancel.Click += new System.EventHandler(button_Click);  ConnectToData(); // establish database connection and create DataSet 
myGrid.SetDataBinding(ds, "CardTest"); 
DataTable t = ds.Tables["CardTest"]; 
t.RowChanged += new DataRowChangeEventHandler(Row_Changed); 
   
} 
        
public void ConnectToData()
{ 
ds = new DataSet(); 
con = new SqlConnection(url); 
adapter = new SqlDataAdapter(); 
adapter.SelectCommand = new SqlCommand(query, con); 
adapter.Fill(ds, "CardTest"); 
insertCommand(); 
updateCommand(); 
} 
        
public void updateCommand() 
{       
string query = "Update CardTest Set CardHolderName = @CardHolderName, CardType = @CardType, CardAmount = @CardAmount WHERE CardNo = @CardNo"; adapter.UpdateCommand = new SqlCommand(query, con); 
        
workParam = adapter.UpdateCommand.Parameters.Add("@CardNo", SqlDbType.VarChar ,50); 
workParam.SourceColumn = "CardNo"; 
workParam.SourceVersion = DataRowVersion.Original; 
        
workParam = adapter.UpdateCommand.Parameters.Add("@CardType",  SqlDbType.VarChar ,50); 
workParam.SourceVersion = DataRowVersion.Current; 
workParam.SourceColumn = "CardType";  workParam = adapter.UpdateCommand.Parameters.Add("@CardAmount",  SqlDbType.VarChar ,50); 
workParam.SourceColumn = "CardAmount"; 
workParam.SourceVersion = DataRowVersion.Current;  workParam = adapter.UpdateCommand.Parameters.Add("@CardHolderName",  SqlDbType.VarChar ,50); 
workParam.SourceColumn = "CardHolderName"; 
workParam.SourceVersion = DataRowVersion.Current; 
}         
public void button_Click(object sender, EventArgs evArgs) 
{ 
if (sender==ok)
{ 
UpdateValue(); // update the database once everything done. 
} 
if (sender==cancel) 
{ 
this.Dispose(); 
} 
}         
private void Row_Changed(object ob, DataRowChangeEventArgs e) 
{ 
DataTable t = (DataTable)  ob; 
Console.WriteLine("RowChanged " + e.Action.ToString() + "\t" + e.Row.ItemArray[0]); 
} 
          
public void insertCommand() 
{       
string insertQuery = "Insert into CardTest VALUES (@CardNo, @CardType, @CardAmount, @CardHolderName)"; 
adapter.InsertCommand = new SqlCommand(insertQuery, con); 
        

        
workParam = adapter.InsertCommand.Parameters.Add("@CardType",  SqlDbType.VarChar ,50); 
workParam.SourceVersion = DataRowVersion.Current; 
workParam.SourceColumn = "CardType";  workParam = adapter.InsertCommand.Parameters.Add("@CardAmount",  SqlDbType.VarChar ,50); 
workParam.SourceColumn = "CardAmount"; 
workParam.SourceVersion = DataRowVersion.Current; 
        
workParam = adapter.InsertCommand.Parameters.Add("@CardHolderName",  SqlDbType.VarChar ,50); 
workParam.SourceVersion = DataRowVersion.Current; 
workParam.SourceColumn = "CardHolderName";  workParam = adapter.InsertCommand.Parameters.Add("@CardNo",  SqlDbType.VarChar ,50); 
workParam.SourceColumn = "CardNo"; 
workParam.SourceVersion = DataRowVersion.Current; 
} 
        
        
public void UpdateValue() 
{ 
try 
{ 
adapter.Update(ds, "CardTest"); 
Console.Write("Updating DataSet succeeded!"); 
} 
catch(Exception e) 
{ 
Console.Write(e.ToString()); 
} 
} 
          
} 

Answer 1

//这是换成oledb后，在adapter.update出错using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Data.OleDb;
using System.Drawing; 
using System.Windows.Forms; public class DataGridSample:Form
{ 
DataGrid myGrid; 
OleDbConnection con;
OleDbDataAdapter adapter;
// SqlConnection con; 
// SqlDataAdapter adapter; 
DataSet ds; 
Button ok, cancel; 
    OleDbParameter workParam=null;    
// SqlParameter workParam = null; 
        
// apply to the columns in the table 
string query = "select * from CardTest"; 
        
// change the Server ,uid, pwd and database accordingly 
string url = "Provider=sqloledb;data source=localhost;initial catalog=myData;persist security info=True;user id=sa;password=sa;"; static void Main()
{ 
Application.Run(new DataGridSample()); 
}  public DataGridSample()
{ 
InitializeComponent(); 
}  public void InitializeComponent()
{ 
this.ClientSize = new System.Drawing.Size(550, 450); 
myGrid = new DataGrid(); 
myGrid.Location = new Point (10,10); 
myGrid.Size = new Size(500, 350); 
this.Text = "C# DataGrid with DataSet - Example"; 
this.Controls.Add(myGrid); 
        
ok = new Button(); 
ok.Location = new Point(10, 375); 
ok.Size = new Size(70, 30); 
ok.TabIndex = 1; 
ok.Text = "OK"; 
this.Controls.Add(ok);  
ok.Click += new System.EventHandler(button_Click);  cancel = new Button(); 
cancel.Location = new Point(95, 375); 
cancel.Size = new Size(70, 30); 
cancel.TabIndex = 1; 
cancel.Text = "Cancel"; 
this.Controls.Add(cancel);              
cancel.Click += new System.EventHandler(button_Click);  ConnectToData(); // establish database connection and create DataSet 
myGrid.SetDataBinding(ds, "CardTest"); 
DataTable t = ds.Tables["CardTest"]; 
t.RowChanged += new DataRowChangeEventHandler(Row_Changed); 
   
} 
        
public void ConnectToData()
{ 
ds = new DataSet(); 
con = new OleDbConnection(url);
adapter = new OleDbDataAdapter ();
adapter.SelectCommand = new OleDbCommand(query,con); 
OleDbCommandBuilder cmdbuilder=new OleDbCommandBuilder(adapter);
adapter.Fill(ds,"CardTest"); 
insertCommand(); 
updateCommand(); 
} 
        
public void updateCommand() 
{       

string query = "Update CardTest Set CardType = @CardType, CardAmount = @CardAmount,CardHolderName = @CardHolderName WHERE CardNo = @CardNo"; adapter.UpdateCommand = new OleDbCommand(query, con); 
        
workParam = adapter.UpdateCommand.Parameters.Add("@CardType", OleDbType.VarChar, 50); 
workParam.SourceVersion = DataRowVersion.Current; 
workParam.SourceColumn = "CardType";  workParam = adapter.UpdateCommand.Parameters.Add("@CardAmount", OleDbType.VarChar ,50); 
workParam.SourceColumn = "CardAmount"; 
workParam.SourceVersion = DataRowVersion.Current;  workParam = adapter.UpdateCommand.Parameters.Add("@CardHolderName", OleDbType.VarChar, 50); 
workParam.SourceColumn = "CardHolderName"; 
workParam.SourceVersion = DataRowVersion.Current;  workParam = adapter.UpdateCommand.Parameters.Add("@CardNo", OleDbType.VarChar,50); 
workParam.SourceColumn = "CardNo"; 
workParam.SourceVersion = DataRowVersion.Original; 
}         
public void button_Click(object sender, EventArgs evArgs) 
{ 
if (sender==ok)
{ 
UpdateValue(); // update the database once everything done. 
} 
if (sender==cancel) 
{ 
this.Dispose(); 
} 
}         
private void Row_Changed(object ob, DataRowChangeEventArgs e) 
{ 
DataTable t = (DataTable)  ob; 
Console.WriteLine("RowChanged " + e.Action.ToString() + "\t" + e.Row.ItemArray[0]); 
} 
          
public void insertCommand() 
{       
string insertQuery = "Insert into CardTest(CardNo,CardType,CardAmount,CardHolderName)  VALUES (@CardNo, @CardType, @CardAmount, @CardHolderName)"; 
adapter.InsertCommand = new OleDbCommand(insertQuery, con); 
        
workParam = adapter.InsertCommand.Parameters.Add("@CardNo", OleDbType.VarChar,50); 
workParam.SourceColumn = "CardNo"; 
workParam.SourceVersion = DataRowVersion.Current; 
        
workParam = adapter.InsertCommand.Parameters.Add("@CardType", OleDbType.VarChar, 50); 
workParam.SourceVersion = DataRowVersion.Current; 
workParam.SourceColumn = "CardType";  workParam = adapter.InsertCommand.Parameters.Add("@CardAmount", OleDbType.VarChar,50); 
workParam.SourceColumn = "CardAmount"; 
workParam.SourceVersion = DataRowVersion.Current; 
        
workParam = adapter.InsertCommand.Parameters.Add("@CardHolderName", OleDbType.VarChar, 50); 
workParam.SourceVersion = DataRowVersion.Current; 
workParam.SourceColumn = "CardHolderName"; 
} 
        
        
public void UpdateValue() 
{ 
try 
{ 
adapter.Update(ds,"CardTest"); 
Console.Write("Updating DataSet succeeded!"); 
} 
catch(Exception e) 
{ 
Console.Write(e.ToString()); 
} 
} 
          
} 

Answer 2

在oledb中参数使用 ？
string insertQuery = "Insert into CardTest VALUES (？, ？, ？，？)"; 其他不变，
我正在使用的就是oledb,哈哈

Answer 3

我给你改了一下using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Data.OleDb;
using System.Drawing; 
using System.Windows.Forms; public class DataGridSample:Form
{ 
DataGrid myGrid; 
OleDbConnection con;
OleDbDataAdapter adapter;
// SqlConnection con; 
// SqlDataAdapter adapter; 
DataSet ds; 
Button ok, cancel; 
    OleDbParameter workParam=null;    
// SqlParameter workParam = null; 
        
// apply to the columns in the table 
string query = "select * from CardTest"; 
        
// change the Server ,uid, pwd and database accordingly 
string url = "Provider=sqloledb;data source=localhost;initial catalog=myData;persist security info=True;user id=sa;password=sa;"; static void Main()
{ 
Application.Run(new DataGridSample()); 
}  public DataGridSample()
{ 
InitializeComponent(); 
}  public void InitializeComponent()
{ 
this.ClientSize = new System.Drawing.Size(550, 450); 
myGrid = new DataGrid(); 
myGrid.Location = new Point (10,10); 
myGrid.Size = new Size(500, 350); 
this.Text = "C# DataGrid with DataSet - Example"; 
this.Controls.Add(myGrid); 
        
ok = new Button(); 
ok.Location = new Point(10, 375); 
ok.Size = new Size(70, 30); 
ok.TabIndex = 1; 
ok.Text = "OK"; 
this.Controls.Add(ok);  
ok.Click += new System.EventHandler(button_Click);  cancel = new Button(); 
cancel.Location = new Point(95, 375); 
cancel.Size = new Size(70, 30); 
cancel.TabIndex = 1; 
cancel.Text = "Cancel"; 
this.Controls.Add(cancel);              
cancel.Click += new System.EventHandler(button_Click);  ConnectToData(); // establish database connection and create DataSet 
myGrid.SetDataBinding(ds, "CardTest"); 
DataTable t = ds.Tables["CardTest"]; 
t.RowChanged += new DataRowChangeEventHandler(Row_Changed); 
   
} 
        
public void ConnectToData()
{ 
ds = new DataSet(); 
con = new OleDbConnection(url);
adapter = new OleDbDataAdapter ();
adapter.SelectCommand = new OleDbCommand(query,con); 
OleDbCommandBuilder cmdbuilder=new OleDbCommandBuilder(adapter);
adapter.Fill(ds,"CardTest"); 
insertCommand(); 
updateCommand(); 
} 
        
public void updateCommand() 
{       

string query = "Update CardTest Set CardType = ?, CardAmount = ?,CardHolderName = ? WHERE CardNo = ?"; //改了这里 adapter.UpdateCommand = new OleDbCommand(query, con); 
        
workParam = adapter.UpdateCommand.Parameters.Add("@CardType", OleDbType.VarChar, 50); 
workParam.SourceVersion = DataRowVersion.Current; 
workParam.SourceColumn = "CardType";  workParam = adapter.UpdateCommand.Parameters.Add("@CardAmount", OleDbType.VarChar ,50); 
workParam.SourceColumn = "CardAmount"; 
workParam.SourceVersion = DataRowVersion.Current;  workParam = adapter.UpdateCommand.Parameters.Add("@CardHolderName", OleDbType.VarChar, 50); 
workParam.SourceColumn = "CardHolderName"; 
workParam.SourceVersion = DataRowVersion.Current;  workParam = adapter.UpdateCommand.Parameters.Add("@CardNo", OleDbType.VarChar,50); 
workParam.SourceColumn = "CardNo"; 
workParam.SourceVersion = DataRowVersion.Original; 
}         
public void button_Click(object sender, EventArgs evArgs) 
{ 
if (sender==ok)
{ 
UpdateValue(); // update the database once everything done. 
} 
if (sender==cancel) 
{ 
this.Dispose(); 
} 
}         
private void Row_Changed(object ob, DataRowChangeEventArgs e) 
{ 
DataTable t = (DataTable)  ob; 
Console.WriteLine("RowChanged " + e.Action.ToString() + "\t" + e.Row.ItemArray[0]); 
} 
          
public void insertCommand() 
{       
string insertQuery = "Insert into CardTest(CardNo,CardType,CardAmount,CardHolderName)  VALUES (?, ?, ?, ?)"; //改了这里
adapter.InsertCommand = new OleDbCommand(insertQuery, con); 
        
workParam = adapter.InsertCommand.Parameters.Add("@CardNo", OleDbType.VarChar,50); 
workParam.SourceColumn = "CardNo"; 
workParam.SourceVersion = DataRowVersion.Current; 
        
workParam = adapter.InsertCommand.Parameters.Add("@CardType", OleDbType.VarChar, 50); 
workParam.SourceVersion = DataRowVersion.Current; 
workParam.SourceColumn = "CardType";  workParam = adapter.InsertCommand.Parameters.Add("@CardAmount", OleDbType.VarChar,50); 
workParam.SourceColumn = "CardAmount"; 
workParam.SourceVersion = DataRowVersion.Current; 
        
workParam = adapter.InsertCommand.Parameters.Add("@CardHolderName", OleDbType.VarChar, 50); 
workParam.SourceVersion = DataRowVersion.Current; 
workParam.SourceColumn = "CardHolderName"; 
} 
        
        
public void UpdateValue() 
{ 
try 
{ 
adapter.Update(ds,"CardTest"); 
Console.Write("Updating DataSet succeeded!"); 
} 
catch(Exception e) 
{ 
Console.Write(e.ToString()); 
} 
} 
          
}