Database nightmare...

vb6 Lebanon
  • 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
    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
    End If

    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 !!!

  • 14 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)

    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

    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.

  • 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.. :)

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Weeks of coding can save you hours of planning.”