[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

sql statement in vb6

hi
i am trying to do querie in vb but i am not getting any where with it

here goes
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (LogDate=#20/06/2002#));"

this returns 0 records but if i do the select as follows
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur'));"
it will find records with Arthur in them

and if i do the other half
MySql = "SELECT * FROM Log WHERE ((LogDate =#20/06/2002#));"
this returns records with 21/6/2002 in them
but when i put the 2 together with an and it does'nt work
i was told that you could copy the sql queurie out of access and just replace the " with ' but it doesnt work either
0
arthur_moody
Asked:
arthur_moody
  • 7
  • 4
  • 3
  • +4
1 Solution
 
inthedarkCommented:
I.H.O. you must always use braces arround all variables and file names. Beacause the syntax is different between Access and ADO (the reserved words are different). Also in this way you future proof your statemnents incase MS change the spec of the reserved words list.

Example:

SQL ="Select * from [log] Where [Name] = etc....

Sometimes you even get a not very helpfull error message.

Try it....it may help.


0
 
arthur_moodyAuthor Commented:
hi
i just tried your suggestion for putting brackets around the feild name but i did'nt help
thanks
anyway
0
 
PaulHewsCommented:
Are there any records in the table with *both* Arthur as name and Jun 20 for the date?  Because unless a record matches both criteria, it will not produce any results.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
arthur_moodyAuthor Commented:
hi Paul
yes there is a record in the database that meets both these requirements
thanks
0
 
PaulHewsCommented:
Does the query work in Access then?
0
 
PaulHewsCommented:
Format your date as follows:

MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (LogDate=#2002/06/20#));"

I suspect that the provider is not understanding the date the way you provided it.  yyyy/mm/dd will never be misunderstood.
0
 
arthur_moodyAuthor Commented:
i have just tried your suggestion Paul with the date but it still does'nt work i also dont think its the date
formating because if i try the queurie just for the date MySql = "SELECT * FROM Log WHERE ((LogDate #20/06/2002#));"
it works and if i just querie for the name

MySql = "SELECT * FROM Log WHERE ((Name ='Arthur'));"
it works it just does'nt work when i querie for both
the querie works correctly in access
0
 
zuijdhoekCommented:
Statement looks OK to me.
This may sound a little bit silly, but are you sure your recordset contains records which satisfy both criteria?
0
 
zuijdhoekCommented:
Statement looks OK to me.
This may sound a little bit silly, but are you sure your recordset contains records which satisfy both criteria?
0
 
arthur_moodyAuthor Commented:
hi  zuijdhoek
i have a access database with a table called log and it has only two fields #1 Name #2 LogDate
and ther is two recordes
the first record Name: Arthur LogDate: 20/06/2002
0
 
zuijdhoekCommented:
Doesn't make sense to me. Are you sure field Date is Date/Time type? Are you sure the resultset is empty?
What happens if you try to loop through the resultset?
How do you retrieve the number of affected records?
How did you establish connection between VB and Access?

Mark
0
 
sanjaykattimaniCommented:
try
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (LogDate=#06/20/2002#));"
0
 
Alon HirschSoftware Development ManagerCommented:
Hi,

It could be that the date field contains time information as well. This would mean that unless the date (and time) was 20/06/2002 12:00:00 it would not be picked up.

You can either use Format in your query :
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (Format(LogDate,"dd/mm/yyyy")='20/06/2002'));"

or you can try passing the dates to the query as mm/dd/yyyy as Access likes them :
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (LogDate=#06/20/2002#));"

HTH,
Alon
0
 
arthur_moodyAuthor Commented:
hi everyone
i have just sorted the problem i was doin up a sample of my problem by copying out the relevant code form my project and pasting it into a new vb project to post for eveyone to look at then i ran it and it works so i then copied this into an older version of the project before i started adding the database stuff and it know works nothing was changed in the code so it beats me as to why it didnt work
0
 
Ryan ChongCommented:
If the SQL statement is the same, there is no why it didn't works! So the possibilty maybe is your database records is already incorrect previously so that no records are selected.

And personally think that a grade 'C' is not fair to PaulHews. And i agree the Paul's point that: "I suspect that the provider is not understanding the date the way you provided it.  yyyy/mm/dd will never be misunderstood"
0
 
arthur_moodyAuthor Commented:
i did not change the code at all i copied and pasted the code from my project unmodified and pasted it into a new project same variables same database connection there was no typing or changing at all it is using the same sql with the same date format i DID'NT change anything.

i only gave a c because i did not know who to give the points to because i tried everything that was posted here but nothing worked so i had to give the points to someone and Paul submitted the best things to try it is not a reflection on what i thought of his programming skills.

if you are unhappy with this and would like to have a look at the original vb project(working and not working) let me know and i will post them
0
 
arthur_moodyAuthor Commented:
i did not change the code at all i copied and pasted the code from my project unmodified and pasted it into a new project same variables same database connection there was no typing or changing at all it is using the same sql with the same date format i DID'NT change anything.

i only gave a c because i did not know who to give the points to because i tried everything that was posted here but nothing worked so i had to give the points to someone and Paul submitted the best things to try it is not a reflection on what i thought of his programming skills.

if you are unhappy with this and would like to have a look at the original vb project(working and not working) let me know and i will post them
0
 
PaulHewsCommented:
Just for future reference, Arthur, you do have options if you find the solution yourself.  Just post a note to customer service to either delete or add the question to the PAQ.  Either way, your question points will be refunded.

If you are awarding the question, the grade is an issue for me and many experts.  Not just because it reflects on my ability (I think I have a pretty good record overall) but because it can be a sign of gratitude or contempt.  Most of us feel like a "C" grade is closer to contempt, especially when I didn't say I had "the answer," I was only making suggestions.

And by the way, if you format your dates as dd/mm/yyyy, it is quite possible you will get it wrong, because the tenth of june will appear as 10/06/2002 and the provider can interpret it as October 6, 2002 despite what your locale settings say.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 4
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now