how to make a parameter sql statement in vb6

Cheyenne

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

Barkeeper

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

Cheyenne

: 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

Barkeeper

: : 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

ilasabba

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/

SachinSoni

contact me at soni16_sachin@yahoo.co.in
I am a software developer and have good knowledge of Programming in VB-Access.

Adekdik

Try to use DtPicker.
for example, "Select * from Customer where Date > '" & DtPicker.value & "'"