hello..
apa kabar teman2..
gw lagi pengen ngeshare aja nih..
buat teman2 yang mw belajar visual basic .Net
di cekibroot aja guys..
haha
hayo diunduh..
hehe.
thx ya udh mampir2..
:))
Thursday, August 26, 2010
Visual Basic .Net coding
'SelectCommand
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim i As Integer
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
Try
connection.Open()
adapter.SelectCommand = New SqlCommand("Your SQL Statement Here", connection)
adapter.Fill(ds)
connection.Close()
For i = 0 To ds.Tables(0).Rows.Count - 1
MsgBox(ds.Tables(0).Rows(i).Item(1))
Next
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'insertCommand
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim sql As String
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
sql = "insert into product (Product_id,Product_name,Product_price) values(7,'Product7',700)"
Try
connection.Open()
adapter.InsertCommand = New SqlCommand(sql, connection)
adapter.InsertCommand.ExecuteNonQuery()
MsgBox("Row inserted !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'UpdateCommand
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim sql As String
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
sql = "update product set product_price = 1001 where Product_name ='Product7'"
Try
connection.Open()
adapter.UpdateCommand = connection.CreateCommand
adapter.UpdateCommand.CommandText = sql
adapter.UpdateCommand.ExecuteNonQuery()
MsgBox("Row updated !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'DeleteCommand
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim sql As String
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
sql = "delete product where Product_name ='Product7'"
Try
connection.Open()
adapter.DeleteCommand = connection.CreateCommand
adapter.DeleteCommand.CommandText = sql
adapter.DeleteCommand.ExecuteNonQuery()
MsgBox("Row(s) deleted !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'DataGridView
Imports System.Data.SqlClient
Public Class Form1
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As SqlDataAdapter
Dim cmdBuilder As SqlCommandBuilder
Dim ds As New DataSet
Dim changes As DataSet
Dim sql As String
Dim i As Int32
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
Sql = "select * from Product"
Try
connection.Open()
adapter = New SqlDataAdapter(Sql, connection)
adapter.Fill(ds)
connection.Close()
DataGridView1.Data Source= ds.Tables(0)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
cmdBuilder = New SqlCommandBuilder(adapter)
changes = ds.GetChanges()
If changes IsNot Nothing Then
adapter.Update(changes)
End If
MsgBox("Changes Done")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
' Dataadapter with dataset
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As SqlDataAdapter
Dim ds As New DataSet
Dim i As Integer
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
Try
connection.Open()
adapter = New SqlDataAdapter("Your SQL Statement Here", connection)
adapter.Fill(ds)
connection.Close()
For i = 0 To ds.Tables(0).Rows.Count - 1
MsgBox(ds.Tables(0).Rows(i).Item(1))
Next
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'STORE PROSEDURE
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
Private Sub ReturnIdentity(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection( _
connectionString)
' Create a SqlDataAdapter based on a SELECT query.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", _
connection)
' Create a SqlCommand to execute the stored procedure.
adapter.InsertCommand = New SqlCommand("dbo.InsertCategory", _
connection)
adapter.InsertCommand.CommandType = CommandType.StoredProcedure
' Create a parameter for the ReturnValue.
Dim parameter As SqlParameter = _
adapter.InsertCommand.Parameters.Add( _
"@RowCount", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
' Create an input parameter for the CategoryName.
' You do not need to specify direction for input parameters.
adapter.InsertCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NChar, 15, "CategoryName")
' Create an output parameter for the new identity value.
parameter = adapter.InsertCommand.Parameters.Add( _
"@Identity", SqlDbType.Int, 0, "CategoryID")
parameter.Direction = ParameterDirection.Output
' Create a DataTable and fill it.
Dim categories As DataTable = New DataTable
adapter.Fill(categories)
' Add a new row.
Dim newRow As DataRow = categories.NewRow()
newRow("CategoryName") = "New Category"
categories.Rows.Add(newRow)
' Update the database.
adapter.Update(categories)
' Retrieve the ReturnValue.
Dim rowCount As Int32 = _
CInt(adapter.InsertCommand.Parameters("@RowCount").Value)
Console.WriteLine("ReturnValue: {0}", rowCount.ToString())
Console.WriteLine("All Rows:")
Dim row As DataRow
For Each row In categories.Rows
Console.WriteLine(" {0}: {1}", row(0), row(1))
Next
End Using
End Sub
atau
SqlConnection conn = new SqlConnection("Data Source=localhost;Database=Northwind;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("GetProducts", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1; //nilai parameeter
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products");
this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = "Products";
'SAVE BERHASIL STORE PROSEDURE
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'atur koneksi
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
connetionString = "Data Source=YOUR-UP77HHWSZF\SQLEXPRESS;Initial Catalog=dbToko;Integrated Security=True"
connection = New SqlConnection(connetionString)
connection.Open()
'siapkan genjatan senjata berupa storeprosedure
Dim command As New SqlCommand("InsertJenis", connection)
command.CommandType = CommandType.StoredProcedure
'atur parameter storeprosedure dan aplikasi
Dim par1 As SqlParameter
par1 = New SqlParameter("@id_jenis", SqlDbType.VarChar, 50)
par1.Value = txtIDJenis.Text
par1 = command.Parameters.Add(par1)
Dim par2 As SqlParameter
par2 = New SqlParameter("@nama_jenis", SqlDbType.VarChar, 50)
par2.Value = txtNamaJenis.Text
par2 = command.Parameters.Add(par2)
'SIAP >> TEMBAK !
command.ExecuteNonQuery()
'ini buat tampilkan hasil di storeprosedure
adapter = New SqlDataAdapter(command)
Me.Tb_jenis_brgTableAdapter.Fill(Me.DbTokoDataSet.tb_jenis_brg)
Tb_jenis_brgDataGridView.DataSource = DbTokoDataSet
Tb_jenis_brgDataGridView.DataMember = "tb_jenis_brg"
MsgBox("Row inserted !! ")
'koneksi ditutup, dan berhasil
connection.Close()
End Sub
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim i As Integer
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
Try
connection.Open()
adapter.SelectCommand = New SqlCommand("Your SQL Statement Here", connection)
adapter.Fill(ds)
connection.Close()
For i = 0 To ds.Tables(0).Rows.Count - 1
MsgBox(ds.Tables(0).Rows(i).Item(1))
Next
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'insertCommand
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim sql As String
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
sql = "insert into product (Product_id,Product_name,Product_price) values(7,'Product7',700)"
Try
connection.Open()
adapter.InsertCommand = New SqlCommand(sql, connection)
adapter.InsertCommand.ExecuteNonQuery()
MsgBox("Row inserted !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'UpdateCommand
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim sql As String
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
sql = "update product set product_price = 1001 where Product_name ='Product7'"
Try
connection.Open()
adapter.UpdateCommand = connection.CreateCommand
adapter.UpdateCommand.CommandText = sql
adapter.UpdateCommand.ExecuteNonQuery()
MsgBox("Row updated !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'DeleteCommand
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim sql As String
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
sql = "delete product where Product_name ='Product7'"
Try
connection.Open()
adapter.DeleteCommand = connection.CreateCommand
adapter.DeleteCommand.CommandText = sql
adapter.DeleteCommand.ExecuteNonQuery()
MsgBox("Row(s) deleted !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'DataGridView
Imports System.Data.SqlClient
Public Class Form1
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As SqlDataAdapter
Dim cmdBuilder As SqlCommandBuilder
Dim ds As New DataSet
Dim changes As DataSet
Dim sql As String
Dim i As Int32
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
Sql = "select * from Product"
Try
connection.Open()
adapter = New SqlDataAdapter(Sql, connection)
adapter.Fill(ds)
connection.Close()
DataGridView1.Data Source= ds.Tables(0)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
cmdBuilder = New SqlCommandBuilder(adapter)
changes = ds.GetChanges()
If changes IsNot Nothing Then
adapter.Update(changes)
End If
MsgBox("Changes Done")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
' Dataadapter with dataset
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As SqlDataAdapter
Dim ds As New DataSet
Dim i As Integer
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
Try
connection.Open()
adapter = New SqlDataAdapter("Your SQL Statement Here", connection)
adapter.Fill(ds)
connection.Close()
For i = 0 To ds.Tables(0).Rows.Count - 1
MsgBox(ds.Tables(0).Rows(i).Item(1))
Next
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
'STORE PROSEDURE
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
Private Sub ReturnIdentity(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection( _
connectionString)
' Create a SqlDataAdapter based on a SELECT query.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", _
connection)
' Create a SqlCommand to execute the stored procedure.
adapter.InsertCommand = New SqlCommand("dbo.InsertCategory", _
connection)
adapter.InsertCommand.CommandType = CommandType.StoredProcedure
' Create a parameter for the ReturnValue.
Dim parameter As SqlParameter = _
adapter.InsertCommand.Parameters.Add( _
"@RowCount", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
' Create an input parameter for the CategoryName.
' You do not need to specify direction for input parameters.
adapter.InsertCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NChar, 15, "CategoryName")
' Create an output parameter for the new identity value.
parameter = adapter.InsertCommand.Parameters.Add( _
"@Identity", SqlDbType.Int, 0, "CategoryID")
parameter.Direction = ParameterDirection.Output
' Create a DataTable and fill it.
Dim categories As DataTable = New DataTable
adapter.Fill(categories)
' Add a new row.
Dim newRow As DataRow = categories.NewRow()
newRow("CategoryName") = "New Category"
categories.Rows.Add(newRow)
' Update the database.
adapter.Update(categories)
' Retrieve the ReturnValue.
Dim rowCount As Int32 = _
CInt(adapter.InsertCommand.Parameters("@RowCount").Value)
Console.WriteLine("ReturnValue: {0}", rowCount.ToString())
Console.WriteLine("All Rows:")
Dim row As DataRow
For Each row In categories.Rows
Console.WriteLine(" {0}: {1}", row(0), row(1))
Next
End Using
End Sub
atau
SqlConnection conn = new SqlConnection("Data Source=localhost;Database=Northwind;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("GetProducts", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1; //nilai parameeter
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products");
this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = "Products";
'SAVE BERHASIL STORE PROSEDURE
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'atur koneksi
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
connetionString = "Data Source=YOUR-UP77HHWSZF\SQLEXPRESS;Initial Catalog=dbToko;Integrated Security=True"
connection = New SqlConnection(connetionString)
connection.Open()
'siapkan genjatan senjata berupa storeprosedure
Dim command As New SqlCommand("InsertJenis", connection)
command.CommandType = CommandType.StoredProcedure
'atur parameter storeprosedure dan aplikasi
Dim par1 As SqlParameter
par1 = New SqlParameter("@id_jenis", SqlDbType.VarChar, 50)
par1.Value = txtIDJenis.Text
par1 = command.Parameters.Add(par1)
Dim par2 As SqlParameter
par2 = New SqlParameter("@nama_jenis", SqlDbType.VarChar, 50)
par2.Value = txtNamaJenis.Text
par2 = command.Parameters.Add(par2)
'SIAP >> TEMBAK !
command.ExecuteNonQuery()
'ini buat tampilkan hasil di storeprosedure
adapter = New SqlDataAdapter(command)
Me.Tb_jenis_brgTableAdapter.Fill(Me.DbTokoDataSet.tb_jenis_brg)
Tb_jenis_brgDataGridView.DataSource = DbTokoDataSet
Tb_jenis_brgDataGridView.DataMember = "tb_jenis_brg"
MsgBox("Row inserted !! ")
'koneksi ditutup, dan berhasil
connection.Close()
End Sub
Subscribe to:
Posts (Atom)
