Stewie

Hello
i have been reading up on ways to program data. and there appear to be two method
1) Bind the data
2) write the code your self to select which data

basicaly i have found lots of information on data binding and now have a good idea of it. but some books and web sites say that data binding is not the way to go because in the long term it is slower. and with writing code you only pull out what data you want. But other books say that data binding is too powerful to not use?
Anyway, i was wondering what your opinion is for what is the best method for the long run
and also if someone could paste a sample of code where they have written out the code themselves so that i could learn from it. because every book and web page that says writing it yourself is better just says "proffesional programmers write their code their selfs but we will look into databinding"
i cant actualy find any examples

another minor question

on a data control thing
instead of selecting a sql query to base my data on
could i not just NOT have a query command and just write the sql code as the record source?
it works, but im not sure if it is good practice or not

thanks for your help

jkcontra

Question 1) Coding is definately better than binding your data to a control. My experience with the ado data control is frustration after frustration.

I will write you a small app and mail it to you as soon as im Done, Give me your email address


Question 2) You can make queries in ACCESS and use that as your table. But I would rather use SQL queries

Stewie

okay briliant!
my email is jamjarman@hotmail.com

thanks again

Stewie

while on topic of coding it yourself

is there a way to globaly dim the database destination
so that i can refer to the dim all the time so if i was to change the destination of my database backend i wont have to go through every bit of code and change it?
i could have like database and database2 instead of //gggg///gggg sort of thing

pup

can you send me the code too? my mail is pup_viet@yahoo.com Thanks a lot.

Stewie

coutesy of jkcontra


'===================================================================== ==========='
' A simple ADODB connection. I Created an Access database just to get you
' started with the Basics. From there on you are welcome to ask me any
' questions.
' Refrences you need to set : Microsoft ActiveX Data Objects 2.5,2.6 or 2.7



Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String 'I will use this string to set the SQL Query
Dim strCN As String 'This string will be used for the connection string


Private Sub cmdAdd_Click()
'===================================================================== ==========='
' Adds a new record

txtCustomer(0).Text = ""
txtCustomer(1).Text = ""
txtCustomer(0).SetFocus
rs.AddNew
cmdExit.Caption = "Cancel"
End Sub



Private Sub cmdDelete_Click()
'===================================================================== ==========='
' Deletes a record
On Error Resume Next
Dim vPosition As Variant
vPosition = rs.AbsolutePosition - 1
rs.Delete
rs.Requery
rs.AbsolutePosition = vPosition
txtCustomer(0) = rs.Fields("Name")
txtCustomer(1) = rs.Fields("Surname")
End Sub

Private Sub cmdEdit_Click()
'===================================================================== ==========='
' I normally lock my textboxes. With my Edit button I unlock my textboxes
' Im just using Edit button to select the first Textbox.
txtCustomer(0).SetFocus
cmdExit.Caption = "Cancel"
End Sub

Private Sub cmdExit_Click()
On Error Resume Next
If cmdExit.Caption = "Exit" Then
rs.Close
cn.Close
End
Else
rs.CancelUpdate
rs.MoveFirst
txtCustomer(0).Text = rs.Fields("Name")
txtCustomer(1).Text = rs.Fields("Surname")
cmdExit.Caption = "Exit"
End If
End Sub

Private Sub cmdFirst_Click()
'===================================================================== ==========='
' Moves to the first record
On Error Resume Next
rs.MoveFirst
txtCustomer(0).Text = rs.Fields("Name")
txtCustomer(1).Text = rs.Fields("Surname")
If rs.AbsolutePosition < 0 Then rs.MoveFirst
End Sub

Private Sub cmdLast_Click()
'===================================================================== ==========='
' Moves to the last record
On Error Resume Next
rs.MoveLast
txtCustomer(0).Text = rs.Fields("Name")
txtCustomer(1).Text = rs.Fields("Surname")
If rs.AbsolutePosition < 0 Then rs.MoveLast
End Sub

Private Sub cmdNext_Click()
'===================================================================== ==========='
' Moves to the next record
On Error Resume Next
rs.MoveNext
txtCustomer(0).Text = rs.Fields("Name")
txtCustomer(1).Text = rs.Fields("Surname")
If rs.AbsolutePosition < 0 Then rs.MoveLast
End Sub

Private Sub cmdPrevious_Click()
'===================================================================== ==========='
' Moves to the previous record
On Error Resume Next
rs.MovePrevious
txtCustomer(0).Text = rs.Fields("Name")
txtCustomer(1).Text = rs.Fields("Surname")
If rs.AbsolutePosition < 0 Then rs.MoveFirst
End Sub

Private Sub cmdSave_Click()
'===================================================================== ==========='
' Save a new or edited record

rs.Fields("Name") = txtCustomer(0).Text
rs.Fields("Surname") = txtCustomer(1).Text
rs.UpdateBatch adAffectCurrent
If rs.BOF Then
txtCustomer(0).Text = ""
txtCustomer(1).Text = ""
Else
txtCustomer(0).Text = rs.Fields("Name")
txtCustomer(1).Text = rs.Fields("Surname")
End If
cmdExit.Caption = "Exit"
End Sub

Private Sub Form_Load()
'===================================================================== ==========='
' Starts the connection and set the recordset
Dim iCount As Integer 'Icount will select the textbox which to add text
Set cn = New ADODB.Connection
strCN = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\Simple.mdb;" & _
"Mode=ReadWrite|Share Deny None;" & _
"Persist Security Info=False"
With cn
.ConnectionTimeout = 30
.ConnectionString = strCN
.Open
End With
strSQL = "Select * from Customer Order by Surname, Name"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

If Not rs.BOF Then
For iCount = 0 To 1
txtCustomer(iCount).Text = rs.Fields(iCount + 1)
Next iCount
End If

Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
cmdExit_Click
End Sub

Stewie

I am having a problem here. everything works perfect. except for the line in bold. it gives me a message "invalid use of a null vale"


rs.MoveFirst
txtEmpID.Text = rs.Fields("EmpID")
txtEmployeeID.Text = rs.Fields("EmployeeID")
txtFirstName.Text = rs.Fields("Firstname")
txtLastName.Text = rs.Fields("Lastname")
txtAddress.Text = rs.Fields("Address")
txtAddress2.Text = rs.Fields("Address2")
txtPostalCode.Text = rs.Fields("postalcode")

GMan_NC

That's because Address2 is Null. You will have to check for this. There are a few ways to do this, here's one.


You will need to do this for any field that may contain a Null value

Stewie

Gman you are THE MAN

Stewie

hey gman, it says to me everytime i try to put in the code
compile error: expected seperator or )

i tried playing around with it but no luck
no luck at all my friend

GMan_NC

i failed to add a closed "

Stewie

works like a charm. i didnt see that bit

**** i actualy have a section of my prject working now lol

Stewie

hey jkcontra has tipped me off with this handy little thing

On Error Goto ErrMsg
Then before end sub put his is

ErrMsg:
If err.number = 94 then
Resume next
End if

greg8872

I was just wondering, is doing

rs.Fields("Address2")

the exact same as doing

rs![Address2]

I first learned to do the second method, and wished at times I could use a vairable for the field name...

-Greg

GMan_NC

if you want to use a variable for the field name, then use the first method

rs.Fields(VarName)