Visual Basic ADO to Page Through Records 10 at a Time

0 comments

Posted on 7th July 2011 by Admin in Visual Basic .Net

, ,

Option Explicit

Private m_CombinedNames As String
‘Display the first 10 records.

Private Sub cmdList_Click()
 ‘ Reset m_CombinedNames
 ‘ to select the first record.
 m_CombinedNames = “,”
 ‘ Get the next 10 records.
 cmdNext.Enabled = True
 cmdNext_Click

End Sub

‘ Display the next 10 records.

Private Sub cmdNext_Click()
 Dim DB_Name As String
 Dim Conn As ADODB.Connection
 Dim RS As ADODB.Recordset
 Dim txt As String
 Dim i As Integer
 ‘ Get the database name.

DB_Name = App.Path


If Right$(DB_Name, 1) <> “” Then DB_Name = DB_Name & “”
 DB_Name = DB_Name & “Employee.mdb”
 ‘ Open a connection.
 Set Conn = New ADODB.Connection
 Conn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & DB_Name & “;” & _
 “Persist Security Info=False”
 Conn.Open
 ‘ Get the next 10 records.

Set RS = Conn.Execute(“SELECT TOP 10 * FROM Employees “ & “WHERE LastName + ‘,’ + _
 FirstName > “ & “‘” & m_CombinedNames & “‘ ORDER BY LastName, “ & “FirstName”)
 ‘ Display the records.
 Do until RS.EOF
 i = i + 1
 txt = txt & vbCrLf & Format$(RS!EmployeeId, “@@@”) & ” “ & _
 Format$(RS!LastName, “!@@@@@@@@@@@@@”) & Format$(RS!FirstName, “!@@@@@@@@@@@@@”)
 m_CombinedNames = RS!LastName & “,” & RS!FirstName
 RS.MoveNext
 Loop
 ‘ See if we ran out of records.
 If i < 10 Then
 txt = txt & vbCrLf & “<END>”
 cmdNext.Enabled = False
 End If
 ‘ Display the data.
 If Len(txt) > 0 Then txt = Mid$(txt, 3)
 txtEmployees.Text = txt

End Sub

Function to Check Record Exist in Visual Basic 2008

0 comments

Posted on 13th May 2010 by Admin in Visual Basic 2008

, , , , 

'This function used to check if record exist in database with SQL statement parameter
' using the visual basic 2008 with SQL Server 2005 Express.
'Note: This code is part of my payroll system can be downloaded at the main page.

Function Check_Record_Exist(ByVal pSQLstr As String) As Boolean
 Dim conSQL As New SqlClient.SqlConnection ' for database connection
 Dim AdaptSql As SqlClient.SqlDataAdapter ' adapter is use to update the dataset and datasource
 Dim Dats As New DataSet
 Check_Record_Exist = False
 conSQL.ConnectionString = "Data Source=SERVERSQLEXPRESS;Initial Catalog=Payroll;Integrated Security=True;User"
 conSQL.Open()
 AdaptSql = New SqlClient.SqlDataAdapter(pSQLstr, conSQL)
 AdaptSql.Fill(Dats, "Inquiry")
 'If Dats.Tables.Count() > 1 Then
 If Dats.Tables.Count > 1 Then

 Check_Record_Exist = True
 MsgBox("Record already exist, please check your entry.", MsgBoxStyle.Information, "Found Record!")
 End If
 End Function

'If you think that this code solved your problem or help your development, please bookmark this page and keep visit.
'I will post of most of my useful codes in Visual Basic.

Display Recordset Data in a ListView Control

0 comments

Posted on 1st May 2010 by Admin in Database Programming |Visual Basic 6

,

A Quick And Easy way to pass a Recordset to the ListView Control. Nice interface and behind the scenes actions for formatting any item in ListView.
Note: The application uses the Northwind database that ships with VB6. If you don’t have this database at C:Program FilesMicrosoft Visual StudioVB98NWIND.MDB, you need to change the connection string in the GetEmployeesData routine in the EmployeesData class.

Download Project

ADO Recordset vs. ADO.NET

0 comments

Posted on 8th April 2010 by Admin in Visual Basic .Net

,

.NET provides a wide range of controls for both Web applications and Microsoft Windowsbased form applications. Many of these controls support data binding with ADO.NET data sources. The question is how to take advantage of your existing ADO infrastructure and still use the new controls. The answer is simple: all you need to do is convert your Recordset to either a DataSet or a Data Table. Once you have one of these managed data objects, data binding is trivial.

‘Here is the familiar ADO stuff

Dim cn As New ADODB.Connection()

Dim rs As New ADODB.Recordset()

cn.Open(“Provider=SQLOLEDB.1;Integrated Security=SSPI;” & _

“Persist Security Info=False;” & _

“Initial Catalog=Northwind;Data Source=bart”)

rs.Open(“Select * From Employees”, cn)

‘This is where things start getting interesting. Here we declare not

‘only the DataAdapter, but also the DataTable the data is destined for.

Dim olead As New Data.OleDb.OleDbDataAdapter()

Dim dt As New Data.DataTable()

‘We use the DataAdapter to fill a DataTable

olead.Fill(dt, rs)

‘Now we bind the DataTable to the DataGrid

Me.DataGrid1.DataSource = dt

‘This is cleanup. Always close your objects as

‘soon as you are done with them.

rs.Close()

cn.Close()

In this simple example, an ADO Recordset is bound to a DataGrid control on a Windows form.

The OleDbDataAdapter.Fill method is capable of populating a DataTable object with data from an ADODB Recordset.

In this way it is possible to take any Recordset and convert it to a managed data structure. Once you have done the conversion, you can bind the DataTable directly to a control,

or you can add it to a DataSet and work with the table just as you would any other DataTable (including creating a DataView for the data, which we will get to a bit later on).