How to retrieve data from Dapper ExecuteReader() into a textbox with MS-ACCESS database in VB.NET

Question

I want to retrieve data in Form2 in TextBox1 which is Invono and TextBox2 which is Created. Actually, I have created a function method, which is GetItemTransfersMaster but I don't know how to use to display in the textbox. Please guide.

Thanks

Code in Form1

Public Class Form1
    Dim itrservice As New ItemtransfersService()
    Private Invno As String
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = itrservice.GetLoadItemTransfersMaster()
        DataGridView1.ReadOnly = True
    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.ColumnIndex = 0 Then
            If DataGridView1.SelectedRows.Count > 0 Then ' make sure user select at least 1 row
                Dim Invno As String = DataGridView1.SelectedRows(0).Cells(0).Value & String.Empty
                Using frm = New Form2(Invno)
                    If frm.ShowDialog() = DialogResult.OK Then

                    End If
                End Using
            End If
        End If
    End Sub
End Class
Public Class ItemtransfersService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dapperdemo.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 Function GetLoadItemTransfersMaster() As IEnumerable(Of ItemTransfers)
        Dim sql = "SELECT * FROM ItemTransfers"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ItemTransfers)(sql).ToList()
        End Using
    End Function
    Public Function GetItemTransfersDetail(ByVal Invno As String) As IEnumerable(Of ItemTransfersDetail)
        Dim sql = $"SELECT * FROM ItemTransfersDetail WHERE Invno = '{Invno}'"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ItemTransfersDetail)(sql).ToList()
        End Using
    End Function
    Public Function GetItemTransfersMaster(ByVal Invno As String) As IEnumerable(Of ItemTransfers)
        Dim sql = $"SELECT * FROM ItemTransfers WHERE Invno = '{Invno}'"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ItemTransfers)(sql).ToList()
        End Using
    End Function
End Class
Public Class ItemTransfers
    Public Property Invno() As String
    Public Property HeaderInvno() As Integer
    Public Property CreatedBy() As String
    Public Property Created() As DateTime
    Public Property ModifiedBy() As String
    Public Property Modified() As DateTime
    Public Property ItemTransfersDetail() As New List(Of ItemTransfersDetail)()
End Class
Public Class ItemTransfersDetail
    Public Property Id() As Integer
    Public Property No() As Integer
    Public Property Invno() As String
    Public Property CodeProduct() As String
    Public Property Barcode() As String
    Public Property Colorcode() As String
    Public Property Size() As String
    Public Property Qty() As Integer
End Class

Code in Form2

Public Class Form2
    Inherits Form
    Dim itrservice As New ItemtransfersService()
    Sub New()
        InitializeComponent()
    End Sub
    Public Sub New(Invno As String)
        Me.New
        DataGridView1.DataSource = itrservice.GetItemTransfersDetail(Invno)
        DataGridView1.Columns(0).Visible = False
    End Sub
End Class

view in form1

view in form2

What textbox? In any case, the code misused Dapper. The whole point of Dapper is to make executing parameterized queries easier. ` $"SELECT * FROM ItemTransfersDetail WHERE Invno = '{Invno}'"` is a bug. Use `Where InvNo=@InvNo"` instead, and execute it with `_conn.Query(Of ItemTransfersDetail)(sql,new {InvNo=InvNo})`. This generates a parameterized query and passes the value of `InvNo` as the `@InvNo` parameter value — Panagiotis Kanavos, Aug 21 '23 at 08:03
Is the actual question how to pass data to the child form and display it in text boxes? You need to pass that data to the other form. Instead or reading cells, you can pass the actual object used to generate the grid row with `.SelectedRows(0).DataBoundItem`. — Panagiotis Kanavos, Aug 21 '23 at 08:09
@PanagiotisKanavos , Thank you for your reply. `What textbox?` textbox I mean is a textbox that is in form2 there are 2 . `The whole point of Dapper is to make executing parameterized queries easier. ` $"SELECT * FROM ItemTransfersDetail WHERE Invno = '{Invno}'"` is a bug. Use Where InvNo=@InvNo" instead, and execute it with _conn.Query(Of ItemTransfersDetail)(sql,new {InvNo=InvNo}). This generates a parameterized query and passes the value of InvNo as the @InvNo parameter value` Thank you for your advice and guidance — roy, Aug 21 '23 at 08:18
@PanagiotisKanavos , `SelectedRows(0).DataBoundItem.` please guide me to use this and if i use the function via dapper what might be implemented — roy, Aug 21 '23 at 08:21
@PanagiotisKanavos , `_conn.Query(Of ItemTransfersDetail)(sql,new {InvNo=InvNo})` I tried your recommendation but there was an error. `Return _conn.Query(Of ItemTransfers)(sql, New {Invno = Invno})` error `')' expected and Type or 'With' expected` — roy, Aug 21 '23 at 08:28
I can't guess what you tried. The question's code is simply invalid. Check [Dapper's documentation](https://github.com/DapperLib/Dapper) to see how it's meant to be used. It's in C# because that's the language used by 99.999% of .NET projects — Panagiotis Kanavos, Aug 21 '23 at 08:31
There is also a [Dapper Query Builder](https://github.com/Drizin/DapperQueryBuilder/) using String Interpolation and Fluent API. — Olivier Jacot-Descombes, Aug 21 '23 at 09:10
With OLEDB, try: `"SELECT * FROM ItemTransfers WHERE Invno = ?InvNo?", new { InvNo }` - this alternative syntax is a Dapper thing called "pseudo-positional parameters", where it rewrites that at runtime with the OLEDB positional syntax — Marc Gravell, Aug 21 '23 at 09:42
@MarcGravell , Thank you for your reply `Dim sql = $"SELECT * FROM ItemTransfers WHERE Invno = '{Invno}'"` You mean change to `Dim sql = $"SELECT * FROM ItemTransfers WHERE Invno = ?invno?"` Is this what you mean? — roy, Aug 21 '23 at 09:46
@MarcGravell , if I make it like that then there is an error `Additional information: Syntax error (missing operator) in query expression 'Invno = ?invno?'.` — roy, Aug 21 '23 at 09:48
@MarcGravell , actually if I make code like this `Dim sql = $"SELECT * FROM ItemTransfers WHERE Invno = '{Invno}'"` It has no errors at all but did I misapply the dapper method? — roy, Aug 21 '23 at 09:53
@MarcGravell , `Dim sql = $"SELECT * FROM ItemTransfers WHERE Invno = '{Invno}'"` and `Dim sql = $"SELECT * FROM ItemTransfers WHERE Invno = '?Invno?'"` .Both codes I tried to succeed — roy, Aug 21 '23 at 10:02
In `Fomr1`, get the `ItemTransfers` object and make sure its `ItemTransfersDetail` property is set. `Dim item = DirectCast(DataGridView1.SelectedRows(0).DataBoundItem, ItemTransfers)`. In `Form2` add a constructor that takes that type. `Sub New(item As ItemTransfers) .. End Sub`. Bind the grid to `item.ItemTransfersDetail` and set the text boxes values from `item` object. That's it all. — dr.null, Aug 21 '23 at 12:20
@dr.null , Thank you reply from you. `In Fomr1,get the ItemTransfers object and make sure its ItemTransfersDetail property is set. Dim item = DirectCast(DataGridView1.SelectedRows(0).DataBoundItem, ItemTransfers)` what appears in that form1 datagridview is GetLoadItemTransfersMaster() so it's not a detail and in form2 I've made it like this `Public Sub New(Invno As String) Me.New End Sub` how can I add this `Sub New(item As ItemTransfers) .. End Sub` PLease guide me — roy, Aug 21 '23 at 12:51
How did you add this `Public Sub New(Invno As String)` ? Same way! — dr.null, Aug 21 '23 at 14:02
@dr.null , Thanks I made it like this works `Public Sub New(Invno As String, item As ItemTransfers) Me.New` — roy, Aug 21 '23 at 14:04
@dr.null , I have updated my answer, but this is thanks to your guidance that solved the problem. Is my answer code anything that needs to be fixed? — roy, Aug 21 '23 at 14:12
@dr.null , If you want to make it as an answer then I will withdraw the answer I posted because you have the right to answer as an answer — roy, Aug 21 '23 at 14:29
No, thank you and well done. You can remove the first param `Invno As String,`. You are not using it, then you don't need to pass it. The `item` object contains it `item.Invno` if I'm not mistaken. — dr.null, Aug 21 '23 at 14:38
@dr.null , `The item object contains it item.Invno if I'm not mistaken.` You're right . Thank you for giving me the best guide. I have also updated in the answers as well — roy, Aug 21 '23 at 14:41
@dr.null , [link](https://stackoverflow.com/questions/76954293/how-to-create-a-user-role-based-permissions-for-each-form-in-dapper-with-access) , I have a recent post maybe you can help new — roy, Aug 22 '23 at 14:30

roy · Accepted Answer · 2023-08-21T14:42:55.897

I thank you very much for the guidance from @dr.null

Code In form1

Public Class Form1
    Dim itrservice As New ItemtransfersService()
    Private Invno As String
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = itrservice.GetLoadItemTransfersMaster()
        DataGridView1.ReadOnly = True
    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.ColumnIndex = 0 Then
            If DataGridView1.SelectedRows.Count > 0 Then ' make sure user select at least 1 row
                Dim Invno As String = DataGridView1.SelectedRows(0).Cells(0).Value.tostring & String.Empty
 Dim item = DirectCast(DataGridView1.SelectedRows(0).DataBoundItem, ItemTransfers)
                Using frm = New Form2(item)
                    If frm.ShowDialog() = DialogResult.OK Then

                    End If
                End Using
            End If
        End If
    End Sub
End Class
Public Class ItemtransfersService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dapperdemo.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 Function GetLoadItemTransfersMaster() As IEnumerable(Of ItemTransfers)
        Dim sql = "SELECT * FROM ItemTransfers"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ItemTransfers)(sql).ToList()
        End Using
    End Function
    Public Function GetItemTransfersDetail(ByVal Invno As String) As IEnumerable(Of ItemTransfersDetail)
        Dim sql = $"SELECT * FROM ItemTransfersDetail WHERE Invno = '{Invno}'"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ItemTransfersDetail)(sql).ToList()
        End Using
    End Function
    Public Function GetItemTransfersMaster(ByVal Invno As String) As IEnumerable(Of ItemTransfers)
        Dim sql = $"SELECT * FROM ItemTransfers WHERE Invno = '{Invno}'"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ItemTransfers)(sql).ToList()
        End Using
    End Function
End Class
Public Class ItemTransfers
    Public Property Invno() As String
    Public Property HeaderInvno() As Integer
    Public Property CreatedBy() As String
    Public Property Created() As DateTime
    Public Property ModifiedBy() As String
    Public Property Modified() As DateTime
    Public Property ItemTransfersDetail() As New List(Of ItemTransfersDetail)()
End Class
Public Class ItemTransfersDetail
    Public Property Id() As Integer
    Public Property No() As Integer
    Public Property Invno() As String
    Public Property CodeProduct() As String
    Public Property Barcode() As String
    Public Property Colorcode() As String
    Public Property Size() As String
    Public Property Qty() As Integer
End Class

Code In form2

Public Class Form2
    Inherits Form
Dim itrservice As New ItemtransfersService()
    Sub New()
        InitializeComponent()
    End Sub
 Public Sub New(item As ItemTransfers)
        Me.New
        DataGridView1.DataSource = itrservice.GetItemTransfersDetail(Invno)
        DataGridView1.Columns(0).Visible = False
        TextBox1.Text = item.Invno
        TextBox2.Text = item.Created
 End Sub
End Class

How to retrieve data from Dapper ExecuteReader() into a textbox with MS-ACCESS database in VB.NET

1 Answers1