Visual Basic application programmers used ADO, DAO, or RDO to perform database- related operations. Visual Basic .NET now introduces a new version of ADO called ADO.NET. Data binding to a DAO or RDO data source isn't supported in Visual Basic .NET. The data control and the remote data control have no equivalent. Applications that rely on DAO or RDO data binding should either be updated to use ADO before upgrading to Visual Basic .NET or left in Visual Basic 6.0 because ADO data binding is supported in Windows Forms. ADO.NET is very different from RDO, DAO, and even ADO technologies used in developing Microsoft applications using Visual Basic 6.0. ADO.NET has been designed to provide full support to disconnected data access. The ADO.NET classes reside in the namespace System.Data . Example 9 In the following example, the Visual Basic code connects to an SQL server database called pubs . It retrieves the count of the rows in the authors table. It then inserts a single row in the authors table. Note that data binding to controls is not used in this example. To execute this application, a System DSN called ADOTest was created to connect to the pubs database of the SQL server on a test machine. In the Visual Basic 6.0 application the component Microsoft ActiveX Data Objects 2.7 Library has been added to the References submenu of the project menu to access ADODB. The following code is in the SimpleDatabaseoperations-VB folder for this chapter: Private Sub Command1_Click() Dim strConn As String Dim strSQL As String Dim strPMID As String Dim DbRecset As Recordset Set DbConn = New ADODB.Connection strConn = "DSN=ADOTest" & ";UID=sa;PWD=" MsgBox "Connecting to Database...... .. ." DbConn.Open strConn MsgBox "Connected to Database." MsgBox " " MsgBox "Querying Table [Authors]." Set DbRecset = DbConn.Execute _ ("Select count(*) from Authors") MsgBox "Number of Records Found : " & _ DbRecset.GetString DbRecset.Close MsgBox "Closed Recordset." MsgBox ("Preparing Insert Statement.") strSQL = "insert into authors_ (au_id,au_lname,au_fname,phone,address, city,_ state, zip, contract) values_ ('172-32-6666','Halari','Prashant','408 496- 7224','Dummy address','Mumbai','MA','45240','1')" MsgBox ("Using Following Insert Statement : ") MsgBox (strSQL) DbConn.Execute (strSQL) MsgBox ("Insert Successful.") MsgBox ("Closing the Database Connection.") DbConn.Close MsgBox ("Closed the Database Connection.") ErrHandler: Set DbConn = Nothing Set DbRecset = Nothing If Err.Number <> 0 Then MsgBox Err.Number & " : " & Err.Description &_ vbCrLf & "Source : " & Err.Source End If End Sub The code is upgraded using the upgrade wizard. Because ADO data binding was not used, the code upgrade is smooth. The developer can compile and execute the code as it is without making any changes in the Visual Basic .NET code. This is possible because the Visual Basic .NET code interacts with the ADO control through the interoperability layer provided in the Microsoft .NET Framework. The following code snippet shows the upgraded Visual Basic .NET code. This code is in the SimpleDatabaseoperations-VB.NET-Modified folder for this chapter. Private Sub Command1_Click(ByVal eventSender As _ System.Object, ByVal eventArgs As System.EventArgs)_ Handles Command1.Click Dim DbConn As Object Dim strConn As String Dim strSQL As String Dim strPMID As String Dim DbRecset As ADODB.Recordset DbConn = New ADODB.Connection strConn = "DSN=ADOTest" & ";UID=sa;PWD=" MsgBox("Connecting to Database...... .. .") 'UPGRADE_WARNING: Couldn't resolve default property of object DbConn.Open. Click for more: 'ms-help://MS.VSCC/commoner/ redir/redirect.htm?keyword="vbup1037"' DbConn.Open(strConn) MsgBox("Connected to Database.") MsgBox(" ") MsgBox("Querying Table [Authors].") 'UPGRADE_WARNING: Couldn't resolve default property of object DbConn.Execute. Click for more: 'ms-help://MS.VSCC/commoner/ redir/redirect.htm?keyword="vbup1037"' DbRecset = DbConn.Execute _ ("Select count(*) from Authors") MsgBox("Number of Records Found : " & _ DbRecset.GetString) DbRecset.Close() MsgBox("Closed Recordset.") MsgBox("Preparing Insert Statement.") strSQL = "insert into authors(au_id,au_lname,au_fname,phone,address, city, state, zip, contract) values('172-32-6666','Halari','Prashant','408 496-7224','Dummy address','Mumbai','MA','45240','1')" MsgBox("Using Following Insert Statement : ") MsgBox(strSQL) 'UPGRADE_WARNING: Couldn't resolve default property of object DbConn.Execute. Click for more: 'ms-help://MS.VSCC/commoner/ redir/redirect.htm?keyword="vbup1037"' DbConn.Execute(strSQL) MsgBox("Insert Successful.") MsgBox("Closing the Database Connection.") 'UPGRADE_WARNING: Couldn't resolve default property of object DbConn.Close. Click for more: 'ms-help://MS.VSCC/commoner/ redir/redirect.htm?keyword="vbup1037"' DbConn.Close() MsgBox("Closed the Database Connection.") ErrHandler: 'UPGRADE_NOTE: Object DbConn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC/com- moner/redir/redirect.htm?keyword="vbup1029"' DbConn = Nothing 'UPGRADE_NOTE: Object DbRecset may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC/ commoner/redir/redirect.htm?keyword="vbup1029"' DbRecset = Nothing If Err.Number <> 0 Then MsgBox(Err.Number & " : " & Err.Description &_ vbCrLf & "Source : " & Err.Source) End If End Sub Figure 4-1 shows that the Visual Basic .NET code references the ADO data control through the Microsoft.VisualBasic.Compatibility and the Microsoft.VisualBasic.Compatibility.Data namespaces. It is seen that in the preceding code no changes are required in Visual Basic .NET to get the same functionality as in the Visual Basic 6.0 application. Figure 4-1. References in solution explorer for the upgraded Visual Basic .NET application. Example 10 In the Visual Basic 6.0 project that is created, an ADODC control is used. The control Microsoft ADO Data Control 6.0 (OLEDB) has been added from the components submenu of the project menu. Right-click on the ADODC and select the properties option. Click on Use Connection String, as shown in Figure 4-2. Figure 4-2. Specifying the connection properties for an ADODC Click on the Build button. Select the driver for SQL Server. Specify the SQL Server and the database to be selected from the server. In the example, the pubs database has been selected. The example will loop through all the first names of the authors found in the pubs database. Now click on the RecordSource property of the ADO control. Figure 4-3 shows the screen that pops up. Select adCmdTable from the command type. Select the authors table from the table dropdown box. Figure 4-3. Specifying the command type and the table for the ADODC. Now with the text box button selected in the form, select the datasource property to the ADODC that has been previously added to the form. Select the datafield property to au_fname field from the authors table. The Visual Basic 6.0 project is kept in the SimpleADOControl-VB folder for this chapter. Figure 4-4 shows the execution of the Visual Basic 6.0 application. Clicking on the right arrow button positions the record on to the next row. Similarly, clicking on the left arrow button positions the record on to the previous row. Note that no coding is required for the developer to construct this sample project except for setting the properties of the ADODC and the TextBox control. Figure 4-4. Visual Basic 6.0 application with ADODC. The preceding Visual Basic 6.0 project is upgraded using the upgrade wizard. There are a few design errors, as shown in Figure 4-5. However, the code can be compiled and executed in Visual Basic .NET. The code behaves the same in Visual Basic .NET as it does in Visual Basic 6.0. In this example, the Visual Basic .NET code interacts with the ADODC through the Microsoft.VisualBasic. Compatibility and Microsoft.VisualBasic.Compatibility.Data namespaces. Hence, for this simple example, ADODC can be easily upgraded to Visual Basic .NET. The upgraded project is kept in the SimpleADOControl-VB.NET folder for this chapter. As shown, if Visual Basic 6.0 applications use ADO data binding, the upgrade to Visual Basic .NET is smoother. Figure 4-5. Upgrade Report. |