Client Server Programming using Microsoft SQL Server 2000

Client Server Programming using Microsoft SQL Server 2000 and Visual Basic 6. 0 Workshop Presented By: Amit Kumar Tailor (Programmer – Computech IT Solutions) Saturday July 12 th 2003 Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Objectives of Workshop Part A Ø Ø Briefly provide an overview/characteristics of Client Server application Explain the tools and features of Microsoft SQL Server and Visual Basic which facilitate Client Server programming especially ADO Programming model Part B Ø Ø Step through the setup of Microsoft SQL Server/Visual Basic and demonstrate by example, the programming tools Enable participants hands-on practice with Microsoft SQL Server and Visual Basic Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Part A Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Overview of Client Server Programming Ø Generally described a client application is an interface to an intelligent database engine – the server Visual Basic Client Application SQL Server 2000 Ø In our case the client being an application designed using Visual Basic 6. 0, which interfaces with SQL Server 2000 Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Characteristics of Client Server Application Ø A Client Server application generally has these characteristics: Ø Centralized Intelligent Servers Ø Network Connectivity Ø Applications leverage Server Intelligence Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Characteristics of Client Server Programming Centralized Intelligent Servers Ø The database servers not only provides shared file services but are intelligent, because they carry out commands in the form of Structured Query Language (SQL) and return answers as result sets or recordsets Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Characteristics of Client Server Programming Network Connectivity Ø The database server is connected to clients by way of a LAN, WAN, or the Internet. A well designed client server application will try to minimize traffic on the network line by transmitting only relevant SQL command result sets. Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Characteristics of Client Server Programming Applications Leverage Server Intelligence Ø A well designed Client/Server application utilizes server’s intelligence by letting the server perform bulky physical data manipulation while the client application's job is to extract and display the results intelligently. Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Features of SQL Server Ø SQL Server provides data services in an organized manner through: Ø Ø Database Services – Tables, Views, Stored Procedures, Triggers, Functions etc Security – User authentication for database access Data Transformation Services – enables bulk data processing which can be defined into packages Replication – enables synchronization of data in distributed applications Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

SQL Server Client Tools Ø SQL Server Client Tools include: Ø Ø Ø OSQL - The osql utility is a Microsoft Win 32 command prompt utility for ad hoc, interactive execution of Transact-SQL statements and scripts Enterprise Manager – GUI based Management utility which enables creation of Tables and data entry through wizards with ease of use SQL Query Analyzer – Enables parsing of Stored Procedures, Functions, Transact-SQL Statements and other ad hoc queries Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Interfacing with SQL Server Ø Generally two kinds of interfacing are used to access the database server. These are: Ø Ø Call Level Interface - offers a set of function calls or APIs that enable client applications to interact with a server database Object Interface - offers a model of database programming “objects” (such as connections, SQL statements, and result sets) that can be created by your application and used to send and retrieve data from the database Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Interfacing with SQL Server Call Level Interface Ø Ø Usually use parameters specified as pointers to data input and output buffers owned by the application. Because of this reliance on pointers, call-level interfaces are almost always used from the C/C++ language SQL Server offers two call-level interfaces: Ø Ø Open Database Connectivity (ODBC)—an industry-standard, calllevel interface DB-Library—the original call-level interface that is specific to SQL Server Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Interfacing with SQL Server Object Interface Ø Ø Vary widely in their level of abstraction, exposure of database features, performance characteristics and are also usually restricted to specific programming languages Some of object interfaces available are: Ø Ø OLE DB – which is an object version of ODBC API ADO – offers a higher level interface on top of OLE DB RDO - closely tied to ODBC and optimized for accessing server databases DAO - closely tied to and optimized for the Jet local database Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Why Visual Basic ADO Application ? Ø ADO is the API most recommended for general-purpose data access to SQL Server for these reasons: Ø Ø Ø ADO is easy to learn and program ADO has the feature set required by most general-purpose applications ADO enables programmers to quickly produce robust applications Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADO for SQL Server Connectivity Ø An application that uses ADO to communicate with SQL Server 2000 performs the following tasks: Ø Connects with a data source Ø Sends SQL statements to the data source Ø Processes the results of statements from the data source Ø Processes errors and messages Ø Terminates the connection to the data source Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Part B Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of SQL Server 2000 Client Tools Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “SQL Server 2000 Components” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Install Database Server” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Yes” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Installation begins …………… Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft SQL Server Client Tools 8 Click on “Finish” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft Desktop Engine Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft Desktop Engine (MSDE 2000) You can add entries in the [Options] section of your Windows Installer setup. ini file that customizes the installation of the instance of the Desktop Engine. These are some of the settings and their descriptions. Ø Ø TARGETDIR="executable_folder_path“ - where the Desktop Engine executable files are installed TARGETDIR="executable_folder_path“ DATADIR="data_folder_path“ - where the Desktop Engine system databases are built DATADIR="data_folder_path“ INSTANCENAME="instance_name" COLLATION="collation_name“ – alias name COLLATION="collation_name“ Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft Desktop Engine (MSDE 2000) 8 Provide a MSDE instance name in “setup. ini” eg “CQUFIC” “setup. ini” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft Desktop Engine (MSDE 2000) 8 Installation begins …………… Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Microsoft Desktop Engine (MSDE 2000) 8 Observe the location “C: Program FilesMicrosoft SQL ServerMSSQL$CQUFIC” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using SQL Server Client Tools Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using Enterprise Manager (SQL Server Client Tool) 8 Activate from “Start Menu > Programs > Microsoft SQL Server > Enterprise Manager” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using Enterprise Manager (SQL Server Client Tool) 8 Observe the instance “CQUFIC” has automatically been detected and registered Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

SQL Server Security Settings for Client Access 8 Right click on the instance and select “Properties” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

SQL Server Security Settings for Client Access 8 Select [Security] tab, choose option “SQL Server and Windows” for Authentication Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

SQL Server Security Settings for Client Access 8 Under folder [Security] > [Logins], right click on user “sa” and select properties “sa” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

SQL Server Security Settings for Client Access 8 Provide/verify the password for user “sa” for client access eg. “spcs” “sa” eg. “spcs” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating a new database using Enterprise Manager 8 Select folder [Database], right click on it and select “New Database” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating a new database using Enterprise Manager 8 Provide a name eg. “ADMIN” eg. Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating a new database using Enterprise Manager 8 Observe the physical database file created in “C: Program FilesMicrosoft SQL ServerMSSQL$CQUFICData” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating a new table under ADMIN database using Enterprise Manager 8 Observe the database objects available under “ADMIN” eg. Stored Procedures, Users, Tables eg. etc. Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating a new table under ADMIN database using Enterprise Manager 8 Select “Tables” object, right click and select “New Table” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating a new table under ADMIN database using Enterprise Manager 8 Create fields, ID as numeric, First. Name as varchar, Last. Name as varchar, assign Primary Key varchar, to ID Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating a new table under ADMIN database using Enterprise Manager 8 Save the table as “STAFF” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Adding records to table STAFF using Enterprise Manager 8 Select “STAFF” table, right click, select “Return all rows” under “Open Table” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Adding records to table STAFF using Enterprise Manager 8 Add some sample record data eg. “ 1, John, Doe”, “ 2, Ram, Prasad”, ” 3, Luisa, Tora” eg. Tora” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using SQL Query Analyzer (SQL Server Client Tool) 8 Activate from “Start Menu > Programs > Microsoft SQL Server > Query Analyzer” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using SQL Query Analyzer (SQL Server Client Tool) 8 Login using SQL Server Authentication, Login Name: “sa”, Password: “spcs” “sa”, “spcs” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using SQL Query Analyzer (SQL Server Client Tool) 8 Observe the Query Analyzer Interface Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating an “INSERT” Stored Procedure Using SQL Query Analyzer 8 Parse a stored procedure “INSERT_INTO_STAFF_SP” to insert a record into “STAFF” table Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating an “UPDATE” Stored Procedure Using SQL Query Analyzer 8 Parse a stored procedure “UPDATE_STAFF_SP” to update a record into “STAFF” table Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating a “DELETE” Stored Procedure Using SQL Query Analyzer 8 Parse a stored procedure “DELETE_STAFF_SP” to delete a record from “STAFF” table Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating a “SELECT” Stored Procedure Using SQL Query Analyzer 8 Parse a stored procedure “SELECT_STAFF_SP” to select all records from “STAFF” table Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Continue” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Ok” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Ensure the above options are selected. Click on “Data Access” and then click on “Change Option” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Ensure all the data access options are selected and then click on “Ok” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Installation begins ……. . Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Ok” when setup completes Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Exit” for Installation of MSDN Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Yes” fpr the above message Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 For our purpose do not select any client tools, simply click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 For our purpose do not select any server setups, simply click on “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setup of Visual Studio 6. 0 8 Click on “Finish” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using Visual Basic 6. 0 Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using Visual Basic 6. 0 8 Activate from “Start Menu > Programs > Microsoft Visual Studio 6. 0 > Microsoft Visual Basic 6. 0” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using Visual Basic 6. 0 8 Select “Standard EXE” and click “Open” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using Visual Basic 6. 0 8 Observe the Visual Basic IDE (Integrated Development Environment) Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting Visual Basic 6. 0 Project References 8 Select Project References from: Project > References Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting Visual Basic 6. 0 Project References 8 Ensure “Microsoft Active. X Data Objects 2. 5 Library” and “Microsoft SQLDMO Object Library” are selected (For using ADODB objects and DMO objects) Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting Visual Basic 6. 0 Project Components 8 Select Project Components from: Project > References Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting Visual Basic 6. 0 Project Components 8 Ensure “Microsoft ADO Data Control 6. 0” is selected (For using data bound controls) Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating Label and Text Controls for Fields 8 Create label and textbox controls with respect to database fields Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADO Data Control Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADODC Data Bound Control 8 Place the ADO Data Control on Form Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting ADODC Properties 8 Right click on ‘Adodc 1’ control and select ”ADODC Properties” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting ADODC Properties 8 Observe the Property Pages and click on “Build. . ” for “Use Connection String” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting ADODC Properties 8 Select “Microsoft OLE DB Provider for SQL Server” and click “Next” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting ADODC Properties 8 Provide the necessary information such as Server Name “(local)CQUFIC”, Username “sa”, Password “spcs”, database “ADMIN” “spcs”, Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting ADODC Properties 8 Observe the Connection String “Provider=SQLOLEDB. 1; Persist Security Info=False; User ID=sa; Initial Catalog=ADMIN; Data Source=(local)CQUFIC” and select [Authentication] tab ID=sa; Initial Catalog=ADMIN; Data Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting ADODC Properties 8 Provider User Name “sa”, Password “spcs” and select [Record. Source] tab “sa”, “spcs” [Record. Source] Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Setting ADODC Properties 8 Select Command Type “ad. Cmd. Table”, Table or Stored Procedure Name “STAFF” and click “ad. Cmd. Table”, “OK” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Binding the Text. Box Controls to ADODC datasource 8 Right click a textbox Control eg. ID and select Properties eg. Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Binding the Text. Box Controls to ADODC datasource 8 Select ‘Adodc 1’ as “Data. Source” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Binding the Text. Box Controls to ADODC datasource 8 Select ‘ID’ as Data. Field. Similarly set Data. Source/Data. Field for other controls respectively Data. Field. Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing ADODC in Action 8 Click on [>] start button, to run the application Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing ADODC in Action 8 Notice that the 1 st record from “STAFF” table is displayed i. e “ 1, John, Doe”. Now navigate through the records by using the forward button of ‘ADODC’ control Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing ADODC in Action 8 Notice the 2 nd record from “STAFF” table is displayed i. e “ 2, Ram, Prasad”. Similarly you’d be able to navigate forward/back through other records Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODC 8 Place a command button control on Form’s Design Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODC 8 Right click and select “Properties” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODC 8 Provide a valid (Name) “cmd. Add. New” and a descriptive caption “Add New” “cmd. Add. New” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODC 8 Right click and select “View Code” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODC 8 Observe the interface for coding Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODC 8 Choose “cmd. Add. New” from the right combo which displays the controls available on form “cmd. Add. New” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODC 8 “cmd. Add. New_Click()” event is exposed cmd. Add. New_Click()” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODC 8 Type in the code as displayed above under “cmd. Add. New_Click()” i. e “cmd. Add. New_Click()” ‘Adodc 1. Recordset. Add. New’ Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing add New Record using ADODC in action 8 Click on [Add New] command button Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing add New Record using ADODC in action 8 Notice the textbox controls have cleared Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing add New Record using ADODC in action 8 Populate the textbox controls as “ 4, Amit, Tailor”. Then use the forward/backward button on Amit, ‘adodc’ control to apply the change adodc’ Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing add New Record using ADODC in action 8 Verify that a record was added into “STAFF” table using SQL Query Analyzer Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to Delete Record using ADODC 8 Place a command button “Delete” and expose its “cmd. Delete_Click()” event “cmd. Delete_Click()” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to Delete Record using ADODC 8 Type in the code as displayed above under “cmd. Delete_Click()” i. e ‘Adodc 1. Recordset. Delete’ “cmd. Delete_Click()” and the logic which moves to display the previous available record Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing Delete Record using ADODC in action 8 Navigate to record “ 3, Luisa, Tora” and click on “Delete” command button Tora” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing Delete Record using ADODC in action 8 Notice that the form now displays record “ 2, Ram, Prasad” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing Delete Record using ADODC in action 8 Verify that a record was deleted from “STAFF” table using SQL Query Analyzer Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADODB Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADODB 8 Right click on Form Design and select “View Code” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADODB 8 In the (General)/Declaration section, define an object for ADODB. Connection & ADODB. Command, a constant for connection string and modularly defined ADODB. Recordset ADODB. Command, Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADODB Dim g. Obj. Connection As New ADODB. Connection Dim gcom. Data As New ADODB. Command Const gstr. Connection. String = "Provider=SQLOLEDB. 1; Data Source=(local)CQUFIC; User ID=sa; Password=spcs; Initial Catalog=ADMIN“ Dim grs. Record as New ADODB. Recordset 8 Displayed above is the code declared in (General)/Declaration section Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADODB Private Sub Form_Load() g. Obj. Connection. Open gstr. Connection. String With gcom. Data. Command. Type = ad. Cmd. Stored. Proc. Command. Text = "SELECT_STAFF_SP". Active. Connection = g. Obj. Connection Set grs. Record =. Execute End With End Sub 8 Displayed above is the code written for “Form_Load()” event “Form_Load()” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADODB Private Sub Form_Activate() If Not grs. Record. EOF Then grs. Record. Move. First txt. ID 1. Text = grs. Record("ID") txt. First. Name 1. Text = grs. Record("First. Name") txt. Last. Name 1. Text = grs. Record("Last. Name") End If End Sub 8 Displayed above is the code written for “Form_Activate()” event “Form_Activate()” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Using ADODB Private Sub Form_Unload(Cancel As Integer) g. Obj. Connection. Close End Sub 8 Displayed above is the code written for “Form_Unload(Cancel as Integer)” event “Form_Unload(Cancel Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing ADODB in action 8 Upon playing the project you’ll notice that the first record is displayed i. e “ 1, John, Doe” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Create Navigation buttons for ADODB 8 Create command button for “Next”, right click and expose underlying code by selecting “View Code” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Create Navigation buttons for ADODB Private Sub cmd. Next_Click() grs. Record. Move. Next If Not grs. Record. EOF Then txt. ID 1. Text = grs. Record("ID") txt. First. Name 1. Text = grs. Record("First. Name") txt. Last. Name 1. Text = grs. Record("Last. Name") Else Msg. Box "No more records available for display" End If End Sub 8 Displayed above is the code written for “cmd. Next_click()” event “cmd. Next_click()” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing navigation in ADODB in action 8 Upon playing the project notice that clicking “Next” command button moves through the records Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing navigation in ADODB in action 8 Notice the message box appears when there aren’t any more records to navigate Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODB 8 Create the command button “Add New”, right click and “View Code” to expose its underlying code Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to add New Record using ADODB Private Sub cmd. Add. New_Click() txt. ID 1. Text = vb. Null. String txt. First. Name 1. Text = vb. Null. String txt. Last. Name 1. Text = vb. Null. String End Sub 8 Displayed above is the code written for “cmd. Add. New_click()” event “cmd. Add. New_click()” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing Add New in ADODB in action 8 Upon playing the project notice that clicking “Add New” command button clears the text boxes. This thus enables the user to type new record information Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to Save New Record using ADODB 8 Create the command button “Save”, right click and “View Code” to expose its underlying code Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to Save New Record using ADODB Private Sub cmd. Save_Click() With gcom. Data. Command. Type = ad. Cmd. Stored. Proc. Command. Text = "INSERT_INTO_STAFF_SP". Parameters. Append. Create. Parameter("@ID", ad. Integer, ad. Param. Input, , txt. ID 1. Text). Parameters. Append. Create. Parameter("@FIRSTNAME", ad. Var. Char, ad. Param. Input, 50, txt. First. Name 1. Text). Parameters. Append. Create. Parameter("@LASTNAME", ad. Var. Char, ad. Param. Input, 50, txt. Last. Name 1. Text). Active. Connection = g. Obj. Connection. Execute End With Msg. Box "Record Saved" Unload Me End Sub 8 Displayed above is the code written for “cmd. Save_click()” event “cmd. Save_click()” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing Save in ADODB in action 8 Upon playing the project, click “Add New” command button, type new record information eg. “ 5, eg. Joe, Wong”, and click on “Save” command button, which displays the message box “Record Saved” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing Save in ADODB in action 8 Verify that a record was saved into “STAFF” table using SQL Query Analyzer Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to Delete Record using ADODB 8 Create the command button “Delete”, right click and “View Code” to expose its underlying code Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Creating the interface to Delete Record using ADODB Private Sub cmd. Delete_Click() With gcom. Data. Command. Type = ad. Cmd. Stored. Proc. Command. Text = "DELETE_FROM_STAFF_SP". Parameters. Append. Create. Parameter("@ID", ad. Integer, ad. Param. Input, , txt. ID 1. Text). Active. Connection = g. Obj. Connection. Execute End With Msg. Box "Record Deleted" Unload Me End Sub 8 Displayed above is the code written for “cmd. Delete_click()” event “cmd. Delete_click()” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing Delete in ADODB in action 8 Upon playing the project, navigate to record “ 4, Amit, Tailor” and click on “Delete” command Amit, button, which displays the message box “Record Deleted” Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Seeing Delete in ADODB in action 8 Verify that a record was deleted from “STAFF” table using SQL Query Analyzer Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Workshop so far ……… Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

Workshop so far ……. Ø We have: Ø Had a brief look at the characteristics of client/server environment Ø Ø Ø Stepped through the setup of SQL Server & Visual Basic Tools Seen examples of ADODC binded controls and ADODB in action For latest reference information: Ø Ø visit http: //www. microsoft. com/sql/default. asp visit http: //www. databasejournal. com Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor

What now ? ? ? ? Client/Server Programming using SQL Server 2000 and Visual Basic 6. 0 Workshop by Amit Kumar Tailor