MySQL DateTime Query Problem

can you show the code that runs this query?
also, can you output the SQL string value before it's executed?

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()

Select all Open in new window

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

Select all Open in new window

With your suggestion om_prakash_p, I still only get 2 records.

are you 200% sure execution the SQL on the same database/schema?

yea. make sure you are using the correct schema, like schema.tablename

How can I double check?
I have 2 MySQL databases and they contain identical information in both databases.

please check the connection string which is being used for this query

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.

The connection string is correct.

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.

>If I use db2 in my program, it doesn't work.
what error?

apart from that, I see no explanation on this "different behaviour"

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?

is any of the fields in the query being returned of type binary/blob/...

My error is  "Unknown column 'db2.clients.lname' in 'field list'"

There are no columns that are binary or blob.

>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()

Select all Open in new window

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()

Select all Open in new window

finally, it could be permission problems ... (the error message). ..
but 2 records returned instead of 8 ... can only data issues.

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?

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

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?

sorry, no, I don't see it ...

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).

can you double-check that the table/fields have indeed the same data type?

I just double checked. They do have the same data type.

My comment is the end solution. Please comment if you feel like you deserve the points.

Thanks for your help as always!