Solved

XSD File

Posted on 2003-12-04
25
879 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
  • 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 500 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now