Megan
Posts: 35
|
| Posted: 08/18/2006, 12:09 AM |
|
I am having the wildest problem. I'm using SQL Server 2000 with ASP. Whenever I use the Report Builder to build a report that contains a bunch of dates and don't manipulate the report at all, the dates display just fine. However, once I start formatting the report and moving the fields around (creating multiple fields across a single row, using drag and drop or anything else to move one of the date fields), the dates don't display anymore. This sometimes happen with regular "text" fields as well, but not as often as the dates (by the way - all of the output on the report is based on "labels" that pull their data from the database on the SQL server). I've also noticed some dates not showing up in some text boxes on other screens as well.
I am seriously confused about this and have been working on it for a few days now. Any ideas? HELP!
Megan
|
 |
 |
Megan
Posts: 35
|
| Posted: 08/20/2006, 2:55 PM |
|
Well, I've narrowed down the problem. It seems that none of my dates are showing up. I modified the format, both in the database and in the label properties, to a text field, and they still wouldn't show.
The datasource for the record invoves four joined tables: one "main" one with left outer joins to three other ones. One of the joined tables is a list of states (state ID and state abbreviation). If I tell the report to display the state ID that's in the main table, it works and all text fields after that field display fine. If I have it display the state abbreviation in the joined table, no labels display any information after that point. I then created a "view" on the sql server, and then had the datasource for the report use the view rather than manually create the joins in the datasource. I still had the same problem. Has anyone else experienced this problem?
Megan
|
|
|
|
Megan
Posts: 35
|
| Posted: 08/22/2006, 2:43 PM |
|
I posted this question with tech support, and they were able to give me the resolution that worked. I'm posting their response in case anyone else has (or does) experienced this problem. (By the way - I was using an ODBC connection and I have changed it to the OLE DB provider for SQL.)
1. In case you’re using ODBC connection to SQL server and tables include ntext type fields then it may be a reason why part of recordset is not displayed. I need to say that it is not related to CCS, but it is ODBC bug - MSDN KBase PRB: 80020009 Error When Retrieving Data from SQL
RESOLUTION:
When dealing with BLOB fields from Microsoft SQL Server, you must put them to the right of non-BLOB columns in the resultset. To be safe, you should also read the columns in left-to-right order, so if you have two BLOB columns as the last two columns in your resultset, read the first one and then the second. Do not read them in the reverse order.
As for CCS, you need to set fields order and sql according to Microsoft recommendations. I.e. the emailed example would work if:
1).
Set TextArea1 = CCCreateField("TextArea1", "Column_Two", ccsText, Empty, Recordset)
Set TextArea2 = CCCreateField("TextArea2", "Column_One", ccsText, Empty, Recordset)
SQL = "SELECT TOP 1 Column_Two,Column_One " & _
"FROM Test_Table"
2):
Set TextArea1 = CCCreateField("TextArea1", "Column_One", ccsText, Empty, Recordset)
Set TextArea2 = CCCreateField("TextArea2", "Column_Two", ccsText, Empty, Recordset)
Fields.AddFields Array(TextArea1, TextArea2)
SQL = "SELECT TOP 1 Column_One,Column_Two " & _
"FROM Test_Table"
So if you have ntext fields in the tables and you do not need to output them on a form please do not use “Select *” but select only fields you need to display in Visual Query Builder.
If you need to display fields then please remember a suggestion provided above.
Other solution is to use Microsoft OLE DB Provider for SQL Server to connect to database, it’s free of reported bug.
2. About date fields. Please open database Connection dialog on Server tab and make sure that Database Date Format is set to yyyy-mm-dd HH:nn:ss. This is the format in which dates are stored in database. Now if you set label’s Data Type property to Date it should output date value.
|
|
|
|
|
