Here, we see how to read the data from the database and store data in ListBox control. To do that we create a table in SQL Server database which has the name emp and use insert command to insert the data in database after that we use select statement to select the data from database and store in the ListBox control.
Creating connection object
To create a connection we pass the connection string as a parameter in connection object.
Dim str As String = "Data Source=.;uid=sa;pwd=123;database=master"
Dim con As New SqlConnection(str)
Reading data from database
To read the data from the database using select statement.
Select empid, empname from Emp
Now we create a database table and insert some values in this table. Table looks like this.
create table emp
(
empid varchar(40),
empname varchar(30)
)
go
insert into employee values(1,'monu')
insert into employee values(2,'Hari')
go
select * from emp
OUTPUT
Store the data in ListBox
To store the data in ListBox using DataAdapter object is to retrieve the data from the database and place that data into a DataSet.
Dim adpt As New SqlDataAdapter(com, con)
Dim myDataSet As New DataSet()
adpt.Fill(myDataSet, "Emp")
Dim myDataTable As DataTable = myDataSet.Tables(0)
Dim tempRow As DataRow
For Each tempRow In myDataTable.Rows
ListBox1.Items.Add((tempRow("empid") & " (" & tempRow("empname") & ")"))
For example
Taking a ListBox control on the form. The form looks like this.
VB.NET code
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim str As String = "Data Source=.;uid=sa;pwd=123;database=master"
Dim con As New SqlConnection(str)
Dim com As String = "Select empid, empname from Emp"
Dim adpt As New SqlDataAdapter(com, con)
Dim myDataSet As New DataSet()
adpt.Fill(myDataSet, "Emp")
Dim myDataTable As DataTable = myDataSet.Tables(0)
Dim tempRow As DataRow
For Each tempRow In myDataTable.Rows
ListBox1.Items.Add((tempRow("empid") & " (" & tempRow("empname") & ")"))
Next
End Sub
End Class
Now run the application and test it.
OUTPUT