How can I run a query on a dataset that returns different columns to the table?

Answer 1

The column is mandatory because the metadata tells the client that to add any new rows (or alter rows), values for this column have to be provided. If you aren't altering the data and don't need two-way mapping, something more lightweight like a DataReader might be more appropriate.

Answer 2

I assume taDevice is a tableadapter ? generated with the typed dataset ?

You could also generate the "FillDataByDeviceSN" method (you can generate a Get and a Fill)

then you get (pseudo-ish code):

tblDeviceDataTable dtService = new tblDeviceDataTable();
dtService.tblLocationID.AllowDbNull = true;
taDevice.FillDataByDeviceSN(dtService,txtDeviceSerial.Text);

Answer 3

Here are a few options:

Create an "untyped dataset" for just the fields you want to use.

OR

Change the NullValue property of the NOT NULL field in your typed dataset from the default of "throw exception" to "empty".

OR

Try what Patrick suggested for setting the EnforceConstraints to False.