Database nightmare...

• • Reply

  14 years ago

  by Random`

  XNeo

  Zahle, Bekaa, Lebanon

  Joined 14 years ago

  I have a database with a table named "Rooms", in this table there are 4 fields but we need to work
  with 2 now, here's what i want to do:
  
  i want to extract from the table two fields: RoomID and BuildID
  these 2 fields represent the id of the room and in which building it stands, how can i do that ?
  
  by the way:
  i'm using this method to extract only one field, i can extract me, at least i don't know how, and
  and it should be clear that the records are not sorted and not in order..
  
  
  Dim ar As ADODB.Recordset
  Dim i As Integer
  Set ar = New ADODB.Recordset
  Dim strSQL As String
  openDB
  strSQL = "SELECT BuildID FROM Rooms"
  ar.Open strSQL, adc, adOpenForwardOnly, adLockReadOnly
  If ar.EOF = False And ar.BOF = False Then
  do while not ar.eof
  i = ar.fields(0).value
  ar.nextmove
  Loop
  End If
  closeDB
  
  With this method i can only get the Building ID but i also need the RoomID, here's the full
  perspective, i have a TreeView and the root is Building, and inside each building there are rooms
  i need to fill this list like this:
  
  Building 1
     Room 1
     Room 5
  Building 2
     Room 2
     Room 6
  
  This is what i need... HELP !!!
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago
  I'm confused by your example code because it seems that you are storing the BuildID into an Integer and do nothing with it, so then overwrite the value until it is at the EOF.  You can order a non-ordered database within your SQL statement.  So, maybe instead of using an Integer, use an array and modify your SQL statement to select both fields and order them for you.  Something along these lines(I changed some variable names, but you should be able to translate easily):
  
  Dim conRecordset As ADODB.Connection
  Dim rsRecordSet As ADODB.Recordset
  Dim strSQL As String
  Dim aryBuildingArrary() as Integer
  Dim lnIndex as Long
  
  Set conRecordset = New ADODB.Connection
  Set rsRecordSet = New ADODB.Recordset
  
  strSQL = "SELECT [BuildID], [RoomID] FROM Rooms ORDER BY [BuildID] + [RoomID]"
  conRecordset.Open() ' I don't know what type of database you are connecting too, so can't fill in your connection string
  
  rsRecordSet.CursorLocation = adUseClient
  rsRecordSet.Open strSQL, conRecordset
  
  ReDim aryBuildingArrary(rsRecordSet.RecordCount)
  rsRecordSet.MoveFirst
  
  lnIndex = 0 ' You can start at 1 if you prefer, just keep a set system
  Do While Not rsRecordSet.EOF
      aryBuildingArray(lnIndex) = rsRecordset!BuildID & " " & rsRecordset!RoomID
      lnIndex = lnIndex + 1
      rsRecordset.MoveNext
  Loop
  
  
  This would give you an array filled in BuildID + RoomID order of every entry in your database.  If you need the two variables separate, you can always define a structure and set the array to that structure.  Since you only have 4 fields in that table, you may want to define a structure and pull all 4 variables in.  That way, if in the future you need one of those extra two variables, you already have the code in place.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  14 years ago

  by Random`

  XNeo

  Zahle, Bekaa, Lebanon

  Joined 14 years ago
  Awesome just what i was looking for, i'm sorry about the example, i copied the wrong part of the function...
  Thanks alot.. u just made my day.. :)
  
  
  
  Report abuse