Go Premium for a chance to win a PS4. Enter to Win

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

ADO and SQL Query

I'm using VB6, Access 97, and the ADO data environment.
I want to run a query on a table titled Confirmed. I want this query to return all the records in that table where the field 'Archived' = No. I then want to access the results returned from the query and display certain fields (fields such as RoomID) from the results in a listbox using ADO (and the data environment). I don't mind if it's hard coded or done in the data environment using a command, but it must be using ADO.
How do I do this?
PLEASE HELP :)   :)

Thnks!
Megan.
0
Megan
Asked:
Megan
  • 3
  • 2
1 Solution
 
setiawanCommented:
Dim objConn as New ADODB.Connection
Dim objRec as New ADODB.Recordset
Dim sql as string

ObjConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\db1.mdb"

sql = "Select * from confirmed where Archived=" & No
ObjRec.Open sql, 2 , 1

That's it

Now, you can do some operation with recordset object

objRec.MoveFirst ' goto first rec
objRec.MoveNext  ' goto next rec
objRec.MoveLast  ' goto last rec

if you want to dispaly the result in the list box, you can do like this :

objRec.MoveFirst
do while objRec.EOF
   list1.addItem objRec.Fields("RoomID")
loop

'dont forget to close the connection and recorset object when you finished

objRec.close
objConn.close
set objRec = Nothing
set objConn = Nothing

hope this helps :)

  danny
0
 
setiawanCommented:
sorry there is some mistake
>ObjRec.Open sql, 2 , 1

should be
objRec.Open sql, objConn, 2, 1

  danny


0
 
wsh2Commented:
Hello Megan.. <smile>

Open a Standard.exe project, add a Listbox (List1), and
Change the .Connectionstring = "c:\FilePath\MyDatabase.mdb" and the strArchive = "2" to use your MDB settings.

<----- Code Begin ----->

Option Explicit

Private Sub Form_Load()
 
  Dim cnAccess As New adodb.Connection
  With cnAccess
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "c:\FilePath\MyDatabase.mdb" ' <---- Change
    .Open
  End With

  Dim strArchive as String
  strArchive = "2" ' <---- Change
  MsgBox ("Record Count: " & xLoadArchive(strArchive, cnAccess))
  cnAccess.Close

End Sub

Private Function xLoadArchive _
(ByVal strArchiveKey As String, ByRef cnConnection As adodb.Connection) _
As Long

  Dim intCount As Long
  Dim rsArchive As New adodb.Recordset
  With rsArchive
    .ActiveConnection = cnConnection
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Source = "Select RoomID From Confirmed " _
      & "Where Archived = " _
      & strArchiveKey
    .Open , , , , adCmdText
    Do Until .EOF
      List1.AddItem .Fields(0) ' or whatever field you want
      intCount = intCount + 1
      .MoveNext
    Loop
    .Close
  End With
  xLoadArchive = intCount

End Function



0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
MeganAuthor Commented:
setiawan,
I've implemented all the code you suggested, but I'm getting an error: "Syntax error (missing operator) in query expression 'Archived='."
This is part of what i've written.

SQLQuery = "Select * from Confirmed where Archived= " & No
rsConfirmed.Open SQLQuery, cnDbase, 2, 1

Also what does the 2, 1 mean? Do you know what's wrong with the query?
I've tried to change the query to... SQLQuery = "Select * from Confirmed where (Archived = ('" & No & "'))"
but it doesn't work either...

Any ideas??
tnks. Meg.
0
 
MeganAuthor Commented:
Disregard my last comment i've figured it out! :)

this is the way that works...
-------------------------
SQLQuery = "SELECT * FROM Confirmed WHERE Archived='No'"
rsConfirmed.Open SQLQuery, cnDbase, 2, 1
 
rsConfirmed.MoveFirst

Do While rsConfirmed.EOF = False
   cmbRoom.AddItem rsConfirmed.Fields("AssetRoomID")
   rsConfirmed.MoveNext
Loop

rsConfirmed.Close
cnDbase.Close
Set rsConfirmed = Nothing
Set cnDbase = Nothing
-------------------------
I still don't understand what the 2, 1 is doing. Could you explain that for me please?..
Thanks sooo much for the help! <Grin>
Megan.
0
 
setiawanCommented:
Hi Megan,

CursorType   Optional.
A CursorTypeEnum value that determines the type of cursor that the provider should use when opening the Recordset. Can be one of the following constants (see the CursorType property for definitions of these settings).

Constant Description
adOpenForwardOnly (Default) Opens a forward-only–type cursor.
adOpenKeyset Opens a keyset-type cursor.
adOpenDynamic Opens a dynamic-type cursor.
adOpenStatic Opens a static-type cursor.



LockType   Optional. A LockTypeEnum value that determines what type of locking (concurrency) the provider should use when opening the Recordset. Can be one of the following constants (see the LockType property for more information).

Constant Description
adLockReadOnly (Default) Read-only—you cannot alter the data.
adLockPessimistic Pessimistic locking, record by record—the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.
adLockOptimistic Optimistic locking, record by record—the provider uses optimistic locking, locking records only when you call the Update method.
adLockBatchOptimistic Optimistic batch updates—required for batch update mode as opposed to immediate update mode.

ps : 2 -> adOpenDynamic (cursor type)
     1 -> adLockReadOnly (lock type)

hope this helps

   danny
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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