Good afternoon, I can not understand how to disable the need to determine the keys and values ​​in the request to T-SQL, I make a request to the server

SELECT Dates, Name, REPLACE(SpecialistName, 'ttt-', ''), WorkerName, PaymentTypeName, CASE InputPlace WHEN 'D' THEN 'Z' WHEN 'K' THEN 'Z' WHEN 'W' THEN 'Z' ELSE 'X' END, COUNT(*), SUM(CASE WHEN Code_Ref IS NOT NULL THEN 1 ELSE NULL END) AS [Обслужено] FROM Cards JOIN Xment ON XmentID = XmentID_Ref JOIN Worker ON WorkerID = MtorID_Ref LEFT JOIN Specialist ON SpecialistID = SpecialistID_Ref JOIN PaymentType ON PaymentTypeID = PaymentTypeID_Ref WHERE Dates BETWEEN '20150401' AND '20150414' AND HelpPlaceID_Ref = 1 GROUP BY Dates, Name, SpecficName, WorkerName, PaymentTypeName, InputPlace ORDER BY Dates, Name, SpecficName, WorkerName, PaymentTypeName, InputPlace

and he returns to me

Could not enable restrictions. At least one line contains a value that violates restrictions on the absence of null values, uniqueness, or foreign keys.

in Management Studio everything works fine, but in the program it gives an error, part of the program code

 InstallConnect.Open(); var StatConnect = InstallConnect.State; if (StatConnect.ToString() == "Open") { ValueStatusSQLC.StatusConnect = true; if (ValueStatusSQLC.ExecuteCommand) { ValueDataConnect.SendCommand.Connection = InstallConnect; ValueDataConnect.SendCommand.CommandText = ValueDataConnect.CodeQuery.ToString(); SqlDataReader ResultQuery = ValueDataConnect.SendCommand.ExecuteReader(); Thread CancelTransaction = new Thread(new ThreadStart(CancelQuery)); CancelTransaction.Start(); CancelTransaction.Join(); int CheckColums = ResultQuery.FieldCount; ValueDataConnect.TablesQuery.Load(ResultQuery); foreach (string Line in ListAnwers) { ComponentForm1.Invoke(new Action(() => ComponentForm1.richTextBox2.AppendText("SQL Server " + NameServer + " > " + Line + '\n'))); } InstallConnect.Close(); if (CheckColums != 0) { if (!ValueStatusSQLC.OpenOutputResultSql) { OutputResultSql StartForm = new OutputResultSql(); ComponentForm1.Invoke(new Action(() => StartForm.ShowDialog())); } else if (ValueStatusSQLC.OpenOutputResultSql) { OutputResultSql StartUpdate = new OutputResultSql(); } } else if (CheckColums == 0) { MessageBox.Show(ResultQuery.ToString(), "Результат соединения", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
  • one
    SUM (CASE WHEN Code_Ref IS NOT NULL THEN 1 ELSE 0 END) - Konst
  • Invalid casting "DateTime" to "Double". WHERE Dates BETWEEN '2015-04-01' AND '2015-04-14' AND HelpPlaceID_Ref = 1 - SergD29
  • one
    '20150401' - format independent of settings. but the error you have is not related to SQL, look in your code, the transfer of parameters may be wrong somewhere - Konst
  • yes .......... on the server this code works fine - SergD29

4 answers 4

"Failed to enable constraints" is not a SQL error. Such an error is thrown by the DataSet class when loading non-consistent data into it. Catch the error in the debugger and see the value of the result of the dt.GetErrors() call from the dt.GetErrors() that threw the error - there will be details in it.

  • Why DataGridView cannot bring a value from DateTime to Double, and why it leads it, this is not in the code. That is, now the main problem with the types transferred from the DataTable to the DataGridView is SergD29

If anyone come in handy. Such an error may occur if the field length is less than in the table.

    When a view is created in SQL Server and then a DataSet is built in the studio, the field length is set in the designer. After that, there is a change (for example) one field in the view that no longer displays a 2-digit string, but a 50-digit one. But in the constructor there will be a maximum length of 2 characters on this field and at startup it will generate the error "Failed to enable restrictions. At least one line contains a value that violates the restrictions on the absence of empty values, uniqueness or foreign keys."

    To fix it, go to the DataSet constructor, select the required field in the table and change its maximum length.

    And the second point happens - when a request is made, the DataSet automatically puts a mark on the unique key for the table, although it is sometimes not needed. We remove this key in the DataSet table and the error disappears.

      The problem is in the DataSet, the default Maxlength of the string is 64 characters, if the overload returns a string longer, then this error is released, therefore it works in Managmet Studio and in Visual Studio it does not