NI Home > Community > NI Discussion Forums

LabVIEW

Showing results for 
Search instead for 
Do you mean 
Reply
Member
Porras
Posts: 131
0 Kudos

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

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

SQLServerCompactConnection.PNG

 

 

Could you please help me?

 

Thanks in advance,

Francisco

Trusted Enthusiast
SteveChandler
Posts: 2,257
0 Kudos

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

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.
=====================
LabVIEW 2012


Trusted Enthusiast
SteveChandler
Posts: 2,257
0 Kudos

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

[ Edited ]

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?

=====================
LabVIEW 2012


Member
Porras
Posts: 131
0 Kudos

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

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

Trusted Enthusiast
SteveChandler
Posts: 2,257
0 Kudos

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

Try not quoting the NULL.

=====================
LabVIEW 2012


Member
Porras
Posts: 131
0 Kudos

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

[ Edited ]

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

Member
Porras
Posts: 131
0 Kudos

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

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!