How to use ADO RecordSet in VB

 
Recordset in VB is used to keep the data retrived from database through the connection.
 
Recordset is defined as follows.
Public rs As ADODB.Recordset
 
Recordset is set as follows.
Set rs = New ADODB.Recordset
 
Recordset is executed (opened) as follows.
rs.open "insert into table1 values('field1','field2')", cnn             '
rs.open "select field1, field2 from table1", cnn, adOpenStatic
 
where cnn is the connection name
 
Recordset is closed as follows.
rs.close
 
The CursorLocation of the recordset is usually set to adUseClient,
to retrive data faster without requiring to contact server every time.
rs.CursorLocation = adUseClient
 
To check the state of Recordset
rs.state
 
if rs.state = 1 then
msgbox "The recordset is open"
elseif rs.state = 0 then
msgbox "The recordset is closed"
end if
 
To find whether end of recordset has reached or not use EOF.
while rs.eof = false
  msgbox rs(0)
  rs.movenext
wend
 
To find if cursor at recordset has reached at begining by back traversing, use BOF
while rs.bof = false
  msgbox rs(0)
  rs.MovePrevious
wend
 
To retrive items of recordset, use it as follows
1.  rs(ordinal_number) 
2.  rs!fieldName
3.  rs("fieldName")
 
Movements of cursor in recordset
rs.movenext to move to next record
rs.moveprevious to move to previous record
rs.movefirst to move to first record
rs.movelast to move to last record
 
 
To add new record in recordset, use it as follows
    rs.AddNew
    rs(0) = field1_value
    rs(2) = field2_value
    rs.Update
   
To get the no of records in recordset, use it as follows
rs.RecordCount
 

Connect to SQL Server database using VB

 
To connect to SQL Server Database, the Steps involved are
 
1. Define A Connection
 
Public cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Data Source=(name of database server);User id=sa;password=sa;Initial Catalog=<databasename>"
cnn.Open
 
2. Define a RecordSet
 
Public rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
3. Now, use the recordset to connect to database using connection object
 
rs.CursorLocation = adUseClient
rs.Open "Select field1, field2 from table1", cnn
 
4. You can use the data returned by query in recordset like this
 
while rs.Eof = false and rs.bof = false
         me.txtField1 = rs("field1")
         rs.movenext
wend
 
Note: Do not forget to use movenext function to move the recordset to next record
otherwise, it will result endless loop.
         The recordset can also be moved backward, moved to first and last at once.
 
5. Finally, do not forget to close the recordset and connection.
But, they are automatically dropped after the application is closed.
 
rs.close
cnn.close
 
set cnn = Nothing
 

DataGrid to Excel

Public Class cmpDataGridToExcel   Inherits System.ComponentModel.Component Public Shared Sub DataGridToExcel(ByVal dgExport As DataGrid, ByVal response As HttpResponse)   'clean up the response.object   response.Clear()   response.Charset = ""   'set the response mime type for excel   response.ContentType = "application/vnd.ms-excel"   'create a string writer   Dim stringWrite As New System.IO.StringWriter()   'create an htmltextwriter which uses the stringwriter   Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)   'instantiate a datagrid   Dim dg As New DataGrid()   ' just set the input datagrid = to the new dg grid   dg = dgExport   ' I want to make sure there are no annoying gridlines   dg.GridLines = GridLines.None   ' Make the header text bold   dg.HeaderStyle.Font.Bold = True   ' If needed, here's how to change colors/formatting at the component level   'dg.HeaderStyle.ForeColor = System.Drawing.Color.Black   'dg.ItemStyle.ForeColor = System.Drawing.Color.Black   'bind the modified datagrid   dg.DataBind()   'tell the datagrid to render itself to our htmltextwriter   dg.RenderControl(htmlWrite)   'output the html   response.Write(stringWrite.ToString)   response.End() End Sub End Class

Reading Text File and Export To Excel (VB)

Attribute VB_Name = "Module1"
Sub ExtractName()
   'Establish database connection
   Dim Conn As New ADODB.Connection
   Dim Rs As New ADODB.Recordset
   Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=eC_recipient.mdb"
   Conn.Open
   With Rs
       .CursorType = adOpenStatic
       .CursorLocation = adUseServer
       .LockType = adLockReadOnly
       .ActiveConnection = Conn
       .Open "SELECT * FROM eC_recipient", , , , adCmdText
   End With
   'Initialize workbook variable
   Dim SourceCol As Range
   Dim ScolCount, colCounter As Long
   On Error Resume Next
   'Preparing a new worksheet for data dumping
   Application.DisplayAlerts = False
   ActiveWorkbook.Worksheets("Results").Delete
   Application.DisplayAlerts = True
   DeleteWorksheet = Not CBool(Err.Number)
   'Count total numbers of worksheet
   Dim i, count As Integer
   Dim lastname As String
   For i = 1 To ActiveWorkbook.Worksheets.count
       count = count + 1
   Next
   lastname = ActiveWorkbook.Worksheets.Item(count).Name
'    MsgBox CStr(count)
   Dim wksNewSheet As Excel.Worksheet
   Set wksNewSheet = Worksheets.Add
   'Name and allocate the new worksheet
   With wksNewSheet
       .Name = "Results"
       .Move After:=Worksheets(lastname)
   End With
   'Make a count of how many cells have to process
   Worksheets(1).Activate
   Set SourceCol = Columns("A")
   For colCounter = 1 To SourceCol.Rows.count
       ScolCount = ScolCount + 1
   Next
'    MsgBox CStr(ScolCount)
   'Start processing
   Dim tempC, tempStr As String
   For i = 1 To ScolCount
       Set curcell = Worksheets("Results").Cells(i, 1)
       Set curcell2 = Worksheets("Results").Cells(i, 2)
       If SourceCol.Cells(i).Value <> "" Then
           tempC = UCase(Replace(SourceCol.Cells(i).Value, Mid(SourceCol.Cells(i).Value, 1, 33), ""))
           Rs.MoveFirst
           Do While Not Rs.EOF
               tempStr = UCase(Replace(Rs.Fields(0).Value, Mid(Rs.Fields(0).Value, 1, 6), ""))
               If tempC = tempStr Then
                   curcell.Value = tempC
                   curcell2.Value = Rs.Fields(1).Value
                   GoTo Exit_Loop
               End If
               Rs.MoveNext
           Loop
           curcell.Value = tempC
           curcell2.Value = "Unknown"
       End If
Exit_Loop:
   Next
   If Err.Number <> 0 Then
       MsgBox Err.Number + " " + Err.Description + " " + Err.Source
   End If
   MsgBox CStr(ScolCount) + " records completed!", vbInformation + vbOKOnly, "Completed!"
   Worksheets("Results").Activate
   Columns("A:A").EntireColumn.AutoFit
   Columns("B:B").EntireColumn.AutoFit
End Sub