VALUE IN FIELD NULL OR VOID STRING!?

Jul 23, 2005

Hi!

I got a column, with different dates in it (Ddata type "nvarchar")...
when running a SELECT on this column, I'm trying to filter those
recordsets out, WHERE this column is NULL (I checked the table, there
are "empty" fields in the column):
"SELECT bla FROM bla WHERE myColumn NOT NULL"
but I still receive those "empty" fields in my resultset ...
so I tried it with:
SELECT bla FROM bla WHERE myColumn <> ''
again, "empty" fields in my resultset ....

what's going wrong there? is there a possibility to check what kind of
value I got in my column!?

Thanks!
Peter

View 3 Replies


ADVERTISEMENT

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 Replies View Related

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:


Code:


IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))



Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

thanks

View 2 Replies View Related

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:


IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))


Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

thanks

View 3 Replies View Related

What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?

Aug 29, 2007

Hi,
What is the difference updating a null value to char/varchar type column

versus empty string to char/varchar type column?Which is the best to do and why?
Could anyone explain about this?

Example:

Table 1 : tCountry - Name varchar(80) nullable
Table 2 :tState - Name char(2) nullable
Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state
So,when the state is not present for the country ,i have two options may be - null,''
tCountryDetails.State = '' or tCountryDetails.State = null?

View 9 Replies View Related

Transact SQL :: Database Null Value And String Null

Oct 27, 2015

We have a case where in we should show date based on conditions for e.g if we had a column defined as

col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition

Normaly date values are stored here e.g under col 20150901 .

Case 
when col>'20150901' then 'NULL'
else col
end as Derivedcol

Note this is an extract process and we are presenting data by pumping the data in a table .

Now there is another similar column -colz varchar(10)  which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.

So whats the difference between database null and string null ?

 How can we show database null for the case condition instead of string "null"?

View 10 Replies View Related

Creating Sql-clr Function With Void Return Type

Mar 18, 2008



hi to all,
i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void)
i am creating sql function using SQL script


CREATE FUNCTION dbo.Amex ()

RETURNS NULL

AS CALLER

WITH EXECUTE

AS

EXTERNAL

NAME [AMEX].[UserDefinedFunctions].[Function1];

GO


but i am getting error.
there is something wrong in my sql syntax..can anybody help me?



when creating funtion having signature


public static void Function1()


i am getting error.......................
Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1

can anybody help me?

thanks in advance

Chetan S. Raut

View 4 Replies View Related

SQL Server 2012 :: Finding Longest String Within A String Field

Mar 20, 2014

We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.

View 9 Replies View Related

Method Not Found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.CleanCurrentUserName'

Dec 22, 2006

my goal is to show reports in sharepoint services 3.0 and use this update Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint

i have this problem:

After update my sql server 2005 to sp2 ctp with SQLServer2005SP2-KB921896-x86-ENU.exe and upate reporting services, try to:http://<localserver>:1010/reportserver show the message.

An internal error occurred on the report server. See the error log for more details. (rsInternalError)

Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.CleanCurrentUserName()'.

i this problem is in the Microsoft.ReportingServices.Diagnostics.dll i see the method and in version (9.0.3027.0) dont have, and have in 9.0.1399, what i can do to this work rigth

Just try Microsoft.ReportingServices.Diagnostics.dll 9.0.3033.0 still dont working...........

 

More Details.........

 

<Header>
  <Product>Microsoft SQL Server Reporting Services Version 9.00.3033.00</Product>
  <Locale>en-US</Locale>
  <TimeZone>GMT Standard Time</TimeZone>
  <Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServer__12_26_2006_12_16_30.log</Path>
  <SystemName>CRM</SystemName>
  <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName>
  <OSVersion>5.2.3790.65536</OSVersion>
</Header>
w3wp!library!1!26-12-2006-12:16:31:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
 Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.MissingMethodException: Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.set_CacheUserName(Boolean)'.
   at Microsoft.ReportingServices.WebServer.Global.StartApp()
   at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)
   --- End of inner exception stack trace ---
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing ConnectionType to '0'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing IsSchedulingService to 'True'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing IsNotificationService to 'True'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing IsEventService to 'True'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing IsWebServiceEnabled to 'True'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing ProcessRecycleOptions to '0'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WatsonFlags to '1064'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing SecureConnectionLevel to '0'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing DisplayErrorLink to 'True'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WebServiceUseFileShareStorage to 'False'  as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing EnableRemoteErrors to default value of 'False'  because it was not specified in Server system properties.
w3wp!library!1!26-12-2006-12:16:31:: Unhandled exception was caught: System.MissingMethodException: Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.CleanCurrentUserName()'.
   at Microsoft.ReportingServices.WebServer.Global.Application_EndRequest(Object sender, EventArgs e)
   at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
w3wp!library!1!26-12-2006-12:16:31:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
 Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.MissingMethodException: Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.CleanCurrentUserName()'.
   at Microsoft.ReportingServices.WebServer.Global.Application_EndRequest(Object sender, EventArgs e)
   at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
   --- End of inner exception stack trace ---

View 3 Replies View Related

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.

Feb 13, 2006

We have the following two tables :

Link  ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )

The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.

The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).

We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.

For instance,

Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.

Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.

View 1 Replies View Related

Null && String Ops Again

Jul 23, 2005

I don't know enough math to demonstrate that any numerical operationwith a null should yield a null; although I would guess that it's true.I just don't buy it, however, when dealing with strings and nulls. In asimple table with first, middle and last name columns, I would inferthat a null value in the middle name column means the HR person forgotto ask. A zero length string, however, tells me HR did ask and there isno middle name. Regardless of whether HR asked, when I concatenate thethree fields, I can't think of a sound reason why I souldn't get thefirst and last names.Having now started a flame war, I actually have a question: How do Iset the default for any given database or table so that concat nullyields null is permanently off? I have tried exec sp_dboption'myDB','concat null yields null',false (and many variations ondelimiting the parameters) but it doesn't do jack. I can use setconcat_null_yields_null off but that only lasts for the immediatesession.I've tried to follow the thread through the BOL but I'm left scratchingmy head on how to accomplish this.Thanks.Randy

View 7 Replies View Related

Null In Bit Field?

Jan 19, 2000

sorry, this is probably a really basic question ..........

is there any way to enable the bit data type in SQL Server to accept nulls?


thanks

nyoobee

View 1 Replies View Related

How To Add NULL To INT Field

Jun 12, 2007

Hi all,

I have a table, named systems, with fields as follows.

field datatype
___________________
system varchar(50)
modelID int
.... ....
... .... etc...

The modelID field is related to the Models table. Therefore, the value it expects is a number that already exists in a Models.modelID field.

In the systems table, I allow nulls for the model ID field. If I edit the table directly through SQL front end it allows me to enter a system name for example and leave modelID value as NULL.

My problem occurs when I try inserting into the table from ASP. Whether I leave the value I'm passing to the stored procedure for insert blank or NULL it gives me problems.

Is this a typical issue that can be resolved with a simple property change?

Please help me resolve this, I need to have the ability to leave certain fields empty even though they are related to other tables. Is that possible?

Thank you for any help in advance.

View 11 Replies View Related

How To Set A Field Value To Null

Mar 26, 2014

I have a column called person.last_name, and I want to set this field to NULL if person.last_name ='test'///

I have tried this but don't work: ISNULL ({person. last_name},'<>"test'))

View 7 Replies View Related

Set Field Value To Null

Apr 22, 2007

May i know how to generate a SQL query to set a field of row to Null? Thank you.

View 2 Replies View Related

How To Convert Null To A String

May 12, 2004

I want all the null (blank) values returned by a stored procedure to be shown as a string like "n/a", how to do that easily? Thanks

View 2 Replies View Related

Empty String To Null

Jul 11, 2001

example
create view v_GuestOrder
as
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order


select * from v_GuestOrder
--
Id_Guest OrderName
-------- -----
1 spoon
2 phone
3
4 tv

I need something similar to

Select
id_Guest,
case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder

So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null

Please help , thanks

View 3 Replies View Related

Please Check This 'not Null' SQL String

Jun 20, 2007

the SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.

"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

Any ideas how I could implement this more robustly?
cheers
M

View 14 Replies View Related

INSERTing A String Containing NULL () From PHP App

Jul 1, 2006

Folks, this isn't exactly a 'Getting Started' question, but I couldn't find a more appropriate Application Development forum.

I'm porting an open source PHP application (http://sourceforge.net/projects/gallery) to use SQL Server as a backend. One of Gallery's unit test scripts tests the ability to insert a string containing a NULL character (). It's OK if the string is truncated during insertion, just so long as everything before the is there.

The string being inserted looks like:




$testString = "The NULL character should be escaped !";



(Note the between "escaped " and " !")

The error that the Gallery test script is getting is:




[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'The NULL character should be escaped '.] in EXECUTE("INSERT INTO g2_PluginParameterMap (g_pluginType, g_pluginId, g_itemId, g_parameterName, g_parameterValue) VALUES ('module','unitTestModule',1,'test19476','The NULL character should be escaped !')")



It looks like SQL Server is complaining about the syntax. I've written a much simpler test script in the hopes of reproducing the problem, but I don't know if what I'm now hitting is the same problem or a different one.

My simple script is:




<?php

$localhost = exec("hostname");
$database = "gallery2";
$uid = "g2user";
$pwd = "g2pwd";
$connectString = "Host=PROVIDER=MSDASQL;DRIVER={SQL Server};";
$connectString .= "SERVER=$localhost\sqlexpress;";
$connectString .= "DATABASE=$database;";
$connectString .= "UID=$uid;PWD=$pwd";

$sqlTableDrop = "drop table ljmtemp";
$sqlTableCreate = "create table ljmtemp (col1 nvarchar(100))";
$sqlTableQuery = "select len(col1) from ljmtemp";

// Connect to the db
$db = new COM("ADODB.Connection") or die("Cannot start ADO");
$db->open($connectString);

// Drop & recreate the table
$db->Execute ($sqlTableDrop);
$db->Execute ($sqlTableCreate);

// Insert the test data
//$testString = "The NULL character should be escaped !";
$testString = "This is a test string.";
$res = $db->Execute("insert into ljmtemp (col1) values ('$testString')");
if (!$res) die ("INSERT failed");

// Disconnect from the db
$db->Close();

?>



And it results in:




C:MyServer>php testMsSqlInsertNull.php
PHP Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string 'The NULL character should be escaped '.' in C:MyServer estMsSqlInsertNull.php:27
Stack trace:
#0 C:MyServer estMsSqlInsertNull.php(27): com->Execute('insert into ljm...')
#1 {main}
thrown in C:MyServer estMsSqlInsertNull.php on line 27



I'm not sure if this is the same problem as Gallery is reporting or another one.

It looks like somebody is treating the as a string terminator, but when i double the backslash the literal '' is inserted into the database.

How can I get SQL Server to insert at least everything up to the ?

Hopefully once I have my simple script working I'll be able to figure out what's wrong in the Gallery test.

Thanks.

View 10 Replies View Related

Null For A Datevalue Field

Mar 26, 2008

Hello all,
Can some one help me with this issue.  It seems to be a common question and I have tried a lot of options but nothing seems to work for me. 
I have a text box and this is linked to ajax calendar extender.  They get a date and I am inserting/updating the date in a sql 2000 database using a stored procdure.
This is the code I use to grab the data from my web form 
If txtTrimsEntryDate.Text = String.Empty Then
_oTrim.TrimsEntryDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.TrimsEntryDate = CDate(txtTrimsEntryDate.Text)
End If
If txtEstCompletion.Text = String.Empty Then
_oTrim.EstCompletionDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.EstCompletionDate = CDate(txtEstCompletion.Text)
End If
If txtProjectClosedDate.Text = String.Empty Then
_oTrim.CloseDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.CloseDate = CDate(txtProjectClosedDate.Text)
End If
_oTrim.InsertOrUpdateAProject()
 
 
This is the code for the stored Procedure
Dim oSql As clsSqlServer = Nothing
oSql = New clsSqlServer(AppSettings(_dbName))oSql.AddParameter("@projTrimsEntryDate ", _trimsEntryDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@projStartDate", _startDate, SQLDataType.SQLDateTime, ParameterDirection.Input)oSql.AddParameter("@estCompletionDate", _estCompletionDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@closeDate", _closeDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
 Now when it inserts or updates if the value is null then it inserts "01/01/1900" in the database.  I want the value to be Null in database.
Is there a way to do this.   
 
 
 
 

View 3 Replies View Related

Exclude Row If A Particular Field Is Null

Jun 12, 2008

Is there any way of excluding an entire row if a particular field contains a null value? Even if other fields in the row aren't null...  This is in SQL Server 2005 

View 2 Replies View Related

How To Write Where For Field That Can Be NULL Or Int

Jan 31, 2005

There must be a better way to write this where clause. Can somebody help

if @id is null
begin
(bla bla bla) where parentid is null
end
else
begin
(bla bla bla) where parentid = @id
end

View 9 Replies View Related

T-SQL (SS2K8) :: Always Set A Field To NULL

Jan 26, 2015

I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.

create table dbo.tblCustomer
(
CID int IDENTITY(1,1) not null,
Fnamevarchar(20) not null,
Lnamevarchar(20) not null,
Extravarchar(20) null

[Code] ....

So when this is executed the field Extra is always NULL

INSERT INTO tblCustomer (Fname, Lname, Extra)
VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty')
update dbo.tblCustomer set Extra = 'something'

If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes

I could create an INSTEAD OF trigger

CREATE TRIGGER TR_I_Customer
ON tblCustomer
INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblCustomer
(Fname, Lname, Extra)
SELECT Fname, Lname, NULL
FROM Inserted
END

This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.

What I would like in the instead of trigger is do something like this...

UPDATE INSERTED SET Extra=NULL

Continue with insert without supplying column /value list.

What would be the best way of achieving this, trigger is the only way I could think of?

View 6 Replies View Related

How To Use BCP With Null Date Field

May 28, 2008

My tests have shown that if you use BCP to import records with a
date field as " ", it won't work.

Is there a way to get BCP to accept empty date fields?

View 3 Replies View Related

ASP UPDATE SET FIELD = NULL

Sep 4, 2003

Using an SQL server and writing ASP code trying to set a numeric field to null.
ie ssql = "UPDATE users SET customer_id = null where name = 'Joe'"

Keeps on generating a syntax error

View 2 Replies View Related

Want To Display Null In A Field

Sep 20, 2006

ad1 ad2
-----------------------------
younge joe
null null
youn null

want a result like that. anyone can help me to do it?


ad1+ad2
-----------------------
younge joe
null
youn

View 6 Replies View Related

Doing A 'between' On A NULL In A Date Field

Mar 29, 2007

Hi everyone,

I have two tables - caseinfo and steps.

Caseinfo shows when a particular case entered and exited a particular project. If the project hasn't ended yet, then the end date is NULL.

Steps shows the steps the case has gone through and the dates of those particular steps.

I need to join the tables to show the steps the case went through during a particular project, but I'm having trouble with the NULL values in the end dates.

If I join the tables so that the step date is between the start and end dates of the project, then I get no step information for the cases where the end date is NULL (that is, where the project hasn't ended yet).

Does anybody have any ideas?

Here are my tables, the query that shows the main idea (with the wrong result), and my expected results.

Thank you for reading.


--- create sample data
set dateformat ymd

declare @caseinfo table (caseid int, startdate smalldatetime, enddate smalldatetime)

insert @caseinfo
select 10, '2006-12-23', '2006-12-27' union all
select 20, '2006-12-23', NULL union all
select 30, '2006-12-23', NULL union all
select 40, '2007-1-15', '2007-3-4'

declare @steps table (caseid int, stepnumber int, stepdate smalldatetime)

insert @steps
select 10, 1, '2006-12-24' union all
select 10, 2, '2007-1-3' union all
select 10, 3, '2007-2-5' union all
select 20, 1, '2006-12-26' union all
select 20, 2, '2007-1-7' union all
select 20, 3, '2007-1-9' union all
select 30, 1, '2007-1-14' union all
select 40, 1, '2007-1-23' union all
select 40, 2, '2007-3-2' union all
select 40, 3, '2007-4-16'

--- the main idea (with the wrong results)

select *
from @caseinfo c
left join @steps s on s.caseid = c.caseid and s.stepdate between c.startdate and c.enddate

--- expected result

declare @expresult table (caseidexp int, startdateexp smalldatetime, enddateexp smalldatetime, stepnumberexp int, stepdateexp smalldatetime)

insert @expresult
select 10, '2006-12-23', '2006-12-27', 1, '2006-12-24' union all
select 20, '2006-12-23', NULL, 1, '2006-12-26' union all
select 20, '2006-12-23', NULL, 2, '2007-1-7' union all
select 20, '2006-12-23', NULL, 3, '2007-1-9' union all
select 30, '2006-12-23', NULL, 1, '2007-1-14' union all
select 40, '2007-1-15', '2007-3-4', 1, '2007-1-23' union all
select 40, '2007-1-15', '2007-3-4', 2, '2007-3-2'

select *
from @expresult

View 1 Replies View Related

How Do I Check If A Field Is Null?

Jul 2, 2007

hi guys, I'm using SQLServer 2005 and I was wondering how I check if some variable is null or not? I tryied to do as the code show but it doesn't work:

declare @number int

set @number = (select number from numbers where number_id = 1)

if (@number = null)
begin
set @number = 12
end

Thank you very much

View 3 Replies View Related

Date Field Cannot Be Null!

May 30, 2006

Visual Basic 2005 Professional Edition:

I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.

View 6 Replies View Related

Null Field Problem

May 1, 2008


Hello,

After the "DAYS" column, some fields are coming null. We don't want it to come null. Beside this, i.e; in the "MONDAY"column, the same "NAMESURNAME" is coming more then one. How can I do the grouping by "NAMESURNAME"?


MachType DayPeriod Monday Tuesday Wednesday Thursday Friday Saturday
Negative- || NIGHT || NULL || CAN AK || NULL || FİRDEVS ATEŞ || NULL || NULL
Negative- || NIGHT || NULL || NULL || CEM TEK || NULL || NULL || NULL
Negative- || NIGHT || NULL || NULL || NULL || FİRDEVS ATEŞ || MÜKREMİN EKELİK || NULL
Negative- || NIGHT || NULL || CAN AK || CEM TEK || FİRDEVS ATEŞ || NULL || NULL
Negative- || NIGHT || NULL || NULL || NULL || NULL || ÖMER CANOĞLU || NULL


select MachineType,DAYS,

CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END AS MONDAY,

CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END AS TUESDAY,

CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END AS WEDNESDAY,

CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END AS THURSDAY,

CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END AS FRIDAY,

CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END AS SATURDAY

from

(SELECT SICK.MACHINETYPE,SICK.NAMESURNAME ,

CASE

WHEN SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' THEN 'NIGHT' ELSE '' END AS DAYS,

CASE

WHEN SICK.MACHINETYPE= 1 THEN 'Negative-'

WHEN SICK.MACHINETYPE= 2 THEN 'B(+)'

WHEN SICK.MACHINETYPE= 3 THEN 'C(+)B(+)'

WHEN SICK.MACHINETYPE= 4 THEN 'C(+)' END AS MachineType,

CASE

WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 1 THEN 'MONDAY'

WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 2 THEN 'TUESDAY'

WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 3 THEN 'WEDNESDAY'

WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 4 THEN 'THURSDAY'

WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 5 THEN 'FRIDAY'

WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 6 THEN 'SATURDAY' END AS DIALYSISDAY

FROM SEANCE,SICK WHERE SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' and

SUBSTRING(SICK.EUCLID,1,5)= '18016' AND SEANCEDATEBETWEEN '20080301' AND '20080307'

AND SICK.EUCLID=SEANCE.EUCLID )AS T ORDER BY MachineType

View 4 Replies View Related

Checking If String Is NULL Or EMPTY In SQL

Nov 9, 2007

I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.

View 6 Replies View Related

Insert Empty String Or Null To Sql Db

Jan 12, 2008

Hi:
Trying to insert null value into sql table, but not working, if I use:
 if (strMyText.Length == 0)        command.Parameters.Add("@Text", DBNull.Value); // or using:("@Text", null), or using:("@Text", DBNull) else         command.Parameters.AddWithValue("@Text", strMyText); 
When I go back to table, I see the value is: 'NULL', has single quotation mark, suppose to be: NULL
Where is the problem?
Thanks a lot.
Jt

View 3 Replies View Related

Null Or Empty String Use More Space?

Aug 9, 2005

Hi all,   I have some columns in my database which allows null.  I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved