Avatar of JohnnyBCJ
JohnnyBCJFlag for Canada

asked on 

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

MySQL ServerDatabases

Avatar of undefined
Last Comment
JohnnyBCJ
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

can you show the code that runs this query?
also, can you output the SQL string value before it's executed?
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

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

Avatar of Om Prakash
Om Prakash
Flag of India image

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

Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

With your suggestion om_prakash_p, I still only get 2 records.
are you 200% sure execution the SQL on the same database/schema?
Avatar of first_major
first_major

yea. make sure you are using the correct schema, like schema.tablename
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

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


Avatar of Om Prakash
Om Prakash
Flag of India image

please check the connection string which is being used for this query
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

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.


Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

The connection string is correct.
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

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"
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

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/...
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

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

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

Open in new window

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

ASKER

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 ...
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

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 ...
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

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?
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

I just double checked. They do have the same data type.
ASKER CERTIFIED SOLUTION
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of JohnnyBCJ
JohnnyBCJ
Flag of Canada image

ASKER

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

Thanks for your help as always!
Databases
Databases

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

62K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo