After reading about lots of different ways you can set up an ADO link in chapter 7 of 'Professional Visual Basic 6 Databases', I decided I liked the command method as a 'catch all'. However I don't know where I should specify record locking.

The following code generates the error:-

'object variable or With block variable not set' on the line
where I set the recordset locktype. Where should I set the locktype in this case?



Dim strValue As String
    '
    ' ADO Command Initialisation
    Dim objCmd01 As ADODB.Command
    Dim objRS01 As ADODB.Recordset
    Set objCmd01 = New ADODB.Command


    '
    ' Define Connection To SQL Server
    objCmd01.ActiveConnection =
          "Provider=SQLOLEDB.1;Persist Security" & _
          "Info=False;User ID=sa;PWD=enterprise;Initial" & _
          " Catalog=MIS;Data Source=XPFGB141"
    objCmd01.CommandType = adCmdText
    objCmd01.CommandText = "Select * from budgets_channels"

    '
    ' Get RecordSet
    objRS01.LockType = adLockOptimistic
    Set objRS01 = objCmd01.Execute
    objRS01.MoveFirst

I assume you want to set the LockType because you're doing updates/inserts? Its v inefficient to use the recordset object for this because you're passing the whole table back to the client. If you're doing VB -> SQL Server you really should do all updates/inserts via a stored procedure.

If you use stored procedures you don't need a recordset at all, so you don't need to set the lock type.

hth
Phil

Hi,

yes I intend to do inserts in this particular case.
I am reading an Excel sheet and writing to an SQL server table.
This is not a regular task, it will be run once a year to import data.
I agree stored procedures are more efficient but I have not yet figured out how to do them and still would like to know how to do it with VB just so I can understand all the options available. Next time I might have to write to an Access database or a database I know nothing about the internal workings off.


So I am still interested to know how to set the locking state with a VB command if anybody can help.


Dazed And Confused

Why don't you just use an alternative syntax, like the Open method of the Recordset, then you can set the LockType.

You can also use the Execute method to run a SQL "INSERT" or "UPDATE" statement, which doesn't need a stored procedure or a recordset.

hi there..

i noted you miss the line
that's why you have the error...

HTH...

Gonzalo Bianchi

[u]Pgtips</u>

the reason I was after this particular solution was to be able to use the command settings to satisfy almost all connection requirements rather than a different style of connection for different programs. Iwas trying to make it easier for me to remember how to code.

I agree I could use the text method without a recordset using direct text SQL statements. It does seem to me that your earlier suggestion about SQL stored procedures is a wise one. If you were setting up any kind of database maintenance ( eg interactive order entry and maintenance ) I guess you would use stored procedures? In fact as a general rule, would you agree VB is to provide a frame work for file updates that occur as close to the database as possible?


[u]gbianchi</u>

I tried adding the line. I now get a different error:-

'Current Recordset does not support updating. This may be a limitation of the providor or the selected locktype.'
The recordset is locked for updates by the fact that I have specified lockoptimistic but something is still wrong.

I have a feeling I am missing something fundemental about the way I should code this.




The actual link it is failing on is the first line I try to and update something in the recordset:-

objRS01("Qty") = objRS01("Qty") + 1






Dazed And Confused

hi there..

can i tell you a diferent aproach??

why use a command object if you will execute something that will be used by a recordset.. just set everything to the recordset..

setting the locktype will dont do a thing if you later set the rs by a command..
can you get rid of the command ??

HTH

Gonzalo Bianchi

Hi Skittle,

Yes I would always use stored procedures. They have a number of advantages:
1. the stored procedure results in a pre-compiled execution plan for your query(ies) so it runs much quicker
2. they provide a good deal of protection against SQL Injection Attacks
3. you can encapsulate everything at the database end, thus making it much easier to ensure the integrity of your database
4. they provide "a frame work for file updates that occur as close to the database as possible"

They are really simple to create in SQL Server, and Microsoft even provide a Stored Procedure Add-in which generates the necessary VB to run your stored proc. Look for the "ADO Stored Procedure Add-in" link at this url http://msdn.microsoft.com/library/de...e/list/sql.asp

rgds
Phil

gbianchi

I think I understand what you mean. You are saying that I do not need a command. I can do it all with a recordset. So a recordset can be defined with a connection, a locktype and a cursor type?

pgtips

I see what you mean. So using stored procedures I can set up a vb front end to find, add, amend and delete records on an SLQ server?



Thanks for your help chaps. It just shows that reading books alone will not make a code jokey.


Dazed And Confused