August 9, 2002 at 4:51 pm
Hi all,
I'm hoping this isn't going to be too stupid of a question. I'm working
on a DTS package that has a couple of script tasks. These tasks are using
ADO to retrieve a recordset with specific fields. Most of the work is done
within Main(), but there is another function I'm using to perform updates or
additions to the recordset. The Main() function passes the appropriate
recordsets as parameters to the update function.
Here's an example:
----------------------------------------------------------------
Function Main()
Dim oConnection
Dim sSql
Dim rs1
Dim rsNew
Set oConnection = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
Set rsNew = CreateObject("ADODB.Recordset")
oConnection.Open <connection info here>
sSql = "select A.ID, A.Company, Address, Phone from CompDB..Info A,
Other..OtherInfo B where A.ID = B.ID"
Set rs1 = oConnection.Execute(sSql)
While Not rs1.EOF Then
rsNew.Open NewDB..SomeInfo, oConnection, 0, 3
rsNew.AddNew
AddOrUpdateRecord oConnection, rs1, rsNew
rsNew.Close
rs1.MoveNext
Wend
rs1.Close
oConnection.Close
Main = DTSTaskExecResult_Success
End Function
Function AddOrUpdateRecord(Conn, rs1, rsNew)
rsNew("SomeValue").Value = rs1("Company")
rsNew("SomeValue2").Value = rs1("Address")
rsNew("SomeValue3").Value = rs1("Phone")
...
...
rsNew.Update
End Function
----------------------------------------------------------------
The problem I'm having is that, for example, rs1("Company") will work fine, but
rs1("Address") will be an empty value, and rs1("Phone") will work fine.
rs1("Address") DOES have a value earlier, though ... I can add a Msgbox
rs1("Address") right after I retrieve the recordset and one just before I
try to set rsNew("SomeValue2").Value in the other function, and the first
msgbox will show the value, the second will be empty. Other times, both boxes will display a value. When this only occurring for one field, I started working around it by using string variables - set it to the retrieved value, then set rsNew's field to that variable, but this is a huge pain to do for every variable.
Has anyone seen this before? Am I doing something horribly wrong? I know
that's possible, but I'd expect NONE of the fields to work if that were the
case. Most of the script works just fine, it just appears to be random
fields that "lose" their value. I would be most appreciative of ANY help
someone can give. The example above is just that, an example. If anyone
wants to look at the actual code I'm using, I can provide it.
Many thanks for any help someone can provide.
Thank you,
Jeff
August 9, 2002 at 9:19 pm
Have you tried putting the code for updating the recordset in your main function and running that? In other words, not use a second function at all? If it works that way then I would say that the problem lies in the way the recordset is being passed to the function. If you are simply adding a new record to rsNew, why not do that in Function Main? Just curious...
hth,
Michael
Michael Weiss
Michael Weiss
August 10, 2002 at 2:38 am
Michael (and anyone else reading),
I did think about the possibility of this being an issue with the passing of the recordset(s), so I made the variables global to the task and tried it that way - no luck. Also - I can add Msgbox's in places both within Main() and the called function, and both will check out.
As for the reason I'm adding to/updating the recordset out of the function - I actually have multiple sections within Main() that call AddOrUpdateRecord(), my post just has an example of what I'm doing. I'd prefer not to have to duplicate code multiple times in Main() (although that may be something I try next).
As mentioned, I have definitely tried opening up the scope of the variables, but with no luck. In my actual task, I probably have about 20 values that are being set in AddOrUpdateRecord, but it's only a few that "lose" their values.
Thank you,
Jeff
August 10, 2002 at 5:58 am
Dont use them often so I'd have to check to be sure, but I'd bet the problem lies with the recordset type. You're taking the defaults, which I "think" are a server side read only forward only.
Andy
August 10, 2002 at 9:29 am
Jeff,
I bet Andy is right...
Try using
Const adLockOptimistic = 3
Const adUseClient = 3
Const adOpenDynamic = 2
rsNew.CursorLocation = adUseClient
rsNew.Open NewDB..SomeInfo, oConnection, adOpenDynamic , adLockOptimistic
Let us know what you find...
hth,
Michael
Michael Weiss
Michael Weiss
August 13, 2002 at 4:38 pm
Andy and Michael,
My apologies for the delayed response, but I didn't have a chance to really work with this again until today (so many things to do, not enough time to do them - you know the story).
Anyway, the solution Michael provided wasn't quite it, but the information you both gave was enough to push me in the right direction.
In case you're curious - the problem wasn't with the way I was opening rsNew (I was already using adLockOptimistic for rsNew), but in the way I was opening rs1. I needed to open rs1 with a CursorType of adOpenStatic or adOpenDynamic (I'm using Static because I don't need to update/insert for rs1) - the default cursortype is adOpenForwardOnly, and I'm guessing that because I wasn't referencing the fields in the order they were read in, I was losing some of them.
Everything seems to be working fine now. I *GREATLY* appreciate the responses and the assistance - you saved me a lot of stress and headaches.
Thank you!
Jeff
August 13, 2002 at 5:27 pm
We try to help! Visit often.
Andy
August 13, 2002 at 11:15 pm
Thank you, Jeff, for the info on how you solved your problem. That is good stuff to know - those things sure can be frustrating to figure out! Glad you got it working though...
Michael
Michael Weiss
Michael Weiss
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply