Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    27

    Unanswered: newbie needs simple help

    my database is on the attachment. Im decent with vb programming but suck with databases! Basically Ive to accomplish the following. I created code for a calendar that allow a user to enter a value for any date in the calendar. I need to take the values they entered into my vb calendar and save the dates with a value to my database. If I were working with one table it wouldnt be a problem. Unfortunately Im using ADO which only allows for 1 table connection at a time. To compensate for this i used 2 ado connections. 1 for each table. The details table shold store the date of an infraction for the employee(infraction-Date that employee screwed their attendance up by a no-call/no-show, coming in late, etc)

    My problem is if an infraction is entered it might be the first time that employee got an infraction and i get an error because im moving both tables at the same time. I know basically im supposed to use an if exists clause for this and then next time the details page is available then re synchronize the tables according to employee id. My prob is I only know what it says in a book. Ive no practical experience. I guess what is like to see is a very simple
    vb program hooked up to a database with 2 table a main table and a details table. Then I would be fine As i can pick apart the code to see what it does.


    Normally Id just attach my project as a whole but as thier are very complicated calculations in it, Im not yet done with my error checking for these calulations.

    Also from what I do know of databases and structure I currently have my table setup correctly I wanted to verify that at this point and hopefully ...see a sample program of this as stated above.
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    You can only have one DATABASE per ADO Connection but the connection CAN operate on multiple tables as long as they all reside in the same database.

  3. #3
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Here's a VERY Q&D example that uses the Northwind DB and lists the products ordered for May 1998.

    Code:
    '******************************
    
    Dim ado As New ADODB.Connection
    
    Dim rs1 As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    
    Dim sql As String
    
    Private Sub Form_Load()
    
    ado.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=GRAHAMT"
    
    ado.Open
    
    sql = "SELECT OrderID, CustomerID FROM Orders WHERE OrderDate >= '1998/05/01' Order By OrderID"
    
    rs1.Open sql, ado, adOpenForwardOnly, adLockReadOnly
    
    If Not rs1.EOF Then
        While Not rs1.EOF
            sql = "SELECT ProductID From [Order Details] Where OrderID=" & CStr(rs1!OrderID)
            rs2.Open sql, ado, adOpenForwardOnly, adLockReadOnly
            
            If Not rs2.EOF Then
                While Not rs2.EOF
                    Debug.Print rs1!OrderID, rs2!ProductID
                    rs2.MoveNext
                Wend
            End If
            
            rs2.Close
            
            rs1.MoveNext
        Wend
    End If
    
    rs1.Close
    ado.Close
    
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set ado = Nothing
    
    End Sub
    Last edited by grahamt; 05-25-04 at 16:44. Reason: Format Code

  4. #4
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    ... and before anybody says it, I know the example is lousy coding, it's just to show that you CAN have multiple tables open through one connection. Obviously a JOIN would be more efficient in the select but I'll leave the syntax of that for one of the SQL experts.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •