1.1.1
SP mendapatkan data
Create procedure [dbo].[spGetM_Harga]
as
begin
select * from M_Harga
end
1.1.2
Coding Vb mendapatkan data
Me.SpGetM_HargaTableAdapter.Fill(Me.DataSetWarung.spGetM_Harga)
1.2.1
SP Insert data
Create procedure
[dbo].[spInsertM_harga]
--declare
@id varchar(50)='LnLod1',
@namaMakanan varchar(50)='Lontong Lodeh',
@harga numeric(18,0)='10000',
@Satuan varchar(50)='porsi'
as
--
begin
insert M_Harga
(ID,NamaMakanan,Harga,Satuan)
select @id,@namaMakanan,@harga,@Satuan
end
Coding Tambahan untuk Cek ID sudah
ada atau belum.
Bikin dulu SP untuk mendapatkan cek id
Dim cek As DataTable
cek =
Sp_cekIDTableAdapter1.GetData(txtIdSave.EditValue)
If cek.Rows.Count > 0 Then
MsgBox("ID sudah ADA")
Else
QueriesTableAdapter1.sp_input(txtIdSave.EditValue,
txtPassSave.EditValue)
MsgBox("Tersimpan")
Me.Sp_GetLogTableAdapter.Fill(Me.DSCoba.sp_GetLog)
End If
1.2.2
coding VB Insert data
QueriesTableAdapter.spInsertM_harga(txtID.EditValue,
txtNamaMakan.EditValue, CDec(txtHarga.EditValue),
txtSatuan.EditValue)
Me.SpGetM_HargaTableAdapter.Fill(Me.DataSetWarung.spGetM_Harga)
MsgBox("tersimpan")
1.3.1
SP Edit/Update Data
Create procedure [dbo].[spUpdateM_harga]
--declare
@id varchar(50)='LnLod1',
@namaMakanan varchar(50)='Lontong Lodeh',
@harga numeric(18,0)='7000',
@Satuan varchar(50)='porsi'
as
--
begin
update M_Harga
set
NamaMakanan=@namaMakanan,Harga=@harga,Satuan=@Satuan
where ID=@id
end
1.3.2
Coding VB Edit/Udate Data
QueriesTableAdapter.spUpdateM_harga(txtID.EditValue,
txtNamaMakan.EditValue, CDec(txtHarga.EditValue),
txtSatuan.EditValue)
Me.SpGetM_HargaTableAdapter.Fill(Me.DataSetWarung.spGetM_Harga)
MsgBox("Di Ubah")
Coding VB Digunakan untuk memangil Form Lain
Try
Dim frm As New frmImportProductionOutInventory()
If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then
End If
Catch ex As Exception
AlertControl1.Show(Me,
"Error Check on btnImport_ItemClick_1()",
ex.Message)
End Try
2.1.1
SP Bikin Tabel Temp
--query bikin
table dari @temp
DECLARE @temp AS TABLE (
id varchar(max),
nama varchar (MAX),
[status] varchar(MAX)
)
INSERT @temp
SELECT '1','ayam ','destop'
UNION ALL SELECT '2','anjing','web'
UNION ALL SELECT '3','kucing','destop'
SELECT nama FROM(
SELECT * FROM @temp
)t
--'t' ( harus di
beri keterangan nama select)
2.1.2
SP kasus toko kaset
--no.1 (Mencari
anggota top 3 yang membayar denda di bulan
8 dan tahun 2017 dengan tampilan
, Nama Naggota dan Total Denda)
-- >>>Dari
google
CREATE PROCEDURE [SP_CariTop3AnggotaTerkenaDenda]
AS
BEGIN
SELECT Ma.NamaAnggota,TPH.Total_Denda
FROM
T_PengembalianHeader TPH
LEFT JOIN dbo.M_Anggota MA
ON TPH.Id_Anggota
= MA.Id_Anggota
WHERE
(
Total_Denda IN
(
SELECT TOP (3) Total_Denda--,MONTH(Tgl_Kembali)
FROM
T_PengembalianHeader as T_PengembalianHeader
WHERE MONTH(Tgl_Kembali) = '8' AND YEAR(Tgl_Kembali)= '2017'
GROUP BY Total_Denda,Tgl_Kembali
ORDER BY Total_Denda DESC
)
)
ORDER BY Total_Denda DESC
end
--==============================================================================
--NO.2 ( Mencari
Harga film paling mahal dan paling murah)
CREATE PROCEDURE [SP_MencariHargaPalingMurah]
AS
begin
SELECT MAX(Harga)
FROM dbo.Film
SELECT * FROM dbo.Film
WHERE Harga= ( SELECT MAX(Harga)
FROM dbo.Film)
SELECT * FROM dbo.Film
WHERE Harga= ( SELECT min(Harga)
FROM dbo.Film)
end
--=================================================================================
--no.3 (
Menampilkang anggita yang tidak pernah kena denda samasekali )
DECLARE @temp as TABLE(
nama varchar (max), denda NUMERIC (18,2)
)
INSERT @temp
SELECT MA.NamaAnggota,SUM(Total_Denda)
FROM dbo.T_PengembalianHeader TPPH
LEFT JOIN dbo.M_Anggota MA
ON TPPH.Id_Anggota
= MA.Id_Anggota
GROUP BY MA.Id_Anggota,NamaAnggota
SELECT * FROM @temp
WHERE denda = '0'
--==================================================================
--no. 4 (Mencari
film yang belom di kembalikan,yang sudah jatuh tempo tgl hari 07/12/17, dengan
tampilan( Anggota,TGL_SEWA,TGL_ harus kembali ))
SELECT MA.NamaAnggota,TPHH.tgl_Sewa,TPH.tgl_Kembali,TPHH.tgl_Kembali
FROM
T_PenyewaHeader TPHH
LEFT JOIN dbo.T_PenyewaDetail
TPD ON TPHH.NoNota=TPD.No_Nota
LEFT JOIN dbo.T_PengembalianHeader
TPH ON TPD.No_Nota=TPH.No_nota
LEFT JOIN dbo.T_PengembalianDetail
TPDD ON TPH.Id_Pengembalian=TPDD.ID_Pengembalian
LEFT JOIN dbo.M_Anggota MA
ON TPHH.IdAnggota= MA.Id_Anggota
WHERE TPH.tgl_Kembali IS NULL AND CAST(TPHH.tgl_Kembali AS DATE) < '2017-12-07'
---================
SELECT *
FROM
T_PenyewaHeader TPHH
LEFT JOIN dbo.T_PenyewaDetail
TPD ON TPHH.NoNota=TPD.No_Nota
LEFT JOIN dbo.T_PengembalianHeader
TPH ON TPD.No_Nota=TPH.No_nota
Tambahan untuk between
create procedure SP_LaporanUsser
--declare
@dariTanggal date='2018-08-18',
@sampaiTanggal date='2018-08-19',
@user varchar(50)='admin'
as
begin
select IDTransaksi [ID Transsaksi],IDBarang [ID Barang],NamaBarang [Nama Barang],HargaJual [Harga Jual],QTY,Satuan,UserPost [User Post],TglPost [Tanggal] from T_TransaksiDetail
where UserPost=@user and (TglPost between @dariTanggal and @sampaiTanggal)
end
Coding untuk
Mendapatkan nilai Dari DataTable Vb.net
Dim maxnim As DataTable
maxnim =
SP_MaxNimTableAdapter.GetData()
Dim tamp As
String
tamp = maxnim.Rows(0).Item("Nim")
Coding
VB Export Ke PDF
Private Sub
itemExportPDF_ItemClick(ByVal sender As System.Object, ByVal e As
DevExpress.XtraBars.ItemClickEventArgs) Handles
itemExportPDF.ItemClick
'GridView1.ExportToPdf(Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
& "\Master Data Price.pdf")
Dim newThread2 As New Threading.Thread(AddressOf ExportPDF)
newThread2.SetApartmentState(ApartmentState.STA)
newThread2.Start()
End Sub
Private Sub
ExportPDF()
Dim saveFileDialog2 As
New SaveFileDialog
saveFileDialog2.InitialDirectory = "C:\"
saveFileDialog2.Title = "Save PDF
Files"
saveFileDialog2.CheckFileExists = False
saveFileDialog2.CheckPathExists = True
saveFileDialog2.OverwritePrompt
= True
saveFileDialog2.DefaultExt = "pdf"
saveFileDialog2.FileName = vbNullString
saveFileDialog2.Filter = "PDF files
(*.pdf)|*.pdf"
saveFileDialog2.FilterIndex = 1
saveFileDialog2.RestoreDirectory = True
If (saveFileDialog2.ShowDialog() = DialogResult.OK) Then
path = saveFileDialog2.FileName
Else
Exit Sub
End If
ExportToPDF()
End Sub
Private Sub
ExportToPDF()
If Me.InvokeRequired Then
Me.Invoke(New MethodInvoker(AddressOf
ExportToPDF))
Else
GridView1.ExportToPdf(path)
End If
Coding
VB export ke excel
Sebelum nya di kasih codding pada Atas
sendiri (Imports
System.Threading)
Dan di berikan coding (Dim
path As String)
di bawah clas
Private Sub
itemExportExcel_ItemClick(ByVal sender As System.Object, ByVal e As
DevExpress.XtraBars.ItemClickEventArgs) Handles
itemExportExcel.ItemClick
Try
'GVPrice.ExportToXls(Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
& "\Master Data Price.xls")
Dim newThread1 As New Threading.Thread(AddressOf ExportExcel)
newThread1.SetApartmentState(ApartmentState.STA)
newThread1.Start()
Catch ex As Exception
End Try
End Sub
Private Sub
ExportExcel()
'User : Chandra
'Date Added/Modified : 14-Jul-2016
'Description : Fungsi ini untuk Export ke Excel
Dim objExcel As Object
Dim objWorkBook As Object
Dim objWorkSheet As Object
Dim saveFileDialog1 As
New SaveFileDialog
saveFileDialog1.InitialDirectory = "C:\"
saveFileDialog1.Title = "Save Excel
Files"
saveFileDialog1.CheckFileExists = False
saveFileDialog1.CheckPathExists = True
saveFileDialog1.OverwritePrompt = True
saveFileDialog1.DefaultExt = "xls"
saveFileDialog1.FileName = vbNullString
saveFileDialog1.Filter = "Excel
1997-2003 files (*.xls)|*.xls|Excel 2007-2010 files (*.xlsx)|*.xlsx"
saveFileDialog1.FilterIndex = 1
saveFileDialog1.RestoreDirectory = True
If (saveFileDialog1.ShowDialog() = DialogResult.OK) Then
path = saveFileDialog1.FileName
Else
Exit Sub
End If
If Strings.Right(path,
3) = "xls" Then
ExportToXLS()
ElseIf Strings.Right(path,
4) = "xlsx" Then
ExportToXLSX()
End If
objExcel = CreateObject("EXCEL.APPLICATION")
objWorkBook = objExcel.Workbooks.Open(path)
objWorkSheet = objWorkBook.Worksheets("Sheet")
objWorkSheet.Range("A1").EntireRow.Insert()
objWorkSheet.Range("A1").EntireRow.Insert()
objWorkSheet.Range("A1").EntireRow.Insert()
objWorkSheet.Range("A1").Value
= "Daftar Harga Menu" ‘Untuk Judul di
Excel
objWorkSheet.Range("A1").Font.Bold
= True
'orng = objWorkSheet.Range("A", "Z")
'orng.EntireColumn.AutoFit()
objWorkBook.Save()
objWorkBook.Close("")
'Process.Start(path)
objExcel = Nothing
End Sub
Private Sub
ExportToXLS()
If Me.InvokeRequired Then
Me.Invoke(New MethodInvoker(AddressOf
ExportToXLS))
Else
GridView1.ExportToXls(path)
End If
End Sub
Private Sub
ExportToXLSX()
If Me.InvokeRequired Then
Me.Invoke(New MethodInvoker(AddressOf
ExportToXLSX))
Else
GridView1.ExportToXlsx(path)
End If
Coding
VB export ke PDF
Sebelum nya di kasih codding pada Atas
sendiri (Imports
System.Threading)
Dan di berikan coding (Dim
path As String)
di bawah clas.
Private Sub
btnExportPDF_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
btnExportPDF.Click
Dim newThread2 As New Threading.Thread(AddressOf ExportPDF)
newThread2.SetApartmentState(ApartmentState.STA)
newThread2.Start()
End Sub
Private Sub
ExportPDF()
Dim saveFileDialog2 As
New SaveFileDialog
saveFileDialog2.InitialDirectory = "C:\"
saveFileDialog2.Title = "Save PDF
Files"
saveFileDialog2.CheckFileExists = False
saveFileDialog2.CheckPathExists = True
saveFileDialog2.OverwritePrompt = True
saveFileDialog2.DefaultExt = "pdf"
saveFileDialog2.FileName = vbNullString
saveFileDialog2.Filter = "PDF files
(*.pdf)|*.pdf"
saveFileDialog2.FilterIndex = 1
saveFileDialog2.RestoreDirectory = True
If (saveFileDialog2.ShowDialog() = DialogResult.OK) Then
path = saveFileDialog2.FileName
Else
Exit Sub
End If
ExportToPDF()
End Sub
Private Sub
ExportToPDF()
If Me.InvokeRequired Then
Me.Invoke(New MethodInvoker(AddressOf
ExportToPDF))
Else
GridView1.ExportToPdf(path)
End If
Coding VB
GridView
Untuk
Merubah data Grid view
'digunakan untuk memberi centang pada
kolom Pending
Dim dttemp As DataTable
dttemp = Me.Sp_TGetLeaveManagementApprovalStatusTableAdapter1.GetData(period,
month, "%", My.Settings.Source, My.Settings.WHCode,
allowedSource, AllowedWarehouse, AllowedEmployeeType, AllowedDepartment,
AllowedPosition, AllowedEmployee)
If CheckEdit1.Checked = True
Then
For i = 0 To
dttemp.Rows.Count - 1
If GridViewApproval.GetRowCellValue(i,
GridViewApproval.Columns("StatusID"))
= "0" Then
GridViewApproval.SetRowCellValue(i, GridViewApproval.Columns("Check"), True)
End If
Next
Else
For i = 0 To
dttemp.Rows.Count - 1
If GridViewApproval.GetRowCellValue(i,
GridViewApproval.Columns("StatusID"))
= "0" Then
GridViewApproval.SetRowCellValue(i, GridViewApproval.Columns("Check"), False)
End If
Next
End If
'digunakan untuk memberi centang pada
kolom Approved
Dim dttemp As DataTable
dttemp = Me.Sp_TGetLeaveManagementApprovalStatusTableAdapter1.GetData(period,
month, "%", My.Settings.Source, My.Settings.WHCode,
allowedSource, AllowedWarehouse, AllowedEmployeeType, AllowedDepartment,
AllowedPosition, AllowedEmployee)
If CheckEdit2.Checked = True
Then
For i = 0 To
dttemp.Rows.Count - 1
'For i = 0 To GridViewApproval.RowCount - 1
If GridViewApproval.GetRowCellValue(i,
GridViewApproval.Columns("StatusName"))
= "Approved" Then
GridViewApproval.SetRowCellValue(i, GridViewApproval.Columns("Check"), True)
End If
Next
Else
For i = 0 To
dttemp.Rows.Count - 1
If GridViewApproval.GetRowCellValue(i, GridViewApproval.Columns("StatusName")) = "Approved" Then
GridViewApproval.SetRowCellValue(i,
GridViewApproval.Columns("Check"),
False)
End If
Next
End If
Atau
bias jg dengan
Dim hitung As Integer
Dim total As Integer
Dim subtotal As Integer
subtotal = 0
hitung = GridView1.RowCount – 1
For i = 0 To hitung
total = GridView1.GetRowCellValue((i), colHarga) *
GridView1.GetRowCellValue((i), colQTY) - GridView1.GetRowCellValue((i),
colDiscRp_)
GridView1.SetRowCellValue((i), colTotal,
total)
subtotal = subtotal + GridView1.GetRowCellValue((i), colTotal)
Next
MemoEdit2.Text = subtotal
Coding input texbox ke gridview
Private Sub btnProses_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProses.Click
Dim a As Integer
a = txtLama.Text - 1
Dim sisapokok As Integer
sisapokok = txtJmlPinjaman.Text
Dim pokok As Integer
pokok = txtJmlPinjaman.Text / txtLama.Text
Dim bungasisapokok As Integer
Dim hasil As Integer
Dim dttest As DataTable
dttest = Nothing
dttest = New DataTable("UANG")
Dim [id], [total] As New DataColumn
[id].ColumnName = "Hasil Cicilan"
[total].ColumnName = "Jumlah dibayar"
dttest.Columns.Add([id])
dttest.Columns.Add([total])
For i = 0 To a
bungasisapokok = (Val(sisapokok) / 100) * Val(txtBunga.Text)
sisapokok = sisapokok - pokok
hasil = pokok + bungasisapokok
dttest.Rows.Add(i + 1, hasil)
Next
DataGridView1.DataSource = dttest
end sub
Pesan Error Vb
Try
‘isi Coding
Catch ex As Exception
AlertControl1.Show(Me, "Error Check on
CheckAllCheckEdit_CheckedChanged()", ex.Message)
End Try
Query SQL Auto
Nim/Nomer
Create procedure SP_AutoNim
--declare
@Katagory varchar(50)='MS'
as--
begin
declare @temp numeric(4)
declare @temp2 as numeric(4)
declare @temp1 as varchar(8)
set @temp = (select max (right(Nim,3))+ 1 Nim from T_Biodata where
Nim like @Katagory +'%')
set @temp1 = (select @Katagory +'000')
set @temp2 = (select LEN(@temp1)-LEN(@temp))
select LEFT(@temp1,@temp2)+ cast(@temp as varchar(5)) Nim
end
Lanjutan Coding VB nya untuk memanggil
Nim
Dim MS As String
MS = "MS"
Dim autonim As DataTable
autonim = SP_AutoNim1TableAdapter1.GetData(MS)
txtNim.Text = autonim.Rows(0).Item("Nim")
Coding VB. Fungsi tombol F1 f2 dan lain2
Isikan
codding ini di kolom load
Me.KeyPreview = True
Bikin private Fangtion seperti d bawah
Private Sub
Form1_KeyDown(ByVal sender As Object, ByVal e As
System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown
Select Case e.KeyCode
Case Keys.F12
LogInvb.Show() ‘F12 Untuk Show form
lain
Case Keys.F4 'untuk mengakili kan antar kolom
Try
Dim jml As
Integer
Dim SubTotal As
Integer
Dim totalHarga As
Integer
totalHarga = 0
SubTotal = 0
jml = GridView1.RowCount - 1
For i = 0 To
jml
' MsgBox(jml)
totalHarga =
GridView1.GetRowCellValue((i), colHarga) * GridView1.GetRowCellValue((i),
colJumlah)
GridView1.SetRowCellValue(i, colTotal1, totalHarga)
' set
SubTotal = SubTotal +
GridView1.GetRowCellValue((i), colTotal1)
Next
TextEdit6.EditValue = SubTotal
Catch ex As
Exception
MsgBox("error")
End Try
Case Keys.F2
GridView1.DeleteSelectedRows() 'untuk Hapus
colom grid view
'
End Select
End Sub
Untuk load Tanggal dan jam
TextEdit1.Text = Format(Now, "dd / MMM / yyyy")
TextEdit10.Text = Format(Now, "HH:mm:ss")
Coding vb pertanyaan yess no
If MsgBox("Apakah Anda
Ingin Menghapus Data ini????", MsgBoxStyle.YesNo,
"Pilihan!") = MsgBoxResult.Yes Then
QueriesTableAdapter.SP_DeleteM_Brand(txtIDBrand.Text)
Me.SpGetBrandTableAdapter.Fill(DstRitel.spGetBrand)
MsgBox("Berhasil Dihapus")
Else
Exit Sub
End If
========================================================================
Coding Save dan View Gambar
copas d atas sendiri coding ini
Imports System.Data.SqlClient
Imports System.IO
Coding cari foto
Dim OpenFileDialog1 As OpenFileDialog = New OpenFileDialog()
If OpenFileDialog1.ShowDialog() =
Windows.Forms.DialogResult.OK Then
PictureBox1.BackgroundImage =
Image.FromFile(OpenFileDialog1.FileName)
PictureBox1.BackgroundImageLayout =
ImageLayout.Zoom
coding save foto
Dim MemoryStream As New MemoryStream
PictureBox1.BackgroundImage.Save(MemoryStream,
PictureBox1.BackgroundImage.RawFormat)
Dim Dgambar As Byte() = MemoryStream.GetBuffer
QueriesTableAdapter.SP_gambar(txtID.Text, txtNama.Text, Dgambar)
MsgBox("Tersimpan")
dan query
create procedure [dbo].[SP_Savegambar]
--declare
@id varchar(10),
@nama varchar(50),
@foto image
as
begin
insert Tb_Gambar
(id,nama,foto)
select ID=@id,nama=@nama,foto=@foto
end
Codding view gambar
Dim view As DataTable
view = Me.SP_viewGambarTableAdapter.GetData(txtviewId.Text)
txtViewNama.Text = view.Rows(0).Item("nama")
PictureEdit1.EditValue = view.Rows(0).Item("foto")
dan query
create procedure SP_viewGambar
--declare
@id varchar(10)='123'--
as
begin
select * from Tb_Gambar
where id=@id
end
========================================================
Coding menampilkan crystal Report 9
Sebelumnya
ketik coding d atas sendiri
Imports CrystalDecisions.CrystalReports.Engine
Dim cryRpt As New ReportDocument
code di dalam tombol view
cryRpt.Load(Application.StartupPath
& "\Report\latihanRpt.rpt") ‘Lokasi
file report
cryRpt.SetParameterValue("@awal",
txtAwal.EditValue) ‘Parameter
cryRpt.SetParameterValue("@akhir",
txtakhir.EditValue)
CrystalReportViewer1.ReportSource = cryRpt
CrystalReportViewer1.Refresh()
Pert 2 klik di sini
part 3 klik disini
