存储过程/参数化查询获取和传递参数 [英] Stored Procedure/Parameterized Query in Access And Pass Parameter

问题描述

我创建了一个访问，看起来像这样的查询...

  SELECT project_master.ProjectID，project_master.OracleNum，project_master.BudgetYear，project_master.ProjectNum，project_master.Description，project_master.Description，project_master.Location，project_master.Region，project_master.ContactOwner，project_master .ContactDesigner，project_master.ProjectPriority，project_master.StartDate，project_master.InitialCloseDate，project_master.CurrentBonus，project_master.CurrentQty，project_master.TotalQty，project_master.TotalQty，project_master.SpendingYTD，project_master.SpendingLTD，project_master.SpendingAnnualBudget，project_master.SpendingForecast，project_master.SpendingMinimumForecast ，project_master.SpendingRemainingCommitted，project_master.SpendingSaveOver，project_master.SpendingPushPull，project_master.IsCanceled，project_master.UserComments，project_master.tmp_reporting_selected，project_master.rep_header，project_master.rep_budget，project_master.rep_work_completed，project_master.rep_work_planned_completed，project_master.rep_variance_reason，project_master.rep_work_to_complete，project_master .rep_cutovers，project_master.rep_issues_resolution，project_master.rep_variance_reason1，project_master.rep_work_to_complete1，project_master.rep_cutovers1，project_master.rep_issues_resolution1从project_master WHERE（（（project_master.ProjectID）= [projectID]））;
 

现在我不熟悉的任何访问，我试图让一个存储过程，但我只是没有看到任何选择这样做。此查询我创建似乎采取了projectID参数，但是当我尝试调用它在VB.NET和传递的参数，我在表中，而不是一个我希望让每一个记录。

 公共共享功能GetProjectByID（BYVAL projectID为整数）作为数据表
        昏暗paramaterList作为新的列表（中DataParameter）
        paramaterList.Add（新DataParameter（@ projectID，projectID，ParameterDirection.Input，OleDb.OleDbType.Integer））
        返回DAL.GetDataTableUsingReader（get_project_by_id，paramaterList）
端功能

公共共享功能创建（BYVAL projectID为整数）作为项目
        如果projectID＆LT; 1然后
            抛出新的ArgumentException（该项目的ID是无效的。）
        结束如果
        昏暗DT作为数据表= ProjectSQL.GetProjectByID（projectID）
        如果dt.Rows.Count＆LT; 1然后
            抛出新的DataException（不被提供的ID发现项目的记录。）
        结束如果
        返回重新包装（DT）（0）
端功能

朋友共享功能重新包装（BYVAL DT作为数据表）方式列表（项目）
    昏暗projectList作为新的列表（项目）
    对于我作为整数= 0〜dt.Rows.Count  -  1
        昏暗的数p作为新项目
        昏暗的行作为的DataRow = dt.Rows（I）
        p.ProjectID = Convert.ToInt32（行（ProjectID））
        p.OracleNum = Convert.ToString（行（OracleNum））
        p.BudgetYear = Convert.ToInt32（行（BudgetYear））
        '等等...
        projectList.Add（p）的
    下一个
    返回projectList
端功能
 

基本上，我调用Create（）方法，并传递一个整数，但我得到的所有记录返回，而不只是一个记录。

解决方案

WHERE（（（project_master.ProjectID）= [projectID]））

在数据库引擎看到 [projectID] ，它不知道你想要的是一个参数。它认为你指的是同一个名字的领域。因此，它返回的每一行，其中 project_master.ProjectID 等于本身，这应该是他的 ProjectID 不为空的所有行

给出的参数不同的名称，这不符合任何字段的数据源。

WHERE（（（project_master.ProjectID）= [which_projectID]））

I've created a query in Access that looks like this...

SELECT project_master.ProjectID, project_master.OracleNum, project_master.BudgetYear, project_master.ProjectNum, project_master.Description, project_master.Description, project_master.Location, project_master.Region, project_master.ContactOwner, project_master.ContactDesigner, project_master.ProjectPriority, project_master.StartDate, project_master.InitialCloseDate, project_master.CurrentBonus, project_master.CurrentQty, project_master.TotalQty, project_master.TotalQty, project_master.SpendingYTD, project_master.SpendingLTD, project_master.SpendingAnnualBudget, project_master.SpendingForecast, project_master.SpendingMinimumForecast, project_master.SpendingRemainingCommitted, project_master.SpendingSaveOver, project_master.SpendingPushPull, project_master.IsCanceled, project_master.UserComments, project_master.tmp_reporting_selected, project_master.rep_header, project_master.rep_budget, project_master.rep_work_completed, project_master.rep_work_planned_completed, project_master.rep_variance_reason, project_master.rep_work_to_complete, project_master.rep_cutovers, project_master.rep_issues_resolution, project_master.rep_variance_reason1, project_master.rep_work_to_complete1, project_master.rep_cutovers1, project_master.rep_issues_resolution1 FROM project_master WHERE (((project_master.ProjectID)=[projectID]));

Now I'm not familiar with Access whatsoever and I'm trying to make a stored procedure but I just don't see any option to do so. This query I've created seems to take the projectID parameter but when I try to call it in VB.NET and pass it the parameter, I get every record in the table instead of the one I want.

Public Shared Function GetProjectByID(ByVal projectID As Integer) As DataTable
        Dim paramaterList As New List(Of DataParameter)
        paramaterList.Add(New DataParameter("@projectID", projectID, ParameterDirection.Input, OleDb.OleDbType.Integer))
        Return DAL.GetDataTableUsingReader("get_project_by_id", paramaterList)
End Function

Public Shared Function Create(ByVal projectID As Integer) As Project
        If projectID < 1 Then
            Throw New ArgumentException("The project ID is invalid.")
        End If
        Dim dt As DataTable = ProjectSQL.GetProjectByID(projectID)
        If dt.Rows.Count < 1 Then
            Throw New DataException("No project record found by the provided ID.")
        End If
        Return Repackage(dt)(0)
End Function

Friend Shared Function Repackage(ByVal dt As DataTable) As List(Of Project)
    Dim projectList As New List(Of Project)
    For i As Integer = 0 To dt.Rows.Count - 1
        Dim p As New Project
        Dim row As DataRow = dt.Rows(i)
        p.ProjectID = Convert.ToInt32(row("ProjectID"))
        p.OracleNum = Convert.ToString(row("OracleNum"))
        p.BudgetYear = Convert.ToInt32(row("BudgetYear"))
        'etc...
        projectList.Add(p)
    Next
    Return projectList
End Function

Basically, I call the Create() Method and pass it an integer but I get all records returned instead of just one record.

解决方案

WHERE (((project_master.ProjectID)=[projectID]))

When the db engine sees [projectID], it does not know you intend that to be a parameter. It thinks you're referring to the field of the same name. So it returns every row where project_master.ProjectID is equal to itself, which should be all rows whose ProjectID is not null.

Give the parameter a different name, which doesn't match any of the fields in your data source.

WHERE (((project_master.ProjectID)=[which_projectID]))

这篇关于存储过程/参数化查询获取和传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！