Friday, May 1, 2020

SQL Stored Procedure SELECT Statement: MS Access Database from VB.net


Imports System.Data.OleDb.OleDbConnection
Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Console.WriteLine("Connection Open!")
            Dim command As New OleDb.OleDbCommand("SELECT * FROM BOM WHERE Parent_PartNumber = @ParentID"cnn)
            command.Parameters.AddWithValue("@ParentID", 2)
            'command.Parameters.AddWithValue("@ParentID", OleDb.OleDbType.Integer).Value = 2
            Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
            While reader.Read()
                Console.WriteLine(reader.GetInt32(0).ToString() + vbTab + reader.GetInt32(1).ToString() + vbTab + reader.GetInt32(2).ToString())
            End While
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine("Can not open connection " + ex.Message)
        End Try
        Console.ReadLine()
    End Sub
End Module

Note that some sites recommend using the:
            'command.Parameters.AddWithValue("@ParentID", OleDb.OleDbType.Integer).Value = 2

I found that is worked fine with just using:

            command.Parameters.AddWithValue("@ParentID", 2)

No comments:

Post a Comment

C# Creating a Picture at Runtime in a windows form app

Add a PictureBox to the form. It will be called PictureBox1 See post:  https://docs.microsoft.com/en-us/dotnet/desktop/winforms/advanced/how...