Hi:
I used this statement, select * from table1 where date1 = null, in SQL Query window and got a few records back. Now, I used the same statement in my VB 5 code and no record is found. How do I select all the records in table1 which do not have values in field date1? Thanks for the help.
-Nicole-
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedHi guys, I have made several Access-based CMSs but now I am using SQL Server. I can read the records but my first attempts at writing are resulting in new records (with new ID) but all the fields are null.
I am posting the data from a form to the same page and an if /then statement catches the flag in the URL and runs the update script below. All the field names are correct.
if request.QueryString("add")<> "" then
Dim rsUpdateEntry
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
rsUpdateEntry.Open "SELECT * from generic_country_info" , oConn, 2, 3
rsUpdateEntry.AddNew
rsUpdateEntry.Fields("title1") = Request.Form("title1")
rsUpdateEntry.Fields("body1") = Request.Form("body1")
rsUpdateEntry.Fields("title2") = Request.Form("title2")
rsUpdateEntry.Fields("body2") = Request.Form("body2")
rsUpdateEntry.Fields("title3") = Request.Form("title3")
rsUpdateEntry.Fields("body3") = Request.Form("body3")
rsUpdateEntry.Fields("title4") = Request.Form("title4")
rsUpdateEntry.Fields("body4") = Request.Form("body4")
rsUpdateEntry.Fields("title5") = Request.Form("title5")
rsUpdateEntry.Fields("body5") = Request.Form("body5")
rsUpdateEntry.Fields("image1") = Request.Form("attach1")
rsUpdateEntry.Fields("image2") = Request.Form("attach2")
rsUpdateEntry.Fields("image3") = Request.Form("attach3")
rsUpdateEntry.Fields("image4") = Request.Form("attach4")
rsUpdateEntry.Fields("image5") = Request.Form("attach5")
rsUpdateEntry.Fields("country") = Request.Form("country")
rsUpdateEntry.Fields("dest_url") = Request.Form("dest_url")
rsUpdateEntry.Update
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
end if
Thanks
Mark
Here is the problem that I am struggling with. The structure is:
States: StateID, StateName
Counties: CountyID, CountyName
Cities: CityID, CityName
Zips: Zip, StateID, CountyID, CityID
Regions: RegionID, RegionName
Region_Data: RegionID, StateID, CountyID, CityID
The thing about the region is that it can be defined by states only, in which case CountyID and CityId are NULL, can be defined by Counties too, in which case only CityID is NULL or it can be defined up to City level, in which case all 3 are set to something. Example, Northeast would be all cities from northeast, but Pocono would be just some counties in PA, and so on...
The issue is now selecting all cities that belong into a region... Normally I would join the Zips table with the Region_Data table and retrieve all CityIDs... The issue is that, as I said, the CountyID and CityID might be null for some records, so I am not sure how to retrieve them?
I came up with one idea in which I create 3 temporary tables and I select in them all the records that have 1) only state, 2) only state and county, 3) all 3, then I join each and I union the results...
But I am wondering if there is a way to do the select in one shot?
Any idea? I appreciate your help,
Thank you,
Iulian
Regards,
Iulian
I'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.
I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.
Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.
Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.
Thanks for any help,
Jon
I know I am missing something basic, here.
I have a date field in a table. The field is 'allowed Nulls'. When a certain thing happens in the program, the date is filled in.
At various times, I need to do a query to find all the rows that have no dates entered.
What do I use in my where clause? SQL server does not like 'where date = null.'
Thanks,
Judith
I am using a DAL and i want to insert a new row where one of the columns is DATE and it can be 'NULL'.
I am assigning SqlTypes.SqlDateTime.Null.
But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL????how can i put an empty date in the database?
THANK YOU!!!
Hi, how do i do a select statement with asp.net to return a record if a field is null. I have the following code:
SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE ParentId = @ParentId", cn);.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId != null ? (object) parentId : DBNull.Value;cn.Open();
The variable parentId is a nullable int. However when i pass in a null value it doesn't return anything even though there are records in the database that have a null value for the ParentId field.
Appreciate if someone could tell me what i am doing wrong. Thanks
Been trying to come up with a query to filter-down my sample set intodistinct records. For instance, lets say column1 is a sample set,column2 is the parameter, and column3 is a name and column4 is a type(lets also say there is a fifth column (id) that is an id). What Ineed is one record per type per sample only if type is given, if not,then return that record as well.I've used a subquery to get as close to the desired query is aspossible:select * from table1where id in (select min(id) from table1where column1="A"group by column1, column2)Here's an example of all data for sample "A":1 2 3 4----------A 1 X PA 1 Y PA 1 Z PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VI want output :1 2 3-------A 1 X PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VExcept the above query will exclude the last two records becausecolumn3 is not 'grouped by'.Basically I need to reduce any 'range' of records per sample (columna) where column4 is not null (ie = 'P'), to only one record andkeeping all others. Thanks in advance:-B
View 6 Replies View RelatedCan someone show me what I need to do to have a result set only displaying records from the specified date range. All the tables can relate on Date, shift, and ProductType (Besides UnprocessedProductTracking which can only relate on Date and Shift) I have a lot of trouble with joins...well I'm just not to experienced. Help!
CREATE Procedure dbo.spButterMarTotals
(
@fromdate smalldatetime,
@todate smalldatetime
)
AS
--
--Exec spButterMarTotals '11/15/06','11/15/06'
--
--
Select Distinct ButterballsThrownPounds, ReworkUsedPounds, ReworkMadePounds, CoolersUsedPounds, CoolersMadePounds, TrucksMadePounds,
ResidualFilmPounds, ResidualSqueezePounds, ProducedPounds, Blocks As ProcessingThrown, Weight AS NFCPounds, Pounds AS BirdsEyePounds,
T1AL50Type, T2Type, T3Type, T4Type, AL51Type, FillMach1Type
from tblproductionMaterialmetrics A
Inner Join tblNFCTracking B on
A.ProductType = B.ProductType
Inner Join tblProcessingDtl C on
C.ProductType = B.ProductType
Inner Join tblIngredientProduction D on
D.shift = C.shift
Inner Join tblUnprocessedProductTracking E on
D.shift = C.Shift
Where A.Date between @Fromdate and @todate
how to select record from the table where the data between a range. example between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks
View 2 Replies View RelatedI have the following table:
Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-2-2012 Water
123 1 1-11-2012 Ice
123 2 1-2-2012 Other
123 2 1-13-2012 Other
123 2 1-19-2012 Wind
I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.
So i would want the following from above:
Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-11-2012 Ice
123 2 1-19-2012 Wind
I'm having trouble with the syntax need to accomplish this.
Hi,
I have a base query that will return the ID, StartDate and Code for all IDs. I SELECT only for Codes 5 and 9. For most of the IDs I return a record for both a Code 5 and Code 9. They will have different dates however. How could I select from this base query one record for each ID with the oldest date? The items in yellow are the ones that I would want to return to a report in SSRS. Is there a way to put this data in a temp table and read through it to compare IDs and grab the one with the older date?
ID
StartDate
Code
100
1/2/2000
5
100
4/6/2004
9
205
3/13/2002
5
205
9/10/2002
9
300
10/10/1999
9
407
2/12/2005
5
407
7/17/2007
9
Thanks,
rb
I have a situation where an agent has number of activities for a certain date range. If an agent has multiple activities within certain date range, I would like BALANCE BEFORE from the first activity and BALANCE AFTER from the last activity. Here is my current SQL query that returns the following data:
DECLARE @BeginDate Datetime
DECLARE @EndDate Datetime
Set @BeginDate = '05-1-2015'
Set @EndDate = '05-31-2015'
SELECT
a.AgentName,
R.BALANCEBEFORE,
[Code] ....
AGENTNAME BALANCE BEFORE BALANCE AFTER DATE
DOUGLAS 9738.75 9782.75 2015-05-11
DOUGLAS 9782.75 9804.75 2015-05-12
DOUGLAS 9804.75 9837.75 2015-05-13
In the sample data above, ideally I would like my query to return data as follow:
AGENTNAME BALANCE BEFORE BALANCE AFTER
DOUGLAS 9738.75 (from first activity) 9837.75 (from last activity)
Not sure how I can write sql query to accomplish this.
Hi All,
Here is my story, how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse.
Here is My source table Name: Source_table,
CREATE TABLE dbo.Source_table
(
AccountID INT PRIMARY KEY,
Price int,
Address varchar(30),
added DATETIME DEFAULT GETDATE(),
edited DATETIME DEFAULT GETDATE(),
Flag_Status Int Default 1
)
GO
Here is the Fact_table
CREATE TABLE dbo.Fact_table
(
Fact_table_Key Int Identity (1, 1) NOT NULL,
AccountID INT,
Price INT,
Address varchar(30),
added DATETIME DEFAULT GETDATE(),
edited DATETIME DEFAULT GETDATE(),
editor VARCHAR(64),
Flag_Status Int Default 1,
Inserted_Date DATETIME DEFAULT GETDATE(),
Updated_Date DATETIME DEFAULT GETDATE()
)
GO
Source_table:
AccountID Price Address added edited Flag_Status
---------------- --------- ------------- ----------- ------------- ------------------
1 10 xyz 01-2006 01-2006 1
2 14 abc 02-2006 02-2006 1
3 13 mno 03-2006 03-2006 1
Here is the fact table, table name= Fact_table
Fact_table_Key AccountID Price Address added edited Flag_Status Inserted_Date Updated_Date
-------------------- ------------- --------- ------------ ------- --------- ---------------- ------------------ -------------------
1 1 10 xyz 01-2006 01-2006 1 05-2006 NULL
2 2 14 abc 02-2006 02-2006 1 05-2006 NULL
3 3 13 mno 03-2006 03-2006 1 05-2006 NULL
4 1 17 ght 01-2006 06-2006 1 NULL 08-2006
5 2 18 dmc 02-2006 07-2006 1 NULL 08-2006
6 3 20 kmc 03-2006 09-2006 1 NULL 10-2006
7 1 19 xyz 01-2006 11-2006 1 NULL 12-2006
8 2 19 klm 02-2006 01-2007 1 NULL 02-2007
9 3 21 pqr 03-2006 03-2007 1 NULL 04-2007
Here is what i am thinking: But it gives me Wrong Flag_Status.
UPDATE Fact_table
SET Flag_Status =
CASE
WHEN (SELECT Max(Updated_Date) From Fact_table
WHERE AccountID IN (SELECT AccountID FROM Fact_table
Group By AccountID Having Count(AccountID)>1)) >
(SELECT Max(edited) From Source_table
WHERE Flag_Status = 1)
THEN 1
WHEN (SELECT AccountID From Source_table
Group By AccountID
Having Count(AccountID) =1) =
(SELECT AccountID From Fact_table
WHERE Updated_Date IS NULL)
THEN 1
ELSE 0
END
Hello! I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like SELECT * FROM My_Table Where (([End] = @EndDate) OR ([End] = null)) @EndDate is a valid DateTime, but the second OR condition doesn't work. What is the best way to check if the [End]-field is empty or null? Thank you very much!
View 1 Replies View RelatedI am working on a query that needs to return the record order number with the most recent requested delivery date.
It seems to work most of the time, but I have found some glitches for some of the items.
example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014
I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014
This query is what I have so far:
SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'
[code]....
WHERE s.rn = 1What am I missing in my query> how can I change it so it only returns the most recent date?
May I know how to use a "date" to select out previous 14 days record from the table? and find the duplicated records?
-- sort out duplicate order from tblOrder
Select * FROM tblOrder
WHERE DDay > @prmDDay("day", -14, getDate())
Group by DDay
Many thanks~~~~~
Fr New Learner
Hi
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks
Is there a way to write a select statement that will pull only fields that are not null?
View 2 Replies View Relatedhow can i write a SQL query that will pull all records that are equal to NULL??
View 1 Replies View RelatedI am working on Sql Server 7.0 with a group of Visual Basic programmers that have made the following request:
1.They want all decimals/integer fields in the database to default to 0(zero).
Is there an way to do this for all fields instead of on an individual basis?
2.They don't want a null returned on any character string field.
What is the best way to take care of these 2 requests? Thanks in advance.
Karen Suenram
Hi,
I have a query like this one
SELECT expense_id, CAST(expense_id AS char(10)) + ' - ' + CAST(trip_km AS char(5))+ ' - ' + CAST(expense_amount AS char(5)) + ' - ' + charge_centre AS ExpenseDesc
If charge center is null, I need to ignore this field. How can I achieve this? The reason is that if any of the field is null, it will return ExpenseDesc as null.
Thanks
How can I make empty cells show NULL on my table? Some cells show NULL others won't. Does this mean that they have contents?
The reason being is that, when I use the code
Select *
From Employees
Where JobDescription1 Like '%montly%'
Those with empty jobdescription1 show with the legitimate results.
Any help please?
Thanks!
Im trying to use MS SQL server mananagement studio to greate a view to recive information from my database.
The problem i have is that i include a tabel where a post have "Null" then that post will not be shown i my qustion/View.
How can i make soure that i get all the post even if they have "null" in a field where it do not mather(its not a Key)
Greatful for very fast, help. Need this to be solved tomorrow :-(
Mitmit
Requirements:
1) Cannot have results in which both m.homephone & d.homephone are both null, or both m.workphone & d.workphone are both null in the result set
2) We also do not want to see any combos where one homephone is null and the other has only the char 0....same for workphone...no null & 0 combinations.
e.g.
m.homephone d.homephone
null 0
0 null
The phone fields are varchar
I know this is hidiously wrong but is my first sloppy attempt at best:
select m.number, m.homephone as master_homephone, d.homephone as debtor_homephone, m.workphone as master_workphone, d.workphone as debtor_workphone
FROM master m
INNER JOIN debtors d ON d.Number = m.number
where (d.homephone <> m.homephone OR d.workphone <> m.workphone)
AND (d.homephone IS NOT NULL AND m.homephone IS NOT NULL)
AND (d.workphone IS NOT NULL AND m.workphone IS NOT NULL)
AND NOT ((d.homephone IS NULL AND m.homephone = '0') OR (d.homephone = '0' AND m.homephone IS NULL))
AND NOT ((d.workphone IS NULL AND m.workphone = '0') OR (d.workphone = '0' AND m.workphone IS NULL))
I've 2 date fields clidlp,clidlc in my data base table. How do I find the newest dates between the fields? Thanks for your help!
View 1 Replies View RelatedHi there,
I have a fairly generic question regarding database design.
I am working on my first database using SQL Express.
I am testing the database by inserting records into the pre defined tables.
Sometimes I forget to input text into a non null field. When
this happens it displays an error and then skips a few rows
which makes my row numbering messy. For example say I have
Row 1, Row 2 Row 3 all nicely formatted but on Row 4 I make a mistake
this row then disapears and their is gap between 3 and 5 where 4 was.
Although I am just testing, I was wondering what happens in a real
scenario if an inexperienced data input person makes a few mistakes it
makes the database numbering systems look bad. In this case
is it common to make most fields NULL to allow for admin errors??
I have a result set stored in a temporary table variable. The fields need to have heirarchy when they finaly get displayed to the user. I need to replace the repeating fields per group with NULL and leave out only one set of unique records per group.
I want to do something like this- make the fields in red NULL while leaving out the ones in green.
{ Col.Group I } { Col. Group II }
Col1 Col2 Col3 Col4 Col5
------- ------- ------- ------- -------
ValA ValA2 ValA3 ValA40 ValA50 {
ValA ValA2 ValA3 ValA40 ValA50 Row. Group A
ValA ValA2 ValA3 ValA41 ValA51 }
ValB ValB2 ValB3 ValB40 ValA50 { Row. Group B
ValB ValB2 ValB3 ValB41 ValA51 }
I was able to achieve this by using @@ROWCOUNT with in a while loop by performing one UPDATE at a time (SET ROWCOUNT = 1). But i have to run Count(*) each time I loop through the update query to figure out the remaining number of rows I have per group as I need to leave out one untouched. But the Count function is taking too much time and I'm having performace issues.
Any idea how I might be able to do this in my stored proc. efficiently?
Thanks,
Samtadsh
Hi all
I have a calculated fields in report designer such as a + b + c + d. In sql server if I run this query
select a + b + c + d from table1
and any of a,b,c or d is null, the result is null.
whereas in calculated fields, it does not return null but infact ignores the null value and treats it as zero.
I want my calculated field to be null if any of the values are null.
Is it possible? I cannot use the isNothing function because I have too many fields and it will be quite cumbersome.
Thanks
Hey everyone,
This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.
Here is my current SELECT statement:
SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1
My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.
The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.
Can someone tell me what I am missing? Is there a function that I can use for this?
Thanks,
Using Transact-SQL how can I copy all fields except one from one record to another? The field in question being the identity field. Since, this field cannot be duplicated a simple INSERT statement fails. How can I specify an exclusion list of fields?
-Sumit
I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database. I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture. I have been following the principles of Scott Mitchell's tutorials. I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor. Thus where the first version had code: -
Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'" Dim message As String = "" Dim Found As Boolean = False Try Using connection As New SqlConnection(GDB_AppSettings.Connection) Dim command As New SqlCommand(queryString, connection) connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
If reader.Read() Then Found = True _USRid = reader("USRid") _USRPassword = reader("USRPassword") _USREmail = reader("USREmail") _USRTitle = reader("USRTitle") _USRName = reader("USRName") _USRRole = reader("USRRole") If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End If End If reader.Close() End Using Catch ex As Exception If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then Return "Cannot open database to logon" Else Return Err.Description End If End Try
the new version is much more elegant: -
Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter()
Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid) If tbUser.Count <> 1 Then ' Anything other than 0 or 1 should be impossible Return "User not found" End If
Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0) _USRid = userid _USRPassword = password _USREmail = trUser.USREmail _USRTitle = trUser.USRTitle _USRName = trUser.USRName _USRRole = trUser.USRRole _USRAgreedConditions = trUser.USRAgreedConditions
However, there is a problem. The database field USRAgreedConditions is a Datetime field that can be null. The new version works perfectly when it is NOT null, but throws an exception: -
System.Data.StrongTypingException was unhandled by user code Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull." Source="App_Code.wt2vzoc1" ....
There is no point in writing: - If Not IsDBNull(trUser.USRAgreedConditions) Then _USRAgreedConditions = trUser.USRAgreedConditions End Ifbecause the exception occurs within the automatically-created code in the data access layer. I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String. Of course USRAgreedConditions is a Datetime field, so I can't change the property.
It seems that my only options are: -1. To stick with the old query-building code. But this doesn't give me the advantages of a proper 3-tier architecture2. To change the generated code in wt2vzoc. This MUST be a bad idea - surely I should leave this code untouched. Besides, what if the table adaptor has to be regenerated when I change the table design?3. Code a Try block within the business layer: - Try _USRAgreedConditions = trUser.USRAgreedConditions Catch ex As Exception _USRAgreedConditions = Nothing End Try
This seems to work OK, but seems less elegant than the original code in the old version: - If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End IfIs there a better way?