[ASK] VB.NET - UPDATE/INSERT DB Access via datagridview

Dear Agan2 and Sista2 para master VB.NET,

- Ane mau tanya soal manipulasi database MS.Access via VB.NET

- Disini ane pake datagridview untuk Insert/Update entries ke tabel yg ada di database MS.Access




Spoiler for Form:




http://img16.imageshack.us/img16/9663/scform.jpg



Uploaded with ImageShack.us








Spoiler for Code:





Code:

Imports System.Data.OleDb
Public Class FormPembelian
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
Dim dr As OleDbDataReader

Private Sub FormPembelian_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
save()
End Sub

Private Sub save()
'input ke TabelBeli
Dim cmd1 As New OleDbCommand
cmd1.Connection = con
If con.State = ConnectionState.Closed Then con.Open()
cmd1.CommandType = CommandType.Text
cmd1.CommandText = "insert into TabelBeli values (@a,@b,@c,@d)"
cmd1.Parameters.Clear()
cmd1.Parameters.AddWithValue("@a", txtKodePembelian.Text)
cmd1.Parameters.AddWithValue("@b", dtpTanggal.Text.Substring(0, 10))
cmd1.Parameters.AddWithValue("@c", txtFaktur.Text)
cmd1.Parameters.AddWithValue("@d", txtSupplier.Text)
cmd1.ExecuteNonQuery()

'input ke TabelBarang
Dim ctrRow As Integer = 0
Dim ctrColumn As Integer = 0

For ctrRow = 0 To (DataGridView1.Rows.Count - 2)
Dim cmd As New OleDbCommand
cmd.CommandText = "select * from TabelBarang where KodeBarang='" & DataGridView1.Item(ctrColumn, ctrRow).Value.ToString & "'"
cmd.CommandType = CommandType.Text
cmd.Connection = con
If con.State = ConnectionState.Closed Then con.Open()
dr = cmd.ExecuteReader

If dr.HasRows Then
Dim cmd2 As New OleDbCommand
cmd2.Connection = con
If con.State = ConnectionState.Closed Then con.Open()
cmd2.CommandType = CommandType.Text
cmd2.CommandText = "UPDATE TabelBarang SET Jumlah = [Jumlah] + @d WHERE KodeBarang= @a;"
cmd2.Parameters.Clear()
cmd2.Parameters.AddWithValue("@a", DataGridView1.Item(ctrColumn, ctrRow).Value.ToString)
ctrColumn += 3
cmd2.Parameters.AddWithValue("@d", DataGridView1.Item(ctrColumn, ctrRow).Value)
cmd2.ExecuteNonQuery()
ctrColumn = 0
Else
Dim cmd3 As New OleDbCommand
cmd3.Connection = con
If con.State = ConnectionState.Closed Then con.Open()
cmd3.CommandType = CommandType.Text
cmd3.CommandText = "insert into TabelBarang values (@a,@b,@c,@d,@e)"
cmd3.Parameters.Clear()
cmd3.Parameters.AddWithValue("@a", DataGridView1.Item(ctrColumn, ctrRow).Value.ToString)
ctrColumn += 1
cmd3.Parameters.AddWithValue("@b", DataGridView1.Item(ctrColumn, ctrRow).Value.ToString)
ctrColumn += 1
cmd3.Parameters.AddWithValue("@c", DataGridView1.Item(ctrColumn, ctrRow).Value.ToString)
ctrColumn += 1
cmd3.Parameters.AddWithValue("@d", DataGridView1.Item(ctrColumn, ctrRow).Value)
ctrColumn += 1
cmd3.Parameters.AddWithValue("@e", DataGridView1.Item(ctrColumn, ctrRow).Value.ToString)
cmd3.ExecuteNonQuery()
ctrColumn = 0
End If

Next
MessageBox.Show("BERHASIL")
hapusDataGrid()
con.Close()
End Sub

Private Sub hapusDataGrid()
DataGridView1.Rows.Clear()
End Sub

End Class









- Jadi user tinggal masukin entries langsung ke datagridview

- Dan button save fungsinya untuk input entries ke database (Insert/Update)



- Yang jadi masalahnya =

* Untuk Insert udah berhasil gan, tapi untuk Update belum.

* Apa yg salah di coding ane??? (soalnya ga muncul error)

* Singkatnya, kalo untuk nambah entries ke tabel bisa, tapi kalo ngerubah entries yg udah ada di tabel belum bisa.



Sorry kalo ngerepotin http://static.kaskus.us/images/smilies/s_sm_peace.gif TERIMA KASIH SEBELUMNYA

</div>