Report Dynamic DataSource Error

[Luke]

Hi All,

 

I have created a report in visual studio with two static datasources on it for testing purposes while in development to ensure that the report shows the data correctly.  I have two one for the main report and then there is a table on the report which has another datasource.  If i preview the report everything works as i would expect.  I then have the following code to create the form and assign the datasources dynamically at runtime so that i can select the correct data from the mysql database based on users input.

Dim ReportViewer As New RadfrmReportViewer
           Dim NotificationReport As New rptDCN
           Dim strMainDataCommand As String = "SELECT * FROM tbldcns WHERE dcn_id = '" & lstDCNs.SelectedItem.Tag & "';"
           Dim strPartsDataCommand As String = "SELECT part_name FROM tbldcn_inc_prts WHERE dcn_id = '" & lstDCNs.SelectedItem.Tag & "';"

           Dim mysqlReportMainDataSource As New SqlDataSource(strMainDataCommand, strDBConnectionString)
           Dim mysqlReportPartsDataSource As New SqlDataSource(strPartsDataCommand, strDBConnectionString)

           NotificationReport.DataSource = mysqlReportMainDataSource
           NotificationReport.rpttblParts.DataSource = mysqlReportPartsDataSource
           NotificationReport.txtFooter.Value = "Printed on {Now()} By " & System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(Replace(Environment.UserName, ".", " "))

           Dim DisplayReport As New InstanceReportSource
           DisplayReport.ReportDocument = NotificationReport
           ReportViewer.ReportViewer1.ReportSource = DisplayReport
           ReportViewer.ReportViewer1.ZoomMode = Telerik.ReportViewer.WinForms.ZoomMode.FullPage
           ReportViewer.ReportViewer1.RefreshReport()

           ReportViewer.Show()

 

However when i now run the application and run the code i am getting the error that's attached.

 

 

 

I have double checked the SQL strings and the connection string including copying and pasting them exactly into the static data sources and its still not working.  I'm sure that i am doing something wrong but i can't see what at all.  Please can someone assist me with this.

 

Also the strDBConnectionString variable is also used in several other locations including to populate a listcontrol which allows the user to select the item they wish to generate the report for and that is all working as expected.

I have also double checked the SQL and connection strings at runtime to ensure that they are populating correctly.

Thanks in advanced

Luke

[Todor]

Hello Luke,

I suspect that you haven't set the correct ADO.NET provider to the newly created SqlDataSources. By default, we use the SqlClient Data Provider that can't resolve MySql connection strings, hence the observed error.

I think you need to set the ProviderName property of the data sources to the corresponding ADO.NET provider name for the MySql database.

Regards,
Todor
Progress Telerik

[Luke]

Hi Todor,

Thanks for your reply.

 

I'm not exactly sure what you mean by this im afraid.  I have the MySql.Data.MySqlClient imported at the top of the form and also have the following code in a separate application i have created that is all working as i would expect.

Dim ReportViewer As New FrmReportViewer
        Dim NotificationReport As New rptNotificationPrintOut
        Dim strCommand As String = "SELECT tblNotifications.URN, tblNotifications.Sender, tblNotifications.Reciever, tblNotifications.Date_Sent, " &
        "tblNotifications.Time_Sent, tblNotifications.Title, tblNotifications.Details, tblNotifications.Notification_Type, tblusernotifications.status " &
        "FROM tblNotifications INNER JOIN tblUserNotifications On tblNotifications.URN = tblUserNotifications.Notification_ID WHERE " &
        "tblUserNotifications.User_ID = '" & strUserID & "'"

        Dim sqlReportDataSource As New SqlDataSource(strCommand, strConnectionString)
        NotificationReport.DataSource = sqlReportDataSource

        Dim DisplayReport As New InstanceReportSource
        DisplayReport.ReportDocument = NotificationReport
        ReportViewer.ReportViewer1.ReportSource = DisplayReport
        ReportViewer.ReportViewer1.RefreshReport()

        ReportViewer.Show()

 

Both applications are accessing the same MySQL Server and 

From looking at them the code is the same across both applications.

Can you please provide some further assistance.

Thanks in advanced.

 

Luke

[Todor]

Hello Luke,

You need to use, for example, the SqlDataSource constructor that sets the Provider Name for the component:

Public Sub New ( 
	providerName As String,
	connectionString As String,
	selectCommand As String
)

Note that the SqlDataSource will utilize the specified provider to access the data or the default one that is System.Data.SqlClient. This is done independently on the data retrievals done in the custom code. You may also set the ProviderName later in the code, after creating the SqlDataSource component, for example:

'mainDataSource
'
Me.mainDataSource.ConnectionString = "My.MySettings.TelerikConnectionString"
Me.mainDataSource.Name = "mainDataSource"
Me.mainDataSource.Parameters.AddRange(New Telerik.Reporting.SqlDataSourceParameter() {New Telerik.Reporting.SqlDataSourceParameter("@Year", System.Data.DbType.Int32, "=Parameters.ReportYear.Value")})
Me.mainDataSource.SelectCommand = resources.GetString("mainDataSource.SelectCommand")
Me.mainDataSource.ProviderName = "MySql.Data.MySqlClient"

Regards,
Todor
Progress Telerik