Dataset Error: Failed to open a connection to the database – Synonym error

Dataset has been in production use for a long time. I use EF a lot now, but these still need maintenance and modifications for user requests. Error message was:

Wizard form
An unexpected error has occurred.
Error Message: Failed to open a connection to the database
Check the connection and network state and try again

Datasets will not connect Synonym

This was on a remote server so I thought there may be a timeout issue. However a search and thanks to:

https://social.msdn.microsoft.com/Forums/en-US/33575eae-24bd-470b-b93e-2083af49cfe8/unable-to-add-new-query-in-tableadapter-query-configuration-wizard?forum=adodotnetdataset

revealed that there may be a bad synonym in the database. The writer of that post used Activity Monitor, but SQL Profiler might also find that there was a wait resource of ExternalResource.

Although I did not check the Activity Monitor, I did check my synonyms and they were pointing at an old server. Repaired them to point at a correct location and the dataset issue has gone.

Solved.

 

 

 

Advertisements

DataTable detect column changed using ColumnChanged event

Background

Because of disconnected nature of VB.Net databinding I have users who have come from MS Access instant save who in .Net are concerned that they fogot to press save. Hence I added CheckForChangesAndPromptForSave. However this is firing too much. I have too much code and something must be changing a column but need to detect which and probably stop changing it.

Solution

Open Dataset and select table with changes. View Code. This generates new code window if not present. On table event use the ColumnChanged event.

Partial Class dsBusiness
    Partial Class QuoteDataTable

        Private Sub QuoteDataTable_ColumnChanged(sender As Object, e As System.Data.DataColumnChangeEventArgs) Handles Me.ColumnChanged
            MsgBox(e.Column.ColumnName)
        End Sub

    End Class
End Class

Error: Format of the initialization string does not conform to specification starting at index 0

The connection string is wrong

How do I … change Config ConnectionString at runtime

See code below


Public Class RuntimeConnection

Const ConStringSettingsName = "MyEntitiesName"

Sub SetRuntimeConnection(UserNameValue As String, PasswordValue As String)
Dim currentConString = ConfigurationManager.ConnectionStrings(ConStringSettingsName).ConnectionString
Dim myWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~")
Dim builder As New EntityClient.EntityConnectionStringBuilder(currentConString)
Dim sqlConStrManager As New SqlClient.SqlConnectionStringBuilder(builder.ProviderConnectionString)
sqlConStrManager.UserID = UserNameValue
sqlConStrManager.Password = PasswordValue
builder.ProviderConnectionString = sqlConStrManager.ConnectionString
myWebConfig.ConnectionStrings.ConnectionStrings(ConStringSettingsName).ConnectionString = builder.ConnectionString
myWebConfig.Save()
ConfigurationManager.RefreshSection("connectionStrings")
'Dim msg = String.Concat(sqlConStrManager.ConnectionString, vbCrLf, vbCrLf, builder.ConnectionString, vbCrLf, vbCrLf, _
' myWebConfig.ConnectionStrings.ConnectionStrings(ConStringSettingsName).ConnectionString)
'MsgBox(msg)
End Sub

End Class

Also see below


Imports System.Configuration

...

Dim config As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
config.ConnectionStrings.ConnectionStrings("myEntities").ConnectionString = value
config.Save()
ConfigurationManager.RefreshSection("connectionStrings")
'MsgBox(config.ConnectionStrings.ConnectionStrings("myEntities").ConnectionString)

.

 

End.

How do I … identify dataset constraint type that has failed when I get exception: System.Data.ConstraintException: Failed to enable constraints. One or more …

How do I … identify dataset constraint type that has failed when I get exception:

This is a nice trick

System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Write a test that Fills the requried table. I use a helper class for datasets, but you could change this to put the tableadapter in here.
Then loop through the columns and remove the constraints
Also remove the primary key constraint
Prove that the Fill now works. Then stop turning the constraints off one-by-one until you find the type of constraint.


<TestMethod()> _

Public Sub FillTest()

Dim dsValue As New <datasetname>
Dim target As New <dataset>Helper(dsValue)
For Each col As DataColumn In dsValue.<tablename>.Columns
If col.Ordinal < 10 Then   'Use to reduce possible columns down
col.MaxLength = -1       'Rem in or out to reduce possible constraints. And next line
col.AllowDBNull = True
End If
Next

dsValue.<tablename>.PrimaryKey = Nothing
Dim aList = dsValue.<tablename>.Constraints

Stop
target.Fill

End Sub

 

Optional helper methods

    Private Sub SetAllowDBNULLTrue(dt As DataTable, Optional MinOrdinal As Integer = 0, Optional MaxOrdinal As Nullable(Of Integer) = Nothing)

        If Not MaxOrdinal.HasValue Then MaxOrdinal = dt.Columns.Count

        For Each col As DataColumn In dt.Columns
            If col.Ordinal > MinOrdinal AndAlso col.Ordinal < MaxOrdinal Then   'Use to reduce possible columns down
                If col.AllowDBNull = False Then
                    Debug.Print("Changing column to allow null " + col.ColumnName)
                    col.AllowDBNull = True
                End If
            End If
        Next

    End Sub

    Private Sub SetMaxLengthToMinus1(dt As DataTable, Optional MinOrdinal As Integer = 0, Optional MaxOrdinal As Nullable(Of Integer) = Nothing)

        If Not MaxOrdinal.HasValue Then MaxOrdinal = dt.Columns.Count

        For Each col As DataColumn In dt.Columns
            If col.Ordinal > MinOrdinal AndAlso col.Ordinal < MaxOrdinal Then   'Use to reduce possible columns down
                If col.MaxLength <> -1 Then
                    Debug.Print("Changing column MaxLength to -1 for " + col.ColumnName)
                    col.MaxLength = -1       'Rem in or out to reduce possible constraints. And next line
                End If
            End If
        Next

    End Sub

 

Windows Forms DataBinding displays ‘System.Data.DataRowView’ in text boxes and other controls

ERROR

Windows Forms DataBinding displays ‘System.Data.DataRowView’ in text boxes and other controls

SOLVED

Was a ComboBox databinding pointing at a field no longer in the dataset.

Problem: Dataset with code behind repeatedly needs Run Custom Tool to compile

Maybe source code control put the vb code in the wrong place. The .vb code should be underneath the dataset in show all files, not alongside it.
Backup the current .vb code, then copy it to clipboard and delete it. Then go into the dataset and right-click > view code > then paste the code in there. The newly created .vb code file will now be behind and everything should work from now on.

Problem: Dataset does not generate Insert command

SOLVED today by:

Changing the select statement to SELECT * FROM then pull the fields in again.

SQL Server Compact Edition and Visual Studio

Motivation

To distribute applications to friends and family

Limitations

  1. No Schemas
  2. No Views
  3. No Stored Procedures
  4. Data types missing include:
    1. nvarchar(max)

Issues

  1. Difficult to switch a dataset from SQL to SQL compact at runtime. Considering having to projects with the datasets one SQL and one SQL Compact. Then having an IObjectData and implementations of both
  2. SQL Server Compact 4.0 is not supported by SSMS SQL Server Management Studio
  3. SQL Server Compact 4.0 is not supported by datasets

Because of limitations of 2 and 3, it may be best to stick with 3.5 SP2 for now. 4.0 seems more linked to Web projects for the minute.

If using 4.0 or in anycase it is possible to use Visual Studio > Views > Server Explorer or also Visual Studio > Data drop down menu item

Tools

Visual Studio Tools for SQL Server Compact 4.0. Unable to find microsoft site but link from here works.
Until this tooling is installed the pre-requisites offers only 3.5 SP2

http://erikej.blogspot.com/2010/12/visual-studio-tools-for-sql-server.html

Error Messages

“Format of the initialization string does not conform to specification starting at index 0” Means that the connection string format is not correct. I had missed out the “Data Source=”

VB.Net programming of SQL Server Replication

The article http://msdn.microsoft.com/en-us/library/ms146869.aspx suggests that Microsoft.SQLServer.Replication.dll is in the .Net references. For me it was not, it could however be found at:
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

Also needed:

  1. Microsoft.SQLServer.Rmo
  2. Microsoft.SQLServer.Replication
  3. Microsoft.SQLServer.Replication.BusinessLogicSupport
  4. Microsoft.SQLServer.ConnectionInfo