Category Archives: VB.Net

How to get access to all column names of table in VB.Net/ADO.net


Using ‘getSchema’ method we can access database properties such as Column names, Tables name etc. ,with some tricky code in Visual Basic.Net . Here is how you can access all columns names in a Access table with OLEDB connection in VB ADO.net 2013.

Sub AddListViewColumsAuto( tbl As String)
Dim Company As OleDb.OleDbConnection
 Company = New OleDb.OleDbConnection
 Company.ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\.Net  Project\Accounting Pack\AccountingPack.accdb”
 Company.Open()
 Dim cmd As New OleDb.OleDbCommand(“Select * from ” & tbl, Company)
 Dim columns As String = “Columns”
 Dim dt As DataTable = Company.GetSchema(columns)
For Each row As DataRow In dt.Rows
 console.writeline(row.Item(“COLUMN_NAME”).ToString)
Next
Company.Close()
End Sub

I

First we create connection object, Company and a connection string. Then we need a OleDb.OleDbCommand object to set database to point a particular table, so that we created the cmd object and used it for query the input table.

II

In the next stage we created a column string in order to point Columns in the table. Using Getschema() method of connection object Company we stored each and every column name in the connection to the table variable dt.

III

In the final step we can access all rows from the dt which contain column names from table dt using COLUMN_NAME  field.

Here is the video tutorial of how you can access and add column names to the console.

How to connect Access Database in MS VB.net 2013


Here is how you can connect and build a Access data base application in Visual Basic.Net. First we need a connection string and Data Adapter or a command object for executing SQL statements. We can execute SQL commands using either the Adapter or the OLEDB command object.

In ADO.net there are plenty of class which make connecting database smooth as silk. Lets do it

Create connection string  

    Public Company As OleDb.OleDbConnection

Now we can configure the connection string.
        Company = New OleDb.OleDbConnection
        Company.ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\.Net Project\Accounting Pack\AccountingPack.accdb”

We are ready to interact with any table in AccountingPack database

Interacting with table

As far as we got everything is right and need to add, retrieve,update and delete rows to the table in the data base. The CURD operation can be made possible with a command object.

 Company.open()
  Dim cmd As OleDbCommand = New OleDbCommand(“select * from product_registration”, Company)

The first argument is the SQL query statement which fetch all columns from the table and the second is the connection string. Before executing this we also need to open the concoction.
To print the rows we need another object called  OleDbDataReader which help us to fetch row from command object and it works with following code

 Dim reader As OleDbDataReader = _
        cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Do While reader.Read
            Console.WriteLine(reader.GetString(0))
        Loop
        reader.Close()
        Company.Close()

Insert statement

All other SQL statements can be made possible with the OleDbCommand object. 

Company.Open()
        Dim s As String = TextBox1.Text
        Dim cmd1 As OleDbCommand = New OleDbCommand(“insert into  product_registration (pruduct_name) values(‘” & s & “‘)”, Company)
        cmd1.ExecuteScalar()
        Company.Close()
Saving record is possible with ExecuteScalar method and it will execute the insert statement.

OleDbData Adapter – DataSet, DataView.

OleDbDataAdapter class is the interface between database engine and visual basic, it is the bridge between backend and frontend. This class brings the concept like dataSet and DataView.  We will discuss all these concepts 

Dataset

DataSet represents the offline/ local view of the database tables, that means no data connection necessary for accessing the fetched Dataset collection. 
DataSet object can fill with Fill Method of Adapter as follows
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(“select * from product_Registration“, Company)       
 Dim dataset As DataSet = New DataSet()       
adapter.Fill(dataset, “table1”)       
DataGridView1.DataSource = dataset
DataGridView1.DataMember = “product_Registration


The dataset can be used to fill DataGrid view too.

DataView

DataView object as the name indicate provides the table view. It can be useful when interact with the table rows.

 Dim dv As DataView
        dv = New DataView(dataset.Tables(“product_Registration”))
        adapter.Fill(dataset, “table1”)
        Dim c As Integer
        c = 0
        Do While c < dv.Count
            Console.WriteLine(dv(c).Item(0).ToString)
            c = c + 1
        Loop
   

I think you got the topic