Send Parameters to Stored Query

I have a VB 2008 app with an access backend.
Within this access db, I have a large table.  
I have made a stored query for this table to handle inserts and updates,  now I need to  know how to I reference this stored query and send in the parameters from the VB 2008 app.
Please provide sample code and no links.  My IE crashes when I click on links.
Thank you
SheritlwAsked:
Who is Participating?
 
aboredmanConnect With a Mentor Commented:
Take a look at this code, it is exactly calling a access procedure from .net and passing parameter to it. You only have to adapt it to your own code.

Good luck


Imports System
Imports System.Data
Imports System.Data.OleDb
 
' Functions and subroutines for executing Stored Procedures in Access.
Public Class DBTier
 
    ' Change Data Source to the location of Northwind.mdb on your local 
    ' system.
    Shared connectionString As String = _
        "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program " _
        & "Files\Microsoft Office\Office10\Samples\Northwind.mdb"
    ' This function returns a dataset containing all records in
    ' the Products Table.
    Function ProductsList() As DataSet
        Dim con As OleDbConnection
        Dim da As OleDbDataAdapter
        Dim ds As DataSet
        Dim sSQL As String
 
 
        sSQL = "EXECUTE procProductsList"
 
        con = New OleDbConnection(connectionString)
        da = New OleDbDataAdapter(sSQL, con)
        ds = New DataSet()
        da.Fill(ds, "Products")
 
        Return ds
 
    End Function
 
    ' This Function adds one record to the Products table.
    Sub ProductsAddItem(ByVal ProductName As String, _
        ByVal SupplierID As Integer, ByVal CategoryID As Integer)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim paramProductName As New OleDbParameter()
        Dim paramSupplierID As New OleDbParameter()
        Dim paramCategoryID As New OleDbParameter()
 
        con = New OleDbConnection(connectionString)
        cmd.Connection = con
 
        With paramProductName
            .ParameterName = "inProductName"
            .OleDbType = OleDbType.VarChar
            .Size = 40
            .Value = ProductName
        End With
        cmd.Parameters.Add(paramProductName)
 
        With paramSupplierID
            .ParameterName = "inSupplierID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = SupplierID
        End With
        cmd.Parameters.Add(paramSupplierID)
 
        With paramCategoryID
            .ParameterName = "inCategoryID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = CategoryID
        End With
        cmd.Parameters.Add(paramCategoryID)
 
        cmd.CommandText = "EXECUTE procProductsAddItem"
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
 
    End Sub
 
    ' This function Updates a specific JobTitle Record with new data.
    Sub ProductsUpdateItem(ByVal ProductID As Integer, _
        ByVal ProductName As String)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim paramProductName As New OleDbParameter()
        Dim paramProductID As New OleDbParameter()
 
        con = New OleDbConnection(connectionString)
        cmd.Connection = con
 
        With paramProductID
            .ParameterName = "inProductID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = ProductID
        End With
        cmd.Parameters.Add(paramProductID)
 
        With paramProductName
            .ParameterName = "inProductName"
            .OleDbType = OleDbType.VarChar
            .Size = 40
            .Value = ProductName
        End With
        cmd.Parameters.Add(paramProductName)
 
        cmd.CommandText = "EXECUTE procProductsUpdateItem"
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
 
    End Sub
 
    ' This function deletes one record from the Products table.
    Sub ProductsDeleteItem(ByVal ProductID As Integer)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim paramProductID As New OleDbParameter()
 
        con = New OleDbConnection(connectionString)
        cmd.Connection = con
 
        With paramProductID
            .ParameterName = "inProductID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = ProductID
        End With
        cmd.Parameters.Add(paramProductID)
 
        cmd.CommandText = "EXECUTE procProductsDeleteItem"
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
 
    End Sub
 
End Class

Open in new window

0
 
aboredmanCommented:
This is for a SQL server stored proc but it's pretty much the same for access...

Imports System
Imports System.Data
Imports System.Data.SqlClient

Class MainClass
    Public Shared Sub Main()
        Using con As New SqlConnection()
            con.ConnectionString = "Data Source = .\sqlexpress;Database = Northwind; Integrated Security=SSPI"
            con.Open()
           
            Dim category As String = "Seafood"
            Dim year As String = "1999"
           
            ' Create and configure a new command.
            Using com As SqlCommand = con.CreateCommand()
                com.CommandType = CommandType.StoredProcedure
                com.CommandText = "SalesByCategory"
               
                ' Create a SqlParameter object for the category parameter.
                com.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = category
               
                ' Create a SqlParameter object for the year parameter.
                com.Parameters.Add("@OrdYear", SqlDbType.NVarChar).Value = year
               
                ' Execute the command and process the results.
                Using reader As IDataReader = com.ExecuteReader()
                    Console.WriteLine("Sales By Category ({0}).", year)
                   
                    While reader.Read()
                        ' Display the product details.
                        Console.WriteLine("  {0} = {1}", reader("ProductName"), reader("TotalPurchase"))
                    End While
                End Using
               
            End Using
        End Using
    End Sub
End Class


***My IE crashes when I click on links***... you might want to use firefox...

0
 
SheritlwAuthor Commented:
I appreciate the example, but I've tried to convert to MS Access and they fail.
I really need an example for Access.
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.