• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

Manually setting ADO KeyColumn property

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
jsullivan
Asked:
jsullivan
  • 7
  • 2
  • 2
  • +3
1 Solution
 
TimCotteeCommented:
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
 
jsullivanAuthor Commented:
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
 
hesCommented:
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jsullivanAuthor Commented:
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
 
TimCotteeCommented:
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
 
jsullivanAuthor Commented:
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
 
samopalCommented:
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
 
jsullivanAuthor Commented:
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
 
samopalCommented:
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
 
jsullivanAuthor Commented:
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
 
DanRollinsCommented:
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
 
jsullivanAuthor Commented:
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
 
DanRollinsCommented:
>>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
 
jsullivanAuthor Commented:
Great.  Let me know if there is anything that I need to do.  Thanks.
0
 
Computer101Commented:
Placed in PAQ

Computer101
E-E Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now