Alpha Video Training
Results 1 to 6 of 6

Thread: sql query statement?

  1. #1
    Member
    Real Name
    ken tjia
    Join Date
    Jan 2006
    Posts
    110

    Default sql query statement?

    dim abcd as c
    abcd = var->sql_upd_search_dept_id
    ui_msg_box(abcd,abcd)
    sqlStatement = <<%sql%
    SELECT upd,upd_desc,mftr_number FROM sku where class_main_id = '"+abcd+"'
    %sql%
    'Execute the Query
    flagResult = cn.Execute(sqlStatement)
    if flagResult = .f. then
    ui_msg_box("Error",cn.CallResult.text)
    cn.close()
    end
    end if

    The above codes seems straight forward
    if i use where class_main_id = '001'
    i get 245 records.ok

    that works, i therefore subsitute it with a variable
    where abcd = "001"

    that gave me an error, can someone see the problem?

    appreciate your comment
    thanks

    ken tjia

    Result set row requested not found, i know

  2. #2
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: sql query statement?

    Try,

    Code:
     sqlStatement = <<%sql%
    SELECT upd,upd_desc,mftr_number FROM sku where class_main_id = ' %sql% +abcd+ <<%sql%
    ' %sql%
    or
    Code:
     sqlStatement = "SELECT upd,upd_desc,mftr_number FROM sku where class_main_id = '"+abcd+"'"
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: sql query statement?

    Don't know much about this but maybe you can use the genie to construct the right expression?

    If the SQL SELECT statement includes a WHERE clause, you can use a ? as a placeholder in the statement to reference variables (or constant values). SELECT CustomerName, CustomerID FROM Customers WHERE City = ? ORDER BY CustomerName. You can then specify how variables map to each ? in the WHERE clause.
    Parameter Mapping

    If you have defined a filter on the SQL Genie or the SQL SELECT tabs, the SQL expression will contain a "?" that refers to a variable or constant. The Parameter Mapping tab will appear.

    Select each parameter that appears in the Specify variables/values list box.

    Optionally, change the mapping selection in the Parameter maps to list box.

    Optionally, change the name of the variable in the Variable name list box.

    Optionally, change the value in the Constant value field.

    Select the data type of the constant or variable in the Parameter type list box.

  4. #4
    Member
    Real Name
    ken tjia
    Join Date
    Jan 2006
    Posts
    110

    Default Re: sql query statement?

    Hi Tim,
    try both concept, i originally got these from the genie and have used sql query the basic way according to book, but this time around, i am running it to populate a listbox, therefore use the concept.

    again, i know where xxx = '001' works, the in the past i simply add the variable as decribed, but this time around just not going anywhere.

    will see what else,

    ken tjia

    Quote Originally Posted by Tim Kiebert View Post
    Try,

    Code:
     sqlStatement = <<%sql%
    SELECT upd,upd_desc,mftr_number FROM sku where class_main_id = ' %sql% +abcd+ <<%sql%
    ' %sql%
    or
    Code:
     sqlStatement = "SELECT upd,upd_desc,mftr_number FROM sku where class_main_id = '"+abcd+"'"

  5. #5
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: sql query statement?

    Quote Originally Posted by ktjia@ampmservice.com View Post
    dim abcd as c
    abcd = var->sql_upd_search_dept_id
    ui_msg_box(abcd,abcd)
    sqlStatement = <<%sql%
    SELECT upd,upd_desc,mftr_number FROM sku where class_main_id = '"+abcd+"'
    %sql%
    'Execute the Query
    flagResult = cn.Execute(sqlStatement)
    if flagResult = .f. then
    ui_msg_box("Error",cn.CallResult.text)
    cn.close()
    end
    end if
    Code:
     ui_msg_box(abcd,abcd)
    sqlStatement = <<%sql%
    SELECT upd,upd_desc,mftr_number FROM sku where class_main_id = '"+abcd+"'
    %sql%
     ui_msg_box(abcd,sqlStatement)
    Ken

    How about adding this message box to see what the sqlstatment is prior to executing it?

    Or use debug(1) to see it...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  6. #6
    Member
    Real Name
    ken tjia
    Join Date
    Jan 2006
    Posts
    110

    Default Re: sql query statement?

    well,

    I finally took <<%sql% off for good and well they run just like the way i want
    not sure what they are hi


    sqlStatement = "SELECT upd,upd_desc,mftr_number FROM sku where class_main_id = '"+abcd+"'"

    i have to say that i copied the original codes from genie

    thanks everyone,

    ken tjia

Similar Threads

  1. SQL select statement (Help) (:-0>
    By Hansolo in forum Web Application Server v6
    Replies: 4
    Last Post: 03-17-2005, 06:12 AM
  2. Using Session Variable in SQL Select Statement
    By Louis Nickerson in forum Web Application Server v6
    Replies: 2
    Last Post: 02-25-2005, 08:46 AM
  3. query SQL database
    By EdwardTsai in forum Alpha Five Version 6
    Replies: 3
    Last Post: 10-23-2004, 10:19 AM
  4. sql query
    By jeffemery in forum Alpha Five Version 4
    Replies: 11
    Last Post: 05-11-2001, 09:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •