|
I've never worked on these old dbase/clipper database files for Account Mate DOS, and I'm having trouble with multiple joins. Says I'm missing a arithmetic operator in the statement. I can't find a reference to study to get an idea of how it should be written. I really don't want to make 2 calls, because it really slows down the load time.
This is the error message
Syntax error (missing operator) in query expression '(c.FCUSTNO = a.FCUSTNO) LEFT JOIN ARCEM01.dbf e ON (c.FCUSTNO = e.FCUSTNO)'.
"SELECT c.FINVNO, c.FCUSTNO, c.FCOMPANY, c.FSALESPN, c.FSHIPVIA, c.FORDDATE, c.FSHIPDATE, c.FNTAXAMT, a.FADDR1 " & _
"FROM ARINV01.dbf c " & _
"LEFT JOIN ARCUS01.dbf a ON (c.FCUSTNO = a.FCUSTNO) " & _
"LEFT JOIN ARCEM01.dbf e ON (c.FCUSTNO = e.FCUSTNO) " & _
"WHERE c.FORDDATE=@startDate"
|
|
|
|
|
If I remember correctly (which might not be the case) dbase only allows one outer join. But this doesn't seem to be a real problem as you're not using ARCEM01.dbf for anything.
The solution otherwise would be to use subqueries in the select. Like this:
SELECT c.FINVNO
,c.FCUSTNO
,c.FCOMPANY
,c.FSALESPN
,c.FSHIPVIA
,c.FORDDATE
,c.FSHIPDATE
,c.FNTAXAMT
,(SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE c.FCUSTNO = a.FCUSTNO) as FADDR1
FROM ARINV01.dbf c
WHERE c.FORDDATE=@startDate
|
|
|
|
|
I kind of thought that.
I didn't put the e.FEMAIL1 in for experimenting yet until I got some concrete information on it.
Thanks!
|
|
|
|
|
That Works! Thanks Jorgen
SELECT c.FINVNO
,c.FCUSTNO
,c.FCOMPANY
,c.FSALESPN
,c.FSHIPVIA
,c.FORDDATE
,c.FSHIPDATE
,c.FNTAXAMT
,(SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE c.FCUSTNO = a.FCUSTNO) AS FADDR1
,(SELECT e.FEMAIL1 FROM ARCEM01.dbf e WHERE c.FCUSTNO = e.FCUSTNO) AS FEMAIL1
FROM ARINV01.dbf c
WHERE c.FORDDATE=@startDate
|
|
|
|
|
Hye, I having a problem with this sql query..
string sql = "SELECT * FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE (Environmnets.DateT =(SELECT MAX(DateT) AS Expr1 FROM Environmnets AS Environmnets_1)) AND TMushroomHouse.Name ='"+ DropDownList1.Text + "'";
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand(sql, con);
// SqlDataReader dr = cmd.ExecuteReader();
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
HouseTextBox.Text = dr["House"].ToString();
DateTTextBox.Text = dr["DateT"].ToString();
TempInTextBox.Text = dr["TempIn"].ToString();
TempOutTextBox.Text = dr["tempOut"].ToString();
HumidityTextBox.Text = dr["Humidity"].ToString();
}
}
There is no error occur when I run this code, but when I select home from dropdownlist, the data from database didn't display in the textbox. Anybody can help me?
|
|
|
|
|
The first problem is that your code is susceptible to SQL Injection[^]. You should be using a parameterized query instead.
While we're fixing that, the SqlConnection, SqlCommand and SqlDataReader objects all implement IDisposable, so they should be wrapped in using blocks to make sure their resources are freed.
We'll also get rid of the SELECT *, since you only need five columns.
string sql = "SELECT House, DateT, TempIn, tempOut, Humidity FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE (Environmnets.DateT = (SELECT MAX(DateT) AS Expr1 FROM Environmnets AS Environmnets_1)) AND TMushroomHouse.Name = @MushroomHouseName";
using (SqlConnection con = new SqlConnection(strConnString))
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@MushroomHouseName", DropDownList1.Text);
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
HouseTextBox.Text = dr["House"].ToString();
DateTTextBox.Text = dr["DateT"].ToString();
TempInTextBox.Text = dr["TempIn"].ToString();
TempOutTextBox.Text = dr["tempOut"].ToString();
HumidityTextBox.Text = dr["Humidity"].ToString();
}
}
}
Now, if the code is definitely executing, and there's no error, but you're not seeing anything in the textboxes, then that means there were no records returned by your query. If I had to guess, I'd say it's probably connected to the DateT filter:
WHERE (Environmnets.DateT = (SELECT MAX(DateT) AS Expr1 FROM Environmnets AS Environmnets_1))
I suspect you want the last record for a specific TMushroomHouse, but you're actually only looking for the last record overall, and only returning it if it matches the selected house.
If that's the case, try changing your query to:
SELECT TOP 1 House, DateT, TempIn, tempOut, Humidity FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE TMushroomHouse.Name = @MushroomHouseName ORDER BY Environmnets.DateT DESC
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Tq..it working right now  And I have one more question, mushroomhousename is not the same table with environmnets..how i want to call mushroomhousename and put it in a textbox?
|
|
|
|
|
Try something like:
SELECT TOP 1 House, DateT, TempIn, tempOut, Humidity, TMushroomHouse.Name As MushroomHouseName FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE TMushroomHouse.Name = @MushroomHouseName ORDER BY Environmnets.DateT DESC
This adds the Name column from the TMushroomHouse table as a new result column called MushroomHouseName. You can then read it from your code and put it in a textbox:
while (dr.Read())
{
...
MushroomHouseTextBox.Text = dr["MushroomHouseName"].ToString();
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
I am having the above error on my .net application. It only shows on customer screen with a basix SELECT statement. Currently I have only 1600 customers in the customers table
This error is not shown on any of the other screens except the customer screen.
My connection timeout is 360 and I even tried to increase it to 800 but getting the same error for the customer screen!
what could be the problem please?
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: "MySQL A connection attempt failed because the connected third party did not properly respond after a period of time"
You're referencing to something that's not there; the exception message comes from one of your older posts. Simply include it with the rest of the text.
Can you connect at all to that particular server? Using Telnet[^]?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Yes I can. I am also able to view invoices, employees and all other data from the same database on the same server using the same .net application. Exceptions occurs on the customers only.
Any timeout parameter or variable I should try to look at?
Technology News @ www.JassimRahma.com
|
|
|
|
|
I would suggest you look at the customer query then, it is not your connection, that works for other queries!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
this is my query:
SELECT customer_id, id_number, customer_name, email_address, landline_number, mobile_number, customer_address_building, customer_address_road, customer_address_block, customer_address_landmark, allow_delete, is_blacklist, is_inactive FROM customers ORDER BY customer_name;
Technology News @ www.JassimRahma.com
|
|
|
|
|
There is no WHERE clause?!
|
|
|
|
|
no WHERE. I am filtering using the XtraGrid. It will be maximum of 2000 records which I though MySQL will be able to handle it easily.
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: which I though MySQL will be able to handle it easily
Stop pointing fingers; it's not MySQL that's failing, but a third-party component.
You might try to connect using another user/role/password at the customers site. And update your MySQL data provider
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I will do
and by the way, I was not pointing fingers Lol
Thank you so much
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: and by the way, I was not pointing fingers Lol
My bad; it could have been, depending on language and intonation
I usually take the exception-message as a starting point;Jassim Rahma wrote: "MySQL A connection attempt failed because the connected third party did not properly respond after a period of time"
Plan of attack;
- Create a console-app, use the users' credentials, and try a minimal query (SELECT 1 or something similar)
- Test once with your current data provider (MySQL Connector.NET?)
- Copy the project, try another data provider (MySQLDotNET[^])
- Try the same again, using the dba-account.
If you can terminal to MySQL, then the server is there; that you said was true. That means that you can skip checking the firewall and the port on which MySQL speaks. The reason I'm asking to test with the dba's account is that he should have unrestricted access to all tables. If that one connects correctly, then you'd need to do some extra grants on your users' accounts.
What does your connection-string look like? Can you post it here? (Without the login-info)
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
I am using this code to loop from start date to end date and add job orders into MySQL table. I only have one problem here. If My start date is 14 April 2014 it will insert records from 15 April 2014 so it's skipping my start date.
can anyone help please...
here is the stored procedure..
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_job_order_date_to date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_recurring bit, IN param_max_recurring_date date, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11, 6), IN param_created_user int, OUT param_record_identity int)
BEGIN
UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;
INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
SET param_record_identity = LAST_INSERT_ID();
IF (param_is_contract = TRUE) THEN
BEGIN
UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
END;
END IF;
IF (param_is_recurring = TRUE) THEN
BEGIN
REPEAT
SET param_job_order_date = DATE_ADD(param_job_order_date, INTERVAL 7 DAY);
INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
SET param_record_identity = LAST_INSERT_ID();
IF (param_is_contract = TRUE) THEN
BEGIN
UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
END;
END IF;
CALL sp_add_event_log("JOBORDER", param_record_identity, param_created_user, "Job order was created.");
CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
UNTIL param_job_order_date >= param_max_recurring_date
END REPEAT;
END;
END IF;
END
Technology News @ www.JassimRahma.com
|
|
|
|
|
So I had a hard time finding where your loop started, so I can't give you details on why its happening. However I can give you a tried and true method to find the issue (its worked for over 30 years for me)
Play computer:
Take a few sheets of paper and write down what you "expect" to happen in plain English.
Then write down the algorithm your code is following in plain English.
Compare - figure out the mis-match, you may need to search vendor docs to find the "gotcha"
Still no luck finding out whats going wrong?
Take a small record set and run it through your written algorithm.
99.9999% of the time this method works!
Good luck
|
|
|
|
|
Where do you set the start date?
|
|
|
|
|
In the job_order table when inserted.
Technology News @ www.JassimRahma.com
|
|
|
|
|
And that is supposed to help? Please edit your question and indicate clearly where the various variables are (and their content) and which line of code causes the problem.
|
|
|
|
|
Which is what I was trying to tell him. Play the SQL parser, read the date in the table.
9 times out of ten when you compare dates there is something with the full timestamp in the field that is messed up. Or maybe the field doesn't have a full date saved which causes comparisons to fail when you think they won't.
For example 22/04/2014 10:00:00 is greater than 22/04/2014 00:01:00 but depending on the way SQL comparisons work (I'm reaching into ancient history) it is not greater than 22/04/2014 because the parser interprets 22/04/2014 into 22/04/2014 12:00:00
sometimes you have to use tochar function to compare dates
|
|
|
|
|
Hi All,
Hope everyone is doing great!!!.
I am new to this group so any mistake in posting, i am sorry.
I have a scenario, We have 5000+ scheduled jobs in the scheduler tool and the back end for that tool is sql server. We are manually monitring the job status and failuers which is taking huge efforts.
For automating the monitoring process, we want to have Stored procedure which can be triggered when there is failure or status of jobs ran for every hour. I need help as how to have body of the message for reporting the job status and run times.
I got the SP, but how to get the body of the message is what i am stuck with. Below is link from where i check the code.
Please let me know if you need any information.
Thanks a lot for your help in advance.
Regards,
Deepa
http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
