cursor type problem 1

[dardartektips]

This is probably really basic. I'm trying to make the move from programming with Microsoft Access 2000 to Visual basic 6.0 and I'm having trouble with trying to update a recordset.

I have imported data into a table named imprtGasPrices from which I would like to update a table named tblfl_dtprc.

This is my SQL statement:

CMD.CommandText = "SELECT * from tblfl_dtprc WHERE dt_fdp = #" & CDate(.Fields("date").Value) & "# AND prdcd_fdp = '" & 
.Fields("prdcd").Value & "' AND trmnlcd_fdp = '" & .Fields("terminalcd").Value & "' AND tm_fdp = #" & .Fields("time").Value & "#"
CMD.CommandType = adCmdText
imprtGasRS.cursortype = adstatic
Set imprtGasRS = CMD.Execute

when i execute this my cursortype reverts back to forward only and editmode is adeditnone and I can't add records to the recordset. I would expect the recordset to return no records since I am updating it with the current rack price of gas.

I am a complete newby at this. This cmd.commandtext is nested inside of another cmd.commandtext which is nested inside of another. Should I be using cmd, cmd1, cmd2 or is this code acceptable. Each one has a different recordset name. I use the first one to loop through the second one to update the third one.

Does anybody understand what I'm asking ? Basically what I want to know is why can't I add new records ?

Thanks,
Laura

 

[RoyVidar]

I think the constant representing a static cursor, is adOpenStatic. Perhaps also specify locktype?

I would use separate command objects in stead of nesting.

Do you use Option Explicit?

Roy-Vidar

 

[dardartektips]

I separated the command objects out and this is the actual code I have where the problem starts. I am not using option explict, isn't that what requires you to declare variables. I have declared my command objects, connection and recordsets. I have not declared minor variables used in procedures to store values.

When I run this, it changes the cursorlocation to aduseclient batch, cursortype to adopendynamic and locktype to adlockoptimistic until the last line then it changes them to adUseServer, adOpenForwardOnly and adLockReadOnly respectively.

CMD2.CommandText = "SELECT * from tblfl_dtprc WHERE dt_fdp  = #" & rckdt & "#"  
CMD2.CommandType = adCmdText
GasPrcRS.CursorLocation = adUseClient
GasPrcRS.CursorType = adOpenDynamic
GasPrcRS.LockType = adLockOptimistic
Set GasPrcRS = CMD2.Execute

Any ideas ???

 

[dardartektips]

Never mind. I found it in a post back in December, I was having a hard time finding it.

Thanks

 

[fredericofonseca]

Glad you found it.

BUT please DO USE the "option explicit", as it will save you lots of trouble in the future.

This should have been made mandatory by Microsoft, but as they are used to bugs they forgot it...


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dardartektips]

Alright, I had it. I changed
set gasprcRS = cmd.execute to
gasprcrs.open cmd
Why does it work sometimes and it doesn't others.

I did change to option explicit

Thanks,
Laura

 

[RoyVidar]

set gasprcRS = cmd.execute

will instantiate the recordset (here re instantiate, so you loose the properties you've set) and assign the result from executing the command object to the recordset -> readonly/forwardonly.

Using the open method, will usually let you keep (most of?) the properties you've set, though I think ADO coerse cursortype to keyset when retrieving a recordset based on Access tables.

Roy-Vidar

 

[dardartektips]

Maybe that's not the problem, it's telling me "object required" it actually won't run the open method it will run the cmd.execute though.

Do i have something declared wrong ?

Thanks,
Laura

 

[Golom]

If you want to use the "Open" method then you first need to create the object

Dim GasPrcRS As ADODB.Recordset
[COLOR=red]Set GasPrcRS = NEW ADODB.Recordset[/color]
GasPrcRS.CursorLocation = adUseClient
GasPrcRS.CursorType = adOpenDynamic
GasPrcRS.LockType = adLockOptimistic

GasPrcRS.Open etc. ...

 

[dardartektips]

Well duh !! I knew that, I would have done that in access.

First attempt at updates in Visual Basic

Thanks your the greatest.

Laura