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: 152
  • Last Modified:

Database Display

Hi

I have an access2000 database and am trying to display fields in vb. What i want it to do is when a certain button is clicked it will do a select sql query and then display these results in a few different labels. I have tryed doing it with ado and recordsets but cant :( So am wondering if anyone can show me how to open a database and the do different sql querys for each button pressed.


0
Corekill
Asked:
Corekill
  • 8
  • 5
  • 5
  • +1
1 Solution
 
Richie_SimonettiIT OperationsCommented:
after open database and create a recordset object  do the following

sub populateLabels(rs as adodb.recordset)
' best if you use a control array of labels

dim i as integer
' it suppose that there are 4 fields
for i= 0 to 3
    label1(i).caption=rs.fields(i).value
next i
end sub
0
 
Richie_SimonettiIT OperationsCommented:
to avoid nulls:
sub populateLabels(rs as adodb.recordset)
' best if you use a control array of labels

dim i as integer
' it suppose that there are 4 fields
for i= 0 to 3
   label1(i).caption="" & rs.fields(i).value
next i
end sub
0
 
CorekillAuthor Commented:
Thanks for that but am wondering how to open up the database and make a different recordset for each different query. Maybe a step by step guide and example code if thats not to much ?

Sorry if that wasnt clear
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CorekillAuthor Commented:
Thanks for that but am wondering how to open up the database and make a different recordset for each different query. Maybe a step by step guide and example code if thats not to much ?

Sorry if that wasnt clear
0
 
MetalheadCommented:

Here's how I would do it, you can do it this way or you can modify it to your preference:

  'This creates and a connection to your DB, I am assuming you are not using User/Password

  ADOConnection.ConnectionString  = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\db1.mdb";Persist Security Info=False"  
  ADOConnection.CursorLocation = adUseClient
  ADOConnection.ConnectionTimeout = 5
  ADOConnection.Open

  Set adoRS = New adodb.Recordset

  'Here's 2 methods of executing your query, I prefer the first as everything is explicitly specified

  'Method 1
  adoRS.ActiveConnection = ADOConnection
  adoRS.CursorLocation = adUseClient
  adoRS.Source = "Select Field1 From Table1 Where Field2 = '" & cmdButton1.Caption & "'"

  adoRS.CursorType = adLockOptimistic
  adoRS.LockType = adOpenDynamic
  adoRS.Open

  'Method 2
  adors.Open "Select Field1 From Table1", ADOConnection
  'Or something like this
  adors.Open "Select Field1 From Table1 Where Field2 = '" & cmdButton1.Caption & "'"

  'Now you have your recordset and you can use your recordset to populate whatever you want.

  'Validate that we DO have recordset
  If adors.recordcount > 0 then
    'Now you have a choice of either using the first record
    'to populate your label or loop in your recordset
    label1.caption = adors.fields("Field1").value
  end if


  All of the initilisation, both the adoConnection and adoRs should only be done once. This is a suggestion only. If you want to initialise these each time you do a adoRs.Open that is fine too.
  The adoRs.Open should be performed has many times as you wish.
0
 
MetalheadCommented:
Forgot

Set adoConnection = new adodb.Connection
0
 
CorekillAuthor Commented:
Thanks but i get an error here :

Set adoConnection = New adodb.Connection ' Error here used type not defined
adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=volcano.mdb;Persist Security Info=False"
 adoConnection.CursorLocation = adUseClient
 adoConnection.ConnectionTimeout = 5
 adoConnection.Open
 

 Set adors = New adodb.Recordset ' Error here used type not defined

Any ideas how to fix that ?
0
 
CorekillAuthor Commented:
Sorry i works fine!

Thanks very much

btw do u know how to use map path to the database ?
0
 
MetalheadCommented:
 Do you have a reference to Microsoft ActiveX Data 2.x Object Library

  Go Project -> Reference

  In the list of Available References make sure that you have

  Microsoft ActiveX Data 2.x Object Library

  The x represents either 5, 6, 7 maybe even 8!

  If you do not a reference to the Microsoft ADO 2.x library or a higher version in the list (checked or unchecked) you might have to get MDAC from Microsoft

 
0
 
MetalheadCommented:
 Do you have a reference to Microsoft ActiveX Data 2.x Object Library

  Go Project -> Reference

  In the list of Available References make sure that you have

  Microsoft ActiveX Data 2.x Object Library

  The x represents either 5, 6, 7 maybe even 8!

  If you do not a reference to the Microsoft ADO 2.x library or a higher version in the list (checked or unchecked) you might have to get MDAC from Microsoft

 
0
 
MetalheadCommented:
Sorry no idea about the map drive to the database
0
 
CorekillAuthor Commented:
Sorry another question

How can i set it so that this code can be used by procedure :

Set adoConnection = New ADODB.Connection
 adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\volcano.mdb;Persist Security Info=False"
 adoConnection.CursorLocation = adUseClient
 adoConnection.ConnectionTimeout = 5
 adoConnection.Open
0
 
vinnyd79Commented:
you could add this to a module:

Public adoConnection As ADODB.Connection

Public Sub OpenConnection(sDataBase As String)
Set adoConnection = New ADODB.Connection
adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & sDataBase & "';Persist Security Info=False"
adoConnection.CursorLocation = adUseClient
adoConnection.ConnectionTimeout = 5
adoConnection.Open
End Sub

Public Sub CloseConnection()
If Not adoConnection Is Nothing Then
    If adoConnection.State <> adStateClosed Then adoConnection.Close
End If
End Sub



Then use it like this:


Private Sub Form_Load()
OpenConnection "C:\volcano.mdb"
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
CloseConnection
End Sub
0
 
CorekillAuthor Commented:
Thanks for that!

I dont have anymore points at the moment but when i do i will give u some.

ps. I have this bit of code

If rsAfrica.recordcount > 0 Then
    For i = 0 To rsAfrica.recordcount
        List1.AddItem (rsAfrica.Fields("volcano_name").Value)
    Next i
End If

where do i put the [i] so it adds a different name ?
0
 
vinnyd79Commented:
try this:

List1.Clear
If rsAfrica.recordcount > 0 Then
    rsAfrica.MoveFirst
    Do Until rsAfrica.EOF = True
        List1.AddItem rsAfrica.Fields("volcano_name")
        rsAfrica.MoveNext
    Loop
End If
0
 
CorekillAuthor Commented:
Thanks again you have been such a great help!

Do you know where i can read up on modules ?
0
 
vinnyd79Commented:
Look up ".BAS Modules" in the vbhelp.It gives a good description.
0
 
CorekillAuthor Commented:
i dont have the msdn libary installed :(

What i wanted was to have a module that had all the different SQL statements so that i could just call them up
0
 
vinnyd79Commented:
you can do that.Just create Sub Routines and/or Functions in your .bas module and make them public so they can be accessed from anywhere in your project.

This example contains a zip file called ado helper.It contains example modules for ADO or DAO.

http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=34627&lngWId=1
0
 
vinnyd79Commented:
Corekill, I just noticed I left something out of the CloseConnection Sub. You should set it to Nothing like so:

Public Sub CloseConnection()
If Not adoConnection Is Nothing Then
   If adoConnection.State <> adStateClosed Then adoConnection.Close
    Set adoConnection = Nothing
End If
End Sub



0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now