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!
|
|
update table with activex
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? |
|
|
|
Re: update table with activex
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. |
|
|
|
Re: update table with activex
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... |
|
|
|
Re: update table with activex
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? |
|
|
|
Re: update table with activex
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;" |
|
|
|
Re: update table with activex
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? |
|
|
|
Re: update table with activex
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. |
|
|
|
Re: update table with activex
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? |
|
|
|
Re: update table with activex
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  |
|
|
| |
|
Re: update table with activex
Posted: 20 Jul 06 11:41 AM
|
fiogf49gjkf0d Originally posted by Valeda
Once again Ryan, you are my night in shinihg armor. Thanks |
|
Hehe. Glad I could help.  |
|
|
|