ADO.NET Datasource, getting Logon Failed

[Former Member]

I have worked my way through the samples and walk-throughs in the blogs and help docs on this site to create a report that is based on an ADO.NET Dataset (XML xsd file).

My actual data dataset is generated from a STored procedure and a command/adapter object. I then set the SetDataSOurce of the report to the dataset created from the stored procedure.

My DB is MySQL

Every time I set the reportsource of the viewer to my report document and view the report it says login failed.

Why?

My XSD is just created by hand, no DB connections. It has just 2 fields, login name and last login date. Fields are named exactly as in the DB. In fact if I comment out the line that does 'SetDataSource' for the report and just show the report with no data, just as it comes, it still says Login Failed.

What is happening? Why is it wanting to login to show a report that has no data other than what is defined in the xdd, which is nothing. There is no data attached to the report in design time. If I preview I just get a list of VB colours which I guess is some kind of design time placeholder.

I just dont know what is going on but this is putting a major project in jeopardy. I have 5 working days to get this working and deliver 20 reports. Otherwise my Customer will cancel. I moved to Crystal from a competitors product thinking it would be easier and faster. I hope I didnt make a big mistake..

Please can someone help me get this working so I can get going on my report designs and deliver this project

Thanks

Mark

[former_member183750 · Accepted solution]

Mark, please see this blog re. my troubleshooting suggestions for this issue.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Hi Again Ludek

Hope you had a good vacation.

I had already gone through all those items one by one, step by step.

Just went through the Reporting off ADO.NET Datasets again, from the start, new report, dataset etc, same thing.

Even with the SetDataSource line commented out (ie just show the bare, naked report) it still says Login Failed.

How can a bare empty report with just an xsd to define the fields need a login of any kind?

I also tried the writing out of the XML and using that instead of the xsd generated from the server table being dropped on the dataset page in design. Still the same. Also tried creating an xsd by hand, no server database table etc. Still the same.

This is driving me mad.

Is there any chance we can do a screen share session? I am happy to pay for a support incident? It is nearly 10pm here and I have to get ths working before tomorrow.

Desperate

Mark

PS

Here is my code that does the work.

   Public Function GetReportData(ByVal strReportProcedure) As Boolean

        Dim ada As MySqlDataAdapter

        Dim cmd As MySqlCommand

        Dim ds As DataSet

        Dim conn As MySqlConnection

        Dim prm As MySqlParameter

        Dim strConnection As String

        Dim strPath As String

        'Dim crRptDoc As New crVMSUsers

        Dim crRptDoc As New ReportDocument

        strPath = Server.MapPath("~/Reports/crVMSUsers.rpt")

        strConnection = ConfigurationManager.ConnectionStrings("conVMS").ToString()

        conn = New MySqlConnection(strConnection)

        ada = New MySqlDataAdapter

        cmd = New MySqlCommand

        ds = New DataSet

        Try

            conn.Open()

            cmd.Connection = conn

            cmd.CommandType = CommandType.StoredProcedure

            cmd.CommandText = "uspOrgUsersFetch"

            'all reports are sectioned by org id

            prm = New MySqlParameter("i_OrgId", MySqlDbType.UInt64)

            prm.Value = Session("OrgId")

            cmd.Parameters.Add(prm)

            cmd.CommandTimeout = 120

            ada.SelectCommand = cmd

            ada.Fill(ds)

            crRptDoc.Load(strPath)

            crRptDoc.SetDataSource(ds)

            CrystalReportViewer1.ReportSource = crRptDoc

            Return True

        Catch ex As Exception

            ShowErrorPage("Error Getting Report Data.", HttpContext.Current.Request.Url.ToString, "ReportParameters.aspx.VB", "GetReportData", "Connection: " & strConnection & ". Procedure: " & strReportProcedure, ex.ToString)

            Return Nothing

        Finally

            'tidy up

            prm = Nothing

            conn.Close()

            conn.Dispose()

            cmd.Dispose()

            ada.Dispose()

        End Try

    End Function

[Former Member · Accepted solution]

Hi Ludek

I tried again to wrote out the xml file from the dataset before attaching it to the report document. I then opened up that XML and the XML (xsd) file generated by VS when I dropped the table from the server explorer onto the dataset page in the designer. (following the instructions in the docs in the blog post you sent me)

They are totally different. The dataset xsd generated by VS has all the db connection info in it, not just scheme definitions. So I am gussing that is why the report is trying to connect to the db all the time.

But why? Surely creating an xsd is purely to create a schema template that has no real connection to any data, just a 'picture' of the shape of the data. That is what all the docs are saying it should be. So why have db connection info embedded in there?

And more importantly, how on earth do I create an xsd that will work in future? Using the one written out in the code flow using the dataset.writexml command does in fact work. Surely I dont have to write debug code for every report I want to create just to get an xsd that will work?

I attach the 2 xml files for review. The one called 'DataSet1.xsd' (renamed to DataSet1.xml as the forum will not accept a file extension of xsd) is the one with all the connection info in it and does not work (just keeps asking for login or gives login failed). That was created in the VS environment in the way documneted in the docs provided by Crystal.

The called 'dsVMSUsers.xml' is the one I wrote out in the code using writexml and it works fine.

I have over 20 reports to create in the next week and to have to create an xml dataset file for each one using debug code will be a real pain. Any ideas?

Thanks

Mark

[former_member183750 · Accepted solution]

Hi Mark

Vac was great - aren't they all

Now, using the code:

myDataset.WriteXml(xmlPath, XmlWriteMode.WriteSchema)

Should result in one file (an xml) that has the data definition XML in it.

As far as " The dataset xsd generated by VS has all the db connection info in", I'm not sure. But this is not important for us as we will not be using any data connection anyhow (once this is working...).

So, what I'd like you to do is write out the schema and xml into one file:

myDataset.WriteXml(xmlPath, XmlWriteMode.WriteSchema)

Zip up the above file plus the report. Rename the zip to a txt extension, and attach. The system will attach the txt as a zip. I'll have a look at it tomorrow.

- Ludek

[Former Member · Accepted solution]

Hi Ludek

I have attached the zip with rpt and xml as requested. This all now works but I just cant see how I can create xml files going forward other than by doing the writexml code for every query/procedure we want to create a report for. That will take ages. And as I said, the xsd created in VS has connection info in it which I suspect is why it is prompting for login information. I attached the offending xsd (renamed to xml) in my previous post.

Hope you can suggest how I can create my xsd files foing forward, other than doing a write xml in my code all the time.

Thanks for you help

Mark

Archive.txt.zip

[former_member183750 · Accepted solution]

Hi Mark

You do not need to create xml / sxd files. Only reason I asked for it was so I could test the issue here (e.g.; do the same steps as in the dataset troubleshooting wiki / create a dataset off of the xml, etc. - only for testing purposes.)

There are only two instances where you need xml / xsd files:

1) When creating the report

2) When the data schema (xsd) changes and the report needs to verify those changes.

I suspect that since this now works, (2) above was the issue; the format of what you were offering the report was not matching what it was expecting. Following the steps in the trouble shooting wiki will demo that for you - if this happens again.

- Ludek

[Former Member · Accepted solution]

Hi Ludek

Thanks for your help, again!

Yes it is indeed now working from the XML generated using the writexml debugging method in your Wiki.

But that is just one report of many I have to deliver.

The reports are of 2 broad types.

1. About 20 that were created in CR8 that use XSD and were populated using an ADO data set (in VB6) to push the data into the report (Dataset generated from a stored procedure). I have managed to convert a couple of those using the existing XSD files as the datasource and then using the code to SetDataSource form an ADO.NET dataset (generated from a stored procedure). So I think I am OK on those (fingers crossed)

2. I also have about 20 new reports to deliver from scratch. The first one of those is the subject of all this hassle. In trying to be consistent I thought it best to generate the reports using the same method as the existing ones. That is, create a XSD dataset, layout the report from the XSD template and then SetDataSource from a Stored Procedure derived dataset.

So if I do follow this route I am thinking I will need to create an XSD dataset and right now I have no idea how to do that in a way that will work. I have followed the examples and walk-throughs in your wiki to the letter but I just keep getting 'Login Failed'. Is it because the DB server connection info is being included in the XSD when I create it in VS? Looking at the generated XSD file in Notepad, it is quite different in structure to the XML generated by the WriteXML operation. Did you take a look at the not-working XSD I sent you? Did you notice all the connection stuff in it?

Surely the XSD should not have DB connection info in it?

If you think there is a better way to generate these new reports pls let me know as time is running out for me.

Many thanks

Mark

[former_member183750 · Accepted solution]

Hi Mark

The point I am trying to make is that there should not be any need to be creating new xml files and use those - as long as the dataset matches what the report expects. Here is a kind of a workflow to explain in a bit more details:

1) Create an xml off of the dataset

2) Point the report at this xml in the CR designer

3) If the dataset (and thus the xml created off of the dataset) does not match what the report is expecting, something will go wrong in the designer. Either you will get a field mapping screen popping up, or the report will not have any data in it, or it will have incorrect data / sums, etc.

4) If the field mapping dialog comes up, the report may start working once the fields are mapped (if the dataset is real wonked out, this may not be possible). If data is missing or incorrect, simple verify of the db in CR should resolve the issue.

5) Verify of the database can be set as an option in the report it's self in the report options (Verify on 1st refresh). Also, make sure the report does not have saved data.

See if your issue is resolved when you ensure there is no "saved data" and the "verify" option is set.

- Ludek

[Former Member · Accepted solution]

Hi Ludek

What you say is fine for my existing reports that already have an XSD file for the definition.

But what about the the 20 or more new ones I have to create?

Are you saying that the Visual Studio environment process of creating a Dataset XML from a DB table will not work and I have to write code to generate my own XML file from the WriteXML function?

(Point 1 above)

Seems a bit of a shame that I cant use the VS design tools in the way they were intended ?

Regards

Mark

[former_member183750 · Accepted solution]

Sorry Mark, I'm missing something here - I think I should have stayed on that vacation

There is no problem creating a report off of any datasource using ODBC, OLEDB, etc. If you then need to use a dataset, all you have to do is ensure that the dataset matches what the report is expecting. E.g.; the report will expect the table structure and fields to be identical to what it was created off of. I've done this a number of times. There are instances where we need to use ReplaceConnection to tell the report explicitly that it is to use crdb_adoplus driver, but that is about it. This doc will also help:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/401c4455-a31d-2b10-ae96-fa57af5a...

- Ludek

[Former Member · Accepted solution]

Hi Ludek

Yes I realise CR can create reports of all sorts of datasources. But my issue here is that I have about 30 old reports to migrate to my Web Application using CR for VS. All the old reports were created using an XSD as the dataset schema.

I need to migrate those reports to use the data I am going to push into them from my stored procedures. This is because the new system has a new database with different table structures etc. We are writing the procedures so that the end result of what gets presented to the report looks just the same as the old report used to get from the old system.

Problem is there are some subtle differences between what the XSD is set to from the old report and what is in an XML file generated from the new stored procedures dataset.

And the error that CR gives is not exactly helpful. 'LOGIN FALED'. Doesnt really tell you much and is really not indicative of a date filed being presented as a string (for example).

Further, not all inconsistencies seem to be an issue. For example. The old system used Integers for Id fields. The new system uses bigint (long integer). CR seems happy enough about that.

So I guess my questions are.

1. How can I tell which inconsistencies are causing my issues.

2. Which inconsistencies will upset CR and which can it live with

As I said, I have over 30 reports to do and today I have done 3! It is going to be a long process if I cant find a better way.

Thanks

Mark

[former_member183750 · Accepted solution]

Well the following are the crux of the problem:

Problem is there are some subtle differences between what the XSD is set to from the old report and what is in an XML file generated from the new stored procedures dataset.

The old system used Integers for Id fields. The new system uses bigint (long integer).

Once a report is created, it expects the data to remain unchanged as far as structure is concerned. Some minor differences can be resolved by the "Verify..." option, others can be resolved by the "Field Mapping" dialog. But there is no magic bullet. It's all hard work and may not work in all cases anyhow. Just depends on the differences between the old and new data.

If verify does not help, field mapping is the next step. To do this in the CR designer, you have to create the XSD and map the fields there. You can also map fields at runtime. See; KBA 1601018 - FieldMapping Event does not work in .NET

- Ludek

[Former Member · Accepted solution]

Add the following between the .Load(path) and the .setDataSource(ds)

rpt.SetDatabaseLogon("user", "password", dbServer, Session("dbname").ToString)

I had to add this to get mine to run.

[former_member183750 · Accepted solution]

Problem is, you essentially told the report engine to look at the dataset, then you told it, never mind - here is an actual database connection. So the report engine is not using the dataset. You can test this by providing an incorrect logon at SetDatabaseLogon...

I suspect you'll get a databse log on error, or a prompt.

- Ludek