It does work sometimes though, I don’t think its a problem with the query. It just because the time taken to go run the query takes longer than a setting somewhere.
I’ve tried changing the ODBC timeout settings on my PC and the driver on the server which didn’t work
When I run the exact same query in MS Access it works fine
Dim sql, dbcomm As Object
Dim Billing As Double
Dim MyConnection As System.Data.Odbc.OdbcConnection
MyConnection = New System.Data.Odbc.OdbcConnection("DSN=2501;DB=sosdb;UID=dd;PWD=dantheman;HOST=newfileserver;PORT=2511")
'MyConnection.ConnectionTimeout = 80
MyConnection.Open()
sql = "SELECT ""DEPARTMENT-DESCRIPTION"", "
sql += " Sum( "
sql += " CASE "
sql += " WHEN ""POST-DATE"" BETWEEN CURDATE()-365 AND CURDATE() THEN ""CURR-VALUE"" "
sql += " END) AS CurrentValue "
sql += " FROM (PUB.TRTRANS LEFT JOIN PUB.FEETR ON PUB.TRTRANS.""FEE-EARNER"" = PUB.FEETR.""FEE-EARNER"") LEFT JOIN PUB.DEPARTMENT ON PUB.FEETR.DEPARTMENT = PUB.DEPARTMENT.""DEPARTMENT-CODE"" "
sql += " WHERE ( (""ACT-CODE"" = 'BILL' OR ""ACT-CODE"" = 'CN') AND UNDONE = 0 AND ""DEPARTMENT-DESCRIPTION"" = '" + fee_earner.SelectedValue + "' AND ""CURR-VALUE"" > 0 )"
sql += " GROUP BY ""DEPARTMENT-DESCRIPTION"" "
dbcomm = New System.Data.Odbc.OdbcDataAdapter(sql, MyConnection)
Dim dt As DataTable = New DataTable()
dbcomm.Fill(dt)
MyConnection.Close()
I've tried taking the brackets out of the FROM clause which works on other queries but i get the same thing
danieldunn10
Member
389 Points
383 Posts
Database Timeout
Mar 13, 2012 09:42 AM|LINK
Hi, I have a sql query which works fine with one table, when I join two more tables to the query it comes up with this error though.
“ERROR [HYT00] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Timeout expired.
ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Query aborted on user request (7495)"
It does work sometimes though, I don’t think its a problem with the query. It just because the time taken to go run the query takes longer than a setting somewhere.
I’ve tried changing the ODBC timeout settings on my PC and the driver on the server which didn’t work
When I run the exact same query in MS Access it works fine
Thanks for any advice
Sandeep Shen...
Participant
1678 Points
492 Posts
Re: Database Timeout
Mar 13, 2012 09:54 AM|LINK
hi
Can you please share the query which generates error ?
Skype : sandeep.d.shenoy
Gmail : sandeepdshenoy@gmail.com
kedarrkulkar...
All-Star
34411 Points
5534 Posts
Re: Database Timeout
Mar 13, 2012 10:00 AM|LINK
try increasing connection and command timeout
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
timeout value in second here
hope this helps...
KK
Please mark as Answer if post helps in resolving your issue
My Site
danieldunn10
Member
389 Points
383 Posts
Re: Database Timeout
Mar 13, 2012 10:10 AM|LINK
Thanks KK, I get this error now!
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
I will post my Query in a moment
Thank You
danieldunn10
Member
389 Points
383 Posts
Re: Database Timeout
Mar 13, 2012 10:18 AM|LINK
Dim sql, dbcomm As Object Dim Billing As Double Dim MyConnection As System.Data.Odbc.OdbcConnection MyConnection = New System.Data.Odbc.OdbcConnection("DSN=2501;DB=sosdb;UID=dd;PWD=dantheman;HOST=newfileserver;PORT=2511") 'MyConnection.ConnectionTimeout = 80 MyConnection.Open() sql = "SELECT ""DEPARTMENT-DESCRIPTION"", " sql += " Sum( " sql += " CASE " sql += " WHEN ""POST-DATE"" BETWEEN CURDATE()-365 AND CURDATE() THEN ""CURR-VALUE"" " sql += " END) AS CurrentValue " sql += " FROM (PUB.TRTRANS LEFT JOIN PUB.FEETR ON PUB.TRTRANS.""FEE-EARNER"" = PUB.FEETR.""FEE-EARNER"") LEFT JOIN PUB.DEPARTMENT ON PUB.FEETR.DEPARTMENT = PUB.DEPARTMENT.""DEPARTMENT-CODE"" " sql += " WHERE ( (""ACT-CODE"" = 'BILL' OR ""ACT-CODE"" = 'CN') AND UNDONE = 0 AND ""DEPARTMENT-DESCRIPTION"" = '" + fee_earner.SelectedValue + "' AND ""CURR-VALUE"" > 0 )" sql += " GROUP BY ""DEPARTMENT-DESCRIPTION"" " dbcomm = New System.Data.Odbc.OdbcDataAdapter(sql, MyConnection) Dim dt As DataTable = New DataTable() dbcomm.Fill(dt) MyConnection.Close()I've tried taking the brackets out of the FROM clause which works on other queries but i get the same thing
This is a picture of the join in Access which works - https://skydrive.live.com/redir.aspx?cid=9c2775bd3b48974e&resid=9C2775BD3B48974E!116&parid=9C2775BD3B48974E!114
Thanks
kedarrkulkar...
All-Star
34411 Points
5534 Posts
Re: Database Timeout
Mar 13, 2012 10:45 AM|LINK
have u created the DSN on u r machine? is it named 2501? seems there is issue with coneection string... check this
http://www.connectionstrings.com/dsn
hope this helps...
KK
Please mark as Answer if post helps in resolving your issue
My Site
danieldunn10
Member
389 Points
383 Posts
Re: Database Timeout
Mar 13, 2012 10:55 AM|LINK
The DSN is on the server where the files are in IIS.
Yep the DSN name is 2501
I've used that connection string in many other apps to connect to the database. Sould I change it?
Thanks
danieldunn10
Member
389 Points
383 Posts
Re: Database Timeout
Mar 13, 2012 11:04 AM|LINK
I've got the page to load for the first time using this!!
MyConnection = New System.Data.Odbc.OdbcConnection("DSN=2501;UID=dd;PWD=dantheman")
MyConnection.ConnectionTimeout = 80
I will check to see if it works when I change the rest
Thanks very much!
kedarrkulkar...
All-Star
34411 Points
5534 Posts
Re: Database Timeout
Mar 13, 2012 11:23 AM|LINK
do u mean it is working now? or still same issue
KK
Please mark as Answer if post helps in resolving your issue
My Site
danieldunn10
Member
389 Points
383 Posts
Re: Database Timeout
Mar 13, 2012 11:47 AM|LINK
I think i still have the problem sorry
It does work if you refresh the page 5 or 6 times.
It seems to work sometimes and others not!?
Thank you