Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

Database Fields

I have seen both the following methods used to retrieve a database field

Method 1 Example

cboStatus.AddItem oRs.Fields("ProjectName")

Method 2 Example

cboStatus.AddItem oRs!Status

What is the difference between the two methods and is one better than the other?


0
mutrus
Asked:
mutrus
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Method 2 is only supported for backwords compatiblity.

The BEST way would be like this:

dim oRs as Recordset  'not sure if you speak ADO or DAO...
dim fStatus as Field

set oRs = new Recordset
oRs.Open ...

set fStatus = oRs.Fields("Status")
cboStatus.Visible = false

while not oRs.eof
  cboStatus.AddItem fStatus.Value
  oRs.MoveNext
wend

cboStatus.Visible = true

set fStatus = nothing
oRs.Close
set oRs = nothing

CHeers
0
 
RSivakumar_SofilCommented:
Method 1
--------
To be honest, you do save a few keystrokes, but that's about it. The syntax itself is "old school". Additionally, using this syntax embeds literals in your code. Change a field name, and you have to change every reference to that field. Misspell the field name, and you have an error (nb. These same problems potentially apply to using the field name as a key, but that method allows you a way to avoid the problem, where this one doesn't).

Method 2
--------
Although using the Fields collection with the field name as a key offers no performance advantage over the first syntax, it is more in line with standard syntax. For that reason, this is more readable as well. As noted above, using this method could lead to maintenance problems if you use literals for your field names. The fix for that, however, is to use meaningful constants instead of literals. That way, any changes to a field name (or spelling errors) can be corrected in one location. The compiler will handle ensuring that the spelling of the constants is correct (assuming you are using 'Option Explicit').

cheers.
0
 
TimCotteeCommented:
I agree with angelIII, as a matter of fact when you use the ! (bang) operator VB has to perform an extra step in its processing, internally converting your code from oRS!Status to oRs.Fields("Status").Value before executing the statement. Whilst this is a very small step and does not take a significant amount of processing in a very high level transaction processing environment it could have a slight effect on speed. Personally I would always use oRs.Fields("Status").Value, as it is more flexible in that you can use variables as your field name:

Dim strFielName As String
Dim i As Integer
For i = 0 To .Fields.Count - 1
  Debug.Print .Fields("MyField" & CStr(i)).Value
Next
0
 
mutrusAuthor Commented:
All answers were appropriate and gave me the information I require. angellll was first.

Thanks to all
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now