Solved

Manually setting ADO KeyColumn property

Posted on 2001-07-03
15
716 Views
Last Modified: 2007-12-19
Do you know if it is possible to manually set the KeyColumn property for a column in an ADO Recordset?  For example, I'd like to be able to do this:
  rs("title_id").Properties("KeyColumn") = True

When I try this I get "The operation is not valid in this context".

Normally ADO and OLE-DB can figure out this property correctly.  However, I'm opening up a recordset against a View which has two tables in the FROM clause (but only SELECTs fields from 1 table).  I can update this view, but if you look at SQL Profiler, you see that the update statment uses every single field in the SELECT clause in the WHERE clause.  It should just be using "WHERE title_id = x".  For performance issues, I want to tell it to use the title_id column (which is guaranteed to be unique in my situation).

The recordset's CursorLocation must be set to adUseClient in my situation, and I am correctly setting the "Update Criteria" property to adCriteriaKey.  If ADO would let me change the KeyColumn property of the title_id column to True, I am sure that the UPDATE statement would just use that field in the WHERE clause.

I am using MDAC 2.6 on the client and SQL 2000 on the back-end.  I have created the view with the "WITH VIEW_METADATA" tag.

Thanks!
0
Comment
Question by:jsullivan
  • 7
  • 2
  • 2
  • +3
15 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 6249028
I don't think that you can using ADO. With RDO i believe that you can though this sets whether the column is part of the primary key which is not applicable in your case. I would have to suggest that you look at your view definition to resolve this. You should specifically check the join conditions between the two tables that you are selecting from. If you can post the view definition then we may be able to help with this.
0
 

Author Comment

by:jsullivan
ID: 6249062
Your are correct, you CAN do this in RDO, so I figure there must be a way to do it in ADO too.  Here is an example of a simple view which demonstrates my problem.  You can create this view in the sample pubs database in SQL Server.

CREATE VIEW titles_v WITH VIEW_METADATA AS
  SELECT titles.title_id, titles.title, titles.price
  FROM titles, publishers
  WHERE titles.pub_id = publishers.pub_id
    AND publishers.pub_name = 'Binnet & Hardley'

As you can see, the view only selects fields from the titles table.  Also, it joins the titles table to the primary key in the publishers table.  This ensures a 1-to-1 relationship.  The view's title_id column is guaranteed to still be unique in this view, so I don't know why ADO doesn't set KeyColumn to True.

I tried creating a unique clustered index on the title_id column of the view, but KeyColumn still comes back False.

Thanks.
0
 
LVL 20

Expert Comment

by:hes
ID: 6249076
0
 

Author Comment

by:jsullivan
ID: 6249106
Yes, I've seen this KB article but it does not apply to my situation.  I am not implicitly creating a recordset.  I am explicitly creating the recordset.  The metadata is correctly being returned in my situation, and both BaseTableName and BaseColumnName are being passed back.  KeyColumn is also being passed back, but it is False and I'd like it to be True.  If I change my View to a straight SELECT of just the titles table, the KeyColumn property for title_id goes back to True.

So, in my situation, where I must join tables in my View, I need a way to force KeyColumn to True.  Unless you can think of another way that I can tell SQL to only use title_id when locating a record for update.

Thanks.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6249153
Perhaps you should use the Inner Join syntax:

CREATE VIEW titles_v WITH VIEW_METADATA AS
 SELECT titles.title_id, titles.title, titles.price
 FROM titles INNER JOIN publishers
 ON titles.pub_id = publishers.pub_id
   AND publishers.pub_name = 'Binnet & Hardley'
0
 

Author Comment

by:jsullivan
ID: 6249249
Yea, I already tried that too.  It didn't make a difference.  I even tried this:

CREATE VIEW titles_v WITH VIEW_METADATA AS
  SELECT * FROM titles
  WHERE pub_id IN
  (SELECT pub_id FROM publishers
   WHERE pub_name = 'Binnet & Hardley')

and that doesn't work either.  Any other ideas?
0
 
LVL 9

Expert Comment

by:samopal
ID: 6249974
Try this :

   rs.CursorLocation = adUseServer
   rs.ActiveConnection = cn
   rs.Properties("Unique Rows").Value = True
   rs.CursorLocation = adUseClient

   rs.Source = "select * from titles_v"
   rs.Fields.Refresh

debug.print rs("title_id").Properties("KeyColumn")
Now it should return True

HTH
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:jsullivan
ID: 6250031
I tried this out, but unfortunately it did not work.  I stepped through the code one line at a time.  After setting "Unique Rows" to True, I checked it in the debug window to make sure that it actually did change to True.  It did.  But after executing the next line to change the CursorLocation back to adUseClient, "Unique Rows" changes back to False automatcially.  Then after stepping through the remaining lines of code and checking KeyColumn, it is still False.

Thanks for the suggestion.  Do you have any other ideas?
0
 
LVL 9

Expert Comment

by:samopal
ID: 6250631
Sorry, I found only this...
http://support.microsoft.com/support/kb/articles/Q245/4/93.ASP

But it works for me when I tested it (NT4/VB6/SQL7). But I didn't use WITH
VIEW_METADATA" tag.
0
 

Author Comment

by:jsullivan
ID: 6255714
The problem in SQL 7 is that your UPDATE statement will update the base table directly.  I bet if you checked the BaseTableName property, you'll see that it gives you the table name and not the view.  That means that your UPDATE statement will be "UPDATE <tablename>" rather than "UPDATE <viewname>".  The problem in my situation is that the user has rights to update the view, but not rights to update the table directly.

In SQL 2000, you can add the "WITH VIEW_METADATA" tag when creating a view.  That way, the metadata will show the view name in BaseTableName and the UPDATE will go against the view.  The only problem is that I can't get KeyColumn to go to True for the unique column.

I'm still hoping that someone will have the magic answer.

Thanks.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7147978
Hi jsullivan,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Refund points and save as a 0-pt PAQ.

jsullivan, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
0
 

Author Comment

by:jsullivan
ID: 7149170
Thanks Dan,

I never really forgot about this question, I'd still like to get an answer.  But if you need to close it down, that's fine.  Will people still be able to find this in their searches if it is closed?  The question was never answered, but I still think there is good information here.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7149882
>>Will people still be able to find this in their searches if it is closed?
Yes, when 'saved to the PAQ' the question and all comments are available to the search engine.  But when deleted, it is gone forever.  So I recommend delete only when there seems to be nothing useful for future database seachers to find.
-- Dan
0
 

Author Comment

by:jsullivan
ID: 7149906
Great.  Let me know if there is anything that I need to do.  Thanks.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 7182113
Placed in PAQ

Computer101
E-E Moderator
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now