?
Solved

Send Parameters to Stored Query

Posted on 2009-04-17
3
Medium Priority
?
635 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Sheritlw
  • 2
3 Comments
 
LVL 7

Expert Comment

by:aboredman
ID: 24171139
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
 

Author Comment

by:Sheritlw
ID: 24171225
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
 
LVL 7

Accepted Solution

by:
aboredman earned 2000 total points
ID: 24171466
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question