来自ODBC的SQL查询中的Excel参数“错误-使用问号"WHERE XXX =?"时未为一个或多个必需参数提供值 [英] Excel parameters from SQL Query from ODBC "Error - No Value given for one or more required parameters when using question mark `WHERE XXX = ?`

问题描述

我有一个与MS SQL服务器建立SQLOLEDB连接的Excel文件，

I have an Excel file with an SQLOLEDB connection to a MS SQL server,

当我使用Oracle链接执行此操作时，我可以简单地在查询中放入WHERE XXX = ?来提示Excel中的值，但是使用MSSQL进行操作时会出现以下错误:

When I do this with an Oracle link I can simply put WHERE XXX = ? in my query to prompt for values in Excel, however doing it with MSSQL I am getting the following error:

没有为一个或多个必需参数提供值

No Value given for one or more required parameters

当尝试将基本的excel查询从值设置为?

When trying to parameterise a basic excel query from a value to ?

如果我使用这样的值，我会得到结果:

If i use values like this I get results:

SELECT * 
FROM srv.stats
WHERE srv.stats.[year] = '2016'
AND srv.stats.[quarter] = '1'

当我添加参数?时，出现上述错误.

When I add in the parameter ? I get the above error.

SELECT * 
FROM srv.stats
WHERE srv.stats.[year] = ?
AND srv.stats.[quarter] = ?

我希望不使用VB就可以做到这一点.

I want to be able to do this without using VB.

推荐答案

由于ODBC连接的MS SQL源本身不允许您在SQL查询中使用WHERE xxx = ?代码(与Oracle连接不同)，因此您需要进行欺骗Excel通过构建Microsoft Query然后覆盖它来允许来自MS SQL数据源的参数.

Since MS SQL sources from ODBC connections don't inherently allow you to use the WHERE xxx = ? code in your SQL query (unlike Oracle connections) you need to spoof Excel to allow parameters from a MS SQL data source by building a Microsoft Query and then overwriting it.

打开一个新的Excel文件，然后转到Data选项卡，选择From Other Sources下拉菜单，然后选择From Microsoft Query.

Open a new Excel file and go to the Data tab, choose the From Other Sources drop down and select From Microsoft Query.

将出现Choose Data Source窗口.选择一个现有的数据源或通过选择<New Data Source>并与服务器建立新的连接，然后单击确定".

The Choose Data Source window will appear. Choose an existing datasource or set up a new connection to your server by selecting <New Data Source> and click OK.

完成后，您将看到查询向导窗口打开以选择表和列，因为稍后您将要添加自己的SQL查询时，只需选择查询中的一个表并将其添加到Columns in your query:部分通过使用>按钮.对于接下来的两个窗口，只需单击Next，然后最后单击Finish.

Once done you will see the query wizard window open to select tables and columns, as you're going to be adding your own SQL query later just select one table that is in your query and add it to the Columns in your query: section by using the > button. For the next 2 windows just click Next and then finally Finish.

然后将提示您选择要在Import Data窗口中查看数据的方式，首先单击Properties...按钮.

You will then be prompted to select how you want to view the data in the Import Data window, first of all click the Properties... button.

然后是Definition选项卡，在Command text:框中，您将有一个SELECT语句，在下面，您需要为实际查询中的金额添加WHERE子句.这些必须以以下格式添加:

And then the Definition tab, in the Command text: box you will have a SELECT statement, below there you will need to add WHERE clauses for the amount you have in your actual query. These need to be added in the format of:

WHERE 1 = ?
AND 2 = ?
AND 3 = ?

完成此操作后，单击确定"返回到导入数据"窗口并选择您的输出类型.表格，数据透视表报表或数据透视图和数据透视表报表，具体取决于您要如何显示数据.

Once this is done click OK to go back to the Import Data window and select your output type; Table, PivotTable report or PivotChart and PivotTable Report depending on what how you want to display your data.

然后将提示您为每个参数输入一个值.如果要从单元格"中获取这些值，请按照将要放入实际参数的顺序选择它们的位置.输入参数源后，返回到数据"选项卡，单击连接"，然后进入定义"选项卡.

You will then be prompted to enter a value for each parameter. If you are getting these values from Cells choose the location of them now in the order you will be putting in your actual parameters. When you have entered your parameter sources go back to the Data tab and click connections and then into the definition tab.

现在在Command text:框中，使用WHERE子句= ?中的参数粘贴您的实际SQL查询，并以与您定义源相同的顺序单击确定"，现在应该像通常那样填充数据并使用您的参数.

Now in the Command text: box paste in your actual SQL query with your parameters in the WHERE clause = ? and in the same order your defined the sources and click OK, Your data should now populate as it usually does with your parameters being used.

这篇关于来自ODBC的SQL查询中的Excel参数“错误-使用问号"WHERE XXX =?"时未为一个或多个必需参数提供值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！