Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 14th, 2003, 06:13 AM
Registered User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO Locking

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
Reply With Quote
  #2 (permalink)  
Old November 14th, 2003, 07:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old November 14th, 2003, 09:44 AM
Registered User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old November 14th, 2003, 09:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Why don't you just use an alternative syntax, like the Open method of the Recordset, then you can set the LockType.
Reply With Quote
  #5 (permalink)  
Old November 14th, 2003, 09:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
Reply With Quote
  #6 (permalink)  
Old November 14th, 2003, 10:28 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there..

i noted you miss the line
Code:
set objRS01 = new ADODB.Recordset
that's why you have the error...

HTH...

Gonzalo Bianchi
Reply With Quote
  #7 (permalink)  
Old November 14th, 2003, 10:47 AM
Registered User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

[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
Reply With Quote
  #8 (permalink)  
Old November 14th, 2003, 10:56 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
Reply With Quote
  #9 (permalink)  
Old November 14th, 2003, 11:14 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #10 (permalink)  
Old November 14th, 2003, 11:54 AM
Registered User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO vs DAO pessimistic locking knowledge76 Access VBA 3 December 3rd, 2008 12:17 PM
ADO Record Locking pjohanne VB Databases Basics 3 March 9th, 2006 05:42 AM
Database Locking royalsurej General .NET 2 November 9th, 2004 08:24 AM
ADO could ADO counot find the specified provider. Rob Collie Classic ASP Databases 2 June 9th, 2003 04:12 AM
Locking Peter Riley SQL Server ASP 3 June 5th, 2003 07:24 AM



All times are GMT -4. The time now is 05:49 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.