Computer Notes

Header Right

Home » VB » VB Database

Explain deference between a data control and data-bound control

By Dinesh Thakur


Data control

Data-bound

Using data control is a two-step process. First you place a data control on a form and set the properties to link it to a database file and table

You create the controls, such as labels and text boxes, to display the actual data. Each control is a bound to particular field in the table. In this example the label is called a data bound control and automatically displays the contents of bound field when the project runs.

Data control generally links one form with one table.

If you want to have data-bound controls on second form, you must place a data control on that form.

Prefix of data control is “dat”

For data-bound control prefix depends upon the control which you are using.

Data control to work you need to set some properties to connect with database like connect property, database name property, record source property.

To display data on the data-bound control that you are using like labels or textboxes. You need set it?s data source property and data field name which is column name form the table.

 

 

Which controls can be data bound

By Dinesh Thakur

1. You can bound data to data bound controls like Labels, Text Boxes, Checkbox. If you don?t want user to change the data you use Label control, where user can only view the data else you can use Text boxes.

 

2. If you want to display data in the form of True or False you can have Check Box control.

 

Explain how BOF and EOF properties are set and how they might be used in a project

By Dinesh Thakur

1. Two handy properties of the record set are BOF (beginning of file) and EOF (end of file). The BOF property is automatically set to true when the record pointer is before the first record in the record set.

This condition happens when first record is current and the user choose MovePrevious. The BOF property is also true if the record set is empty

 

2. The EOF property is similar to BOF; it is true when the record pointer moves beyond the last record in the record set and when the record set is empty.

 

3. When you are doing your own navigation in code, you need to check for BOF and EOF so that run-time errors do not occur. If the user clicks MoveNext when on the last record, what do you to do? Have the program cancel with a run time error? Display a message? Wrap around the first record? Keep the record pointer to last record?

 

4. In the following example, we will wrap-around method. If the user clicks on the MoveNext button from the end of the table, the first record becomes active record.

 

Private Sub cmdNext_Click()

„Move to Next record

datBooks.Recordset.MoveNext

If datBooks.Recordset.EOF Then

datBooks.Recordset.MoveFirst

End If

End Sub

 

What are the steps needed to add a new record to database

By Dinesh Thakur

1. When you want to add new record you have couple of choices. If you are using data control?s navigation buttons, you can allow Visual Basic to do the adds automatically. Set the data control?s EOF action property to

 

2 – AddNew. When the user moves to end of the file and clicks the arrow buttons, the Update method is automatically executed and the new record is added.

 

2. You need a different approach when you use code to accomplish record navigation. Assume that you have a command button to menu choice to add a new record. In the click event for the command button use AddNew method:

 

3. datBooks.Recordset.AddNew

 

4. When this statement executes, all bound controls are cleared so that the user can enter the data for the new record. After the data fields are entered, the new record must be saved in the file. You can explicitly save it with an update method; or, it the user moves to anther record, the update method is automatically executed.

 

5. You may want to use two buttons for adding a new record –an Add button and s save button. For the Add button, use an AddNew method; for the Save button, use the Update method.

 

6. When adding new records, some conditions can cause errors to occur.

 

7. For example, if the key field is blank on a New record, a run time error holts the program execution.

 

What are the steps needed to delete a record from database

By Dinesh Thakur

1. The delete method deletes current record. The user should display the record to delete and click a Delete command button or menu choice.

 

2. When a record is deleted, the current record is no longer valid. Therefore, a delete method must be followed by MoveNext method.

 

With datBooks.Recordset

.Delete

.MoveNext

End With

 

3. But what if the record being deleted is the last record in the table?

 

4. Remember that if the navigation buttons are used to navigate, moving beyond EOF just resets the current record to the last record.

 

What steps are needed to change the data in a database

By Dinesh Thakur

The record set object has an Update method, which you can use to save any changes in the data.

Most of the time, updating is automatically executes the Update method any time the user clicks one of the navigation buttons or one the Move methods executes.

When would it be a good idea to use a grid control

By Dinesh Thakur

Database tables can be bound to controls, such as text box, labels, checkbox and list boxes. But it displays one row data at a time.

But if you want to display multi row data then there is another good way to display data that is, data can be displayed in table in rows and columns format.

 

Visual Basic comes with several grid controls. In addition, many companies sell powerful grid controls that you can include in your project.

 

When does a control’s Validate event occur? How is the Cancel argument used

By Dinesh Thakur

When you want to make sure that a required field has an entry or that field has valid data, the Validate event is good location to check. If the field is not valid, you can display an error message and cancel the operation.

 

New Visual Basic 6, most controls now have a new property and a new event that greatly aid in validating the entries in controls. The Causes Validation property can be set to True or False, which indicates whether validation event occurs for the control just before it loses the focus.

 

For example, assume you have txtISBN and txtTitle. The user is expected to enter a value in txtISBN and tab to txtTitle. If you have the Causes Validation property of txtTitle set true, then the validate event of txtISBN will occur. Of course, the user may do something else, such as click in a different field, o a command button. You must set the Causes Validation property to true for all controls. Default setting for Causes Validation property is true.

 

Syntax of Validate Event is:-

Private Sub ControlName_Validate(Cancel As Boolean)

Over here Cancel Argument decides whether focus should be kept or not.

 

What is a record set ? What are its type ? Explain

By Dinesh Thakur

Recordset:

 

The current group of records associated with a data control; may be a table recordset , a dynaset, or a snapshot.

 

Visual Basic supports 3 kinds of Record Set as follows:-

 

Table Record Set:-

 

Table Record Set represents a single table as it exist in a Database file. Table Record Set are usually updatable unless the file is locked or open for read only.

 

Dynaset:-

 

A Dynaset is temporary set of data taken from one or more table in the form one of many table in the underlined file. A Dynaset may be a query. That was defined in a access of table of result of joining multiple tables. Like a table, a Dynaset is updatable if file is not locked or open for read only. Data in Dynaset is live that is any changes made to data as project is executing will appear in Record Set.

 

Snapshots:-

 

Snapshot Record Set, like a dynaset, may be taken from one or more table. The difference is that snapshot is not updatable and also not live. A snapshot is like photograph a picture of reality a give point. TYBSC (IT) Visual Basic 2

 

What is SQL, and how is it used in Visual Basic

By Dinesh Thakur

Visual Basic uses Structured Query Language (SQL) to create new dynasets. SQL is an Industrial Standard language for processing relational database.

 

Visual Basic uses SQL to create new recordsets.Set the data controls RecordSource property to an SQL query and execute a Refresh method

Visual Basic use SQL to select fields and records from a table and join the fields from row tables.

 

Syntax:

 

Select [ distinct ] FieldList From TableNames

Where SearchConditions

Group By FieldList

Having GroupCriteria

Order By FieldList

 

Example:

 

Select * From Books

Select * From Books, Subjects

Where Books.Subject_Code = Subjects.SubjectCode

Order By Title

Select Distinct Author from Books

 

What is grid control ? How will you create it

By Dinesh Thakur

The Databound grid control in VB 6.0 adds power and flexibility to your DataBase programs. you can easily provide grid access to any available DataBase.

You can provide simple display only access for used with summary data and on-screen reports.You can also provide editing capability to your dara grid including modifying only, add rights or delete rights.

 

It’s very easy to create a data grid form

A Grid Control-Step-by-Step

 

STEP 1: Begin a new project and widen the form.You may want to close the Project Explorer an Form Layout windows and float the properties

window to allow room to work on the wide form.

 

STEP 2:Add a data control along the bottom of the form.Set the control’s Name property to datBooks and its DatabaseName property to RnrBooks.mdb.Set the

RescordSource property to Books and verify that the RecordsetType is 1 – Dynaset.

 

STEP 3:Select project/Components to display the Components dialog box.Then locate Microsoft Data Bound Grid Control 5.0, select it , and close the dialog box.You should see the new tool in the toolbox.

 

STEP 4:Click on the DBGrid tool and draw a large grid on the form.Then , using the properties window , change the control’s Name property to dgbBooks and its DataSource property to datBooks.

 

STEP 5:Create the menu bar. It should have a File menu with only an Exit command.

 

STEP 6:Create the large label at the top of the form with the form’s title: Book List.Change the font and size to something like.

 

What is the purpose of record count and absolute position properties

By Dinesh Thakur

You can use the Record count and absolute position properties of the Data Control Recordset to display the RecordNum and RecordCount when viewing DataBase Records.

 

 

Example:

 

Record 10 of 45

 

The RecordCount property holds the number of records in a recordset.The absolute position holds the position of the current Record in the Recordset.However absolute postion is zerobased i.e; the first record has an absolute position of 0.Therefore, you must add 1 to the proprty to produce the RecordNum.

 

Example:

 

IntCurrentRecord = Data1.Rescordset.Absolute postion + 1

You cannot use the absolute position property as a record Num , because the position num changes as records are added and deleted.

 

Example:

 

Private sub Record Num( )

Dim RecordCount, CurrentRecord as integer

RecordCount = Data1.Recordset.RecordCount

CurrentRecord = Data1.Recordset.Absolute Position + 1

If Data1.Recordset.eof then

Data1.Caption = “EOF”

else

Data1.Caption = ” Record ” & CurrentRecord & ” of ” & RecordCount

Endif

End sub

 

Seek method

By Dinesh Thakur

The fastest way to search for a particular record is to use the Seek method , which can be used only with a table recordset tab.The Seek method uses indexes for the search , which is much faster than checking every record in the table as you do in a find .

 

In order to do a seek , the field on which you search must be defined with an index.

Syntax:

Data1.Recordset.Seek ComparisonOperator , FieldValue

The comparison operator must be enclosed in quotation marks and can be any one of this “=”,”>=”,”>”,”<=”,”<“.The field value is the actual value for which to search string literal must be enclosed in quotation marks;numeric values must not be so enclosed.

Example:

i. Data1.Recordset.Seek “=”, “0-671-66398-4”

ii.Data1.Recordset.Seek “>=”, 1 ‘find first record greater than or equal to 1 in the first position of the key