Using .net gridview control with AS/400

5 pts.
Tags:
.NET 2.0
AS/400
AS/400 Parameters
GridView
GridView Control
SQL Query
I am tiring to update a table in the as400 with the .net gridview control and I am having trouble binding the parameters to the sql query. My select query work if I use a liked server but it is slow. Next I tried the odbc driver but my select query is not working with the same input parameters. When I execute the code from the query analyzer it fails: SELECT EPESTMM.DATE_INQUIRY_ES, EPESTMM.ESTIMATE_NUMBER_ES AS EST_NO, EPESTMM.STATUS_ES AS STATUS, EPESTMM.ESTIMATOR_ES AS REP, EPESTMM.SHIP_TO_NAME_ES AS SH_NAME, EPESTMM.SHIP_CITY_ES AS SH_CITY, EPESTMM.SHIP_STATE_ES AS SH_ST, EPESTMM.TOTAL_PRICE_ES AS TOTAT_PRICE, EPESTMM.CUSTOMER_NAME_ES AS BILL_TO, EPESTMM.JOB_TITLE_ES AS NOTES, WPEMADI.OUTSIDE_SALES_EMADI, CASE WHEN INSIDE_SALES_EMADI = '23' THEN 'NAmilligad' WHEN INSIDE_SALES_EMADI = '22' THEN 'NAolingerr' WHEN INSIDE_SALES_EMADI = '26 ' THEN 'NAJeffcoat ' WHEN INSIDE_SALES_EMADI = '25' THEN 'NAduncanw' WHEN INSIDE_SALES_EMADI = '23' THEN 'NADeLaGarc' WHEN INSIDE_SALES_EMADI = '14 ' THEN 'NAjohnsond' END AS ISRUSER, WPEMADI.INSIDE_SALES_EMADI, CASE WHEN OUTSIDE_SALES_EMADI = '16' THEN 'Nick Szczechowski' WHEN OUTSIDE_SALES_EMADI = '14' THEN 'Intercompany' WHEN OUTSIDE_SALES_EMADI = '1' THEN 'Bill Davis' WHEN OUTSIDE_SALES_EMADI = '10' THEN 'Marty Magill' WHEN OUTSIDE_SALES_EMADI = '11' THEN 'Wanda Duncan' WHEN OUTSIDE_SALES_EMADI = '12' THEN 'Wanda Duncan' WHEN OUTSIDE_SALES_EMADI = '13' THEN 'Wanda Duncan' WHEN OUTSIDE_SALES_EMADI = '2' THEN 'OPEN' WHEN OUTSIDE_SALES_EMADI = '3' THEN 'Pete Bortnichak' WHEN OUTSIDE_SALES_EMADI = '4' THEN 'Tim Mclaughlin' WHEN OUTSIDE_SALES_EMADI = '5' THEN 'Russ Kuc' WHEN OUTSIDE_SALES_EMADI = '6' THEN 'Joe Craig' WHEN OUTSIDE_SALES_EMADI = '7' THEN 'David Cross' WHEN OUTSIDE_SALES_EMADI = '8' THEN 'Mark Ziegler' WHEN OUTSIDE_SALES_EMADI = '9' THEN 'Brian Cappallo' WHEN OUTSIDE_SALES_EMADI = 'NO' THEN 'NA' WHEN OUTSIDE_SALES_EMADI = '15' THEN 'Wanda Duncan' END AS OSR, WPMEDT.YEAR_DT, WPMEDT.QUARTER_DT, WPMEDT.MONTH_DT FROM TWCSYS."JBS11D.T".EPESTMM EPESTMM, TWCSYS."JBMOD11D.T".WPEMADI WPEMADI, TWCSYS."JBMOD11D.T".WPMEDT WPMEDT WHERE EPESTMM.ESTIMATE_NUMBER_ES = WPEMADI.ESTIMATE_NUMBER_EMADI AND EPESTMM.DATE_INQUIRY_ES >= WPMEDT.BEGINNING_DATE_DT AND EPESTMM.DATE_INQUIRY_ES <= WPMEDT.ENDING_DATE_DT AND (WPMEDT.YEAR_DT = 201000) AND (WPMEDT.QUARTER_DT = ?) AND (CASE WHEN INSIDE_SALES_EMADI = '23' THEN 'NAmilligad' WHEN INSIDE_SALES_EMADI = '22' THEN 'NAolingerr' WHEN INSIDE_SALES_EMADI = '26 ' THEN 'NAJeffcoat ' WHEN INSIDE_SALES_EMADI = '25' THEN 'NAduncanw' WHEN INSIDE_SALES_EMADI = '23' THEN 'NADeLaGarc' WHEN INSIDE_SALES_EMADI = '14 ' THEN 'NAjohnsond' END = ?) in addition I need to filter this data using the year. how can you get current year from the system? thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

A few things to check…

Look into the parameters for your ODBC connection. In particular, the Library List and Naming Convention. If naming convention is *SYS rather than *SQL and you are separating the library and file with a period, that could be a problem. Likewise, if the library list is not properly set (or it is *USRLIBL, but the library is not in *USRLIBL), you could have errors:

<a href=”http://www.itjungle.com/fhg/fhg072005-story03.html”></a>

Also, you could do yourself a favor and look into IBM’s provider, iDB2Connection for better performance:

<a href=”http://www.itjungle.com/fhg/fhg101304-story02.html”></a>

Lastly, when having difficulty with more complex SQL statements, you may want to pare it down to bare bones if you’re not getting enough feedback from the error message to isolate the issue. Get it working in slimmed-down form, then build it back up until you hit the error. Using this approach can help you identify the problem. Of course, if the problem IS with the library list, naming convention, user id, password, etc. it is going to hit the wall regardless of how much you simplify it.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Yo might want to let the community know how it fails. Do you get errors ? What is the error message ? Needless to say, this query would be much simpler with a proper design in which all those names were stored in a table with their corresponding code or id.
    69,175 pointsBadges:
    report
  • TomLiotta
    ...my select query is not working with the same input parameters. You should show us what "parameters" you use. Show us how you run the query. how can you get current year from the system? One basic way is:
    select year(current_date) from...
    Tom
    125,585 pointsBadges:
    report
  • slateken
    Sample links didn't show, so I'll just post them as text... Parameters: http://www.itjungle.com/fhg/fhg072005-story03.html iDB2Connection: http://www.itjungle.com/fhg/fhg101304-story02.html
    230 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following