Inserting data to a . SDF database (SQL Server Compact Edition connection)

[Porras]

Hi, all.

I'm working on a project that worked fine while connecting to SQL Server 2008. I used a connection string like this:

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Now, I've replaced all database connections in my project to work with SQL Server Compact Edition, and my connection string is something like this:

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=myDir\myDataBase.sdf; 

After changing the type of connection, I'm getting an error on "DB Tools Insert Data.vi". This is what I get:

Error -2147217872 occurred at NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib: DB Tools Insert Data.vi->Main.vi

Possible reason(s):ADO Error: 0x80040E30 Exception occured in Microsoft SQL Server Compact OLE DB Provider: The given type name was unrecognized. [,,,,,] in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib: DB Tools Insert Data.vi->Main.vi

This is a simplified block diagram (as I said, it worked perfectly while using SQL Server 2008 connection):

Could you please help me?

Thanks in advance,

Francisco

[SteveChandler]

Create a system DSN from the Windows control panel and use the name you give it in the connection settings in your VI. You can test the connection from the control panel.

[SteveChandler]

Nevermind, that's probably not it since the error happened in the inset and not in the open. Can you try to create the SQL insert yourself and use the DB Tols Execute Query VI under advanced?

[Porras]

Hi, Steve. Thanks for your answers.

I worked around this problem by using directly a SQL query instead of this function (you were right, using DBTools Execute Query VI). But now I'm getting problems with other functions, when inserting data into the DB including NULL values.

I make a SQL query like this using DBTools Execute Query VI:

INSERT INTO Table1 (Column1, Column2, Column3, Column4, Column5, Column6) VALUES ('Value1', 'Value2', 'Value3', 'NULL', 'Value5', 'Value6') 

and get the following error:

ADO Error: 0x80040E07Exception occured in Microsoft SQL Server Compact OLE DB Provider: A literal value in the command could not be converted to the correct type due to a reason other than data overflow. [,,,,,] in NI_Database_API.lvlib:Conn Execute.vi->INSERT (new) restricción (DBCT).vi->Restricciones a Base de Datos (DBCT).vi->Definir - editar restricciones.vi->Main.vi

The same SQL query worked perfectly when using the SQL Server 2008 connection, so it's not a database structure issue.

Could somebody please help me?

Regards,

Francisco

[SteveChandler]

Try not quoting the NULL.

[Porras]

Not quoting the NULL value, I get a different error:

SQL command:

INSERT INTO Table1 (Column1, Column2, Column3, Column4, Column5, Column6) VALUES ('Value1', 'Value2', 'Value3', NULL, 'Value5', 'Value6')

Error:

ADO Error: 0x80040E2F Exception occured in Microsoft SQL Server Compact OLE DB Provider: A specified value violated the integrity constraints for a column or table. [,,,,,] in NI_Database_API.lvlib:Conn Execute.vi->Main.vi

[Porras]

Finally I solved the problem!

It wasn't a SQL Server Compact Edition issue. I was trying to introdruce a value in a column that is foreign key which didn't have a corresponding value in the table where it is primary key.

Thanks!