Dear sir,
I'm trying to update sql to database for fields (CatProd) without duplicates with dapper in VB.NET
in the code below there is method `UpdateCatProd` but how can I do an update sql for the catprod field itself and without duplicates as well
Please Guide me
Imports System.ComponentModel
Imports System.Data.OleDb
Imports Dapper
Public Class Form1
Dim CatProdService As New CatProdService()
Private bindingSource As BindingSource = Nothing
Private selectedOrder As String
Private selectedIndex As Integer
Private Sub Btnsave_Click(sender As Object, e As EventArgs) Handles Btnsave.Click
Dim id As Integer
Integer.TryParse(btxtid.Text, id)
Dim itemid = CatProdService.GetCatProdById(id)
Dim item = CatProdService.GetByCatProd(txtCatProd.Text)
Try
If txtCatProd.TextLength > 3 Then
MessageBox.Show("CatProd Product Only 3 Characters or Digits")
Return
End If
If itemid IsNot Nothing Then
If String.IsNullOrEmpty(txtCatProd.Text) OrElse String.IsNullOrEmpty(txtDesCatProd.Text) Then
MsgBox("Required fill CatProd,DesCatProd", MsgBoxStyle.Information, "Information")
Return
End If
If item IsNot Nothing Then
MessageBox.Show("CatProd already exists")
Return
End If
'insert new record
CatProdService.InsertCatProd(New CatProd() With {
.CatProd = txtCatProd.Text,
.DesCatProd = txtDesCatProd.Text
})
Else
'Update record
CatProdService.UpdateCatProd(New CatProd() With {
.DesCatProd = txtDesCatProd.Text,
.CatProd = txtCatProd.Text
})
End If
MessageBox.Show("Successfull")
LoadData1()
Catch ex As Exception
MessageBox.Show(ex.Message, "TEST", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End Try
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
LoadData1()
End Sub
Private Sub LoadData1()
Dim CatProd = CatProdService.Getitem()
bindingSource = New BindingSource With {.DataSource = New BindingList(Of CatProd)(CType(CatProdService.Getitem(), IList(Of CatProd)))}
DataGridView1.DataSource = bindingSource
DataGridView1.ReadOnly = True
End Sub
Private Function GetSELECT() As CatProd
Return If(DataGridView1.SelectedCells.Count = 0, Nothing, TryCast(DataGridView1.SelectedCells(0).OwningRow.DataBoundItem, CatProd))
End Function
Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
Dim dgv = DirectCast(sender, DataGridView)
If e.RowIndex >= 0 Then
Dim SelectDgv = GetSELECT()
txtCatProd.Text = SelectDgv.CatProd
txtDesCatProd.Text = SelectDgv.DesCatProd
btxtid.Text = SelectDgv.ID
End If
End Sub
End Class
Public Class CatProd
Public Property ID() As Integer
Public Property CatProd() As String
Public Property DesCatProd() As String
End Class
Public Class CatProdService
Public Function GetOledbConnectionString() As String
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CatProd.accdb;Persist Security Info=False;"
End Function
Private ReadOnly _conn As OleDbConnection
Private _connectionString As String = GetOledbConnectionString()
Public Sub New()
_conn = New OleDbConnection(_connectionString)
End Sub
Public Sub InsertCatProd(ByVal Obj As CatProd)
Dim sql = $"INSERT INTO `CatProd` (`CatProd`,`DesCatProd`) VALUES ('{Obj.CatProd}','{Obj.DesCatProd}');"
Using _conn = New OleDbConnection(GetOledbConnectionString())
_conn.Execute(sql)
End Using
End Sub
Public Sub UpdateCatProd(ByVal Obj As CatProd)
Dim sql = $"UPDATE `CatProd` Set `DesCatProd` = '{Obj.DesCatProd}' WHERE `CatProd`='{Obj.CatProd}';"
Using _conn = New OleDbConnection(GetOledbConnectionString())
_conn.Execute(sql)
End Using
End Sub
Public Function Getitem() As IEnumerable(Of CatProd)
Dim sql = "SELECT * FROM CatProd"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of CatProd)(sql).ToList()
End Using
End Function
Public Function GetByCatProd(ByVal CatProd As String) As CatProd
Dim sql = $"SELECT * FROM CatProd WHERE CatProd = '{CatProd}'"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of CatProd)(sql).FirstOrDefault()
End Using
End Function
Public Function GetCatProdById(ByVal CatProdNo As Integer) As CatProd
Dim sql = $"SELECT * FROM CatProd WHERE id = {CatProdNo}"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of CatProd)(sql).FirstOrDefault()
End Using
End Function
End Class
Link ms access database