1 / 26

Project Progress Report

Project Progress Report. Design Phase is complete Analysis of the Requirements Design of the Project Screen Design (using Visual Basic) Database Design (Normalization) Process Design (Pseudocode/Program Logic) Construction and Implementation Visual Basic Coding and Testing

homer
Télécharger la présentation

Project Progress Report

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Project Progress Report • Design Phase is complete • Analysis of the Requirements • Design of the Project • Screen Design (using Visual Basic) • Database Design (Normalization) • Process Design (Pseudocode/Program Logic) • Construction and Implementation • Visual Basic Coding and Testing • Technical and User Documentation • Installation of the New System

  2. Visual Basic Unit • How to read an ACCESS table randomly in VB • By primary key [single key] • By another field [single field] • How to read an ACCESS table using a Composite Key

  3. Visual Basic Review • How to Read Data from an Access Database • Data Control • FindFirst/FindNext statements • NoMatch • Fields • Sample Test Questions • Sample Application

  4. The Registration Database Class info: • Course ID FK • Section • Building • Room • Days • Start Time • End Time • No Enrolled • Inst SSN FK Student info: • SSN • Name • Address • Major • Cum hours • Cum points Stu-Class info: • SSN FK • CourseID FK • Section FK • Grade 1:M M:1 1:M M: 1 Instructor info: • Instructor SSN • Instructor Name • E-mail • other stuff Course info: • Course ID • Title • Credit

  5. How to Read An Access Data Base VB toolbar • Add a data control on the form for each table in the access database that is needed in the form

  6. Data Control Properties

  7. Data Control Properties • Visible: False • Name: datTable • Examples: datStudent; datStuClass; datClass • Connect: ACCESS • Database Name: (location and name) • a:\registration.mdb or click on the … which will allow you to search for the database name • Record Source: Table name in the Access database • Example: student, stuclass, class, course or click on the down arrow to view the tables in the database named on the database name property.

  8. Visual Basic Procedural Statements and Options • FindFirst • FindNext • NoMatch • fields

  9. FindFirst, FindNext • What are you finding? • A record in the ACCESS database containing the desired value of a field: “[Access field name] = ‘value of the field’” • The Access field name must match the field name in the Access table exactly • The brackets around the field name are required if the field name contains a special character such as a space • The brackets are optional in all other cases • The brackets will always work

  10. FindFirst, FindNext • “[Access field name] = ‘value of the field’” • The value of the field is usually a variable name or textbox. • The query then becomes:“[Access field name] = ‘” & variable & “’” • The & is a concatenation symbol • Assume the social security number for the query is stored in txtSSN.text: strFind = “[Social Security] = ‘” & txtSSN.text & “’” • Assume txtSSN.text contains 123456789. The query becomes: strFind = “[Social Security] = ‘123456789’”

  11. FindFirst, FindNext • “[Access field name] = ‘value of the field’” • If the Social Security field on the access table is a text field, you must use the apostrophes around the value of the field • strFind = “[Social Security] = ‘” & txtSSN.text & “’” • If the Social Security field on the Access table is a number field, you do NOT use the apostrophes • strFind = “[Social Security] = ” & txtSSN.text

  12. FindFirst, FindNext • datStudent.Recordset.FindFirst strFind • datStudent.Recordset.FindNext strFind • datStudent is the name of the data control pointing to the desired table in the Access database • Recordset is required • FindFirst, FindNext -- the desired action • FindFirst starts at the start of the table looking for the desired record • Find Next starts at the current location in the table • strFind - identifies the desired record

  13. NoMatch • After a FindFirst/FindNext: the NoMatch option can determine if the desired record was fetched • If you are reading your table by primary key, the check for nomatch is usually done with an IF statement • IF datStudent.Recordset.NoMatch = False then the desired record was located • IF datStudent.Recordset.NoMatch = True then the desired record was not located • datStudent is the data control pointing to the desired table in the Access database • Recordset is required

  14. NoMatch • After a FindFirst/FindNext: the NoMatch option can determine if the desired record was fetched • If you are reading your table by a field other than the primary key, the check for nomatch is usually done with a DO LOOP [there can be multiple records with the desired value] • Do While datStudent.Recordset.NoMatch = False • The statements in the loop will be executed if the desired record was located • There is ususally a datStudent.Recordset.FindNext statement at the end of the loop • The loop will be executed as long as a record match the query is found

  15. NoMatch ExampleNomatch used in an IF statement • DIM strFind as String • strFind = “[SSN] = ‘” & txtSSN.text & “’” • datStudent.Recordset.FindFirst strFind • IF datStudent.Recordset.NoMatch = False then • . • . • End If SSN is the entire PK of the student table, so an IFstatement is used to check the nomatch.

  16. NoMatch ExampleNomatch used in a loop condition • DIM strFind as String • strFind = “[SSN] = ‘” & txtSSN.text & “’” • datStuClass.Recordset.FindFirst strFind • Do While datStuClass.Recordset.NoMatch = False . . datStuClass.Recordset.FindNext strFind • Loop SSN is NOT the entire PK of the student class table, so a Do Loop is used to check the nomatch.

  17. Fields Option • After finding a record (FindFirst, FindNext) • the fields in that table are accessible: • txtName.text = datStudent.Recordset.Fields(“Name”) • datStudent is the name of the data control • Recordset is required • Fields is required • “Name” is the field name from the Access table which you want to display in the txtName textbox

  18. Fields Option Example Write the VB which will print [in picture box picName the name of the student whose SSN is entered in txtSSN. • DIM strFind as String • strFind = “[SSN] = ‘” & txtSSN.text & “’” • datStudent.Recordset.FindFirst strFind • IF datStudent.Recordset.NoMatch = False then • picName.print datStudent.Recordset.Fields(“Name”) • End If SSN and Name are field names on the Student Table in the ACCESS database

  19. Fields Option Example Write the VB which will print [in picture box picClasses] the course ID/sections in which the student (whose SSN is entered in txtSSN) is enrolled. • DIM strFind as String • strFind = “[SSN] = ‘” & txtSSN.text & “’” • datStuClass.Recordset.FindFirst strFind • Do While datStuClass.Recordset.NoMatch = False • picClasses.print datStuClass.Recordset.Fields(“CourseID”); • tab(20); datStuClass.Recordset.Fields (“Section”) • datStuClass.Recordset.FindNext strFind • Loop

  20. Sample Problem 1 Assume the user has entered an instructor’s SSN in the textbox txtINST. Print in picName the name of the instructor. Use the fields names shown on the registration database at the start of this presentation

  21. Answer to Problem 1 Assumes there is one data control with the name datInstr pointing to the Instructor table in the Access database. • DIM strFind as String • strFind = “[Instructor SSN] = ‘” & txtSSN.text & “’” • datInstr.Recordset.FindFirst strFind • IF datInstr.Recordset.NoMatch = False then • picName.print datInstr.Recordset.Fields(“Instructor Name”) • End If

  22. Sample Problem 2 Assume the user has entered an instructor’s SSN in the textbox txtINST. Print in picClasses the courseID and Section that the instructor teaches.

  23. Answer to Problem 2 Assumes there is one data control with the name datClass pointing to the Class table in the Access database Note: the instructor SSN, course ID, and section are all on the class table. We only need to use this one table. Next week we will use multiple tables! • DIM strFind as String • strFind = “[Inst SSN] = ‘” & txtSSN.text & “’” • datClass.Recordset.FindFirst strFind • Do While datClass.Recordset.NoMatch = False • picClasses.print datStuClass.Recordset.Fields(“Course ID”); • tab(20); datStuClass.Recordset.Fields (“Section”) • datClass.Recordset.FindNext strFind • Loop Inst SSN, Course ID and Section are the field names on the Class Table.

  24. Sample Problem 3 • Assume that txtCourse.text contains a Course ID • Write a routine to compute the total number enrolled in all sections of this course. • Print the total in picEnrolled picture box • Be SURE to identify the data control and its properties

  25. Pseudocode for Problem 3 Initialize the Grand total to 0 Find the first record on the class table for the Course ID in the textbox Loop while a record on the class table for the Course ID is found Add the number enrolled on the class table to the grand total Find the Next record on the class table for the Course ID in the textbox End the Loop Display the grand total in the textbox

  26. Visual Basic for Problem 3 DIM strFind as String DIM intGrandTot as Integer strFind = “[Course ID] = ‘” & txtCourse.text “’” datClass.Recordset.FindFirst strFind intGrandTot = 0 Do While datClass.Recordset.Nomatch = False intGrandTot = intGrandTot + datClass.Recordset.Fields (“No Enrolled”).value datClass.Recordset.FindNext strFind Loop txtEnrolled.text = intGrandTot

More Related