MySQL DateTime Query Problem

If I do a query in the MySQL workbench, I get 8 records (which is correct), but when I do the same query in my program, I only get the 1st 2 records. The only thing I'm really querying against is a tblARInvoice.Invoice_Date datetime field.

txtYY.text contains the last 2 digits of the year. ex 10 for 2010
txtMM.text contains 2 digits for the month. ex 09
txtDD.text contains 2 digits for the day. ex 03

The majority of the select statement is below.

What am I doing wrong to get different results?
SELECT 
Clients.FNAME, tblARInvoice.SEQ, tblARInvoice.Invoice_Date AS 'INVOICE_DATE', tblARInvoice.YEAR, tblARInvoice.strStatus AS 'STATUS', tblARReceipts.DATE as 'RECEIPT DATE' 
FROM tblARInvoice INNER JOIN Clients ON tblARInvoice.strClientSIN = Clients.SIN LEFT OUTER JOIN tblARReceipts ON tblARInvoice.strINVOICE_NUMBER = tblARReceipts.strInvoiceNumber 
WHERE (tblARInvoice.Invoice_Date <= '20" & txtYY.Text & "-" & txtMM.Text & "-" & txtDD.Text & "')  ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME

Open in new window

JohnnyBCJAsked:
Who is Participating?
 
JohnnyBCJAuthor Commented:
I believe the problem with this was with the time stamp. If you don't include the time stamp, MySQL assumes the time to be 00:00:00. If the record had a timestamp of 1:20:04, it wouldn't be included in the record. Maybe I'm wrong but the problem seems to be solved now.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show the code that runs this query?
also, can you output the SQL string value before it's executed?
0
 
JohnnyBCJAuthor Commented:
I sure can angellll

The string value is :

SELECT
Clients.FNAME, tblARInvoice.SEQ, tblARInvoice.Invoice_Date AS 'INVOICE_DATE', tblARInvoice.YEAR, tblARInvoice.strStatus AS 'STATUS', tblARReceipts.DATE as 'RECEIPT DATE'  FROM tblARInvoice INNER JOIN Clients ON tblARInvoice.strClientSIN = Clients.SIN LEFT OUTER JOIN tblARReceipts ON tblARInvoice.strINVOICE_NUMBER = tblARReceipts.strInvoiceNumber
WHERE (tblARInvoice.Invoice_Date <= '2010-09-03')  
ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME


The code that runs the query is below:

myConnection = New MySqlConnection(frmLogin.strConnection)
myConnection.Open()
Dim daReportARInvoice As New MySqlDataAdapter("SELECT Clients.FNAME, tblARInvoice.SEQ, tblARInvoice.Invoice_Date AS 'INVOICE_DATE', tblARInvoice.YEAR, tblARInvoice.strStatus AS 'STATUS', tblARReceipts.DATE as 'RECEIPT DATE' FROM tblARInvoice INNER JOIN Clients ON tblARInvoice.strClientSIN = Clients.SIN LEFT OUTER JOIN tblARReceipts ON tblARInvoice.strINVOICE_NUMBER = tblARReceipts.strInvoiceNumber WHERE (tblARInvoice.Invoice_Date <= '20" & txtYY.Text & "-" & txtMM.Text & "-" & txtDD.Text & "')  ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME", myConnection)
daReportARInvoice.Fill(dsReportARInvoice)
myConnection.Close()

Open in new window

0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Om PrakashCommented:
Convert string to date
str_to_date('10-10-2006', "%d-%m-%Y");

SELECT 
	Clients.FNAME, 
	tblARInvoice.SEQ, 
	tblARInvoice.Invoice_Date AS 'INVOICE_DATE', 
	tblARInvoice.YEAR, 
	tblARInvoice.strStatus AS 'STATUS', 
	tblARReceipts.DATE as 'RECEIPT DATE' 
FROM tblARInvoice 
	INNER JOIN Clients ON tblARInvoice.strClientSIN = Clients.SIN 
	LEFT OUTER JOIN tblARReceipts ON tblARInvoice.strINVOICE_NUMBER = tblARReceipts.strInvoiceNumber 
WHERE (tblARInvoice.Invoice_Date <= str_to_date('" & txtDD.Text & "-" & txtMM.Text & "-20" & txtYY.Text  & "', "%d-%m-%Y"))  ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME

Open in new window

0
 
JohnnyBCJAuthor Commented:
With your suggestion om_prakash_p, I still only get 2 records.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
are you 200% sure execution the SQL on the same database/schema?
0
 
first_majorCommented:
yea. make sure you are using the correct schema, like schema.tablename
0
 
JohnnyBCJAuthor Commented:
How can I double check?
I have 2 MySQL databases and they contain identical information in both databases.


0
 
Om PrakashCommented:
please check the connection string which is being used for this query
0
 
JohnnyBCJAuthor Commented:
If I try to do the query with db1.tablename.columnnames it works but if I try to do it with db2.tablename.columnnames, it fails.


0
 
JohnnyBCJAuthor Commented:
The connection string is correct.
0
 
JohnnyBCJAuthor Commented:
Let me rephrase myself:

In WorkBench, if I was to select either of the two identical databases (db1.table or db2.table) I get all 8 rows, which is correct.

If I use db1 in my program, I get 2 rows.

If I use db2 in my program, it doesn't work. I would believe this is because the connection string is pointing db1 and not db2.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>If I use db2 in my program, it doesn't work.
what error?

apart from that, I see no explanation on this "different behaviour"
0
 
JohnnyBCJAuthor Commented:
Another note:

In my connection string I have respect binary flags=false;

I have this because when I was doing queries I would get System.Byte[] except of the string values.

Would this possibly cause my problem?


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is any of the fields in the query being returned of type binary/blob/...
0
 
JohnnyBCJAuthor Commented:
My error is  "Unknown column 'db2.clients.lname' in 'field list'"

There are no columns that are binary or blob.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>My error is  "Unknown column 'db2.clients.lname' in 'field list'"

your query is written incorrectly, then...
you should use table aliases in your queries, like:

myConnection = New MySqlConnection(frmLogin.strConnection)
myConnection.Open()
Dim daReportARInvoice As New MySqlDataAdapter("SELECT c.FNAME, i.SEQ, i.Invoice_Date AS `INVOICE_DATE`, i.`YEAR`, i.strStatus AS `STATUS`, r.`DATE` as `RECEIPT DATE` FROM tblARInvoice i INNER JOIN Clients c ON i.strClientSIN = c.SIN LEFT OUTER JOIN tblARReceipts r ON i.strINVOICE_NUMBER = r.strInvoiceNumber WHERE (i.Invoice_Date <= '20" & txtYY.Text & "-" & txtMM.Text & "-" & txtDD.Text & "') ORDER BY r.`DATE`, c.LNAME, c.FNAME ", myConnection)
daReportARInvoice.Fill(dsReportARInvoice)
myConnection.Close()

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, if you now had to specify a db name in the query:
myConnection = New MySqlConnection(frmLogin.strConnection)
myConnection.Open()
Dim daReportARInvoice As New MySqlDataAdapter("SELECT c.FNAME, i.SEQ, i.Invoice_Date AS `INVOICE_DATE`, i.`YEAR`, i.strStatus AS `STATUS`, r.`DATE` as `RECEIPT DATE` FROM db2.tblARInvoice i INNER JOIN db2.Clients c ON i.strClientSIN = c.SIN LEFT OUTER JOIN db2.tblARReceipts r ON i.strINVOICE_NUMBER = r.strInvoiceNumber WHERE (i.Invoice_Date <= '20" & txtYY.Text & "-" & txtMM.Text & "-" & txtDD.Text & "') ORDER BY r.`DATE`, c.LNAME, c.FNAME ", myConnection)
daReportARInvoice.Fill(dsReportARInvoice)
myConnection.Close()

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
finally, it could be permission problems ... (the error message). ..
but 2 records returned instead of 8 ... can only data issues.
0
 
JohnnyBCJAuthor Commented:
I agree. That is the only thing that makes sense to me is it being data related, but I don't know where it could be.   My lack of experience with MySQL doesn't help any. I'm use to MS SQL.


Do you know of any possible tests I can do that might give us more information on what is causing this?




0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
reduce the queries to see as from when it returns to "normal" ...
or start with a minimum query, and add up conditions/joins to see as from when it starts going wrong ...
0
 
JohnnyBCJAuthor Commented:
I'm not quite sure what I did cause I've tried a number of things to get this to work.

The last thing I attempted to do was select put in db1. in front of all column names, and it worked. I get all 8 results.

I assumed it would automatically select the database that is mentioned in the connection string. On top of that, the two databases are identical so I don't understand on why mentioning the name of the database that is mentioned in the connection string would do anything to change the query results.

Any idea what could have caused this?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, no, I don't see it ...
0
 
JohnnyBCJAuthor Commented:
My Mistake!! This isn't over yet!

I accidently removed the invoice_date portion of the where statement. If I remove the "where arinvoice.invoice_date ... ' portion of the where statement, I get the correct results.

If I keep that portion in, I get no records (where previously I got 2 records).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you double-check that the table/fields have indeed the same data type?
0
 
JohnnyBCJAuthor Commented:
I just double checked. They do have the same data type.
0
 
JohnnyBCJAuthor Commented:
My comment is the end solution. Please comment if you feel like you deserve the points.

Thanks for your help as always!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.