|
Issue with Selects and 6.2.1 Provider?
Posted: 09 Jul 06 4:53 PM
|
fiogf49gjkf0d Hi all,
Last week I went to a client to deliver a solution. They're on SLX 6.2.1. I had written a DTS package using a few VB Script tasks to import some data. It worked perfectly here on my 6.2.3 system. (yes, I know, different SP's)
Now, here's a code snippet so that you can see what I am doing... (you'll get the drift - it's not perfect)
set cnSLX = CreateObject("ADODB.Connection")
cnSLX.ConnectionString = strSLXConnect ' a valid connection string found from a UDL file - stored in a global variable for the DTS
cnSLX.Open
[snip - get some values from another recordset]
idAccount = GetSLXID(cnSLX, "ACCOUNT")
strInsert = "INSERT INTO ACCOUNT " & _
"(ACCOUNTID, ACCOUNT, TYPE, ADDRESSID, SHIPPINGID, MAINPHONE, EMAIL, SECCODEID, ACCOUNT_UC, " & _
"ACCOUNTMANAGERID, USERFIELD1, IMPORTSOURCE, CREATEDATE, CREATEUSER, MODIFYDATE, MODIFYUSER) " & _
"Values " & _
"('" & idAccount & "', '" & Replace(strOrgName, "'", "''") & "', 'Organisation', '" & idAAddress & "', '" & idAAddress & "', '" & strPhone & "', '" & strEmail & "', 'SYST00000001', '" & UCase(Replace(strOrgName, "'", "''")) & "', " & _
"'ADMIN', '" & strID & "', 'EnergyAuditAccessDB', GETDATE(), 'ADMIN', GETDATE(), 'ADMIN') "
cnSLX.Execute strInsert
[snip - a bunch of other inserts]
set rs = cnSLX.Execute("SELECT * FROM ACCOUNT WHERE ACCOUNTID = '" & idAccount & "'")
msgbox rs.Fields("ACCOUNT")
rs.Close
set rs = nothing
So, here's what I'm seeing...
My connection opens OK. I can check the state of it and it says it's open.
The ID Creation works fine - it uses the standard SLX Stored procedure. I get a value back.
My insert statement works fine. The record I create I can see in the database using Query Analyser and it's visible in the SLX client...
The final Select statement returns me an empty recordset, yet I know the record has been inserted as I can see it in the DB.
Also, changing the select statement to run using rs.Open SQLStatement, cnSLX also doesn't make any difference... still comes back empty
Any ideas? This is a bit of a show stopper as we have a lot of selects going on with some custom stuff we've built, and I'm not confident that I can get it to work using the SLX OLEB Provider.
Anyone know of an issue with the 6.2.1 provider????
Antony Currington
|
|
|