Deskripsi
Create, Update , Read / Retrieval , Insert on VB.NET (Different from VB 6) , before go to the next step we must know conceptual ADO.NET. here's the picture (taken from MSDN).
How To
Concept :
1. Import OleDb
2. Create Connection String , if you don't know how to create read
this article
3. Specify the type of statement :
- Create , Update , Insert is
execute type (required open connection) , you can use adapter (not use open connection) it's not recommended but it's depends on user habits (manipulation data should use open connection).
- Read / Retrieval is safe without required open connection , it's just get the data without manipulation.
i only give raw code vb.net , you should create your own class in order to look better code and manageable, ok let's start :)
SQL Type Execute (Create , Update , Insert), create 1 button on form and double click :
// import collection
Imports System.Data.OleDb
Private Sub Command1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdshutdown.Click
// Declare variable
Dim dbcmd
Dim strsql As String
Dim dbconn = New OleDbConnection
// create connection string , and open the connection
dbconn.ConnectionString = "Provider=SQLOLEDB.1;Password=xxxxx;Persist Security Info=True;User ID=sa;Initial Catalog=mytable;Data Source=192.168.0.xx"
dbconn.Open()
//create your sql statement
strsql = "insert test (id,name) values ('1','yeah')"
//add your sql statement to oledb command
dbcmd = New OleDbCommand(strsql, dbconn)
dbcmd.CommandType = CommandType.Text
//executed the oledbcommand
dbcmd.ExecuteReader()
End Sub
SQL Type Retrieval(select), create 1 button and 1 datagridview on form and double click the button:
// import collection
Imports System.Data.OleDb
Private Sub Command1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdshutdown.Click
// Declare variable
Dim dbcmd,adaptor, datacontent
Dim strsql As String
Dim dbconn = New OleDbConnection
// create connection string , and open the connection
dbconn.ConnectionString = "Provider=SQLOLEDB.1;Password=xxxxx;Persist Security Info=True;User ID=sa;Initial Catalog=mytable;Data Source=192.168.0.xx"
//dbconn.Open()
//create your sql statement
strsql = "select * from table where xxx=yyy"
//add your sql statement to oledb command
dbcmd = New OleDbCommand(strsql, dbconn)
dbcmd.CommandType = CommandType.Text
//running sql command via adapter (look the conceptual)
adaptor = New OleDbDataAdapter(dbcmd)
datacontent= New DataTable
adaptor.Fill(datacontent)
datagridview1.DataSource = datacontent
End Sub
note : to fill data on gridview , datagridview require an object datatable that filled by adaptor , look my red mark.
Single Retrieval
Dim adaptor
Dim strsql As String
Dim i As Double
Dim dbcmd As OleDbCommand
Dim datacontent As DataTable
Dim dbconn = New OleDbConnection
Dim reader As OleDbDataReader
Dim fetchReader As OleDbDataReader
dbconn.ConnectionString = "FILE NAME=" & My.Application.Info.DirectoryPath & "\SQLServer.udl"
strsql = "select USER_ID,USERNAME from MS_USER_LOGIN"
'add your sql statement to oledb command
dbcmd = New OleDbCommand(strsql, dbconn)
dbcmd.CommandType = CommandType.Text
dbconn.Open()
reader = dbcmd.ExecuteReader
datacontent = New DataTable
datacontent.Load(reader)
'item(1) is index row number 2 , the first row always at index 0.
'ItemArray(1) refers index column, total index based on your query and always start at index 0
MsgBox(datacontent.Rows.Item(1).ItemArray(1).ToString())
MsgBox(datacontent.Rows(0).Field(Of String)("USERNAME").ToString)
'this for fetching all row
For Each row As DataRow In datacontent.Rows
MsgBox(row.Field(Of String)("USERNAME").ToString)
Next
reader.Close()
