Hi there,

I'm new to nhibernate and I would like to start using it in my applications.

I'm trying to run a small testprogram to see how nhibernate works with an access database. The testprogram should be able to read data out of the database and to write a line to the database. I'm using VB.net for the coding.

Now reading the rows in the database is no problem, but when I try to write a line to the database i get this exception:

An unhandled exception of type 'NHibernate.ADOException' occurred in testmdb.exe

Additional information: Could not insert

When I take a look at my log file that I have created I see this:

2005-07-10 14:35:38,250 [2956] DEBUG NHibernate.Persister.EntityPersister [] <> - Inserting entity: Tabel1 (native id)
2005-07-10 14:35:38,250 [2956] DEBUG NHibernate.Impl.BatcherImpl [] <> - about to open: 0 open IDbCommands, 0 open DataReaders
2005-07-10 14:35:38,265 [2956] DEBUG NHibernate.Impl.BatcherImpl [] <> - Building an IDbCommand object for the SqlString: INSERT INTO Tabel1 (familienaam, naam) VALUES (:familienaam, :naam); select SCOPE_IDENTITY()
2005-07-10 14:35:38,265 [2956] DEBUG NHibernate.Persister.EntityPersister [] <> - Dehydrating entity: Tabel1#
2005-07-10 14:35:38,265 [2956] DEBUG NHibernate.Type.NullableType [] <> - binding 'famnaam' to parameter: 0
2005-07-10 14:35:38,265 [2956] DEBUG NHibernate.Type.NullableType [] <> - binding 'naam' to parameter: 1
2005-07-10 14:35:38,265 [2956] INFO NHibernate.Impl.BatcherImpl [] <> - Preparing INSERT INTO Tabel1 (familienaam, naam) VALUES (?, ?); select SCOPE_IDENTITY()
2005-07-10 14:35:41,031 [2956] ERROR NHibernate.Persister.EntityPersister [] <> - Characters found after end of SQL statement.
Exception: System.Data.OleDb.OleDbException
Message: Characters found after end of SQL statement.
Source: Microsoft JET Database Engine
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Persister.EntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlString sql, Object obj, ISessionImplementor session)

2005-07-10 14:35:41,031 [2956] DEBUG NHibernate.Impl.BatcherImpl [] <> - done closing: 0 open IDbCommands, 0 open DataReaders
2005-07-10 14:35:41,046 [2956] ERROR NHibernate.ADOException [] <> - Could not insert
Exception: System.Data.OleDb.OleDbException
Message: Characters found after end of SQL statement.
Source: Microsoft JET Database Engine

the data im trying to write:

ID = int32 autonumber in database
naam= string
voornaam = string


Can someone tell me what I'm doing wrong and what I can do to make this small testprogram work?

Thnx

S.

anyone??

From the insert statement it looks like you're using the MsSql2000 dialect (select scope_identity()). People have reported some success with the MsSql7 dialect on Access. At least it will solve the issue in your case because it does a select @@identity after an insert, which Access supports.

Hi,

thnx for the reply!
I tried the dialect but I get this:

2005-07-12 22:21:06,870 [484] INFO NHibernate.Impl.BatcherImpl [] <> - Preparing INSERT INTO Tabel1 (familienaam, naam) VALUES (?, ?); select @@identity
2005-07-12 22:21:09,464 [484] ERROR NHibernate.Persister.EntityPersister [] <> - Characters found after end of SQL statement.
Exception: System.Data.OleDb.OleDbException
Message: Characters found after end of SQL statement.
Source: Microsoft JET Database Engine
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Persister.EntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlString sql, Object obj, ISessionImplementor session)

2005-07-12 22:21:09,464 [484] DEBUG NHibernate.Impl.BatcherImpl [] <> - done closing: 0 open IDbCommands, 0 open DataReaders
2005-07-12 22:21:09,464 [484] ERROR NHibernate.ADOException [] <> - Could not insert
Exception: System.Data.OleDb.OleDbException
Message: Characters found after end of SQL statement.


I get indeed select @@identity, but it still won't work

I don't understand it, is it so hard to make a connection to a MS access database with nhibernate? I tried nhibernate on an sql server DB and that worked, but now I have to use a MS access database and I get a lot of problems. Even when I use a database with only 1 table.

these are the xml files I use:

tabel1.hbm.xml:


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" >
<class name="Tabel1, testmdb" table="Tabel1">
<id name="Id" type="Int32" column="Id" unsaved-value = "0">
<generator class="native" />
</id>
<property name="familienaam" column="familienaam" type="String" />
<property name="naam" column="naam" type="String" />
</class>
</hibernate-mapping>


app.config:



<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="nhibernate" type="System.Configuration.NameValueSectionHandler, System, Version=1.0.5000.0,Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
</configSections>

<nhibernate>

<add
key="hibernate.connection.provider"
value="NHibernate.Connection.DriverConnectionProvider"
/>
<add
key="hibernate.connection.driver_class"
value="NHibernate.Driver.OleDbDriver"
/>
<add key="hibernate.connection.connection_string"

value="Provider=Microsoft.Jet.OLEDB.4.0;User Id=admin;Data Source=C:\testvb\test.mdb"

/>

<add
key="hibernate.dialect"
value="NHibernate.Dialect.MsSql7Dialect"
/>





</nhibernate>

<log4net>


<appender name="rollingFile" type="log4net.Appender.RollingFileAppender,log4net" >

<param name="File" value="log.txt" />
<param name="AppendToFile" value="true" />
<param name="RollingStyle" value="Date" />
<param name="DatePattern" value="yyyy.MM.dd" />
<param name="StaticLogFileName" value="true" />

<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c [%x] &lt;%X{auth}&gt; - %m%n" />
</layout>
</appender>



<root>
<priority value="ALL" />
<appender-ref ref="rollingFile" />
</root>

</log4net>
</configuration>

Well, Access is not really (or really not) supported. There have been various attempts in the past to make it work, but there are just way too many issues.

There have been some topics about it on the old forum (and maybe also here) and there are a couple of JIRA issues related to Access. Maybe you can find some points that will get you anything further.

Is there a simple (free) alternative for MS access that I can use then? I would like to use a DB without installing a lot of things.

I'd check out Firebird or SQLite for simple light-weight solutions.

MSDE is free and SQL Compatible. Or, depending on your deployment date look at SQL Server 2005 Express.

BOb

Have you been to this URL?

http://wiki.nhibernate.org/display/NH/Microsoft+Access?showAttachments=true#attachments

I was able to add it into the hibernate source, and compile it. I had to make some changes though.

For instance, the constructor ended up being this:



I also modified some of the base class's variables from private to protected.

It worked fine for me. However, I decided not to use it because it isn't supported and it looked like I would have to worry about avoiding certain situations using joins.