StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I run a query on a dataset that returns different columns to the table?
text
Body
copied!<p>I'm trying to pull some data from a SQL table in my dataset using C#.</p> <p>In this case I do not need all the columns just a few specific ones, however as I am not pulling back a column with a mandatory NOT NULL, the copy of the table is throwing the exception </p> <pre><code>"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." </code></pre> <p>I'm sure I can work around this by returning the unNullable column to my table however I want to avoid returning unnecessary data.</p> <p>The query I am using which throws this exception is </p> <pre><code>SELECT DeviceSerialNumber, BuildID, LEMSCredentialsID, LEMSSoftwareID, OwnerID, RestagedDate FROM tblDevice WHERE (DeviceSerialNumber = @SerialNumber) </code></pre> <p>This excludes the mandatory column "tblLocationID". In reality though, this column is only mandatory when considering the database as a whole, not when I just need build and software detail for use in my form.</p> <p>I am trying to use this query in the following manner.</p> <pre><code>private DataTable dtDevice; dtDevice = taDevice.GetDataByDeviceSN_ForRestage(txtDeviceSerial.Text); </code></pre> <p>I notice when browsing the preview data, Visual Studio draws columns that are not specified in my SQL including the column tblLOcationID it does not however populate these columns with data. </p> <p>Is there anyway I can use this data in a temporary table without importing the non-nullable aspect of the column? preferably by not pulling throught the non-selected columns at all?</p> <p><BR><BR><BR><BR><BR> For completeness, here's the definition (- minus foreign key definitions) of the source table:</p> <pre><code>CREATE TABLE [dbo].[tblDevice]( [DeviceSerialNumber] [nvarchar](50) NOT NULL, [Model] [nvarchar](50) NULL, [ManufactureDate] [smalldatetime] NULL, [CleanBootDate] [smalldatetime] NULL, [BuildID] [int] NULL, [Notes] [nvarchar](3000) NULL, [AuditID] [int] NULL, [LocationID] [int] NOT NULL, [SimID] [int] NULL, [LEMSCredentialsID] [int] NULL, [LEMSSoftwareID] [int] NULL, [OwnerID] [int] NULL, [RestagedDate] [smalldatetime] NULL, [Boxed] [bit] NULL, CONSTRAINT [PK_tblDevice_1] PRIMARY KEY CLUSTERED ([DeviceSerialNumber] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload