C# 如何连接到 MS Access 2007
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/508345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# How to connect to MS Access 2007
提问by
I have a problem connecting to my MS Access DB 2007. Code:
我在连接到 MS Access DB 2007 时遇到问题。代码：
private void btnSave_Click(object sender, EventArgs e)
{
OleDbConnection Conn = new OleDbConnection();
try
{
string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ Directory.GetCurrentDirectory() +"\dvd_manager.accdb;Persist Security Info=False;";
Conn.ConnectionString = conn;
Conn.Open();
int i = cbbLocatie.SelectedIndex + 65;
char c = (char)i;
string sql = "INSERT INTO DVD (titel, locatie)VALUES(@titel, @locatie)";
OleDbCommand Com = new OleDbCommand();
Com.CommandText = sql;
Com.Connection = Conn;
OleDbParameter Param = new OleDbParameter("@titel", txtTitle.Text);
Com.Parameters.Add(Param);
Param = new OleDbParameter("@locatie", c);
Com.Parameters.Add(Param);
Com.ExecuteNonQuery();
Conn.Close();
MessageBox.Show("Data is opgeslagen " + sql);
}
catch (Exception ex)
{
MessageBox.Show("Fout opgetreden: " + ex.Message);
}
finally
{
Conn.Close();
}
}
When I run this code, the messagebox comes up. This should mean that my data is inserted. But when i open the accdb file no data is inserted. What am i doing wrong?
当我运行此代码时，出现消息框。这应该意味着我的数据已插入。但是当我打开 accdb 文件时，没有插入数据。我究竟做错了什么？
Thnx
谢谢
Edit: The return value of ExecuteNonQuery() is 1 (I edit my post, because i cannot add any comments, when I click add comment, the box doesn't show up..)
编辑：ExecuteNonQuery() 的返回值为 1（我编辑了我的帖子，因为我无法添加任何评论，当我点击添加评论时，该框不显示..）
Edit 2: I have created a class with the Title and Location properties. Code: private void btnSave_Click(object sender, EventArgs e) { OleDbConnection Conn = new OleDbConnection();
编辑 2：我创建了一个具有 Title 和 Location 属性的类。代码：private void btnSave_Click(object sender, EventArgs e) { OleDbConnection Conn = new OleDbConnection();
try
{
string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ Directory.GetCurrentDirectory() +"\dvd_manager.accdb;Persist Security Info=False;";
Conn.ConnectionString = conn;
// Create object
Medium M = new Medium();
int i = cbbLocatie.SelectedIndex + 65;
char c = (char)i;
M.Location = c;
M.Title = txtTitle.Text;
Conn.Open();
string sql = "INSERT INTO DVD (titel, locatie)VALUES(@titel, @locatie)";
OleDbCommand Com = new OleDbCommand();
Com.CommandText = sql;
Com.Connection = Conn;
OleDbParameter Param1 = new OleDbParameter("@titel", M.Title);
Com.Parameters.Add(Param1);
OleDbParameter Param2 = new OleDbParameter("@locatie", M.Location);
Com.Parameters.Add(Param2);
int ret = Com.ExecuteNonQuery();
Conn.Close();
MessageBox.Show("Data is opgeslagen " + ret);
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Fout opgetreden: " + ex.Message);
}
finally
{
Conn.Close();
}
}
}
Since i still cannot click on the add comment button, here's my new code with nameless sql parameters:
由于我仍然无法点击添加评论按钮，这是我的带有无名 sql 参数的新代码：
// some code
Conn.Open();
string sql = "INSERT INTO DVD (titel, locatie)VALUES(?, ?)";
OleDbCommand Com = new OleDbCommand();
Com.CommandText = sql;
Com.Connection = Conn;
OleDbParameter Param1 = new OleDbParameter("@p1", OleDbType.VarChar, 1);
Param1.Value = M.Title;
Com.Parameters.Add(Param1);
OleDbParameter Param2 = new OleDbParameter("@p2", OleDbType.VarChar, 255);
Param2.Value = M.Location;
Com.Parameters.Add(Param2);
int ret = Com.ExecuteNonQuery();
Conn.Close();
// morde code
回答by Khadaji
ExecuteNonQuery will return an int indicating the number of rows affected. The first thing I would do is check the return. ExecuteNonQuery can execute and not affect any rows, that won't trigger the catch.
ExecuteNonQuery 将返回一个 int 值，指示受影响的行数。我要做的第一件事就是检查退货。ExecuteNonQuery 可以执行并且不会影响任何不会触发捕获的行。
回答by Jesper Palm
To my knowledge you can't use named parameters with the OleDbParameter.
据我所知，您不能将命名参数与 OleDbParameter 一起使用。
Your insert should look like:
您的插入内容应如下所示：
string sql = "INSERT INTO DVD (titel, locatie)VALUES(?, ?)";
And then you have to add OleDbParameters in the correct order. The names are not used.
然后您必须以正确的顺序添加 OleDbParameters。不使用名称。
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx
Edit:
编辑：
Untested code below but here is an example of how I would do it.
下面未经测试的代码，但这里是我将如何做的一个例子。
using(OleDbConnection connection = new OleDbConnection(CONNECTION_STRING))
{
using(OleDbCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "INSERT INTO DVD(title,locatie)VALUES(?,?)";
command.Parameters.Add("@p1", OleDbType.VarChar, 1).Value = M.Title;
command.Parameters.Add("@p2", OleDbType.VarChar, 255).Value = M.Location;
connection.Open();
int ret = command.ExecuteNonQuery();
}
}
回答by Ahmed Yassin
When I code, I like to simplify my work and not to redo ever time I have a method. I would simple create a method for parameters such as:
当我编码时，我喜欢简化我的工作，而不是在我有方法时重做。我会简单地为参数创建一个方法，例如：
public void setParameter(String paramAT, String paramTxt)
{
OleDbCommand myCommand;
DbParameter parameter = myCommand.CreateParameter();
parameter.ParameterName = paramAT;
parameter.Value = paramTxt;
myCommand.Parameters.Add(parameter);
}
public int CreateDVD()
{
try
{
string strSqldvd = "INSERT INTO DVD(title,locatie)VALUES(@title,@locate?)";
myCommand = (OleDbCommand)dbconn.MyProvider.CreateCommand();
dbconn.MyConnection.Open();
myCommand.Connection = dbconn.MyConnection;
myCommand.CommandText = strSqldvd;
setParameter("@title",M.Title );
setParameter("@locate", M.Location);
}
catch (Exception)
{
throw new ArgumentException();
}
int count = myCommand.ExecuteNonQuery();
dbconn.MyConnection.Close();
return count;
}
This is how simply. I insert and keep using this parameter method in my update and insert etc.... Hope this will help.
这是多么简单。我在我的更新和插入等中插入并继续使用这个参数方法......希望这会有所帮助。
