Lookout

cancel
Showing results for 
Search instead for 
Did you mean: 

Exporting text data to MicroSoft Acess via SQLExec

Solved!
Go to solution

I have sucessfully been able to export numberical data into a Micosoft Access database (mdb).  However I'm having issues exporting text data from a datamember.  I've been using the SQLExec object to accomplish this.

 

Below is the SQLExec general syntax i've implemented for text values:

"INSERT INTO test (column1) VALUES('"&(text(\\.\Process1\TEXTENTRY.value,"0")&"')

The value from the datamember TEXTENTRY will import into the mdb file, column1 if it is numeric.  Not if it is alpha-numeric.  The column is set up for text format.

 

However, I am able to import 'fixed' characters (not datamember text) to the database i.e:

"INSERT INTO test (column1) VALUES('hello')  the result is hello will be in column1

 

Please advise.

Thank you !

0 Kudos
Message 1 of 5
(6,063 Views)
Solution
Accepted by topic author HJH72

The text() function converts the numeric to text, so you don't need it.

 

Try VALUE ('"&TEXTENTRY&"') 

Ryan Shi
National Instruments
0 Kudos
Message 2 of 5
(6,052 Views)

Hello,

It seems you found the solution to the problem.  Quick note though; if you are allowing users to enter text and they enter an apostrophe ('), it will error out.  Simple add a replace(textentry, "'","''") and replace single apostrophe with a double apostrophe.

 

Mike

-----------------------------------------------------------------------
Forshock - Consult.Develop.Solve.
0 Kudos
Message 3 of 5
(6,022 Views)

Hi,

 

I want to add one line to the sqlexec that I have. This is what I want to add:

 

ON DUPLICATE KEY UPDATE TextSerialNo=\\.\ION7500\Textentry\TextSerialNo

 

to this:

 

"INSERT INTO dielectricpretest
(RunTime,
TextEntryModelNo,
TextEntryPartNo,
TextPO,
TextSerialNo,
TextkVARating,
TextVPrimary,
TextVSecondary,
ExpTopologyPrimary,
ExpTopologySecondary,
ExpMaterialPrimary,
ExpMaterialSecondary,
TextEntryTargetEfficiency,
TextEntryTargetNoise,
ExpHiPotPrimaryGroundPass,
ExpHiPotSecondaryToGroundPass)
VALUES
(NOW(),
'"&\\.\ION7500\Textentry\TextEntryModelNo&"',
'"&\\.\ION7500\Textentry\TextEntryPartNumber&"',
'"&\\.\ION7500\Textentry\TextPO&"',
'"&\\.\ION7500\Textentry\TextSerialNo&"',
'"&\\.\ION7500\Textentry\TextkVARating&"',
'"&\\.\ION7500\Textentry\TextVprimary&"',
'"&\\.\ION7500\Textentry\TextVSeconadry&"',  
'"&\\.\ION7500\expression\ExpTopologyPrimary&"',  
'"&\\.\ION7500\expression\ExpTopologySecondary&"',
'"&\\.\ION7500\expression\ExpMaterialPrimary&"',
'"&\\.\ION7500\expression\ExpMaterialSecondary&"',
'"&\\.\ION7500\Textentry\TextEntryTargetEfficiency&"',
'"&\\.\ION7500\Textentry\TextEntryTargetNoise&"',
'"&\\.\ION7500\expression\ExpHiPotPrimaryGround&"',
'"&\\.\ION7500\expression\ExpHiPotSecondaryToGround&"');

 

I'm not sure about the & sign and the brackets. Everytime I add it, it gives me a syntax error. Can anyone help? Thanks.

 

0 Kudos
Message 4 of 5
(5,779 Views)

Nevermind,

 

I got it:

 

"INSERT INTO dielectricpretest
(RunTime,
TextEntryModelNo,
TextEntryPartNo,
TextPO,TextSerialNo,
TextkVARating,
TextVPrimary,
TextVSecondary,
ExpTopologyPrimary,
ExpTopologySecondary,
ExpMaterialPrimary,
ExpMaterialSecondary,
TextEntryTargetEfficiency,
TextEntryTargetNoise,
ExpHiPotPrimaryGroundPass,
ExpHiPotSecondaryToGroundPass)
VALUES
(NOW(),
'"&\\.\ION7500\Textentry\TextEntryModelNo&"',
'"&\\.\ION7500\Textentry\TextEntryPartNumber&"',
'"&\\.\ION7500\Textentry\TextPO&"',
'"&\\.\ION7500\Textentry\TextSerialNo&"',
'"&\\.\ION7500\Textentry\TextkVARating&"',
'"&\\.\ION7500\Textentry\TextVprimary&"',
'"&\\.\ION7500\Textentry\TextVSeconadry&"',  
'"&\\.\ION7500\expression\ExpTopologyPrimary&"',  
'"&\\.\ION7500\expression\ExpTopologySecondary&"',
'"&\\.\ION7500\expression\ExpMaterialPrimary&"',
'"&\\.\ION7500\expression\ExpMaterialSecondary&"',
'"&\\.\ION7500\Textentry\TextEntryTargetEfficiency&"',
'"&\\.\ION7500\Textentry\TextEntryTargetNoise&"',
'"&\\.\ION7500\expression\ExpHiPotPrimaryGround&"',
'"&\\.\ION7500\expression\ExpHiPotSecondaryToGround&"'),
ON DUPLICATE KEY UPDATE TextSerialNo='"&\\.\ION7500\Textentry\TextSerialNo&"';"

0 Kudos
Message 5 of 5
(5,767 Views)