?
Solved

XSD File

Posted on 2003-12-04
25
Medium Priority
?
885 Views
Last Modified: 2007-12-19
Can someone point me to a resource, or help me in creating a XSD file, using a database connection string? Everything I've read or looked at has a database connection set up through VSS, and all they do is drag-n-drop tables on the XSD file. I'm trying to create a dynamic Crystal Reports report, using an Access query that takes in params, but I can't find a good reference. My connection string is stored in the web.config file.

Thanks in advance,
Junoti
0
Comment
Question by:Junoti
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 5
25 Comments
 

Expert Comment

by:RuiSousa
ID: 9878838
If you open a DataSet object with the result of the query, you can save a XSD file with the schema for that query:

private void WriteSchemaWithXmlTextWriter(DataSet thisDataSet){
   // Set the file path and name. Modify this for your purposes.
   string filename="mySchema.xml";
   // Create a FileStream object with the file path and name.
   System.IO.FileStream myFileStream = new System.IO.FileStream
      (filename,System.IO.FileMode.Create);
   // Create a new XmlTextWriter object with the FileStream.
   System.Xml.XmlTextWriter MyXmlTextWriter =
      new System.Xml.XmlTextWriter(myFileStream, System.Text.Encoding.Unicode);
   // Write the schema into the DataSet and close the reader.
   thisDataSet.WriteXmlSchema(MyXmlTextWriter );
   MyXmlTextWriter.Close();
}
This code was taken from: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclasswritexmlschematopic.asp

Then add the XSD file to your project, and designing the report select the XSD as your DataSource.

Good Luck
0
 
LVL 1

Author Comment

by:Junoti
ID: 9878890
Thanks RuiSousa for the quick response, however, the way this is described won't work for me right now. The dataset will be changing based on a parameter. A user selects a date, a dataset is returned based on that date, and then the info in the dataset is used as the source for the report. The way the above method describes it is, I would need to know which request the user is going to make, and create the report for them...not on the fly...correct?

Thanks
0
 
LVL 12

Expert Comment

by:vascov
ID: 9878999
I'm not sure if this is what you're after:

                  string connString = @"DRIVER=Microsoft Access Driver (*.mdb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;DriverId=25;DefaultDir=D:\my documents;DBQ=D:\my documents\db1.mdb";

                  string command = "SELECT * FROM Table1";

                  DataSet ds = new DataSet();

                  using ( OdbcConnection conn = new OdbcConnection( connString ) )
                  {
                        OdbcDataAdapter da = new OdbcDataAdapter( command, conn );

                        da.FillSchema( ds, SchemaType.Source );
                  }

                  MessageBox.Show( (ds != null ? ds.GetXmlSchema() : "oops" ) );


Let me know if this is what you're after...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:Junoti
ID: 9879010
Vascov, thanks for the reply. That's close. I've already got the dataset populated correctly with the information. My question is, how can take the dataset, and either turn it into a xsd file dynamically and tie that xsd file to a crystal report dynamically, or just tie the dataset to the report dynamically. Is this even possible. I would think this isn't an uncommon scenario, but I may be wrong.

I'm going after a crystal report that is generated dynamically, depending on what parameters are sent to a stored proecedure in Access.

Thanks for the help
0
 
LVL 12

Expert Comment

by:vascov
ID: 9879079
But do you have a report previously generated ?
or what kind of dynamic report do you intend to produce ? (a simple tabular one ? )

Is the structure of your query in terms of returned info changing ? or is it just the data itself that changes according to some criteria ?

0
 
LVL 12

Expert Comment

by:vascov
ID: 9879206
If all you want is to pass the DataSet into your report here's another sample:

                  string connString = @"DRIVER=Microsoft Access Driver (*.mdb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;DriverId=25;DefaultDir=D:\my documents;DBQ=D:\my documents\db1.mdb";
                  string command = "SELECT * FROM Table1";

                  DataSet ds = new DataSet();

                  using ( OdbcConnection conn = new OdbcConnection( connString ) )
                  {
                        
                        OdbcDataAdapter da = new OdbcDataAdapter( command, conn );

                        da.FillSchema( ds, SchemaType.Source );

                  }

                  MessageBox.Show( (ds != null ? ds.GetXmlSchema() : "oops" ) );

                  CrystalReport1 r = new CrystalReport1();

                  r.SetDataSource( ds );

                  r.ExportToDisk( CrystalDecisions.Shared.ExportFormatType.WordForWindows, @"C:\temp\myrep.doc" );

If using web, then you will probably want to use a reportviewer, and assign to ReportSource, the report we instantiaded just now (in this sample r )

HTH
0
 

Expert Comment

by:RuiSousa
ID: 9881182
I have a Web App that produces reports based on criterias the user inputs, like BeginDate, EndDate, etc.

The result of the query is allways diferent, but with the same columns. In this cenario this will work.

If the query outputs diferent columns depending on the paramaters, then you will need one report, and one XSD, for each columns layout. The report's design must be defined and saved as a .rpt file. I don't think you can generate a report dinnamicaly at runtime, but you can populate a report at runtime:

SqlConnection myConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
myConn.Open();
SqlCommand myComm = new SqlCommand("MyStoredProcedure",myConn);
myComm.CommandType = CommandType.StoredProcedure;                           
SqlCommandBuilder.DeriveParameters(myComm);

DataSet MyDataSet = new DataSet();
myAdapter.Fill(MyDataSet,"MyStoredProcedure");
oRpt.Load(Server.MapPath("MyReport.rpt"));
oRpt.SetDataSource(MyDataSet);

crViewer.HasDrillUpButton = true;                        
crViewer.ReportSource = DaReport(myComm);
crViewer.DataBind();

myConn.Close();
0
 

Expert Comment

by:RuiSousa
ID: 9881197
Sorry!!
Were I wrote:
crViewer.ReportSource = DaReport(myComm);

Please read:
crViewer.ReportSource = MyReport;

0
 

Expert Comment

by:RuiSousa
ID: 9881299
Oops.

I'm copying and pasting the code from diferent places, and i'm not doing it right...

This time is for real:


SqlConnection myConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
myConn.Open();
SqlCommand myComm = new SqlCommand("MyStoredProcedure",myConn);
myComm.CommandType = CommandType.StoredProcedure;                      
SqlCommandBuilder.DeriveParameters(myComm);

//Add parameters filling in here

SqlDataAdapter myAdapter = new SqlDataAdapter(myComm);
ReportDocument oRpt= new ReportDocument();

DataSet MyDataSet = new DataSet();
myAdapter.Fill(MyDataSet,"MyStoredProcedure");
oRpt.Load(Server.MapPath("MyReport.rpt"));
oRpt.SetDataSource(MyDataSet);

crViewer.HasDrillUpButton = true;          
crViewer.ReportSource = DaReport(myComm);
crViewer.DataBind();

myConn.Close();
0
 
LVL 1

Author Comment

by:Junoti
ID: 9882560
Here's the code I have. objSet is successfully created. However, here's the error I receive: Unable to find the report in the manifest resources. Please build the project, and try again. DailyActivity.rpt exists in the directory.

                  Activity objAct = new Activity();
                  DataSet objSet = new DataSet();
                  DailyActivity objReport = new DailyActivity();
                  
                  ReportDocument objDoc = new ReportDocument();
                  objDoc.Load(Server.MapPath("DailyActivity.rpt"));
                  objSet = objAct.GetActivitiesForDay("12/2/2003","3");

                  objReport.SetDataSource(objSet);                  
                  
                  objCRViewer.ReportSource = objDoc;
                  objCRViewer.BestFitPage = true;
                  objCRViewer.BorderWidth = 1;
                  objCRViewer.DisplayToolbar = false;
                  objCRViewer.DataBind();
0
 
LVL 12

Expert Comment

by:vascov
ID: 9883292
Try to instantiate the object directly

DailyActivity dr = new DailyActivity();

(this assumes the Crystal Assembly is acessible from within your project)

0
 
LVL 12

Expert Comment

by:vascov
ID: 9889566
Did it help ?
0
 
LVL 1

Author Comment

by:Junoti
ID: 9896227
Sorry, I was out of the office for the last few days. The line of code you suggested I implement already existed (line 3 of my pasted code). Unfortunately, it is still not working.
0
 
LVL 12

Expert Comment

by:vascov
ID: 9896710
Hi Junoti,

You're right, i tried to point you to the problem, but didn't post the solution :(

In your code, you'll notice, that objReport is only used in 2 places. the 3rd line and the 9th. You're not passing objReport to the viewer.

So, here's part of your code "revised":
               Activity objAct = new Activity();
               DataSet objSet = new DataSet();
               DailyActivity objReport = new DailyActivity();
               
               objSet = objAct.GetActivitiesForDay("12/2/2003","3");

               objReport.SetDataSource(objSet);              
               
               objCRViewer.ReportSource = objReport;
               objCRViewer.BestFitPage = true;
               objCRViewer.BorderWidth = 1;
               objCRViewer.DisplayToolbar = false;


Let me know if this works
0
 
LVL 1

Author Comment

by:Junoti
ID: 9914400
Thanks vascov, here's the scenario now. The above code appears to "work" by not returning an error...but...now I get, what appears to be an empty report. I get nice little scroll bars, but no data. I've put breaks into the code, and I've verified that objSet has valid data. Here are the steps I used to create the actual rpt file:

1) Created a dataset file called "DailyActivities.xsd" with the exact schema of the data returned from objSet
2) Created a report called "DailyActs.rpt" and used "DailyActivities.xsd" as it's data source. The schema appears fine.
3) After creating the rpt file, I implemented the above code.

Thanks for the help. This is the only part of my project left, so I'm increasing the points also.

TIA
0
 
LVL 12

Expert Comment

by:vascov
ID: 9914924
Have you validated that objSet has the correct data ?

You can profile it as SQL using SQL Profiler, to make sure the data is getting out of SQL, and then use the Trace mechanisms from ASP.NET to make sure the data is getting set along the way.
0
 
LVL 1

Author Comment

by:Junoti
ID: 9915004
Yep, data is being returned correctly. I use the same method in several other places, and it works fine.
0
 
LVL 1

Author Comment

by:Junoti
ID: 9916273
Ok, I think I'm getting close. I have an XSD file called "DailyActivities.xsd". A code behind file name "DailyActivities.cs" is created from the XSD file, and inherits System.Data.DataSet. Rather than instantiating a new DataSet object (line 2 of above code), I think I should have been instantiating a DailyActivities object. My function called "GetActivitiesForDay(...,..)" returns a DataSet object. I try to cast/copy the DataSet returned from the function to a DailyActivities object, but get an error stating the the cast is invalid. Any ideas?
0
 
LVL 12

Accepted Solution

by:
vascov earned 1500 total points
ID: 9916560
Can you post your project /code (or the relevant parts) here (or somewhere), so that i can take a closer look at it ?

Taken from your previous emails, i guess that you have to make sure that you are making use of the same typed DataSet in every (needed) place, namely, in your SQL code to send the command and get the results back, and in your Crystal Report.

In your GetActivitiesForDay code, are you creating a DataSet or a DailyActivities object internally ? If you are creating just a DataSet, than it's base type would be DataSet, and when you try to "copy" it to a DailyActivities object, it naturally complains, because it doesn't know how to do it.
(As a work around you can do  da.LoadXml( ds.GetXml() )... but this is just to confirm.)

If you do a <name of DailyActivities var>.GetType().ToString() what does show up ?

0
 

Expert Comment

by:RuiSousa
ID: 9916916
To the code vascov wrote:

               Activity objAct = new Activity();
               DataSet objSet = new DataSet();
               DailyActivity objReport = new DailyActivity();
               
               objSet = objAct.GetActivitiesForDay("12/2/2003","3");

               objReport.SetDataSource(objSet);              
               
               objCRViewer.ReportSource = objReport;
               objCRViewer.BestFitPage = true;
               objCRViewer.BorderWidth = 1;
               objCRViewer.DisplayToolbar = false;

You should add

               objCRViewer.DataBind();

I hope this is it.



0
 
LVL 12

Expert Comment

by:vascov
ID: 9917004
Olá Rui,

Actually, there's no explicit need to databind.  (it wont hurt either :)
When you set the reportsource it "binds".

HTH

0
 
LVL 1

Author Comment

by:Junoti
ID: 9922719
Alright guys, this is perplexing. Here's something I found:
http://groups.google.com/groups?q=%22unable+to+find+the+report+in+the+manifest+resources.+Please+build+the+project,+and+try+again.%22&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=02fd01c31994%245d707090%24a501280a%40phx.gbl&rnum=9

I take the same exact code from my project, create a new project, follow the exact same steps of creating a dataset file and bind a report to it, and voila it works. According to this above post (and I hope someone can validate), reports do not like being in namespaces. For instance, my namespace for the report is Project.CompanyName.Reports, and this doesn't work. In the new project I created as a test, it was just Reports, and this works fine. Can anyone validate this and/or suggest a work around?

Thanks for all the help fellas/fella-ettes!

0
 
LVL 1

Author Comment

by:Junoti
ID: 9932099
Woohoo! Thanks for the help! This line kinda lead me into a new direction:

In your GetActivitiesForDay code, are you creating a DataSet or a DailyActivities object internally ? If you are creating just a DataSet, than it's base type would be DataSet, and when you try to "copy" it to a DailyActivities object, it naturally complains, because it doesn't know how to do it.

I ended up creating a new method called ReportActivitiesForDay that returned a DailyActivities object (base class is DataSet). One key lesson I learned (off of CrystalDecisions.com) is that the name of the DataSet that is being filled HAS to match the name of the dataset in the XSD file. For instance, my XSD file had a dataset name of "ActivityReport". However, when using the dataadapter to fill the dataset object, the code was similar to objAdapter.Fill(objSet,"DailyActivity"). Once I changed the "DailyActivity" to "ActivityReport", my report showed up!! Albeit, it doesn't look pretty, at least I'm reporting off the database now!

Thanks again for the effort!
0
 
LVL 12

Expert Comment

by:vascov
ID: 9932147
Junoti, so how did you pull it ?

Regarding the namespace, i don't have any authoritative answer, but i've developed the report for testing using the namespace vascov.problems.ee. And it works :)
0
 
LVL 1

Author Comment

by:Junoti
ID: 9932166
Pull the data? Using a stored proc.

I'm not sure about the namespace stuff either, must have been an issue on the other machine I was developing on. The namespace I currently have the report sitting in has a somewhat deep nomenclature, and it works fine also. Hope it still works when it moves to the production server :)

Thanks for the help vascov.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month11 days, 10 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question