How to create database & tables ?? SQL-create Database & Table
-----------------------------------------------------------------------------------------------------------------------------------
Some Basics about Database.Refer the 6 steps given below :
Understand the below given 6 steps thoroughly , before going to do any Database Program.
The Steps involved are :
1 . Creating Database
1 : Refer Linking to Database also to know linking with MS-Access
Select Add-Ins --> Visual DataManager . Then we can see a form as :
2 : Select File-->New-->Microsoft Access--> Version 7.0 MDB
Give a name (Say , Sample1) and Save as type: *.mdb
Then , we can see :
3 : In the Database Window of the above picture,we can see Properties , right click on the property to select New Table.Then we will get a window called Table Structure , as given below :
4 : In the Table Name Field of Table Structure , give a name (Say , Address). Then Click on the Add Field option .Then another window will appear as :
5 : In the above figure ,create two fields with details as given below :
Name -Name , Type-Text and Size-50 , Click OK.
Again , Name -Address , Type-Text and Size-50 ,Click OK.
These are illustrated in figure given below :
6 : Then , Close the Add Field window .
This will lead to the same Table Structure field with two fields as Name & Address , With Table Name - Address.( See Point (4) above).
And the below given figures the created Table Structure.
7 : In the Table Structure window given above , we can see another option Build the Table . Clicking that will lead to the same picture (Picture (2) above ) , with a Table named Address, below to the Properties.
See the figure :
========================================================
2 . Adding Records in a Database
Next step is to add records in the created Database :
We have created this :
Double Click on the Address Field.Then we will get a Table with the created Fields , in which we can enter values.See the figure :
Click Add and enter Name and Address. Repeat the process as per our requirements.
We can use the arrow keys to move between Records.
Anotherv, most efficient Data Control is ADO-Data Control .This is known as ActivX data Object(ADO).
See next section to know more about ADO Data Controls.
Use these 2 steps we described before to create Database.
Select Project --> Components .
Then a Dialog Box will appear and select Microsoft ADO Data Control 6.0 and Click OK, as given below :
Click , Apply then Close to bring that Control in to our Form.
The ADO Data Control Looks just like Data Control..
Right Click on the ADO Data Control to get ADODC Property page.It's look like this :
Click Use Connection String , then Build .
Then Data Link Property Window will appear .
Select Microsoft Jet 3.51 OLE DB Provider , and Click Next.
When we click Next , Data Link Property Window appear , as shown ;
Then Click the first step to select the Database as shown below :
Give a User Name and Password , if needed : as shown ;
Click Test Connection .
If your connection is ok , then a message box will appear with a message ;
"Test Connection Succeeded " and Click OK.
After testing the connection ,again Click OK in the Data Link Property window.
Note the "Use Connection String" tab in the Property Page window :
Click Apply.
Next select the RecordSource from the above given Property Page.The window appeared will look like :
We can see two fields . Select as per given Below ;
Dim con As ADODB.Connection
(Here con = Name of the Connection Object )
Set con = New Connection
con.open"Connection String" (user ID),(Password).
See the created Connection String in the figure below . We have to give the name of that connection string in the place of "Connection String" , in Code.
con.close
To see Database Records using Code , and to Modify them , we have to create an object named Recordset . (rs)
Dim rs As ADODB.Recordset
(Here rs = Name of Recordset )
rs.open [Source] , [Active Connection] , [Cursor Type] , [Lock Type]
Example : rs.open"Select* from STUDENT",cn , adopen keyset , adLockpressimistic
==>
cn = Connection Object
adopen keyset = CursotrType (How to navigate in record)
adLockpressimistic = To lock a record from other user
==================================================================================
Refer
If found any errors while running your VB program, or want to put your comment about any program , please click Comment section , to post
-----------------------------------------------------------------------------------------------------------------------------------
Some Basics about Database.Refer the 6 steps given below :
Understand the below given 6 steps thoroughly , before going to do any Database Program.
The Steps involved are :
- Creating Database
- Adding Records in a Database
- How to see Database records using Data Controls
- How to connect ADO DataControl with Database
- How to create a Connection using Code
- How to see Database Records using Code
1 . Creating Database
1 : Refer Linking to Database also to know linking with MS-Access
Select Add-Ins --> Visual DataManager . Then we can see a form as :
2 : Select File-->New-->Microsoft Access--> Version 7.0 MDB
Give a name (Say , Sample1) and Save as type: *.mdb
Then , we can see :
3 : In the Database Window of the above picture,we can see Properties , right click on the property to select New Table.Then we will get a window called Table Structure , as given below :
4 : In the Table Name Field of Table Structure , give a name (Say , Address). Then Click on the Add Field option .Then another window will appear as :
5 : In the above figure ,create two fields with details as given below :
Name -Name , Type-Text and Size-50 , Click OK.
Again , Name -Address , Type-Text and Size-50 ,Click OK.
These are illustrated in figure given below :
6 : Then , Close the Add Field window .
This will lead to the same Table Structure field with two fields as Name & Address , With Table Name - Address.( See Point (4) above).
And the below given figures the created Table Structure.
7 : In the Table Structure window given above , we can see another option Build the Table . Clicking that will lead to the same picture (Picture (2) above ) , with a Table named Address, below to the Properties.
See the figure :
========================================================
2 . Adding Records in a Database
Next step is to add records in the created Database :
We have created this :
Double Click on the Address Field.Then we will get a Table with the created Fields , in which we can enter values.See the figure :
Click Add and enter Name and Address. Repeat the process as per our requirements.
And Click update . Then a dialog box will appear for Saving .Save it..
=========================================================================
3 .Data Control : To see Database Records
Data Controls are used to connect Database Applications.
For example , see the Form below which is using a Data Control to connect the Database.
Before that , we have to create a Database (Order Details as Table Name)with the following 5 Fields , as in the Form.And enter some Values in the 5 Fields.
Use these 2 steps we described before to create Database.
For example , see the Form below which is using a Data Control to connect the Database.
Before that , we have to create a Database (Order Details as Table Name)with the following 5 Fields , as in the Form.And enter some Values in the 5 Fields.
Use these 2 steps we described before to create Database.
- Creating Database
- Adding Records in a Database
(A)Select The Data Control (Data1 ) and Change its Property as given below :
- Name & Caption - Data1
- Connect - Access
- Database Name - (Select Path of Database you saved)
- RecordSourceType - 1 (Dynaset)
- Record Source - (Select the Table Name ; here , Order Details)
(B) Select all the 5 TextBoxes in the Form
- Change iall of its DataSource - Data1
(C) Select Text1 : DataField - Order ID
Select Text2 : DataField - Product ID
Select Text3 : DataField - Unit Prize
Select Text4 : DataField - Quantity
Select Text5 : DataField - Discount
Run the Project
We can use the arrow keys to move between Records.
Anotherv, most efficient Data Control is ADO-Data Control .This is known as ActivX data Object(ADO).
See next section to know more about ADO Data Controls.
=========================================================================
4 .ADO Data Control :To Connect Database
Use these 2 steps we described before to create Database.
- Creating Database
- Adding Records in a Database
Select Project --> Components .
Then a Dialog Box will appear and select Microsoft ADO Data Control 6.0 and Click OK, as given below :
Click , Apply then Close to bring that Control in to our Form.
The ADO Data Control Looks just like Data Control..
Right Click on the ADO Data Control to get ADODC Property page.It's look like this :
Click Use Connection String , then Build .
Then Data Link Property Window will appear .
Select Microsoft Jet 3.51 OLE DB Provider , and Click Next.
When we click Next , Data Link Property Window appear , as shown ;
Then Click the first step to select the Database as shown below :
Click Test Connection .
If your connection is ok , then a message box will appear with a message ;
"Test Connection Succeeded " and Click OK.
After testing the connection ,again Click OK in the Data Link Property window.
Note the "Use Connection String" tab in the Property Page window :
Click Apply.
Next select the RecordSource from the above given Property Page.The window appeared will look like :
We can see two fields . Select as per given Below ;
- Command Type - 2-AdCmdTable (Because we are selecting Access Data Base File)
- Table or Stored Procedure Name - (Select any Table from the Database)
- Click Apply then OK
=========================================================================
5 .How to create a Connection using Code?
- To declare a Connection Object :
Dim con As ADODB.Connection
(Here con = Name of the Connection Object )
- To create a New Connection :
Set con = New Connection
- To open a Connection :
con.open"Connection String" (user ID),(Password).
See the created Connection String in the figure below . We have to give the name of that connection string in the place of "Connection String" , in Code.
- To Close a Connection :
con.close
================================================================
6 . How to see Database using Code?
To see Database Records using Code , and to Modify them , we have to create an object named Recordset . (rs)
- To declare a Recordset :
Dim rs As ADODB.Recordset
(Here rs = Name of Recordset )
- To open a Connection Object :
rs.open [Source] , [Active Connection] , [Cursor Type] , [Lock Type]
Example : rs.open"Select* from STUDENT",cn , adopen keyset , adLockpressimistic
==>
cn = Connection Object
adopen keyset = CursotrType (How to navigate in record)
adLockpressimistic = To lock a record from other user
- To Close a Recordset
rs.close
- To add a new Record
rs.Add New
- To update a Record
rs.update
- To delete a Record
rs.Delete
- To Navigate through Records in a Database:
rs.move (n) , rs.move(-n)
rs.Movenext
rs.Moveprevious
rs.Movefirst
rs.Movelast
==================================================================================
- Linking to Database
- Create a database structure for a university
- Displaying State if the city is given
- Create an Employee Profile
- Select a detail from the given list
- Employee details
- Vote gained by the Candidate(Polling Application)
- Electricity Bill (NC)
- Maintain BCA students Examination Reports(Project) (NC)
- Program using ADO Data Control
- How to see Data in Grid
(E) Errors :
- ====================================================================
