1. Stefania Georgiadi
  2. Valentina Reports ADK
  3. Friday, May 13 2016, 09:27 AM
  4.  Subscribe via email
I am trying to produce a report from Xojo with data from MSSQL Server (Express 2014).

However, when I try to access the first property from mReport (mReport.PageCount) I get the error:
Datasource error: "DB-Library error: General SQL Server error: Check messages from the SQL Server:.

Any ideas?
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Source query can be executed as a subquery.
And I suspect this error comes from the limitation of SQL Server, that ORDER BY is not allowed in subqueries.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 1
Sergey Pashkov Accepted Answer
Setting a sorting in report itself on the "Data" tab of the main toolbar should work.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 2
Sergey Pashkov Accepted Answer
Hi Fotis,

From Valentina Studio it works,
but from Xojo doesn't, right?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 3
Stefania Georgiadi Accepted Answer
Yes
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 4
Stefania Georgiadi Accepted Answer
Also the string I use for datasource is:

mssql://host='192.168.1.167',user = 'sa', password = '123456', db = 'worktimer'
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 5
Sergey Pashkov Accepted Answer
And the port is 1433?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 6
Stefania Georgiadi Accepted Answer
Yes. Should I specify that somehow?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 7
Sergey Pashkov Accepted Answer
No, normally it is set by default.

Is 192.168.1.167 a remote PC?
And a version of Valentina Studio is the latest 6.5.3?

Here's how the Valentina Studio would construct such string:

mssql://host = '192.168.1.167' port = '1433' dbname = 'worktimer' user = 'sa' password = '123456' timeout = '10'
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 8
Stefania Georgiadi Accepted Answer
192.168.1.167 is a local ip.
I will try your connection string and let you know...
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 9
Stefania Georgiadi Accepted Answer
Thanks Sergey, that is working now. One other problem is that a simple report (a few columns from a single table) works.

However, a more complex report the report doesn't show in Valentina Studio (or Xojo) and in Studio from the same data when I set the data source to MS SQL Server (even though they show on the "Test" tab of the query editor in Valentina Studio.
Exactly the same data appear with no problem in Postgres! The report has a grouping by wteId and sorting by wteSurname, wteName and wtdDate

Any ideas why this happens?

The query is:

SELECT wtEmployee.wteId, wteDepartment, wteClockCard, wtdDate, wtdTimeFrom, wtdTimeTo, wtdTime2From, wtdTime2To, wtdTime3From, wtdTime3To, wtdTotalHours, wteSurname, wteName
FROM wtDailyWork, wtEmployee
WHERE wtdDate BETWEEN '2016-4-1' AND '2016-4-15'
AND wtEmployee.wteId = wtDailyWork.wteId
ORDER BY wteSurname, wteName, wtEmployee.wteId, wtdDate
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 10
Sergey Pashkov Accepted Answer
Can't tell yet, we'll check it.
But is there any error shown - either in VStudio or in Xojo as exception?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 11
Stefania Georgiadi Accepted Answer
No error in VStudio, just an "empty" report.
Xojo crashes in accessing the PageCount property, I assume because there is no actual report produced, so no count was available.

Now in setting the query, the test tab - as I said - returns the data successfully...
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 12
Stefania Georgiadi Accepted Answer
Sergey, just to help you, I kept testing, and when I removed the "ORDER BY" part of the query, it printed...

Is that normal?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 13
Stefania Georgiadi Accepted Answer
Thanks Sergey,
That is exactly what I did!:)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 14
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories