LabVIEW communication between SQL server and LabVIEW

[Robinm76]

Hello,

It is my last topic their because it is probably my last problem. I started LabVIEW 1month ago in my internship, in fact I have no real problem with LabVIEW because my code is not that hard.

The goal of my project is:

1) Create a program to communicate with the Power Supply GW INSTEK GPD-2303S to test the current on some cards.

2) Generate an Excel report to give information on the test (Name of the card, Execution Date, Currents, Voltages)

3) Stock the datas of the test in a Database, using SQL Server thanks to ODBC Data Sources (32bits).

Ok so I am at the last step, I have created a program that works at 99% the problem is that the first column is "Id", and it is generate Automatically in Database. I got an error, I have 14 Columns + Id = 15 Columns. I can not explain to LabVIEW that I have 15 columns or it will try to erase the Id that is Automatically generate after each execution. But I can not say to LabVIEW that I have 14 Columns, because it say some datas have the wrong type, because it has the information that the 1st Column need a "int"...

On the last post some guys said me to learn SQL because that's the only solution. So I started to learn it.

My problem is there:

SELECT * FROM dbo.[Tests.Step1.PowerSupplies]

INSERT INTO dbo.[Tests.Step1.PowerSupplies] (AssemblyPN, SerialNumber, ComputerName,
UserName, TestTime, BSTP_Version, Passed, Measure1_Name, Measure1_Value,
Measure1_Unit, Measure1_BoundMin, Measure1_BoundMax, Measure1_Passed,
Measure1_BoundCheck)

VALUES ('100','CHAR','CHAR','CHAR','11-11-11 11:11:11','CHAR','0','CHAR','0.01','CHAR','0.01','0.01','0','0');
GO

What do I have to insert in "VALUES" to communicate the datas from LabVIEW in each columns? The "%d, %c" don't work.

If there is any other way to explain LabVIEW to don't erase the first value of the column then it will be great.

- Here is the SUB-VI DATABASE_vs9, that's where I stock the DATAS to send it to DATABASE.

I hope someone can help me, I have to find a solution before the end of this week.

Best Regards,

Robin.

[tst]

Mike is certainly right that learning about DBs and SQL is useful, but your current VI should work - you just need to tell it which columns you're giving it. You don't need to tell it about the ID column or give it any value for it, because it's set as an automatic column. Once you give it a cluster with 14 values and an array with the correct 14 column names, it should work and insert a new line (and create an ID value for it automatically).

As for how to do it without the DB toolkit, a useful tool for it is the Format into String function, where the % codes are relevant (you use them to define how to format specific values which you wire into the function).

[Robinm76]

Thanks for your answer,

I am using MS SQL Code Factory to change the code.

- Here is a screenshot of the SQL Generator, do I need to change it? Like without "Id"? Or maybe it is not here...

- A screenshot of the error after I add format into String function where I think it was needed.

- The DATABASE_VS9 with the modifications.

[tst]

You have badly misunderstood. First, if the ID value is automatic, then you should not feed it in. It will be created automatically.

Secondly, there are different ways of interacting with DBs. SQL is one, and I'm sure Mike's blog covers it. The code used by the DB toolkit is another. The format into string option would be the relevant one for the SQL option. Here are two ways of inserting the same data:

Hopefully that clears things up.

[Robinm76]

Oh ok, I have tried the second option but I got another error, there is a screenshot on this.

I know that point can make problem but underscores are bad too? I am going to try with " " and maybe with out underscores. I have 14 Values, 14 Columns specified, 14 Datas.

That clear things up and I know I am going out of that databases problems

[Robinm76]

Hi,

I am doing it step by step and it will be really hard...

1st problem is the format date/time, I don't know what format I have to send, but "%t" and " '%s' "do not work making an other error.

2nd problem is the "_" in the columns names, it makes an other error code too.

3rd problem and there I do not know why: ADO Error: 0x000001F8 Open recordset object. What does it mean please?

[dkfire]

You need to take a step back and learn a little about SQL and the SQL functions in LabVIEW. 

Right now you are trying to do the same thing 2 time in a row. 

When you have a complet insert SQL statement, you only need to call the DB execute Query. 

When you only have the data and a list of column names, then you can use the Insert Data vi. 

But don't use them like you do now. 

Now take a look at some of the examples on the DB tool kit. 

One thing you should see is that when you call the DB Execute Query, you need a call to the Free Object vi after that. 

[Robinm76]

I had attached the wrong one that's why you think it, sorry. Probably I did not save at the end of the modifications.

- My problem is not on underscores only BSTP_Version is like that.

Edit: Now I can see on execution some columns are working,

The only problems are there:

- TestTime: Wrong format

- BSTP_Version: ??! Making no sense but well...

- Measure1_Value, BoundMin, BoundMax: Looking like %f or something do not work.

About the Free OBject vi, really thanks. I had never used DB Execute Query so I was looking for something but I did  not find it.

Looking for the others problems

[Robinm76]

Well, about Measure1_Value, BoundMin, BoundMax: If I use %d it can transmit it in %f to the database. That's the single solution for the moment, but well it works.

I am going to look how to change string in a date/time format or is it ok if I ask them to change the date/time format in string in the database? In fact the string that I send looks like "30/06/2016 à 10:13:11" is it possible to send it in another format? what about DB Tools Format DateTime String?

That's my last problem to make the database and LabVIEW fully communicate. I am looking on forum for it. I am coming back after it.

Just to know: Is there a way to get the Computer name and the User Name in string? I know that you can use commands in LabVIEW but it do not send the answer in string format right?

[dkfire]

How does the format look like in the database for the date/time coloumn? 

You could make use of the database time function now() in your instert: 

INSERT INTO [Tests.Step1.PowerSupplies] ([AssemblyPN], [SerialNumber], [ComputerName],
[UserName], [TestTime], [BSTP_Version], [Passed], [Measure1_Name], [Measure1_Value],
[Measure1_Unit], [Measure1_BoundMin], [Measure1_BoundMax], [Measure1_Passed],
[Measure1_BoundCheck])

VALUES (%d,'%s','%s','%s',NOW(),'%s',%b,'%s',%.3f,'%s',%.3f,%.3f,%b,%b);