Check SQL Field? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Check SQL Field?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-09 : 09:28:01

Anyone know the syntax to check for a field in the sql datbase to see if it's null or not?

Example:

If field1 Is Null Then
Insert blah blah
Else
Update blah blah
End If

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-03-09 : 09:41:37

IS NULL is the correct syntax to use to check to see if a field is null or not. Perhaps if this isn't working for you then you have a problem with your IF-Else Syntax.

IF(SELECT Field1 FROM Table1 WHERE Field1 IS NULL)
BEGIN
   INSERT blah blah
END

ELSE
BEGIN
UPDATE blah blah
END

Dustin Michaels

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-09 : 09:48:53

OR....Maybe...

UPDATE YerTable
SET Field1 = ISNULL(Field1,' '),
Field2 = ISNULL(Field2,' ')

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-09 : 09:55:30

That's what I was looking for, but I can't seem to get that to work for my aspx page.

Here's what I have for the syntax:

IF(SELECT Bits FROM Binary WHERE Bits IS NULL)
BEGIN
Dim strInsert As String = "INSERT INTO Binary " _
& "(ID, TYPE, BITS, BITS_LENGTH) " _
& "VALUES('" & strID & "', 'X', '" & txbDespr.Text & "', '')"

Dim myConnection As New SqlConnection(strConnect)
Dim myCommand As New SqlCommand(strInsert, myConnection)
myConnection.Open()
myCommand.ExecuteQuery()
myConnection.Close()
End

Else
Begin
UPdate...blah...blah..
End

WHen I do the "IF(Select....)" it says that expression expected.

I want to check if a field is null, if it is then insert or else just update that field.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-09 : 10:04:46

Xerxes, thanks for the example, but I'm not following. I'm trying to do a condition that if field1 is Null then insert the following or else just update.

Can you go into more detail?

Thanks.

jhermiz

3564 Posts

Posted - 2005-03-09 : 14:46:54

Why didnt you use what Dustin Michael posted, his was correct.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-09 : 15:09:39

you're combing VB code with SQL code.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-09 : 16:03:48

quote:
Originally posted by chriskhan2000

Xerxes, thanks for the example, but I'm not following. I'm trying to do a condition that if field1 is Null then insert the following or else just update.

Can you go into more detail?

Thanks.

Chris,

In this example:

UPDATE YerTable <-- you'll update this table
SET Field1 = ISNULL(Field1,' '), <-- if Field1 is Null then put a blank else leave as it is
Field2 = ISNULL(Field2,' ') <-- ditto for Field2

Is that better?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-09 : 16:06:40

I guess I'm not being too clear here. I assume that since it's a asp.net forum and I talk about sql, then it's relating to an asp.net page with sql database. hehe.

Sorry for the confusion.

What I'm meaning to do is from my asp.net page, determine if a field within the database is null or not.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-09 : 16:09:04

Why aren't you using stored procedures?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-09 : 16:13:10

Chris -- you should really look into stored procedures. Once you create your stored proc with the parameters needed, you can simply call it from ASP and pass in what you want to insert as parameters (which solves the concatenating a sql string manually issue I tried to explain to you in the other thread) and then SQL Server can process the rest in the stored proc. The stored procedure can search for NULLS or previous existance and decide whether it needs to insert a new row or update an existing one.

Keep in mind that you probably don't want to check to see if a column is NULL, but rather if a particular row EXISTS() already with whatever you are using for the PK.

for example, if a stored proc that let's you either add new customers or update the name of existing ones might look like this:


create procedure UpdateCustomer(@CustID int, @CustName varchar(1000))
as
begin
  if exists(select * from Customers where CustID = @CustID)
     update Customers set CustName = @CustName where CustID = @CustID
  else
     insert into Customers (CustID, CustName) values (@CustID, @CustName)
end

Does this make sense? do you see the benefits of this approach?

And while you are at it, can you please explain why you are using a binary datatype to store strings? Why are you overcomplicating things?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-09 : 16:16:14

Is this what you're looking for?


USE Northwind
GO

sp_help orders
GO

DECLARE @COLUMN_NAME sysname, @TABLE_NAME sysname

SELECT @COLUMN_NAME = 'EmployeeId', @TABLE_NAME = 'Orders'

IF EXISTS(
	SELECT COUNT(*) 
	  FROM INFORMATION_SCHEMA.Columns
	 WHERE TABLE_NAME = @TABLE_NAME 
	   AND COLUMN_NAME = @COLUMN_NAME
	   AND IS_NULLABLE = 'YES')
	PRINT 'INSERT'
  ELSE
	PRINT 'UPADTE'
GO

Brett

8-)

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-09 : 16:59:31

Thanks all for the help. The stored procedure now works.

AS for the binary datatype, that's the way how it is for the table that I'm working with. It's in binary and when I want to view it, I have to do a cast to convert it to string.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources