Connect to SQL Server Using ADODB Retrieving Records

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
Button3.Click
' 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)
        MsgBox(rs.RecordCount)
        cr3.SetDataSource(rs)
        CRViewer.ReportSource = cr3

  End Sub
End Class