reusing command object

  • Thread starter Thread starter Andy G
  • Start date Start date
A

Andy G

I want to run the below code and then use the recordset after the update to
loop through and do necessary updates to the data that came through in the
stored procedure. I'm getting some locking errors. Any ideas would be
great.

Thanks.


Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim rst As Recordset
With cmd
.CommandText = "sApptDateStaffFinalCC"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
Set rst = .Execute
End With

rst.LockType = adLockOptimistic
rst.Open cmd
Set ApptPopulate = rst
 
Not sure what you want to do here: first you open a recordset using the
..Execute function of the command object and then you reopen this same
recordset using its .Open function.

You should make a choice between these two opens.
 
I'm trying to do exactly what the code says I'm trying to do, I don't
understand your confusion. I need to use the command object to populate a
recordset and then update certain values in that recordset by looping
through it.

That's why I use the lines;

rst.LockType = adLockOptimistic
rst.Open cmd

If I don't the recordset will be opened forward only (read only) and I will
not be able to update the recordset. If I should approach this a different
way then that's fine, but I know the way that I intially proposed will work
fine. In ADO you can reuse a single command object.

-Andy
 
Oh, I see now where your misunderstanding is. All you have to do is drop
the .Execute call, create a recordset and open it using the command object
as its connection parameter. Also, instead of simply writing "Dim rst As
Recordset", its better to write "Dim rst As ADODB.Recordset".

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"

.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)

.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)

End With

Dim rst As ADODB.Recordset
set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenStatic, adLockOptimistic

Finally, I'm not sure what you want to do exactly with your last statement
about ApptPopulate.
 
Thank you. I believe I am a little closer to making this work. After
opening the recordset using the command object I want to move through the
recordset and make updates to certain records. After the rst.Update (below)
I get the error, "Row cannot be located for updatin. Some values may have
been changed since it was last read." If this is something you haven't seen
before don't spend too much time on it, but if you can point me in the right
direction then that would be greatly appreciated.


Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim rst As ADODB.Recordset
With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"
.Parameters.Append .CreateParameter("@staffNumber", adBigInt,
adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open cmd, , adOpenStatic, adLockOptimistic

Do Until rst.EOF
rst![ColorBlue] = clrBlue
rst![ColorGrey] = clrGrey
rst![ColorBlack] = clrBlack
rst![ColorRed] = clrRed
rst![ColorNone] = clrNone
rst![StartTime] = apptStartTime
rst![Locked] = apptLocked
rst![ID] = apptID
rst.Update
rst.MoveNext
Loop
 
This is usually the fact of a missing primary key. To be sure, you must
take a look with the SQL-Server Profiler to see what's happens on the
SQL-Server side

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Andy G said:
Thank you. I believe I am a little closer to making this work. After
opening the recordset using the command object I want to move through the
recordset and make updates to certain records. After the rst.Update
(below) I get the error, "Row cannot be located for updatin. Some values
may have been changed since it was last read." If this is something you
haven't seen before don't spend too much time on it, but if you can point
me in the right direction then that would be greatly appreciated.


Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim rst As ADODB.Recordset
With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"
.Parameters.Append .CreateParameter("@staffNumber", adBigInt,
adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open cmd, , adOpenStatic, adLockOptimistic

Do Until rst.EOF
rst![ColorBlue] = clrBlue
rst![ColorGrey] = clrGrey
rst![ColorBlack] = clrBlack
rst![ColorRed] = clrRed
rst![ColorNone] = clrNone
rst![StartTime] = apptStartTime
rst![Locked] = apptLocked
rst![ID] = apptID
rst.Update
rst.MoveNext
Loop


Sylvain Lafontaine said:
Oh, I see now where your misunderstanding is. All you have to do is drop
the .Execute call, create a recordset and open it using the command
object as its connection parameter. Also, instead of simply writing "Dim
rst As Recordset", its better to write "Dim rst As ADODB.Recordset".

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"

.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)

.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)

End With

Dim rst As ADODB.Recordset
set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenStatic, adLockOptimistic

Finally, I'm not sure what you want to do exactly with your last
statement about ApptPopulate.
 
I bet that is what is going on. Could the fact that my stored procedure is
joining 6 tables have anything to do with the problems? All of those 6
tables do have their own primary keys.

I think I'm going to come up with a different way to do this...

Thanks for all your help!


Sylvain Lafontaine said:
This is usually the fact of a missing primary key. To be sure, you must
take a look with the SQL-Server Profiler to see what's happens on the
SQL-Server side

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Andy G said:
Thank you. I believe I am a little closer to making this work. After
opening the recordset using the command object I want to move through the
recordset and make updates to certain records. After the rst.Update
(below) I get the error, "Row cannot be located for updatin. Some values
may have been changed since it was last read." If this is something you
haven't seen before don't spend too much time on it, but if you can point
me in the right direction then that would be greatly appreciated.


Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim rst As ADODB.Recordset
With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"
.Parameters.Append .CreateParameter("@staffNumber", adBigInt,
adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open cmd, , adOpenStatic, adLockOptimistic

Do Until rst.EOF
rst![ColorBlue] = clrBlue
rst![ColorGrey] = clrGrey
rst![ColorBlack] = clrBlack
rst![ColorRed] = clrRed
rst![ColorNone] = clrNone
rst![StartTime] = apptStartTime
rst![Locked] = apptLocked
rst![ID] = apptID
rst.Update
rst.MoveNext
Loop


Sylvain Lafontaine said:
Oh, I see now where your misunderstanding is. All you have to do is
drop the .Execute call, create a recordset and open it using the command
object as its connection parameter. Also, instead of simply writing
"Dim rst As Recordset", its better to write "Dim rst As
ADODB.Recordset".

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"

.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)

.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)

End With

Dim rst As ADODB.Recordset
set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenStatic, adLockOptimistic

Finally, I'm not sure what you want to do exactly with your last
statement about ApptPopulate.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I'm trying to do exactly what the code says I'm trying to do, I don't
understand your confusion. I need to use the command object to
populate a recordset and then update certain values in that recordset
by looping through it.

That's why I use the lines;

rst.LockType = adLockOptimistic
rst.Open cmd

If I don't the recordset will be opened forward only (read only) and I
will not be able to update the recordset. If I should approach this a
different way then that's fine, but I know the way that I intially
proposed will work fine. In ADO you can reuse a single command object.

-Andy


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message Not sure what you want to do here: first you open a recordset using
the .Execute function of the command object and then you reopen this
same recordset using its .Open function.

You should make a choice between these two opens.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I want to run the below code and then use the recordset after the
update to loop through and do necessary updates to the data that came
through in the stored procedure. I'm getting some locking errors.
Any ideas would be great.

Thanks.


Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim rst As Recordset
With cmd
.CommandText = "sApptDateStaffFinalCC"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
Set rst = .Execute
End With

rst.LockType = adLockOptimistic
rst.Open cmd
Set ApptPopulate = rst
 
Is rst![ID] the primary key for the main table? If so, then you cannot
change the value of this key without having trouble.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Andy G said:
I bet that is what is going on. Could the fact that my stored procedure is
joining 6 tables have anything to do with the problems? All of those 6
tables do have their own primary keys.

I think I'm going to come up with a different way to do this...

Thanks for all your help!


Sylvain Lafontaine said:
This is usually the fact of a missing primary key. To be sure, you must
take a look with the SQL-Server Profiler to see what's happens on the
SQL-Server side

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Andy G said:
Thank you. I believe I am a little closer to making this work. After
opening the recordset using the command object I want to move through
the recordset and make updates to certain records. After the rst.Update
(below) I get the error, "Row cannot be located for updatin. Some
values may have been changed since it was last read." If this is
something you haven't seen before don't spend too much time on it, but
if you can point me in the right direction then that would be greatly
appreciated.


Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim rst As ADODB.Recordset
With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"
.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open cmd, , adOpenStatic, adLockOptimistic

Do Until rst.EOF
rst![ColorBlue] = clrBlue
rst![ColorGrey] = clrGrey
rst![ColorBlack] = clrBlack
rst![ColorRed] = clrRed
rst![ColorNone] = clrNone
rst![StartTime] = apptStartTime
rst![Locked] = apptLocked
rst![ID] = apptID
rst.Update
rst.MoveNext
Loop


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message Oh, I see now where your misunderstanding is. All you have to do is
drop the .Execute call, create a recordset and open it using the
command object as its connection parameter. Also, instead of simply
writing "Dim rst As Recordset", its better to write "Dim rst As
ADODB.Recordset".

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"

.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)

.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)

End With

Dim rst As ADODB.Recordset
set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenStatic, adLockOptimistic

Finally, I'm not sure what you want to do exactly with your last
statement about ApptPopulate.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I'm trying to do exactly what the code says I'm trying to do, I don't
understand your confusion. I need to use the command object to
populate a recordset and then update certain values in that recordset
by looping through it.

That's why I use the lines;

rst.LockType = adLockOptimistic
rst.Open cmd

If I don't the recordset will be opened forward only (read only) and I
will not be able to update the recordset. If I should approach this a
different way then that's fine, but I know the way that I intially
proposed will work fine. In ADO you can reuse a single command
object.

-Andy


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in message
Not sure what you want to do here: first you open a recordset using
the .Execute function of the command object and then you reopen this
same recordset using its .Open function.

You should make a choice between these two opens.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I want to run the below code and then use the recordset after the
update to loop through and do necessary updates to the data that came
through in the stored procedure. I'm getting some locking errors.
Any ideas would be great.

Thanks.


Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim rst As Recordset
With cmd
.CommandText = "sApptDateStaffFinalCC"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
Set rst = .Execute
End With

rst.LockType = adLockOptimistic
rst.Open cmd
Set ApptPopulate = rst
 
Back
Top