January 30, 2004 at 10:29 am
I have two pieces of code that do about the same thing in concept. I want the code to return a disconnected updateable recordset. I want the piece that uses the command to work so I can pass parameters to a stored procedure rather than calling an "Exec sp ? ? ?". Both pieces of code create disconnected recordsets, it's just that using the Command does not leave it updateable because you can't set the lock and cursor properties. The code I have commented out in the second section would never hold since I am doing a Set rstLocations = cmdLocations.Execute. This will overwrite any properties of a previously created recordset.
Does this make sence???
Any suggestions?
Is this even possible? Any help will be appreciated!!!
-----------------This works---------------------
Set conn = New ADODB.Connection
Set rstLocations = New ADODB.Recordset
conn.Open connString
rstLocations.CursorLocation = adUseClient
rstLocations.LockType = adLockBatchOptimistic
rstLocations.CursorType = adOpenForwardOnly
rstLocations.Open "Select * From esi_PriceHistoryLocations", conn
Set rstLocations.ActiveConnection = Nothing
conn.Close
-------------------------------------------------
--------------This Does Not Work-----------------
Dim cmdLocations As ADODB.Command
Set cmdLocations = New ADODB.Command
Set conn = New ADODB.Connection
' Set rstLocations = New ADODB.Recordset
conn.Open connString
' rstLocations.CursorLocation = adUseClient
' rstLocations.LockType = adLockBatchOptimistic
' rstLocations.CursorType = adOpenForwardOnly
cmdLocations.CommandType = adCmdStoredProc
cmdLocations.CommandText = "esi_PH_GetAllLocations"
cmdLocations.ActiveConnection = conn
Set rstLocations = cmdLocations.Execute
' rstLocations.Open "Select * From esi_PriceHistoryLocations", conn 'It works if I use this line instead of the line above
Set rstLocations.ActiveConnection = Nothing
conn.Close
--------------------------------------------------
February 2, 2004 at 8:00 am
This was removed by the editor as SPAM
February 13, 2004 at 7:57 am
Hi,
I'm not quite sure I understand what it is that you're trying to do but if you want to update a table in your database by using a stored procedure and passing the updateable fields as parameters then this is what you do:
Public Function UpdateFields(field1 As String, field2 As Integer, field3 As Integer)
Dim objCmd1 As ADODB.Command
Set objCmd1 = New ADODB.Command
Set objCmd1.ActiveConnection = whatever connection string you have set up
objCmd1.CommandType = adCmdStoredProc
objCmd1.CommandText = "esi_PH_GetAllLocations"
objCmd1.Parameters(1) = field1
objCmd1.Parameters(2) = field2
objCmd1.Parameters(3) = field3
objCmd1.Execute
UpdateFields = objCmd1.Parameters(0) 'if you have a return value (pass/fail ??)
Set objCmd1 = Nothing
End Function
Hope this helps - if I have misunderstood your query completely, pl. explain in detail.
**ASCII stupid question, get a stupid ANSI !!!**
February 13, 2004 at 8:07 am
Yes you have misunderstood. Sorry I was not clear. I want an disconnected recordset that I can keep in my client application and update as the user makes changes and then run an UpdateBatch to update the database with all of the changes.
This is easy to do in my Working Example, however, I was wondering if you can do this same thing with a command object by passing a stored procedure parameters.
It works when you use an rst.Open but not when you use a
Set rst = cmd.Execute
I guess it kind of makes sence that this doesn't work because you can't set any of the rst parameters required for a disconnected recordset such as Cursorlocation = adUseClient, adLockType = adLockBatchOptimistic, and CursorType = adOpenForwardOnly before you bring back the recordset since you are setting it.
In short, I want an updateable disconnected recordset which I can get. I just want to bring it back from a stored procedure by passing parameters rather than doing an rst.Open.
This may not be possible. I was just wondering if anyone had got it to work.
I hope this is more clear.
February 13, 2004 at 8:18 am
Sorry - I don't know that this is possible as well & I'd be interested in knowing if anyone has accomplished this.
The only thing that I have done is to store all user changes in an array and then use either a recordset.update or pass updateable fields to a stored procedure by looping through the array - in this code have also used a "begin tran" and "commit tran" - but this is the closest I've come to what I think you're describing.
**ASCII stupid question, get a stupid ANSI !!!**
February 13, 2004 at 8:18 am
Sorry - I don't know that this is possible as well & I'd be interested in knowing if anyone has accomplished this.
The only thing that I have done is to store all user changes in an array and then use either a recordset.update or pass updateable fields to a stored procedure by looping through the array - in this code have also used a "begin tran" and "commit tran" - but this is the closest I've come to what I think you're describing.
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply