Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cannot pass nulls over to sql server 2005 using .NET and a stored procedure

Posted on 2007-07-31
2
Medium Priority
?
379 Views
Last Modified: 2013-11-26
Using VB.NET 2005, talking to SQL Server 2005 using a stored procedure, I am having a heck of a time trying to give an official NULL to the database whenever a user has not entered a particular value in a field. I was sending empty strings, but the folks on the SQL end need official NULLs for any empty field.

In .NET, I am doing an executeQuery with my stored procedure name and the parameters from the textboxes on the GUI (am actually doing this through the MS SQLHelper class). Note that everything compiles fine. The stored procedure itself looks like the following (at the beginning, youll see various different attempts at getting this to work):

ALTER PROCEDURE [dbo].[save_similiar_data]
(
@tableName VARCHAR(10) = '',
@comment VARCHAR(255)= '',
@disposition VARCHAR(20)= '',
@uac1 VARCHAR(46)= '',
@uac2 VARCHAR(46)= '',
@feedback1 VARCHAR(35)= '',
@feedback2 VARCHAR(35)= '',
@feedback3 VARCHAR(35)= '',
@ncr VARCHAR(35)= '',
@coder VARCHAR(20)= '',
@fClass VARCHAR(20)= '',
@status VARCHAR(37)= '',
@rProvider VARCHAR(40)= '',
@bProvider VARCHAR(40)= '',
@rfProvider VARCHAR(40)= '',
@lfDocID VARCHAR(9)
)

AS

DECLARE @tName VARCHAR(10)
DECLARE @sqlStatement VARCHAR(500)


SET @tName = (SELECT dbo.getTDTableName(@tableName))


--Note that char(44) is the ASCII comma and char(39) is the ASCII single quote.
SET @sqlStatement = 'UPDATE ' + @tName + ' SET '
 
if Not @comment like '""'
SET @sqlStatement = @sqlStatement + '[Comment:] = ' + char(39) + @comment +  char(39)

if Not @disposition like '""'
SET @sqlStatement = @sqlStatement + char(44) + ' [Disposition:] = ' + char(39) + @disposition +  char(39)


--SET @sqlStatement = @sqlStatement + char(44) + ' [Unable_to_Code_Reason:] = ' + (case @uac1
--when '' then NULL
--else char(39) + @uac1 +  char(39)
--end)


--if @feedback1 <> char(34)+char(34)
SET @sqlStatement = @sqlStatement + char(44) + ' [Feedback_Reason:] = ' + char(39) + @feedback1 +  char(39)


--if @fClass <> char(34)+char(34)
SET @sqlStatement = @sqlStatement + char(44) + ' [Financial_Class:] = ' + char(39) + @fClass +  char(39)
--if @status <> char(34)+char(34)
SET @sqlStatement = @sqlStatement + char(44) + ' [Status:] = ' + char(39) + @status +  char(39)


SET @sqlStatement = @sqlStatement + ' WHERE TocId = ' + @lfDocID


--Need to do this way due to the dynamic passing of the tablename
EXEC(@sqlStatement)

IF @@ROWCOUNT = 0
      BEGIN
            RAISERROR('Record not found.', 16, 1)
            RETURN 1
      END




I have tried various things, but no NULL shows up in the database cell for the empty fields. Ive tried default values of , default values of , and default values of NULL. Ive tried literally passing NULL. It seems, the only time that NULL shows up in the database cell is when I do not set the empty fields AT ALL in my my update statement. However, in order to do that, I need to put each field in an if-statement to see if I should add it to the sqlstatement. That has also been problematic as it falls through the if when I check it for , NULL, and even char(39)+char(39) for , among others.

Bottom line is, in the Update statement, I need to either not set any empty string field OR give the database official NULLs for these empty fields. It sounds like such a simple damn thing. Please respond if you are a SQL Server expert and are sure your idea will work as Ive already been working on this for several days, and, I believe, Ive tried everything that the average user would have tried. Thank you!!!
0
Comment
Question by:JavaAndStuff
2 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 1600 total points
ID: 19604532
There are several ways to accomplish that.  From where you are now, probably the easiest is to use the NULLIF function in your procedure.  

The eventual SQL string should look like this:

UPDATE YourTable
   SET YourColumn=NULLIF('yourtextparameter','')
  ,SET ...
WHERE ...

However, the challenge is distinguishing setting a column to NULL from not updating it.  As it stands, you need to update every column every time. Maybe that's OK, but if not, you can to backup a step and defaults to NULL, i.e.
ALTER PROCEDURE [dbo].[save_similiar_data]
(
@tableName VARCHAR(10) = NULL,
@comment VARCHAR(255)= NULL,
@disposition VARCHAR(20)= NULL,
...

Then a NULL in the parameter means do not update it and an empty string means update it to NULL.  Something like:

IF @comment is not null
   SET @sqlStatement = @sqlStatement + NULLIF('[Comment:] = ' + char(39) + @comment +  char(39),'')

 







 
0
 
LVL 12

Assisted Solution

by:CmdoProg2
CmdoProg2 earned 400 total points
ID: 19604551
I'm not clear on exactly what you need. However, I think you what to update a NULL in a field if the parameter is an empty string or an actual NULL.  If so, ....

SET @sqlStatement = @sqlStatement + char(44) + ' [Unable_to_Code_Reason:] = ' +
              (case when IsNull(@uac1,'') = '' then 'NULL'
                else char(39) + @uac1 +  char(39) end)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question