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

VBScript; Query DBF FIles

Hello All,

I will do my best to explain this problem. I am attemping to query 3 dbf files for student information. The School uses a program called SASI. Some of you may be familiar with this. I would like to use vbscript to query the files directly. The information I need is located in 3 different files. Here is how the files are laid out. One File contains the General Student Information (ASTU.DBF). Another file contains the student schedules (ACLS.DBF). The last file ties the class to the teacher (AMST.DBF).

The first file (ASTU.DBF) conatins a STULINK field which ties the student information with the Schedules (ACLS.DBF). The second file (ACLS.DBF) contains a CLASSLINK field which ties this file to the Teacher File(AMST.DBF).

This is the info that I need. Student Name, Address, Phone, located in the first file, and 1st Period Teacher' Name  located in the 3rd File. Here is a snippett of my code, minus all the variable declerations.



DO WHILE NOT (StuRS.EOF)   ' ASTU.DBF
strTest = 01  
    DO WHILE NOT (ClsRS.EOF)  ' ACLS.DBF
        IF StuRS.Fields(StuLink) = ClsRS.Fields(StuLink) Then
              DO WHILE NOT (SchRS.EOF) ' AMST.DBF
                    IF ClsRS.Fields(ClassLink) = SchRS.Fields(ClassLink) Then
                           IF SchRS.Fields(Period) = 01 Then
                                wscript.echo StuFirstName & StuLastName & TchName
                                strTest = 02
                                Exit Do
                           Else
                           End If
                    Else
                    End If
              SchRS.MoveNext
              Loop
        Else
        End If
    IF strTest = 02 Then
        Exit Do
    Else
    End If  


    ClsRS.MoveNext
    Loop

ClsRS.MoveFirst
SchRS.MoveFirst
StuRS.MoveNext
Loop

wscript.quit


I hope by this code you can see what I am trying to accomplish. I am not to concerned with the syntax of the code, but more with the logic and flow. I can actually get some of the information i need back, but I am only getting half of the records.

Thanks in Advance
0
defore
Asked:
defore
  • 7
  • 5
  • 2
  • +1
1 Solution
 
RobSampsonCommented:
Hi, this may be one reason why you're only getting partial results....you're exiting the loop early with these lines:
IF strTest = 02 Then
   Exit Do
Else

because you set strTest to 02 as soon as you find one match in the class table.

Try commenting that out and see if you get different results.

Regards,

Rob.
0
 
grayeCommented:
I'm not familiar with SASI, but I assume you can execute an SQL-based query against the database?   If so, that would be a way to link all of the databases together via the SQL "JOIN" keyword.   It would look something like this:

Select StudentName, Address, Phone, TeacherName from ASTU Inner Join ACLS on ASTU.STULINK = ACLS.STULINK Inner Join AMST on AMST.CLasslink = ACLS.ClassLink
0
 
deforeAuthor Commented:
Hey Guys,

I appreciate the comments. I shortened the code because I didn't have it in front of me at the time. Here is the actual code. The program works, but it only writes out 137 records, when there are 378 records.

DO WHILE NOT (HSStuRS.EOF)
' Create High School File
  DO WHILE NOT (HSClass.EOF)
   IF HSStuLink = ClsStuLink Then
    DO WHILE NOT (HSSchedule.EOF)

          IF ClsClassLink = SchClassLink Then
          ClassLink = ClsClassLink

          IF SchPeriod = 01 Then
             strExport.Write HSStuLink & Chr(34) & int(HSStuPermNum) & Chr(34) & "," & Chr(34) & "0" & HSSchNum & Chr(34) & "," & _
             Chr(34) & HSStuLastName & Chr(34) & "," & Chr(34) & HSStuFirstName & Chr(34) & "," & Chr(34) & _
             HSStuMiddleName & Chr(34) & "," & Chr(34) & HSStuGender & Chr(34) & "," & Chr(34) & HSStuGrade & _
             Chr(34) & "," & Chr(34) & SchTeacher & Chr(34) & "," & Chr(34) & HSStuGradYR & Chr(34) & "," & _
             Chr(34) & HSStuBirth & Chr(34) & "," & Chr(34) & int(HSStuPermNum) & Chr(34) & "," & Chr(34) & _
             HSStuParent & Chr(34) & "," & Chr(34) & HSStuAddress & Chr(34) & "," & Chr(34) & HSStuCity & _
             Chr(34) & "," & Chr(34) & HSStuState & Chr(34) & "," & Chr(34) & HSStuzip & Chr(34) & "," &_
             Chr(34) & HSStuPhone & Chr(34) & vbCRLF
             strCount = strCount + 1
             strPeriod = SchPeriod
             Exit Do
          Else

          End If
          'Exit Do
          ELSE

          END IF

    HSSchedule.MoveNext
    Loop

   ELSE
     'Exit Do
     'HSClass.MoveFirst
   END IF
  HSClass.MoveNext
  Loop
HSClass.MoveFirst
HSSchedule.MoveFirst
HSStuRS.MoveNext
LOOP
0
Independent Software Vendors: 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!

 
deforeAuthor Commented:
Sorry about the write statement. You gotta love wordwrap!  :)
0
 
RobSampsonCommented:
Hi, why are you exiting the Do loop when you find a match?  Is that because you know there is only one match in the HSSchedule recordset?

Regards,

Rob.
0
 
deforeAuthor Commented:
Hi Rob,

Thats right. These are student schedules. Each student has 6 classes. I only want their first period. The stulink is listed six times in the ACLS file tied to six different class links. The AMST file contains the class link and the class information, including section id and Teacher Name.

Hope this helps
0
 
RobSampsonCommented:
OK, that's no problem.  Maybe we should add some counters on each recordset just to see if we're getting the expected total.....we'll start with the HSStuRS recordset.  Just above this line:
HSStuRS.MoveNext

Put in
intHSStu = intHSStu + 1

Then after the final loop:
MsgBox "Total records in HSStuRS: " & intHSStu

and if that's full expected value, we'll try another one....if not....it's not populating the recordset properly....for which the code you haven't posted.....

Regards,

Rob.
0
 
deforeAuthor Commented:
Hey Rob,

I have tried that and I am getting the correct number of records. 378 Student Records.
0
 
RobSampsonCommented:
OK, just before
Do While Not (HSSchedule.EOF)

try putting
HSSchedule.MoveFirst

so that you make sure you are iterating through the entire HSShedule with each record in HSClass

Regards,

Rob.
0
 
deforeAuthor Commented:
Hey Rob,

Thanks a lot for your help man. Putting that HSSchedule.MoveFirst in the second Do loop appears to give me  all the results. I need to make sure the data lines up correctly,  Just Tell me how much I owe you. :) I will wait to award the points until  tomorrow, just in case i need to update the question.

Again, Thanks a lot.

Matt Defore
0
 
RobSampsonCommented:
Cool, no worries....you don't owe me anything....I'm just volunteering my services....

I will await any further findings.....

Rob.
0
 
A5technologyCommented:
Matt,

Would it be possible to get a copy of your script? I'm working with SASI to gather some information using VBScript and you could save me a LOT of time.


Thanks! Ben
0
 
deforeAuthor Commented:
What are you trying to do. I have written several scripts to extract student data. I can send you one that will better suit your needs.

Thanks

Matt
0
 
A5technologyCommented:
Im trying to write a script to find new students at each of my schools in order to create accounts for them while at the same time finding students are have been marked inactive. I just need to be able to connect to the ASTU.DBF file but I havent connected to a DBase file before with VBS. If I could just get a copy of the script you worked on above it would be great. I can create a new new question if you want.


Thanks,
Ben
0
 
deforeAuthor Commented:
This is the one I used to automatically create a csv file for the schools library catalog system. It also has a statement in there to check if the student is inactive or active. Let me know if you have any questions.

Thanks

Matt
StudentExport.txt
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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