download gratis video tutorial vb 6 dan vb.net plus contoh aplikasi

cara menampilkan struktur tabel dan export data ke excel

 

 

 

Imports System.Data.SqlClient
Imports System.IO


Public Class Form1

 

Dim Conn As SqlConnection
Dim DA As SqlDataAdapter
Dim DS As DataSet
Dim CMD As SqlCommand
Dim DR As SqlDataReader

 

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.Items.Add("USER-PC\SQLEXPRESS") 'silakan ganti dengan nama server anda..
End Sub

 

Sub KoneksiMaster()
CONN = New SqlConnection("Data Source=" & ComboBox1.Text & ";Database=Master;integrated security=SSPI;")
CONN.Open()
CONN.Close()
End Sub


Sub Koneksiwindows()
CONN = New SqlConnection("Data Source=" & Trim(ComboBox1.Text) & ";initial catalog=" & listbox1.Text & ";integrated security=true;")
CONN.Open()
End Sub

 

Sub KoneksiServer()
Try
Conn = New SqlConnection("Data Source=" & ComboBox1.Text & ";initial catalog=" & ListBox1.Text & ";User Id=" & TextBox1.Text & ";password=" & TextBox2.Text & "")
Conn.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

 

Public Sub Koneksi()
Conn = New SqlConnection("data source='" & ComboBox1.Text & "';initial catalog='" & ListBox1.Text & ";integrated security=true")
Conn.Open()
End Sub

 

Sub CariDatabase()
Conn = New SqlConnection("Data Source=" & Trim(ComboBox1.Text) & ";Database=Master;integrated security=SSPI;")
CONN.Open()
ListBox1.Items.Clear()
CMD = New SqlCommand("select * from sysdatabases", CONN)
DR = CMD.ExecuteReader
While DR.Read
ListBox1.Items.Add(DR(0))
End While
DR.Close()
End Sub


Sub CaraLogin()
If ComboBox2.Text = "Windows Authentication" Then
TextBox1.Enabled = False : TextBox1.Clear()
TextBox2.Enabled = False : TextBox2.Clear()
Else
TextBox1.Enabled = True
TextBox2.Enabled = True
End If
End Sub

 

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
ComboBox2.Items.Clear()
ComboBox2.Items.Add("Windows Authentication")
ComboBox2.Items.Add("SQL Server Authentication")
TextBox1.Clear()
TextBox2.Clear()
ListBox1.Text = ""
ListBox1.Items.Clear()
Call CaraLogin()
End Sub

 

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Call Koneksiwindows()
CMD = New SqlCommand("select TABLE_NAME from INFORMATION_SCHEMA.TABLES", Conn)
DR = CMD.ExecuteReader
ListBox2.Items.Clear()
Do While DR.Read
ListBox2.Items.Add(DR.Item(0))
Loop
End Sub

 

Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
Call CaraLogin()
End Sub

 

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If ComboBox2.Text = "Windows Authentication" Then
Call Koneksiwindows()
Else
Call KoneksiServer()
End If
Call CariDatabase()
End Sub

 

Private Sub ListBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox2.SelectedIndexChanged
If ComboBox2.Text = "Windows Authentication" Then
Call Koneksiwindows()
DA = New SqlDataAdapter("select * from " & ListBox2.Text & "", Conn)
DS = New DataSet
DA.Fill(DS)
DGV.DataSource = DS.Tables(0)
DGV.ReadOnly = True
Label1.Text = Application.StartupPath + "\" + ListBox2.Text + ".xls"
Else
Call KoneksiServer()
DA = New SqlDataAdapter("select * from " & ListBox2.Text & "", Conn)
DS = New DataSet
DA.Fill(DS)
DGV.DataSource = DS.Tables(0)
DGV.ReadOnly = True
Label1.Text = Application.StartupPath + "\" + ListBox2.Text + ".xls"
End If
End Sub

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If ListBox2.Text = "" Then
MsgBox("pilih nama tabel di listbox sebelah kanan")
Exit Sub
End If

Dim dt As New DataTable()
For Each col As DataGridViewColumn In DGV.Columns
dt.Columns.Add(col.HeaderText, col.ValueType)
Next

Dim count As Integer = 0
For Each row As DataGridViewRow In DGV.Rows
If count < DGV.Rows.Count - 1 Then
dt.Rows.Add()
For Each cell As DataGridViewCell In row.Cells
dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
Next
End If
count += 1
Next

Dim wr As New StreamWriter(Application.StartupPath + "\" + ListBox2.Text + ".xls")
For i As Integer = 0 To dt.Columns.Count - 1
wr.Write(dt.Columns(i).ToString().ToUpper() & vbTab)
Next
wr.WriteLine()
For i As Integer = 0 To (dt.Rows.Count) - 1
For j As Integer = 0 To dt.Columns.Count - 1
If dt.Rows(i)(j) IsNot Nothing Then
wr.Write(Convert.ToString(dt.Rows(i)(j)) & vbTab)
Else
wr.Write(vbTab)
End If
Next
wr.WriteLine()
Next
wr.Close()
MsgBox("Data Exported Successfully")

If MessageBox.Show("open file...?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
Try
System.Windows.Forms.Help.ShowHelp(Me, Label1.Text)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
End Sub
End Class