Accessing Database Fields 1

[PaulHamill]

Does anyone know?

When I am Placing/Retrieving Data to/from a Database via ADO code I use the following..

RS.AddNew
RS!Forename = "John"
RS!Surname = "Smith"
Etc...
RS.Update

Is it possible to place a Variable instead of the actual hardcoded fieldname.

RS![Variable] = "John"

Any help would be most appreciated,

Thanking you all in advance

 

[johnwm]

ADO recordsets have a Fields collection, which you can access either by index (0 - based) or by name.

rs.Fields(0)

or

rs.Fields(fldName)
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[LazyMe]

You can also use string constants like so:

const FIRST_NAME as String = "Forename"

objRS(FIRST_NAME)="John"

Provides for somewaht more flexibility if the ordinal position of columns are likely to be changed due to database changes.

Anyway, the ! notation is obsolete, it is not a recommended notation anymore.
Greetings,
Rick

 

[mattKnight]

>Anyway, the ! notation is obsolete, it is not a recommended notation anymore.

True,

but so is using the default property!

i.e.

rs.fields(fldName)

vs

rs.fields.item(fldName)

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[LazyMe]

Also true )
Greetings,
Rick

 

[atruhoo]

Question pertaining above about ! being obsolete. Trying to change up code in an application for based on this post and when I change my ! to log.Fields.Item(User)/log.Fields(User). I get error message saying that Item could not be found in collection corresponding to requested name of ordinal. Code works fine with the !, but since you stated obselete would like to get with times. Appreciate input since I am a new programmer in VB and would like to keep my job.

Dim connLog As ADODB.Connection
Dim log As ADODB.Recordset


Private Sub cmdCancel_Click()
End
End Sub

Private Sub cmdLogin_Click()

'If txtUser = log!User And txtPwd = log!Passwd Then
If txtUser = log.Fields.Item(User) And txtPwd = log.Fields.Item(Passwd) Then
Call Menu.Show
Me.Hide
Else
MsgBox ("Password not correct, try again&quot
txtPwd.SetFocus
SendKeys "{Home}+{End}"
End If

End Sub

Private Sub Form_Load()
Set connLog = New Connection
Set log = New Recordset

connLog.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\default\Desktop\parking.mdb;Persist Security Info=False"
connLog.Open
Set log = connLog.Execute("Select * From Pwd&quot

'connLog.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\default\Desktop\parking.mdb;Persist Security Info=False"
'log.Open "Select * From Pwd", connLog, adOpenDynamic, adLockOptimistic

'connLog.Close
End Sub

Richard

 

[CajunCenturion]

You might try putting User in quotes.

If txtUser = log.Fields.Item("User") And txtPwd = log.Fields.Item("Passwd") Then

Without the quotes, User is being interpreted as a variable, which opens up other doors

Dim FldName as String

FldName = "User"
If txtUser = log.Fields.Item(FldName)
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[mcnews]

! obsolete

says who and why.

thanks.

 

[atruhoo]

Thanks CajunCenturion, it worked fine once I put into quotes.

Richard