Solved

No value given for one or more required parameters

Posted on 2014-11-17
48
131 Views
Last Modified: 2014-12-11
PUB_invmas TableHi,
 
 I have the following query in .CFM page.

      <CFQUERY NAME="GetPartDetail" datasource="Procomm">
            SELECT  inv-part-num,inv-desc, qty-on-hand, inv-rpt-cost
               FROM         pub_invmas
            WHERE   inv-part-num = '#Form.PartNo#'
      </CFQUERY>

When it is executed, I get the following error:
-----------------------------------------------------------------
 Error Executing Database Query.
No value given for one or more required parameters.
 
The error occurred in E:\webroot\NewsBags\Procomm\DisplayPartDetail.cfm: line 31

29 :             SELECT  PUB_invmas.inv-part-num,PUB_invmas.inv-desc, PUB_invmas.qty-on-hand, PUB_invmas.inv-rpt-cost
30 :                FROM         PUB_invmas
31 :             WHERE   PUB_invmas.inv-part-num = '#Form.PartNo#'
32 :       </CFQUERY>
33 :

SQL          SELECT PUB_invmas.inv-part-num,PUB_invmas.inv-desc, PUB_invmas.qty-on-hand, PUB_invmas.inv-rpt-cost FROM PUB_invmas WHERE PUB_invmas.inv-part-num = '76000075'
DATASOURCE         Procomm
VENDORERRORCODE         3088
SQLSTATE         &nbsp;
-----------------------------------------------------------------

The table "PUB_invmas" is linked to Progress database off Linux server thru ODBC driver. I can open this linked table (as seen in the screenshot). I can run a query within MS ACCESS, but it fails to run when I do it thru the website.
 
I have already tried to select different fields from the table. I also tried to add table name in front of every field (or column name) in SELECT query, but to no avail.

Can you help?
0
Comment
Question by:sglee
  • 24
  • 12
  • 11
  • +1
48 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 287 total points
Comment Utility
I think the hyphens in the column names are causing you trouble.   Try referring to the select values using the array/structure notation..

queryName["column-name"][rowNumber]

So if your query was called, GetData, you would get the column value like this..
#getData["inv-part-num"][getData.currentRow]#
0
 

Author Comment

by:sglee
Comment Utility
table@gdemaria
 I understand what you are trying to say, but I would like to see an example.
 Here is my query in CFM page. Can you show me an example using one of the fields like inv-part-num which is first column in the table like seen in the screenshot.

      <CFQUERY NAME="GetPartDetail" datasource="Procomm">
            SELECT  inv-part-num,inv-desc, qty-on-hand, inv-rpt-cost
               FROM         pub_invmas
            WHERE   inv-part-num = '#Form.PartNo#'
      </CFQUERY>
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 36 total points
Comment Utility
In Access, the "No Value Given" can mean you've misspelled a column name, so check those carefully. You might also try enclosing your column names in square brackets:

            SELECT  [inv-part-num], [inv-desc], [qty-on-hand], [inv-rpt-cost]
               FROM         [pub_invmas]
            WHERE   [inv-part-num] = '#Form.PartNo#'
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 287 total points
Comment Utility
Sure..

Just use this...

   #GetPartDetail["inv-part-num"][GetPartDetail.currentRow]#

Where you would normally use this...

   #GetPartDetail.inv-part-num#
0
 

Author Comment

by:sglee
Comment Utility
@Scott,

The following did not work.

            SELECT  [inv-part-num],[inv-desc], [qty-on-hand], [inv-rpt-cost]
               FROM         [pub_invmas]
            WHERE   [inv-part-num] = '#Form.PartNo#'
0
 

Author Comment

by:sglee
Comment Utility
@gdemaria
Just use this...     #GetPartDetail["inv-part-num"][GetPartDetail.currentRow]#
Where you would normally use this...     #GetPartDetail.inv-part-num#

Are you referring to the section of the code where I display the contents of fiels?
I don't see how I can use " #GetPartDetail["inv-part-num"][GetPartDetail.currentRow]# " in SELECT statement...
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 287 total points
Comment Utility
Are you referring to the section of the code where I display the contents of fiels

Yes, sorry, that is what I was referring to.

Now it strikes me that you are not even able to get past the CFQUERY statement?   I haven't see that before.  Is that the issue?

Perhaps you can create a view in the native database where the view just selects from the main table except using valid column names?   Or do you have no control over that database?
0
 

Author Comment

by:sglee
Comment Utility
ACCESS queryACCESS query result@gdemaria
The table is linked from Progress database (on another server on the same network) via ODBC.
I don't have control over the Progress database, however I can link to any table in that database and read. Like I said, if I can run a query in MS ACCESS against the same table (please see the screen shots - MS Access query design and run of query) but my CF code can't execute the same sql statement.
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 287 total points
Comment Utility
Right, CF does not expect a hyphen and putting [ brackets ] does not help


    Try changing  your query to SELECT *  instead of listing out the column names.

Also..

  It's been a really long time since I've touched MS Access, but if you can create a "view" that would help.   In MS Access create a Query of a Query and put an alias on each column name.    Then CF can just access the "clean" query
0
 

Author Comment

by:sglee
Comment Utility
@gdemaria

Having changed to SELECT .*, I am getting ODBC error. But when I open my ACCESS database, I can browse the contents of [pub_invmas] table without a problem. I can also run a query that brings up a part related information (like the screenshot in my previous posting ID: 40447892)

----------------------------------------------------------------------------
ODBC--connection to 'Progress' failed.

The error occurred in E:\webroot\NewsBags\Procomm\DisplayPartDetail.cfm: line 31

29 :             SELECT  [pub_invmas].*
30 :                FROM         [pub_invmas]
31 :             WHERE   [pub_invmas].[inv-part-num] = '#Form.PartNo#'
32 :       </CFQUERY>
33 :
----------------------------------------------------------------------------
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
EDIT:

Any change if you drop the table prefix and just use SELECT * ?

 SELECT  *
 FROM     pub_invmas
 WHERE   [inv-part-num] = '#Form.PartNo#'


Can you try GD's other suggestion?  Create a VIEW in MS Access that selects from that table, but alias the columns. Use names without dashes, ie

CREATE VIEW YourViewName
   AS
   SELECT   [inv-part-num] AS PartNum
                  , [inv-desc] AS PartDesc
                  , [qty-on-hand] AS QtyOnHand
                  , [inv-rpt-cost] AS RptCost
   FROM     [pub_invmas]

Open in new window

Test it in MS Access to be sure it works:


      SELECT PartNum, PartDesc, QtyOnHand, RptCost
      FROM    YourViewName

Open in new window


Then use the VIEW in your CFQuery
      SELECT PartNum, PartDesc, QtyOnHand, RptCost
      FROM    YourViewName
      WHERE  PartNum  = '#Form.PartNo#' 

Open in new window

0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 287 total points
Comment Utility
for the sake of testing, try this...   there is no WHERE CLAUSE so it removes all columns using the hyphen.

    SELECT  TOP 1  *   FROM pub_invmas

If TOP 1 causes an error, just remove it.


You should also try creating a query based on the query and using names that are valid in CF, etc.
0
 

Author Comment

by:sglee
Comment Utility
I dropped table prefix, but still same error.

 Error Executing Database Query.
No value given for one or more required parameters.
 
The error occurred in E:\webroot\NewsBags\Procomm\DisplayPartDetail.cfm: line 31

29 :             SELECT  *
30 :                FROM         pub_invmas
31 :             WHERE   pub_invmas.inv-part-num = '#Form.PartNo#'
32 :       </CFQUERY>
33 :

SQL          SELECT * FROM pub_invmas WHERE pub_invmas.inv-part-num = '76000075'
DATASOURCE         Procomm
VENDORERRORCODE         3088
SQLSTATE         &nbsp;
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 287 total points
Comment Utility
Please try the other suggestions, particularly creating the  "view" or query based on a query as agx and I both suggested.

Best bet is that the latter will be your solution.
0
 

Author Comment

by:sglee
Comment Utility
I don't know how to create a VIEW in MS ACCESS. I thought that applies to MS SQL or SQL in general. I can create a query in ACCESS and create a new table with new fields/columns that I need and try to open that in CFM page.
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 287 total points
Comment Utility
agx provided a link for you and gave you the command to create a view, its as easy as writing the select statement and adding an alias to each column name

http://msdn.microsoft.com/en-us/library/office/ff836312%28v=office.15%29.aspx

and see his examples above
0
 

Author Comment

by:sglee
Comment Utility
@gdemaria,

 That article applies to ACCESS 2013 version. I have 2003 version.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
Can you create a table or query based on another table/query?
0
 

Author Comment

by:sglee
Comment Utility
I can certainly create a CREATE table query in ACCESS that will read the table from PROGRESS database and it will create a new table with normal field names like PartNo, Desc,  Qty. But how can I run this Make-Table Query from CFM page?
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 177 total points
Comment Utility
>> That article applies to ACCESS 2013 version. I have 2003 version.

@sglee - I don't know if 2003 supports CREATE VIEW, but it definitely supports "views" or stored queries.  You may have to use the query wizard like you did earlier.  I don't remember it much, but after you select the columns - and *before* you close the wizard - select "View > SQL (SQL View)" in the top menu.  That should bring up the actual SQL statement.  Just overwrite it with the sql I posted earlier:

SELECT   [inv-part-num] AS PartNum
                  , [inv-desc] AS PartDesc
                  , [qty-on-hand] AS QtyOnHand
                  , [inv-rpt-cost] AS RptCost
   FROM     [pub_invmas]

Open in new window


Run it ensure it works. Then click the save button. It'll prompt you for a name. Enter "yourQueryName".  That's it.  You should be able to query that VIEW from CF, just like any table.
0
 

Author Comment

by:sglee
Comment Utility
@agx,

Let me tackle the following later this evening - about 3 hours later. I will post result.

SELECT   [inv-part-num] AS PartNum
                  , [inv-desc] AS PartDesc
                  , [qty-on-hand] AS QtyOnHand
                  , [inv-rpt-cost] AS RptCost
   FROM     [pub_invmas]
0
 

Author Comment

by:sglee
Comment Utility
@agx,
I just don't think there is such a thing as "Creating/Saving a VIEW" in MS ACESS like in MS SQL.
Having said that, I decided to create a new query that retrieve part information and append the result of the query to "PartTable" table.


      <CFQUERY NAME="InsertPartTable" datasource="Procomm">
        INSERT INTO PartTable ( [inv-part-num], [inv-desc] )
        SELECT PUB_invmas.[inv-part-num], PUB_invmas.[inv-desc]
        WHERE  PUB_invmas.[inv-part-num] = '#Form.PartNo#'
      </CFQUERY>

This time, I get a different  error like below. I am happy that I am not getting the same error "No value given for one or more required parameters." However it is still an error.

**********************************
Error Executing Database Query.

Syntax error (missing operator) in query expression 'PUB_invmas.[inv-desc] WHERE PUB_invmas.[inv-part-num] = '541779''.
 
The error occurred in E:\webroot\NewsBags\Procomm\DisplayPartDetail.cfm: line 37
35 :         INSERT INTO PartTable ( [inv-part-num], [inv-desc] )
36 :         SELECT PUB_invmas.[inv-part-num], PUB_invmas.[inv-desc]
37 :         WHERE  PUB_invmas.[inv-part-num] = '#Form.PartNo#'
38 :       </CFQUERY>
39 :
SQL         INSERT INTO PartTable ( [inv-part-num], [inv-desc] ) SELECT PUB_invmas.[inv-part-num], PUB_invmas.[inv-desc] WHERE PUB_invmas.[inv-part-num] = '541779'
DATASOURCE        Procomm
VENDORERRORCODE        3092
SQLSTATE        &nbsp;
**********************************
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 177 total points
Comment Utility
@sglee - I'm absolutely positive there is, because I've done it in Access 2000.  I'm probably not describing the steps clearly. I don't use Access much and used the wizard type screens even less. I was trying to describe the query wizard you had open here.  While that screen is open, click "View" in the top left menu. That will bring up the actual SQL code.
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 177 total points
Comment Utility
>> Syntax error (missing operator) in query expression

The FROM clause is missing:

INSERT INTO PartTable ( [inv-part-num], [inv-desc] )
SELECT PUB_invmas.[inv-part-num], PUB_invmas.[inv-desc]
FROM PUB_invmas
WHERE  PUB_invmas.[inv-part-num] = '#Form.PartNo#'
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:sglee
Comment Utility
@_agx_
after adding "FROM PUB_invmas" clause, I am getting a different error.

ODBC--connection to 'Progress' failed.
 
The error occurred in E:\webroot\NewsBags\Procomm\DisplayPartDetail.cfm: line 38
36 :         SELECT PUB_invmas.[inv-part-num], PUB_invmas.[inv-desc]
37 :         FROM PUB_invmas
38 :         WHERE  PUB_invmas.[inv-part-num] = '#Form.PartNo#'
39 :       </CFQUERY>
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 177 total points
Comment Utility
Sorry for all the back-to-back postings. I finally found some instructions for Access 2000/2003:

Next, highlight the tables that you wish to use in the query. In this example, we've selected the Customers table and clicked on the Add button. When you are done selecting the tables, click on the Close button.

Immediately after that step, click View >> SQL View from the top menu.  Replace whatever SQL is shown with:

SELECT   [inv-part-num] AS PartNum
                  , [inv-desc] AS PartDesc
                  , [qty-on-hand] AS QtyOnHand
                  , [inv-rpt-cost] AS RptCost
   FROM     [pub_invmas]

Open in new window


Then follow the instructions to save the query.
0
 

Author Comment

by:sglee
Comment Utility
Query Design ViewQuery SQL View
"While that screen is open, click "View" in the top left menu. That will bring up the actual SQL code. " --> are you referring to the screenshot above (SQL view)?
If that is the case, what do you want me to do with it?
Actually that is where I copy the SQL and paste into CFM page.
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 287 total points
Comment Utility
@sglee, just want to be clear that the key part here is that you are using different names for the columns because hyphens are causing the error in Coldfusion...

SELECT   [inv-part-num] AS PartNum
                                               ^^^^^^^^^
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 177 total points
Comment Utility
EDIT:

>> are you referring to the screenshot above (SQL view)?

Yes.  Replace all that SQL code with this:

SELECT   [inv-part-num] AS PartNum
                  , [inv-desc] AS PartDesc
                  , [qty-on-hand] AS QtyOnHand
                  , [inv-rpt-cost] AS RptCost
   FROM     [pub_invmas]

Open in new window


Then click the [ ! ] button to run it.  If it works without error, click "Save" and save it as: someQueryName

Then in your cfquery try:

       <cfquery ....>
               SELECT PartNum, PartDesc, QtyOnHand, RptCost
               FROM    someQueryName
       </cfquery>
0
 

Author Comment

by:sglee
Comment Utility
@_agx_
I got you. Let me try.
0
 

Author Comment

by:sglee
Comment Utility
Query in ACCESSI pasted your SQL into the query, SAVED AS "PartLookup" and ran it successfully. Please see the screenshot.

However when I used "PartLookup" query in Select statement in CFM page, I got this error:

Error Executing Database Query.

The Microsoft Jet database engine cannot find the input table or query 'PartLookup'. Make sure it exists and that its name is spelled correctly.
 
The error occurred in E:\webroot\NewsBags\Procomm\DisplayPartDetail.cfm: line 30
28 :
29 :
30 :       <CFQUERY NAME="GetPartDetail" datasource="Procomm">
31 :          SELECT PartNum, PartDesc, QtyOnHand, RptCost
32 :          FROM   PartLookup
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
In your image, the table is spelled   PartLookUkP   (note the last three letters are UKP, not UP)
0
 

Author Comment

by:sglee
Comment Utility
PartLookup Query and ResultThanks for pointing out "mis-spelling".
I corrected  the query name to "PartLookup" and ran the query, but the same error.
Anyway I added WHERE clause to specify the part number because PartLookup query (in ACCESS) retrieves all the parts from the table. But I go the same error.


Error Executing Database Query.

ODBC--connection to 'Progress' failed.
 
The error occurred in E:\webroot\NewsBags\Procomm\DisplayPartDetail.cfm: line 33
31 :          SELECT PartNum, PartDesc, QtyOnHand, RptCost
32 :          FROM   PartLookup
33 :          WHERE  PartNum = '#Form.PartNo#'
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
That error says you can't connect to the Progress Database.   You can try selecting from any table over there to see if you are just not able to connect or if there is an error specific to this table/view we're trying.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> try selecting from any table over there

Good idea. I assumed (perhaps incorrectly) you could connect to some tables - but not this one.

I don't use ODBC much anymore. It's quirky IMO.  I'm starting to think there's a more fundamental problem at work here...
0
 

Author Comment

by:sglee
Comment Utility
@agx
I agree with you that there may be a fundamental problem here. I will ask Progress database administrator to create a table with several fields without hypens for testing and then try to run a query against new table in CFM page tomorrow.
0
 

Author Comment

by:sglee
Comment Utility
My progress database admin created a new table with several records with column names without any hyphens.
Now I am getting the following error everytime.
 "Error Executing Database Query.
ODBC--connection to 'Progress' failed."

So it was NOT because of hyphens in column names. I think there is a fundamental issue in running a query in CFM page against the table linked via ODBC.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
They hyphens may still be an issue because you were getting a different error when using the other table.   But certainly the connection issue has to be resolved before getting back to the possible hyphen problem
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Just speculation, but some of the earlier errors might just be red herrings. DB's usually check syntax before attempting to execute a command. So it's possible you hit an before the db engine even got as far checking the connection to the remote db.

Is there a reason you must ODBC? I'd imagine Progress has a JDBC driver. Meaning you could set up a CF DSN to it directly.
0
 

Author Comment

by:sglee
Comment Utility
Can you elaborate on it
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
EDIT:
In CF you can set up a DSN of type "Other" for any database type that provides a JDBC driver. Usually vendors provide a jdbc jar when you install the db, or you may need to download it from their site.  

I don't think CF comes with a Progress jdbc driver built in, so you'll probably have to add the driver jar to your CF class path first and restart CF. Then you'll be able to create the DSN. Create a DSN and select type "Other", then enter the proper values:

* JDBC URL  (check vendor docs for the url string)
* Driver class (check vendor docs for classs name. note - it's case sensitive)
* Driver name (arbitrary)
* User name
* Password
0
 

Author Comment

by:sglee
Comment Utility
Ok. I will try that on my Cf v7 tonight.
0
 

Author Comment

by:sglee
Comment Utility
@agx
"Meaning you could set up a CF DSN to it directly. "
If I can create DSN using JDBC in CF, then are you saying that I could run a query directly against tables in Progress database instead of running a query against Linked tables in ACCESS database?
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Yep.  As long as they provide a JDBC driver you should be able to connect the Progress db (and query it) directly.
0
 

Author Comment

by:sglee
Comment Utility
Have you done it in Coldfusion?
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
(Edit)

I've never used Progress, but I've used type "Other" DSN's many times with other db's. SQL Server, MySQL, Access, Oracle, HSQL, etc...

JDBC drivers are pretty standard. It's basically the same as ODBC, just for java. All you need is the driver jar, the vendor's jdbc url, class name and credentials. The driver jar takes care of the rest.
0
 

Author Comment

by:sglee
Comment Utility
Other Driver in CF v7
I created other drive per your suggestion.
 jdbc:datadirect:openedge://192.168.x.x:2550;databasename=xxxx
But I get an error like below:
----------------------------------------------------------------------------------------------------------
Connection verification failed for data source: Progress
java.sql.SQLException: No suitable driver available for Progress, please check the driver setting in resources file, error: null
The root cause was that: java.sql.SQLException: No suitable driver available for Progress, please check the driver setting in resources file, error: null
----------------------------------------------------------------------------------------------------------
0
 

Author Comment

by:sglee
Comment Utility
Since I am not getting no solution using ODBC, I am going to create a new question with JDBC.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now