Click here to Skip to main content
14,735,487 members
Please Sign up or sign in to vote.
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
ID: auto number
Name: text(20)
Address: text(50)


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


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
Updated 14-Jul-12 2:57am
v2
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

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.
   
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
Add a line

ctx.Log = Console.Out;


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


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>
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900