Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB.NET
Expertise: Advanced
Mar 2, 2005



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Connect to SQL Server Using ADODB Retrieving Records

This tip will show you how to connect to SQL Server using ADODB Retrieving Records using Record Set and connecting it to Crystal Reports.

Follow these steps before adding the code:

  1. Click on Project—>Add References.
  2. Select Com Tab on Top. In Com, select Microsoft ActiveX Data Objects 2.7 Library. Click on select.
  3. Select .Net Tab . In .Net Tab select system.data.dll. Click on select and press OK.
  4. Add the following code in your project.

Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.Common
Imports ADODB
Imports System.Data.OleDb
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class Form1
    Inherits System.Windows.Forms.Form

    Public reporttype As String
    Public CN As New ADODB.Connection

    Public cr3 As New CrystalReport1

'add a button on the form and paste the following code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
' database connection string
        CN = New ADODB.Connection

        CN.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        CN.Open("User ID=sa;Data Source=server;Tag with column collation when possible=False;Initial
Catalog='databasename';Use Procedure for Prepare=1;Auto Translate=True;Persist Security
Info=False;Provider=SQLOLEDB.1;Use Encryption for Data=False;Packet Size=4096")
        Dim SQL As String = "select * from tablename"
        'creating recordset
        Dim rs As New ADODB.Recordset

        rs.Open(SQL, CN, ADODB.CursorTypeEnum.adOpenStatic,
    ADODB.LockTypeEnum.adLockBatchOptimistic, 1)
        CRViewer.ReportSource = cr3

  End Sub
End Class
Yatin Karekar
Thanks for your registration, follow us on our social networks to keep up-to-date