SQL / .Index / .Seek Question 
Author Message
 SQL / .Index / .Seek Question

i'm using the following code to retrieve the employee the user clicked
on

Private Sub tvEmployee_NodeClick(ByVal Node As MSComctlLib.Node)
If (Len(Node.Key) = 1) Then Exit Sub
lCurrentEmployeeNumber = CLng(Mid$(Node.Key, 3, Len(Node.Key)))
With rsEmployeeTable
   .Index = "PrimaryKey"
   .Seek "=", lCurrentEmployeeNumber
   If Not .NoMatch Then
     bFieldsPopulated = True
     sCurrentEmployeename = tvEmployee.SelectedItem
     Call populateFields
     Call populateListView
     tbEmployee.Enabled = True
   Else
     MsgBox ("OOOOOOPS!!")
   End If
End With
End Sub

-------------------
when rsEmployeeTable was set as follows, it worked fine.

Set rsEmployeeTable = dbHRM.OpenRecordset("Employee", dbOpenTable)

however, i normalized and had the need to left outer join a couple
tables and could no longer use "Employee" as the basis for
rsEmployeeTable.  i now use employeeSQL (a sql string) and use the
following form:

Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL)

b/c vb6 will no longer let me use dbOpenTable as follows:

Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL, dbOpenTable)

using

Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL)

vb6 gets hung up on

.Index = "PrimaryKey"  (I've also tried .Index = "EmployeeNumber" -
which is the primary key)

saying it isn't supported.

i would appreciate any guidance as to a resolution.

jeff



Wed, 11 Sep 2002 03:00:00 GMT  
 SQL / .Index / .Seek Question
Hi Jeff

SQL will accept an Order By clause in lieu of a primary key. Just list the
field(s) that you want the record sorted by. This clause would appear at the
end of your SQL statement.

In your example, the syntax would be:

Select Whatever from MyTable Order by EmployeeNumber.

Does this help?

Quote:

>i'm using the following code to retrieve the employee the user clicked
>on

>Private Sub tvEmployee_NodeClick(ByVal Node As MSComctlLib.Node)
>If (Len(Node.Key) = 1) Then Exit Sub
>lCurrentEmployeeNumber = CLng(Mid$(Node.Key, 3, Len(Node.Key)))
>With rsEmployeeTable
>   .Index = "PrimaryKey"
>   .Seek "=", lCurrentEmployeeNumber
>   If Not .NoMatch Then
>     bFieldsPopulated = True
>     sCurrentEmployeename = tvEmployee.SelectedItem
>     Call populateFields
>     Call populateListView
>     tbEmployee.Enabled = True
>   Else
>     MsgBox ("OOOOOOPS!!")
>   End If
>End With
>End Sub

>-------------------
>when rsEmployeeTable was set as follows, it worked fine.

>Set rsEmployeeTable = dbHRM.OpenRecordset("Employee", dbOpenTable)

>however, i normalized and had the need to left outer join a couple
>tables and could no longer use "Employee" as the basis for
>rsEmployeeTable.  i now use employeeSQL (a sql string) and use the
>following form:

>Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL)

>b/c vb6 will no longer let me use dbOpenTable as follows:

>Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL, dbOpenTable)

>using

>Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL)

>vb6 gets hung up on

>.Index = "PrimaryKey"  (I've also tried .Index = "EmployeeNumber" -
>which is the primary key)

>saying it isn't supported.

>i would appreciate any guidance as to a resolution.

>jeff



Wed, 11 Sep 2002 03:00:00 GMT  
 SQL / .Index / .Seek Question
robbo,

so, i need to use sql to find the single recordset that match the criteria?
i'll give it a shot.

thanks,

jeff

Quote:

> Hi Jeff

> SQL will accept an Order By clause in lieu of a primary key. Just list the
> field(s) that you want the record sorted by. This clause would appear at the
> end of your SQL statement.

> In your example, the syntax would be:

> Select Whatever from MyTable Order by EmployeeNumber.

> Does this help?


> >i'm using the following code to retrieve the employee the user clicked
> >on

> >Private Sub tvEmployee_NodeClick(ByVal Node As MSComctlLib.Node)
> >If (Len(Node.Key) = 1) Then Exit Sub
> >lCurrentEmployeeNumber = CLng(Mid$(Node.Key, 3, Len(Node.Key)))
> >With rsEmployeeTable
> >   .Index = "PrimaryKey"
> >   .Seek "=", lCurrentEmployeeNumber
> >   If Not .NoMatch Then
> >     bFieldsPopulated = True
> >     sCurrentEmployeename = tvEmployee.SelectedItem
> >     Call populateFields
> >     Call populateListView
> >     tbEmployee.Enabled = True
> >   Else
> >     MsgBox ("OOOOOOPS!!")
> >   End If
> >End With
> >End Sub

> >-------------------
> >when rsEmployeeTable was set as follows, it worked fine.

> >Set rsEmployeeTable = dbHRM.OpenRecordset("Employee", dbOpenTable)

> >however, i normalized and had the need to left outer join a couple
> >tables and could no longer use "Employee" as the basis for
> >rsEmployeeTable.  i now use employeeSQL (a sql string) and use the
> >following form:

> >Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL)

> >b/c vb6 will no longer let me use dbOpenTable as follows:

> >Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL, dbOpenTable)

> >using

> >Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL)

> >vb6 gets hung up on

> >.Index = "PrimaryKey"  (I've also tried .Index = "EmployeeNumber" -
> >which is the primary key)

> >saying it isn't supported.

> >i would appreciate any guidance as to a resolution.

> >jeff



Wed, 11 Sep 2002 03:00:00 GMT  
 SQL / .Index / .Seek Question
Yes Jeff. The SQL statement would be (something like:)

Select Whatever From Wherever Where EmployeeNo = 'Criteria'

You could add the Order By clause - it would go at the end. However, if
you're searching for what you know will be single unique record, ordering
the result is somewhat irrelevant.

Quote:

>robbo,

>so, i need to use sql to find the single recordset that match the criteria?
>i'll give it a shot.

>thanks,

>jeff


>> Hi Jeff

>> SQL will accept an Order By clause in lieu of a primary key. Just list
the
>> field(s) that you want the record sorted by. This clause would appear at
the
>> end of your SQL statement.

>> In your example, the syntax would be:

>> Select Whatever from MyTable Order by EmployeeNumber.

>> Does this help?


>> >i'm using the following code to retrieve the employee the user clicked
>> >on

>> >Private Sub tvEmployee_NodeClick(ByVal Node As MSComctlLib.Node)
>> >If (Len(Node.Key) = 1) Then Exit Sub
>> >lCurrentEmployeeNumber = CLng(Mid$(Node.Key, 3, Len(Node.Key)))
>> >With rsEmployeeTable
>> >   .Index = "PrimaryKey"
>> >   .Seek "=", lCurrentEmployeeNumber
>> >   If Not .NoMatch Then
>> >     bFieldsPopulated = True
>> >     sCurrentEmployeename = tvEmployee.SelectedItem
>> >     Call populateFields
>> >     Call populateListView
>> >     tbEmployee.Enabled = True
>> >   Else
>> >     MsgBox ("OOOOOOPS!!")
>> >   End If
>> >End With
>> >End Sub

>> >-------------------
>> >when rsEmployeeTable was set as follows, it worked fine.

>> >Set rsEmployeeTable = dbHRM.OpenRecordset("Employee", dbOpenTable)

>> >however, i normalized and had the need to left outer join a couple
>> >tables and could no longer use "Employee" as the basis for
>> >rsEmployeeTable.  i now use employeeSQL (a sql string) and use the
>> >following form:

>> >Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL)

>> >b/c vb6 will no longer let me use dbOpenTable as follows:

>> >Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL, dbOpenTable)

>> >using

>> >Set rsEmployeeTable = dbHRM.OpenRecordset(employeeSQL)

>> >vb6 gets hung up on

>> >.Index = "PrimaryKey"  (I've also tried .Index = "EmployeeNumber" -
>> >which is the primary key)

>> >saying it isn't supported.

>> >i would appreciate any guidance as to a resolution.

>> >jeff



Thu, 12 Sep 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. 2 questions: Code doesn't work when I split database and Seek/Index question

2. Does SQL 7 Support Index and Seek commands

3. index / seek question

4. ms SQL 6.5 Index optimizing question ???

5. using .index and .seek function

6. Problems with the methods Index and Seek

7. How to set controlling index before doing SEEK in Access Basic

8. Multiple Index Seek (Acc97)

9. sequential file, line indexing, seeking and readline

10. ADO Index property and Seek method

11. Index Property/SEEK method Problem with ADO

12. Seek, Index on ADO Recordset

 

 
Powered by phpBB® Forum Software