Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

how to make a parameter sql statement in vb6

Howdy,
I may need some help/advice for the following issue,
I have a database table with all kinds of sqlstatement in it, in order to show data from the database.
When I use any of the statements by clicking of them, and the executing needs a date then I always have to edit the statement.
I'll tryed to get a parameter in the staement bud always get an error.

for example:
PARAMETERS Begin Date; SELECT * FROM Customers WHERE DateMemberShip > # & Begin & #
this won't work, I almost explored the internet all day, found many items bud nothing specific for VB6, I have read my books, can't find a solution, this is driving me crazy.
Can someone please give me an example or a hint how to solve this
I'll try to acces an Access database using an ADO connection.
Thanks in advance
Cheyenne

Comments

  • BarkeeperBarkeeper Posts: 335Member
    Why are you storing SQL-Statements with Parameters in your Database?
    IMHO it's much easier to create the SQL-Statement in your Code and then execute it directly.

    Example:
    Dim SQLString as String
    Dim Begin as Date

    'Fill Code in here to get a valid Date

    SQLString="SELECT * FROM Customers WHERE DateMemberShip>#" & Begin & "#"

    DB.OpenRecordset(SQLString,dbOpenDynaset) <-- This is a Statement from DAO, not ADO but i think you get the Idea

    : Howdy,
    : I may need some help/advice for the following issue,
    : I have a database table with all kinds of sqlstatement in it, in
    : order to show data from the database.
    : When I use any of the statements by clicking of them, and the
    : executing needs a date then I always have to edit the statement.
    : I'll tryed to get a parameter in the staement bud always get an
    : error.
    :
    : for example:
    : PARAMETERS Begin Date; SELECT * FROM Customers WHERE DateMemberShip
    : > # & Begin & #
    : this won't work, I almost explored the internet all day, found many
    : items bud nothing specific for VB6, I have read my books, can't find
    : a solution, this is driving me crazy.
    : Can someone please give me an example or a hint how to solve this
    : I'll try to acces an Access database using an ADO connection.
    : Thanks in advance
    : Cheyenne
    :

    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein
  • CheyenneCheyenne Posts: 28Member
    : Why are you storing SQL-Statements with Parameters in your Database?
    : IMHO it's much easier to create the SQL-Statement in your Code and
    : then execute it directly.
    :
    : Example:
    : Dim SQLString as String
    : Dim Begin as Date
    :
    : 'Fill Code in here to get a valid Date
    :
    : SQLString="SELECT * FROM Customers WHERE DateMemberShip>#" & Begin &
    : "#"
    :
    : DB.OpenRecordset(SQLString,dbOpenDynaset) <-- This is a Statement
    : from DAO, not ADO but i think you get the Idea
    :
    : : Howdy,
    : : I may need some help/advice for the following issue,
    : : I have a database table with all kinds of sqlstatement in it, in
    : : order to show data from the database.
    : : When I use any of the statements by clicking of them, and the
    : : executing needs a date then I always have to edit the statement.
    : : I'll tryed to get a parameter in the staement bud always get an
    : : error.
    : :
    : : for example:
    : : PARAMETERS Begin Date; SELECT * FROM Customers WHERE DateMemberShip
    : : > # & Begin & #
    : : this won't work, I almost explored the internet all day, found many
    : : items bud nothing specific for VB6, I have read my books, can't find
    : : a solution, this is driving me crazy.
    : : Can someone please give me an example or a hint how to solve this
    : : I'll try to acces an Access database using an ADO connection.
    : : Thanks in advance
    : : Cheyenne
    : :
    :
    : ------------------------------------------
    : Only stupidity of mankind and the universe
    : are infinite, but i'm not sure concerning
    : the universe. A. Einstein

    Hello Barkeeper,

    at first thanks for the replay, but, I have a programm that is beeing used by someone(s) that doesn't know how to make a sql statement, so I give him (them) a listbox with all kinds of statements and the purpose off them.
    When the user select the statement just the date should be filled in to execute the statement, altough that's the idee.
    Cheyenne
  • BarkeeperBarkeeper Posts: 335Member
    : : Why are you storing SQL-Statements with Parameters in your Database?
    : : IMHO it's much easier to create the SQL-Statement in your Code and
    : : then execute it directly.
    : :
    : : Example:
    : : Dim SQLString as String
    : : Dim Begin as Date
    : :
    : : 'Fill Code in here to get a valid Date
    : :
    : : SQLString="SELECT * FROM Customers WHERE DateMemberShip>#" & Begin &
    : : "#"
    : :
    : : DB.OpenRecordset(SQLString,dbOpenDynaset) <-- This is a Statement
    : : from DAO, not ADO but i think you get the Idea
    : :
    *snipp*
    : :
    : : ------------------------------------------
    : : Only stupidity of mankind and the universe
    : : are infinite, but i'm not sure concerning
    : : the universe. A. Einstein
    :
    : Hello Barkeeper,
    :
    : at first thanks for the replay, but, I have a programm that is
    : beeing used by someone(s) that doesn't know how to make a sql
    : statement, so I give him (them) a listbox with all kinds of
    : statements and the purpose off them.
    : When the user select the statement just the date should be filled in
    : to execute the statement, altough that's the idee.
    : Cheyenne
    :

    OK, but that's still not a reason to store the SQL-Statements as procedures in the DB.

    Gathering from your words, your offering on your form a List with generic SQL-Statements (e.g. the SQL-Statement as above with after selecting it in the Listbox a Input-Box popping up asking for a valid date). Why not treat the Items in your ListBox just as Strings? After hitting an item in the ListBox your code (to which the user doesn't have access to!!!) proccesses something like this:

    [code]
    Sub ListBox_Click(ByVal Index as Integer)
    Dim SQLString as String
    Dim InputDate as String 'Or Date - whatever you prefer

    SQLString=ListBox.Text
    InputDate=InputBox("Input a valid Date")
    'Fill in some code to validate InputDate such If Not IsDate(InputDate) then DoSomething
    SQLString=SQLString & InputDate
    Set RS=DB.OpenRecordset(SQLString,dbOpendynaset)

    End Sub
    [/code]
    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein
  • ilasabbailasabba Posts: 13Member
    Follow this URL. You may find the answer there. I have explained all about using sql in vb6 in this lesson

    http://visualbasic.freetutes.com/learn-vb6-advanced/lesson9/


  • SachinSoniSachinSoni Posts: 36Member
    contact me at soni16_sachin@yahoo.co.in
    I am a software developer and have good knowledge of Programming in VB-Access.
  • AdekdikAdekdik Posts: 1Member
    Try to use DtPicker.
    for example, "Select * from Customer where Date > '" & DtPicker.value & "'"
Sign In or Register to comment.