insert data in ms access using LINQ

Question

3.00/5 (2 votes)

See more:

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

Person

XML

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

In c#, I mapped this table to

SQL

[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:

C#

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();

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.

Posted 14-Jul-12 1:17am

tanishtaman

Updated 14-Jul-12 1:57am

Sebastian T Xavier

v2

Add a Solution

Comments

[no name] 14-Jul-12 7:26am

What part of "Missing semicolon (;) at end of SQL statement." is unclear to you?

tanishtaman 14-Jul-12 7:30am

Its clear but dont know how to resolve it....? Can u please tell.

I want to insert record in MSAccess using linq

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

El_Codero · Answer 1 · 2012-07-14T04:18:00

Solution 1

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.

Posted 14-Jul-12 4:18am

El_Codero

Updated 14-Jul-12 4:20am

v2

Comments

tanishtaman 18-Jul-12 7:40am

i have already posted all what i did.

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();

here it is and on line "db.SubmitChanges();" error comes

El_Codero 18-Jul-12 15:50pm

Ok, we need some more info. What enviroment are you using?VS 2010?64 Bit or 32 bit system? which version of oledb driver? What does db.? GetTable Method is used by DataContext (ctx in your case). Regards

tanishtaman 28-Jul-12 1:25am

i'm using VS: 2010,system bit: 32bit, OLEDB server: Microsoft.Jet.OLEDB.4.0,

El_Codero 28-Jul-12 4:27am

Do you have installed ACE? http://www.microsoft.com/en-us/download/details.aspx?id=23734

Vladimir Tregub · Answer 2 · 2013-08-09T03:52:00

Add a line

C#

ctx.Log = Console.Out;

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

SQL

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

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

C#

    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>