使用LINQ在ms访问中插入数据 [英] insert data in ms access using LINQ

问题描述

我创建了一个Access数据库，并添加了一个名为 Person 的表。这个表的定义是：



人

I created a Access database, and added a table named "Person". the definition of this table is:

Person

ID: auto number
Name: text(20)
Address: text(50)

在c＃中，我将此表映射到

In c#, I mapped this table to

[Table]
class Person
{
[Column(IsPrimaryKey = true, IsDbGenerated=true)]
public long ID {get; set;}
[Column]
public string Name{get; set;}
[Column]
public string Address{get; set;}
}

并编写了这样的代码：

And wrote such code:

string connectionString = "Provider=.....";
OleDbConnection con = new OleDbConnection(connectionString);
DataContext ctx = new DataContext(con);
Table<person> PersonTable = db.GetTable<person>();
Person p1 = new Person();
            p1.Name = "Name";
            p1.Address = "Address";
PersonTable.InsertOnSubmit(p1);
db.SubmitChanges();

我收到错误说用户代码对OLEDB异常进行了处理 - 缺少分号（; ）在SQL语句结束时。，为什么？如何解决？



非常感谢你的建议。

I got an error says "OLEdb exception was handeled by usercode - Missing semicolon (;) at end of SQL statement.", why? and how to resolve it?

Many thanks for your advice.

推荐答案

Wes已经指出了答案，你仍然不知道该怎么做？

再次，你在SQL结束时错过了一些分号声明，请检查您的SQL查询并用分号填写或在此发布您的查询，我们可以查看它。



问候



PS @Wes：投票给你写过的解决方案。

Hi,

Wes pointed the answer already, and you still don't know what to do?
Again, you missed some semicolon at the end of your SQL statement, so please check your SQL-Query and fill it up with a semicolon or post your Query here that we can have a look at it.

Regards

PS @Wes: Voted up a solution you've wrote.

添加一行

Add a line

ctx.Log = Console.Out;

你的代码，你发现生成的SQL是smth。喜欢

to your code and you find out that a generated SQL is smth. like

INSERT INTO [Person]([Name], [Address])
VALUES (@p0, @p1)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input String (Size = 4000; Prec = 0; Scale = 0) [Name]
-- @p1: Input String (Size = 4000; Prec = 0; Scale = 0) [Address]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

似乎ACE是n不满意'INSERT INTO ... VALUES（@ p0，@ p1）SELECT CONVERT ...'的结构。



它的SQL结束了'SELECT CONVERT'因此它要求分号放在那里。



补救措施非常简单。从Person.ID成员的Column属性中删除字符串IsDbGenerated = true，并负责在代码中生成主键。类似

It seems that ACE is not happy with the construct of 'INSERT INTO ... VALUES (@p0, @p1) SELECT CONVERT ...'.

It rather had the SQL ended at 'SELECT CONVERT' and therefore it requests a semicolon put there.

A remedy is quite simple. Remove a string IsDbGenerated=true from a Column attribute on Person.ID member, and take responsibility of generating a Primary Key in your code. Something like

    class insertwithlinqmapping
    {
	static void Main()
	{
	    string ConnectionString = 
                   String.Concat("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\\db.accdb");
	    OleDbConnection connection = new OleDbConnection(ConnectionString);
	    DataContext ctx = new DataContext(connection);
	    ctx.Log = Console.Out;
	    Table<objtable> objs = ctx.GetTable<objtable>();
	    var newObj = new ObjTable();
	    newObj.ID = ((from obj in objs
			  select (int?) obj.ID).Max() ?? 0) + 1;
	    newObj.Field1 = string.Concat("Field 1, rec ", newObj.ID.ToString());
	    newObj.Field2 = string.Concat("Field 2, rec ", newObj.ID.ToString());
	    objs.InsertOnSubmit(newObj);
	    ctx.SubmitChanges();
	}
    }

    [Table]
    class ObjTable
    {
	[Column(IsPrimaryKey = true)]
	public int ID {get; set;}
	[Column]
	public string Field1 {get; set;}
	[Column]
	public string Field2 {get; set;}
    }
</objtable></objtable>

这篇关于使用LINQ在ms访问中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！