Jump to content

Insert NULL value in a SQL Server datetime field


Recommended Posts

Hi,

 I'm trying to insert some NULL values in a datetime field.

In the example, the DATA_INSERIMENTO field has a non empty value and it works correctly but DATA_INTERVENTO doesn't accept NULL.
If I use an empty string instead of null, the VI run without any errors but it fill the database field with a 1900-01-01 and it's not what I want.


nullstring1.png

 

If I use the DB Tools NULL VI it gives me another type of error, maybe 'cause I'm connecting a variant to a cluster of string.
If i use a Variant to Data VI for the NULL value it returns an empty string so not the result I need.

 

nullstring2.png

If use the string you see in the label at the bottom of my diagram in SQL Server manager, it works correctly.
How can I obtain the same result with labview?

Edited by Gepponline
Link to post
Share on other sites

If the value is null, you could probably just leave it out from the insert (ie insert using a 3 element array and a 3 element cluster, leaving out the null value).

Worst comes to worst, you could create the insert string yourself and execute it, but I know thats not ideal.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By ChuckBorisNorris
      Hi All, Long-time lurker, first-time poster here.
      As the title (hopefully) implies, I have an issue connecting to a database I've created. 
      The problems started this week after coming back to look at the project after ~3 months. I know I had to repair my Office 365 installation last week and I'm wondering if that could be the cause but other than that I'm at a complete loose end.
      Microsoft support say it's a LabVIEW issue, NI support say it's an MSDN issue. The official NI forum, after looking at similar posts, was even less helpful. So I thought I'd come and see if the nice guys at LAVA had experienced a similar issue.
      I'm aware that the error code is a generic one and has multiple causes but I am simply writing a "SELECT [column] FROM [table];" statement to an 'Execute Query' vi. The error returned from my sanity checking vi is: 
      (Code -2147217900) NI_Database_API.lvlib:Conn Execute.vi->Untitled 1<ERR>ADO Error: 0x80040E14
      Exception occured in Microsoft Access Database Engine:  in query expression '[UserID]'. in NI_Database_API.lvlib:Conn Execute.vi->Untitled 1
      The connection string returned is (without line breaks):
      Provider=Microsoft.ACE.OLEDB.12.0;
      User ID=Admin;
      Data Source=T:\PROJECTS\02-ACTIVE\119888 RF Power upgrade\Database\RFPU.accdb;
      Mode=Share Deny None;
      Jet OLEDB:System database="";
      Jet OLEDB:Registry Path="";
      Jet OLEDB:Database Password="";
      Jet OLEDB:Engine Type=6;
      Jet OLEDB:Database Locking Mode=1;
      Jet OLEDB:Global Partial Bulk Ops=2;
      Jet OLEDB:Global Bulk Transactions=1;
      Jet OLEDB:New Database Password="";
      Jet OLEDB:Create System Database=False;
      Jet OLEDB:Encrypt Database=False;
      Jet OLEDB:Don't Copy Locale on Compact=False;
      Jet OLEDB:Compact Without Replica Repair=False;
      Jet OLEDB:SFP=False;
      Jet OLEDB:Support Complex Data=False;
      Jet OLEDB:Bypass UserInfo Validation=False;
      The BD for my sanity check looks like this:

      I'm using LabVIEW 2016 32-bit on a W7 Dell Ultrabook with Access2013 as part of our Office 365 installation. BTW the query executes fine in Access2013. Is that enough info?
      Regards,
      Boris
       
    • By shahril
      Hi all,
      I need to query data from MS SQL server at different PC using LAN cable. Do you guys have any example to do this?
      Please help.
    • By lisam
      Hi all. I recently got the Database Connectivity Toolkit (DBCT) on LabVIEW 2016 and I'm trying to insert multiple rows into a database.
      Using a sine signal input I attempted to insert 50 rows in one iteration, but every method I tried returned 50 values in one row. Using the end of line constant and array to spreadsheet string, returned what looked like 50 rows, but when I queried "SELECT COUNT(*) FROM.." the no of rows was equal to the number of iterations.
      On another forum post, someone suggested it is not possible to insert multiple rows using the DBCT. Can anyone confirm this?
      I have written some code which allows me to insert 3 rows in one iteration, under a single connection. However, this method is very tedious and wouldn't work for large amounts of data.
       

       
      Can anyone think of a better way to do this?
       
      Thanks in advance,
       
      Lisa
    • By shoneill
      My question relates to retrieving decimated data from the database.
       
      Given the case where I have 1M X and 1M Y steps (a total of 1000000M data points per channel) how do I efficiently get a decimated overview of the data?
       
      I can produce the correct output data be using
       
      Select X,Y,Z,Float1 from P
      WHERE X=0
      GROUP BY Y/1000
       
      This will output only 1x1000 data instead of 1x1000000 datapoints, one dimension quickly decimated  Problem is that is iterates over all data (and this takes quite some time).  If I do a different query to retrieve only 1000 normal points, it executes in under 100ms.
       
      I would like to use a CTE to do this, thinking that I could address directly the 1000 elements I am looking for.
      WITH RECURSIVE cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1000 FROM cnt LIMIT 1000 )WHAT GOES HERE?; So if I can create a cte with column x containing my Y Indices then how do I get from this to directly accessing
      Float1 FROM P WHERE X=0 AND Y=cte.x
       
      SELECT Float1 from P WHERE X IN (SELECT x FROM cnt) AND Y=0 AND Z=0
      Using the "IN" statement is apparently quite inefficient (and seems to return wrong values).  Any ideas?
       
      In addition, accessing even a small number of data points from within an 8GB SQL File (indexed!) is taking upwards ot 30 seconds to execute at the moment.  With 1.5GB files it seemed to be in the region of a few milliseconds.  Does SQLite have a problem with files above a certain size?
    • By SDietrich
      Name: libpq
      Submitter: SDietrich
      Submitted: 01 Mar 2014
      Category: Database & File IO
      LabVIEW Version: 2009License Type: Other (included with download)



      This is a package containing LabVIEW bindings to the client library of the PostgreSQL database server (libpq).
       
      The DLL version 9.3.2 and its dependencies are included in the package. This DLLs are taken out of a binary distribution from the Postgres-Website and are thread-safe (e.g. the call to PQisthreadsafe() returns 1). As of the moment the DLLs are 32bit only.
       
      The VIs are saved in LabVIEW 2009.
       
      So this package works out of the box if you have a 32bit LabVIEW 2009 or higher on any supported Windows operating system.
       
      Because this obviously is a derived work from PostgreSQL it is licensed by the PostgreSQL license.
       
       
      A few words regarding the documentation: This package is meant for developers who know how to use the libpq. You have to read and understand the excellent documentation for the library. Nonetheless all VIs contain extracts of that documentation as their help text.
       
      What's coming next?
      - adding support for 64bit
      - adding support for Linux (anybody out there to volunteer for testing?)
      - adding support for MAC (anybody out there to volunteer for testing?)

      Click here to download this file
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.