[RESOLVED] ADO VB6 Access DB

[Ricky1]

ok VB 6 with a Access Database using ADO

I have an autonumber fields which puts my records in the order i require them.

I just need pointing in the right direction, wot function to look at etc. What i need to do it, make sure that a few fields are the same and then give me whats in another field and then check the whole database.

Example.

Field "Type" to be "Turbo NL"
Field "Entry Fee" to be "6.00"

then for all the records that have them two (both that is) i want to know whats in the field called "Placed"

Hope this is clear.

[Dnereb]

Not to me.
If you like to Post it in dutch (seems your tongue is dutch-> turbo NL)and I will translate it in English for you. Be sure to be clear and keep in mind your readers have no clue what you are up to so be precise and rather write a bit to much as skipping some vital info.

[Ricky1]

Basically, i need to find all the records that contain two specific critea. If that record does meet the two criteas then i want to take a value from another field that wasn't part of the critea.

Say my table had 4 fields "Name", "Age", "Location", "Gender"

I want all the records that have the "Name" smith, and are "Age" 15 then from them records that have both them. I want to take the value from the field "Location"

[si_the_geek]

You would use an SQL statement like this:

SELECT Location
FROM tablename
WHERE [Name] = 'Smith'
AND Age = 15

(name needs to be in square brackets as it is a reserved word)

How you would run this from VB depends on exactly what you are using.. as there are two forms of ADO (the dreaded Data Control, and code).

[Ricky1]

i used Beacons ADO Tuturial for the basics so that form of ADO i have this at the top of my code

Private cn As ADODB.Connection
Private rs As ADODB.Recordset

as you can tell i am new to this.

[Dnereb]

Basicly it could look like this.

Private Sub Form_Load()
 
Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset
 
 
Set Cn = New ADODB.Connection
'The path will differ so you need to dajust it
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\YourDatabase.mdb Persist Security Info=False"
'You need to fill in the correct table name as well, and maybe opt for another cursor
Rs.Open "SELECT Location From tablename WHERE [Name] = 'Smith' AND Age = 15", Cn, adOpenForwardOnly, adLockReadOnly
 
While Not Rs.EOF
    
    Me.text1 = Me.text1 & Rs.Fields("Location") & vbCrLf
    Rs.MoveNext
    
Loop
 
 
End Sub

[Ricky1]

ok how do i see how many times something appears?

Such as the name "Smith" and gender "male" just want to see how many records have them two in them.

[Ricky1]

Private Sub Command1_Click()

Dim Count As Integer

rs.Close
rs.Open "SELECT Placed from tblData where Type = 'NL Holdem Turbo'AND Entry = '6.00'", cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF

    If rs.Fields("Placed") = 1 Then
        Count = Count + 1
    End If

Loop

Text1.Text = Count

End Sub

tried this to tell me how many times placed has been 1 with the given critea but if freezes

[si_the_geek]

The problem with that code is that you do not move on to the next record (so it just repeatedly reads the first one). To correct that, just put in Rs.MoveNext before the Loop line.

..however, there is a better way - get the database to do the work instead (it is quicker, and the code is simpler). To do this, just change your code to this:

Private Sub Command1_Click()
 
rs.Close
rs.Open "SELECT [u]Count(*)[/u] from tblData where Type = 'NL Holdem Turbo'AND Entry = '6.00'", cn, adOpenForwardOnly, adLockReadOnly
 
'the recordset now only returns a single value (the count) so put it straight into the textbox
If Not rs.EOF Then   
  Text1.Text = rs.Fields(0).Value
Else
  '(this should never happen - the database should always give a count)
  MsgBox "oops... there was an error!"  
End If
 
End Sub

Note that if Entry only ever contains numbers (or blanks) it should be a Numeric data type, instead of Text. (if it is already a numeric data type, remove the ' marks from around the value in your SQL statement).

[Dnereb]

I suspect this line needs to be altered

rs.Open "SELECT Count(*) from tblData where Type = 'NL Holdem Turbo'AND Entry = '6.00'", cn, adOpenForwardOnly

in:

rs.Open "SELECT Count(*) from tblData where Type = 'NL Holdem Turbo AND Entry = '6.00' And Placed = 1" ', cn, adOpenForwardOnly

To count the number of placed items with the value 1

edit removed syntax error