VB.NET 2003 / SQLS2K
The Stored Procedure returns records within Query Analyzer.
But when the Stored Procedure is called by ADO.NET ~ it produced the
following error message.
---------------------------
Exception Message: Failed to enable constraints. One or more rows
contain values violating non-null, unique, or foreign-key constraints.
---------------------------
---------------------------
Exception Source: System.Data
---------------------------
If I click OK past the error messages I will get data filling the
datagrid. However not as I would like to see it.
Even though it returns the proper data rows and includes all the
columns I asked for, it also returns plenty of columns I didn't ask for
(all the columns of the main table) and all those columns are filled
with "null"
In addition each row header contains a red exclaimation mark whch when
hovered over reads;
"Column 'cmEditedBy' does not allow DBNull.Values."
An interesting thing about this column 'cmEditedBy' is that there is
noting wrong with it and all rows for that column contain data.
I believe this error is a mistake! But it probably indicates some other
problem. How should I track its cause?
M O R E ...
Below is the code in the data layer, the stored procedure, and the data
returned within query analyzer.
\\
'DataAdapter
Friend daView041CmptCy ln As New SqlDataAdapter
'SqlCommand
Private daView041CmptCy ln_CmdSel As New SqlCommand
'Add the command
daView041CmptCy ln.SelectComman d = daView041CmptCy ln_CmdSel
'Select
With daView041CmptCy ln_CmdSel
.CommandType = CommandType.Sto redProcedure
.CommandText = "usp_View_041Cm pt_ByJobCyln"
.Connection = sqlConn
With daView041CmptCy ln_CmdSel.Param eters
.Add(New SqlParameter("@ RETURN_VALUE", SqlDbType.Int, _
4, ParameterDirect ion.ReturnValue , False, CType(0,
Byte), _
CType(0, Byte), "", DataRowVersion. Current, Nothing))
'Criteria
.Add("@fkJob", SqlDbType.Text) .Value = _
"48c64a55-874d-40d0-addc-7245f5d9c118"
'.Add("@fkJob", SqlDbType.Text) .Value = f050View.jobID
End With
End With
//
\\
ALTER PROCEDURE usp_View_041Cmp t_ByJobCyln
(@fkJob char(36))
AS SET NOCOUNT ON;
SELECT
JobNumber,
DeviceName,
ComponentName,
Description,
Quan,
Bore,
Stroke,
Rod,
Seconds,
CylPSI,
PosA,
PosB,
PosC,
PosD,
PosE,
HomeIsRet,
RetIsRetrac,
POChecks,
Regulated,
FlowControl,
PortSize,
LoadMass
FROM tbl040cmpt
INNER JOIN tbl030Devi ON fkDevice = pkDeviceId
INNER JOIN tbl020Proc ON fkProcess = pkProcessId
INNER JOIN tbl010Job ON fkJob = pkjobId
INNER JOIN lkp202Component Type ON fkComponenttype = pkComponentType Id
INNER JOIN lkp201DeviceTyp e ON fkDeviceType = pkDeviceTypeId
INNER JOIN lkp101PortSize on cmSmallint05 = pkPortSizeId
WHERE
(fkJob = @fkJob)
-- fkJob = '48c64a55-874d-40d0-addc-7245f5d9c118'
AND fkComponentType = 2
GO
//
(note - columns are wrapped)
\\
F1111 Clip Driver Cylinder Clip Driver_2 - Top -
Cylinder 9 1.250 2.250 .875 2.250 NULL 0 1 1 0 1 1 1 0 0 1 1/8 NPT NULL
F1111 Punch Mech Cylinder Punch Mech_1 -
Cylinder_2 2 2.100 2.000 1.000 1.234 NULL 1 1 0 0 0 1 1 0 0 1 1/8
NPT NULL
F1111 Clip
Driver Cylinder Bottom 9 2.100 2.000 1.000 1.000 NULL 1 1 0 1 0 1 1 0 0 1 1/4
NPT NULL
F1111 Punch Mech Cylinder Punch Mech_1 -
Cylinder_1 2 2.100 2.000 1.000 1.000 NULL 0 1 0 0 0 1 1 0 0 1 1/8
NPT NULL
F1111 Degate Cylinder Degate 1 -
Cylinder 2 1.188 2.500 .875 1.000 NULL 1 1 0 0 0 1 1 0 0 1 1/8 NPT NULL
F1111 Clip Driver Cylinder Clip Driver 1 -
Bottom 1 1.180 1.250 .875 1.000 NULL 0 0 0 1 1 1 1 0 0 1 1/4 NPT NULL
// 1 2077
dbuchanan (db*********@ho tmail.com) writes: VB.NET 2003 / SQLS2K
The Stored Procedure returns records within Query Analyzer. But when the Stored Procedure is called by ADO.NET ~ it produced the following error message.
--------------------------- Exception Message: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. ---------------------------
Nah, it sounds as if that message is produced by .Net Framework. The
stored procedure pleads innocense.
Even though it returns the proper data rows and includes all the columns I asked for, it also returns plenty of columns I didn't ask for (all the columns of the main table) and all those columns are filled with "null"
In addition each row header contains a red exclaimation mark whch when hovered over reads;
"Column 'cmEditedBy' does not allow DBNull.Values."
Well, that column is not in the result set, so obviously when you try
to populated the DataSet, NULL values is all you get. And apparently
they are not permitted.
I don't have that much experience of ADO .Net, but it sounds to me that
you have run some wizard that has constructed your dataset, and you then
have not been careful which columns to include. (Personally, if I were
to work with data sets, I would probably construct them manually.) Or is
there some thought behind of including columns that are not reported by the
query?
While not relevant to your problem, permit me to point an issue of style
with your query: you table includes six tables, no column is prefixed
with any alias (or the table name). This makes it very difficult for
anyone who looks at query to tell which table, the columns are coming
from. This also mean that if the DBA adds, say, "Descriptio n" to one
more table, the procedure will no longer compile because that column name
is now ambiguous.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Martin Lucas-Smith |
last post by:
I wrote the function below as part of a larger class.
The fopen stage works, and, as according to the documentation at
www.php.net/fopen that succesfully creates a new file.
The fwrite stage returns false, however, on the first time the function is
run, but returns true the second time it is run.
Can anyone suggest why fwrite only works the second time?
|
by: lkrubner |
last post by:
www.php.net says:
>>>>>>>>>>>>
Only for SELECT,SHOW,EXPLAIN or DESCRIBE statements mysql_query()
returns a resource identifier or FALSE if the query was not executed
correctly. For other type of SQL statements, mysql_query() returns TRUE
on success and FALSE on error. A non-FALSE return value means that the
query was legal and could be executed by the server. It does not
indicate anything about the number of rows affected or returned. It...
|
by: Eugene |
last post by:
Hello all,
I've been trying to figure this out for a few days now, and still have
no clue what's going on...
I have a few related tables in MS Access (Clients, Cars, Sales), and a
datagrid, binded to dataview.
Here's a catch - whenever I select a client, and then find a car which
belongs to this client, and click on empty space in datagrid (that is
in gray area below rows) - I get "Index was outside the bounds of the
array" error......
|
by: Michelle Keys |
last post by:
Subject: DataBinder.Eval Error!
Server Error in '/MSPOS' Application.
------------------------------------------------------------------------
--------
DataBinder.Eval: 'System.Data.Common.DbDataRecord' does not contain a
property with the name REPORTTO.
Description: An unhandled exception occurred during the execution of the
|
by: Boris Zakharin |
last post by:
I am using the .Net framework 1.1 and VS.NET 2003.
I have some code where, for some reason, IsPostBack returns false messing up
my code. I have a DataGrid, whose data I am trying to edit. Instead of using
inline editing I have the code set a variable to the primary key of the row
I'm going to edit and then displaying editting controls and filling them
with data. Also I have code in the load event which clears the above
variable when...
| |
by: mehul |
last post by:
CheckBox template always evaluate to False even if checked in a DataGrid
hosted inside a TabStrip in ASP.NET
Hi,
I am trying to develop an ASP.NET application. I am using TabStrip
(which is part of IE WebControls).
Inside a tab I have a datagrid defined as follows:
|
by: Wayne Wengert |
last post by:
I am using VB in a VSNET 2003 Windows application. I've run into a situation
where, when trying to set a bit value in a SQL Server 2000 database I get
errors because the values extracted from a datarow return True or False. In
the snippet below, the SQL becomes "Update myTable SET EQ = True" which
fails with an error that "True" is not a valid column name? I gather that
the datarow object returns True or False for bit fields? I can always...
|
by: Paulustrious |
last post by:
Newbie using VC# Express.....
Part the First
========
Can some one tell me why Example 1 compiles and Example 2 does not...
Example 1
------------
using System;
|
by: =?Utf-8?B?SnVsaWEgQg==?= |
last post by:
Solved it. I had to rewrite my classes in my business & datalayer changing
their constructors.
Julia
"Julia B" wrote:
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |