Hapus log sql server

Use HRMDB
BACKUP LOG HRMDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(2, 2)

Paging Data VB.net datagridview dengan Fungsi

Fungsi cek jumlah data yang di select

Public Shared Function CekRowSelect(ByVal Sql As String) As Integer
Dim NewSql As String
NewSql = “Select count (*) from (” & Sql & “) a”

Dim sDa As New SqlDataAdapter(NewSql, DbConString)
Dim Dt As New DataTable
sDa.Fill(Dt)

Return CType(Dt.Rows(0).Item(0), Integer)

Dt.Dispose()
sDa.Dispose()

End Function

Fungsi untuk ambil data

Public Shared Function GetTableDataPaging(ByVal Sql As String, _
ByVal JmlPerPage As Integer, ByVal JmlSelect As Integer, ByVal OrderBy As String) As DataTable

Dim sSql As String
Dim i As String
Dim a() As String
Dim j As Integer

Dim OrderBySql1 As String = “”
Dim OrderBySql2 As String = “”

i = OrderBy
a = i.Split(“#”)
For j = 0 To a.GetUpperBound(0)
If OrderBySql1 = “” Then
OrderBySql1 = OrderBySql1 & a(j) & ” ASC “
Else
OrderBySql1 = OrderBySql1 & “,” & a(j) & ” ASC “
End If
If OrderBySql2 = “” Then
OrderBySql2 = OrderBySql2 & a(j) & ” DESC “
Else
OrderBySql2 = OrderBySql2 & “,” & a(j) & ” DESC “
End If
Next

sSql = “SELECT * FROM (SELECT TOP ” & JmlPerPage & ” * FROM (SELECT TOP ” & JmlSelect & ” * FROM ( ” & Sql & “) c ORDER BY ” & OrderBySql1 & “) a ” _
& “ORDER BY ” & OrderBySql2 & “) b ORDER BY ” & OrderBySql1

Dim sDa As New SqlDataAdapter(sSql, DbConString)
Dim Dt As New DataTable
sDa.Fill(Dt)
Return Dt
Dt.Dispose()
sDa.Dispose()

End Function

Cara pake:

deklarasi di paling atas biar bs diakses dr seluruh proses dl class

Dim StatusPage, JmlHalaman, TotalRecord, JmlRecordPerHal, S1, S2 As Integer
Dim SqlData, SortBy As String

Private Sub InisialisasiData(ByVal Sql As String)
Try
JmlRecordPerHal = 30
TotalRecord = CekRowSelect(Sql)

If TotalRecord Mod JmlRecordPerHal > 0 Then
JmlHalaman = (TotalRecord / JmlRecordPerHal) + 1
Else
JmlHalaman = (TotalRecord / JmlRecordPerHal)
End If

S1 = JmlRecordPerHal
S2 = StatusPage * CInt(S1)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub EksekusiData(ByVal sql As String, ByVal TSort As String)
Try
dt = GetTableDataPaging(sql, S1, S2, TSort)

DGV.Rows.Clear()
For Each Dr As DataRow In dt.Rows
DGV.Rows.Add(SetString(Dr(“ID_Barang”)), SetString(Dr(“Nama_Barang”)), SetString(Dr(“Satuan”)), SetString(Dr(“Kelompok”)), SetString(Dr(“Panjang”)), _
SetString(Dr(“Lebar”)), SetString(Dr(“Tinggi”)), SetString(Dr(“Barcode_Logistik”)), SetString(Dr(“Barcode_ISBN”)), SetString(Dr(“Maksimum_Tumpukan”)), SetString(Dr(“ROQ”)), SetString(Dr(“Prioritas”)))
Next
HeaderColor(DGV)
Blereng2(DGV)

Posisi.Text = “Halaman : ” & StatusPage & ” dari ” & JmlHalaman & “.” & vbNewLine _
& “Halaman ini berisi ” & dt.Rows.Count & ” record.” & vbNewLine & “Seluruh data ” & TotalRecord & ” Record.”
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Load data:

Try
SqlData = “Select * from T_Barang”
SortBy = “Nama_Barang”
StatusPage = 1
InisialisasiData(SqlData)
EksekusiData(SqlData, SortBy)

Catch ex As Exception
Pesan(Err)
End Try

navigasi:

Private Sub BtAfter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtAfter.Click
Try
‘MsgBox(StatusPage)

If StatusPage = JmlHalaman – 1 Then
If TotalRecord Mod JmlRecordPerHal > 0 Then
S1 = TotalRecord Mod JmlRecordPerHal
Else
S1 = JmlRecordPerHal
End If
Else
S1 = JmlRecordPerHal
End If
StatusPage = StatusPage + 1
S2 = StatusPage * S1
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub BtBefore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtBefore.Click
Try
StatusPage = StatusPage – 1
S1 = JmlRecordPerHal
S2 = StatusPage * S1
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub BtFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtFirst.Click
Try
StatusPage = 1
S1 = JmlRecordPerHal
S2 = StatusPage * S1
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub BtLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtLast.Click
Try
StatusPage = JmlHalaman
If TotalRecord Mod JmlRecordPerHal > 0 Then
S1 = TotalRecord Mod JmlRecordPerHal
Else
S1 = JmlRecordPerHal
End If
S2 = TotalRecord
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Preview:

Fungsi terbilang untuk crystal reports

Sori ga sempet nulis banyak. nanti aja ya kalo lagi ada waktu. btw ini contoh dll yg siap pakai.

http://125.163.73.146/dll/

dengan membuat dll dengan kata depan CRUFLxxx. kemudian register. untuk dll yang dibuat dengan vb6 sangat mudah membuat dan diregistrasi dengan regsrv32 CRUFLTerbilang.dll, nanti akan muncul sebagai additional function di CR. untuk dll yang dibuat di dotnet memang tidak semudah membuat dll di vb6. baiknya liat dulu refrerensi ke msdn http://msdn.microsoft.com/en-us/library/ms227603.aspx , http://msdn.microsoft.com/en-us/library/ms227637.aspx
http://msdn.microsoft.com/en-us/library/ms227568.aspx,
http://msdn.microsoft.com/en-us/library/ms227566.aspx
untuk registernya contoh regasm /codebase CRUFL_VBNet_Terbilang.dll. ingat untuk membuat dll under .net harus benar2 memperhatikan contoh di msdn.