Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Unanswered: user created sql queries

    Hi Folks,

    I am trying to make it that a user can just click on my form and generate a custom sql query.


    for example:
    If I had three list boxes,I would like my user to be able to click on any of the items in the three boxes and come up with a datagrid displaying the query results.(in effect creating an individual query

    i.e
    ListBox1
    robert redford
    dustin hoffman
    steve mc queen
    ListBox2
    butch Cassidy and the Sundance Kid
    the Sting
    straw dogs
    pappilon
    List 3
    1965
    1966(through to)
    2008)

    So then I want my user to come along click on Dustin Hoffman then click on a film(the sting perhaps) then the year,to find out if Dustin was actually in that film in that particular year...and if he was produce the details.
    This will create a "custom" sql statement like

    select * from lst 1 ...ect.

    What I would like to know is can this actually be done.

    This is only a small example of the datbase.I have thousands of films and name ect so it can get quite big.

    any help fully appreciated
    Gracie

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well sure. But you don't need to give the user the ability to create a true "custom query". (Somewhat akin to saying "I am trying to make it that a patient can just pick up a scalpel in my hospital and perform their own custom surgery.")
    Instead, create a stored procedure with the three parameters mentioned, and use it to return a filtered dataset.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2008
    Posts
    2
    HI Blindman,

    Thankyou for your reply.I have never written a stored procedure before.I might(and probably am) wrong about this bit isn't a stored procedure a bit of re-usable code in sql that can be called on from VB code.

    If this is so then wont I be back to square one.Lets say the user clicked on Dustin Hoffman,so this invoked a stored procedure that gave Dustins details,but how would a stored procedure be able to anticipate the entries into the remaining boxes and create a new SQL query from that.

    I think my idea is to allocate the decision made by the user(i.e Robert Redford) to an sql query,then add other another selection from the next drop down list(Paul Newman) and then have this added to the individual query.
    Is this at all possible.
    It's basically making a query up that depends on the users decision in the box.

    Does this make any more sense.!!! ...or am i talking through my botty

    Regards
    Gracie.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your stored procedure will take Actor, Movie, and Year as parameters.
    You can use other stored procedures or queries to populate the contents of your list boxes, depending upon previous selections made by the user.
    You are going to need to either spend some time reading about SQL and stored procedure in books online, or you are going to need to find assistance from a dba with a moderate level of SQL coding experience.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    your parameters will become variables for your WHERE statement. dig?


    Code:
    Create Proc MyProc
    @MyActor varchar(50)
    As
    SELECT column1,column2 
    FROM MyTable
    WHERE MyActorColumn = @MyActor
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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