Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Sunday, July 14, 2024 
 
slxdeveloper.com Community Forums  
   
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
 Data & Imports Forums - SSIS/DTS
Forum to discuss using SQL SSIS or DTS to perform SalesLogix imports. View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to SSIS/DTS | New ThreadView:  Search:  
 Author  Thread: update table with activex
Valeda
Posts: 15
 
update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 14 Jul 06 11:00 AM
fiogf49gjkf0d
Hello,

I need to update a table with the results from an activex task. Can I do that within the task? If so, how do I accomplish this?
[Reply][Quote]
Valeda
Posts: 15
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 14 Jul 06 3:19 PM
fiogf49gjkf0d
I've tried creating a execute SQL task that inserts a record into the existing table. I am not sure how to get it to accept the global variable.
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 16 Jul 06 4:37 AM
fiogf49gjkf0d
You can do this in an ActiveX script.

Here's a chunk of code that executes a SQL statement. Note that it relies on the existence of a global variable SQLConnectionString, containing the ADODB, erm, connection string - if you need help setting this, please post again.

Function Main()
Dim strConn
Dim objConn
dim strSQL

'Create connection
strConn = DTSGlobalVariables("SQLConnectionString").Value

Set objConn=CreateObject("ADODB.Connection")
objConn.open strConn

strSQL = "insert statement here"
'Execute the SQL statement
objConn.execute strSQL

'Tidy up
Set objConn=nothing

Main = DTSTaskExecResult_Success
end function

You'll see from this that your insert statement can be as complex as you want...
[Reply][Quote]
Valeda
Posts: 15
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 17 Jul 06 7:27 AM
fiogf49gjkf0d
Thanks for you reply Phil. I am actually using JScript. Sorry I didn't mention that. Could you give me an example in JScript? Wouldn't I need to reference the table on the SQL server in the connection string?
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 17 Jul 06 8:07 AM
fiogf49gjkf0d
Sorry, I don't know JScript, but ADODB SQL connection strings do not reference tables, merely databases. It will look something like this:

Provider=SLXOLEDB.1; Password=xxxxxx; Persist Security Info=True;User ID=admin;Initial Catalog=[dbname];Data Source=[servername];Extended Properties="PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;"
[Reply][Quote]
Valeda
Posts: 15
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 17 Jul 06 8:13 AM
fiogf49gjkf0d
Thanks for trying, but this does not really tell me how to insert a record into an existing table in activex jscript.

Anyone else out there familiar with this process?
[Reply][Quote]
Valeda
Posts: 15
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 17 Jul 06 11:53 AM
fiogf49gjkf0d
How do I get the Execute SQL Task to recognize the DTSGlobalvariable created in an Activex Task?

I am using a simple insert statement:
INSERT
INTO lastWorkDate
(lstWrkDt)
VALUES
(rundate)

rundate should be the DTSGlobalvariable.
[Reply][Quote]
Ryan Farley
Posts: 2265
slxdeveloper.com Site Administrator
Top 10 forum poster: 2265 posts
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jul 06 6:21 PM
fiogf49gjkf0d
To use the global variable in an Execute SQL task, simply do the following:

1) Provide a param placeholder "?" for the value in your statement. For example:

insert into mytable (myfield) values (?)
--or
update mytable set myfield = ?


2) Then you need to specify the global you want to use in the place of the param. To do this click the Parameters button.

3) In the parameters dialog, in the first column select the global you want to use and in the second column you choose the parameter you want to assign it to (they will be numbered based on order they appear in the query).

That is all. Make sense?
[Reply][Quote]
Ryan Farley
Posts: 2265
slxdeveloper.com Site Administrator
Top 10 forum poster: 2265 posts
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jul 06 6:35 PM
fiogf49gjkf0d
I should mention that you can specify output variables for Execute SQL tasks. With SLX, you can use an Execute SQL task to create ID values too. Pass in a global to specify the table name to use with slx_dbids, and then an output param to catch the value which you can use later. Of couse, if this is v6.2.3 or higher I would suggest just using the autoincrement key feature instead
[Reply][Quote]
Valeda
Posts: 15
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Jul 06 8:25 AM
fiogf49gjkf0d
Once again Ryan, you are my night in shinihg armor. Thanks
[Reply][Quote]
Ryan Farley
Posts: 2265
slxdeveloper.com Site Administrator
Top 10 forum poster: 2265 posts
 
Re: update table with activexYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Jul 06 11:41 AM
fiogf49gjkf0d
Quote:
Originally posted by Valeda

Once again Ryan, you are my night in shinihg armor. Thanks


Hehe. Glad I could help.
[Reply][Quote]
 Page 1 of 1 
  You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

   Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
 

 
 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 7/14/2024 1:43:14 AM