Monitor your entire network from a single platform. Free 30 Day Trial Now!
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?
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
Who is Participating?
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.
can you show the code that runs this query?
also, can you output the SQL string value before it's executed?
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_NU
WHERE (tblARInvoice.Invoice_Date
ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME
The code that runs the query is below:
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_NU
WHERE (tblARInvoice.Invoice_Date
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()
Convert string to date
str_to_date('10-10-2006', "%d-%m-%Y");
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
How can I double check?
I have 2 MySQL databases and they contain identical information in both databases.
I have 2 MySQL databases and they contain identical information in both databases.
If I try to do the query with db1.tablename.columnnames it works but if I try to do it with db2.tablename.columnnames,
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.
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"
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?
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?
My error is "Unknown column 'db2.clients.lname' in 'field list'"
There are no columns that are binary or blob.
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:
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()
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()
finally, it could be permission problems ... (the error message). ..
but 2 records returned instead of 8 ... can only data issues.
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?
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 ...
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?
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?
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).
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).
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.
