August 14, 2008 at 10:50 pm
I use a stored procedure to perform database updates to a table named Driver. I cannot update rows with a DateOfHire that is NULL.
The stored procedure is as follows;
USE [dbCityDispatch]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUpdateDriver]
@OldDriverID int,
@OldLastName varchar(25),
@OldFirstName varchar(25),
@OldMiddleInitial varchar(1),
@OldEmployeeID varchar(7),
@OldDateOfHire smalldatetime,
@OldSeniorityLevel varchar(3),
@OldNextelUnitNo nchar(3),
@OldNextelCellNo nchar(12),
@NewDriverID int,
@NewLastName varchar(25),
@NewFirstName varchar(25),
@NewMiddleInitial varchar(1),
@NewEmployeeID varchar(7),
@NewDateOfHire smalldatetime,
@NewSeniorityLevel varchar(3),
@NewNextelUnitNo nchar(3),
@NewNextelCellNo nchar(12)
AS
DECLARE @RowCount INT
IF EXISTS(SELECT * FROM Driver WHERE DriverID = @OldDriverID)
BEGIN
UPDATE dbo.Driver
SET LastName = @NewLastName,
FirstName = @NewFirstName,
MiddleInitial = @NewMiddleInitial,
EmployeeID = @NewEmployeeID,
DateOfHire = @NewDateOfHire,
SeniorityLevel = @NewSeniorityLevel,
NextelUnitNo = @NewNextelUnitNo,
NextelCellNo = @NewNextelCellNo
WHERE ((DriverID = @OldDriverID)
AND ((LastName = @OldLastName)
OR (LastName IS NULL AND @OldLastName IS NULL)
)
AND ((FirstName = @OldFirstName)
OR (FirstName IS NULL AND @OldFirstName IS NULL)
)
AND ((MiddleInitial = @OldMiddleInitial)
OR (MiddleInitial IS NULL AND @OldMiddleInitial IS NULL)
)
AND ((EmployeeID = @OldEmployeeID)
OR (EmployeeID IS NULL AND @OldEmployeeID IS NULL)
)
AND ((DateOfHire = @OldDateOfHire)
OR (DateOfHire IS NULL AND @OldDateOfHire IS NULL)
)
AND ((SeniorityLevel = @OldSeniorityLevel)
OR (SeniorityLevel IS NULL AND @OldSeniorityLevel IS NULL)
)
AND ((NextelUnitNo = @OldNextelUnitNo)
OR (NextelUnitNo IS NULL AND @OldNextelUnitNo IS NULL)
)
AND ((NextelCellNo = @OldNextelCellNo)
OR (NextelCellNo IS NULL AND @OldNextelCellNo IS NULL)
)
)
SET @RowCount = @@RowCount
RETURN @RowCount
END
ELSE
BEGIN
RAISERROR('[spUpdateDriver] - Record not updated, record not on file.', 11, 1)
END
The Presentation Layer is written using ADO.NET 2.0 with VB 2005. I cannot assign NULL value columns to Text Boxes. How is this done in ADO.NET. I used to get around this problem easily in VB 6.0 by concatenating blank to a database field.
August 15, 2008 at 7:10 am
I think you are asking how to assign a null value to a parameter in ADO.NET. So that is what I am going to answer.
You would do something like this:
Parameter.Value = IIF(txtHireDate.Text = String.Empty, System.DBNull.Value, txtHireDate.Text).
I normally create a function EmptyStringToNull in my .NET app to handle this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply