XSD File

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
LVL 1
JunotiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RuiSousaCommented:
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
JunotiAuthor Commented:
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
vascovCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

JunotiAuthor Commented:
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
vascovCommented:
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
vascovCommented:
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
RuiSousaCommented:
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
RuiSousaCommented:
Sorry!!
Were I wrote:
crViewer.ReportSource = DaReport(myComm);

Please read:
crViewer.ReportSource = MyReport;

0
RuiSousaCommented:
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
JunotiAuthor Commented:
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
vascovCommented:
Try to instantiate the object directly

DailyActivity dr = new DailyActivity();

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

0
vascovCommented:
Did it help ?
0
JunotiAuthor Commented:
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
vascovCommented:
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
JunotiAuthor Commented:
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
vascovCommented:
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
JunotiAuthor Commented:
Yep, data is being returned correctly. I use the same method in several other places, and it works fine.
0
JunotiAuthor Commented:
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
vascovCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RuiSousaCommented:
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
vascovCommented:
Olá Rui,

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

HTH

0
JunotiAuthor Commented:
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
JunotiAuthor Commented:
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
vascovCommented:
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
JunotiAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.