Page 1 of 1 1
Topic Options
#200295 - 2010-10-19 02:44 AM Writing NULL into an SQL database
paulba Offline
Just in Town

Registered: 2006-06-04
Posts: 4
Loc: Melbourne, Australia
Hi I'm trying to remove an entry in a database table and need it to be a "NULL". Does anyone know how this can be done? Below is my base code.

 Code:
        $machine = @WKSTA
        $Extension = "999"
        $UsersKey = @USERID
        $Active = "N"
        $Clientkey="NULL"
        
    
        $cnstring = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=Database1;Data Source=SQLServer6;Password='
        $cmdtext = "SELECT * FROM XXXWorkstation where workstationkey = '" + $machine + "'"
        $cn = CreateObject("adodb.connection") 
        $cmd = CreateObject("adodb.command") 
        $cn.connectionstring = $cnstring 
        $cn.Open 
        $cmd.activeconnection = $cn 
        $cmd.commandtext = $cmdtext 
        
        $rs = CreateObject("adodb.recordset") 
        $rs.cursortype = 3 
        $rs.locktype = 3 
        $rs.Open($cmd)
        

            $rs.Fields.Item("Extension").Value = $Extension
            $rs.Fields.Item("MachineKey").Value = $MachineKey
            $rs.Fields.Item("UsersKey").Value = $UsersKey
            $rs.Fields.Item("Active").Value = $Active
            $rs.Fields.Item("Clientkey").Value = $Clientkey

            
            $rs.Update
        
        $rs.Close
        $cn.Close


If I use a number in the $clientkey all works fine without error.

Thanks in advance.

Paul



Edited by paulba (2010-10-19 06:15 AM)

Top
#200296 - 2010-10-19 06:30 AM Re: Writing NULL into an SQL database [Re: paulba]
paulba Offline
Just in Town

Registered: 2006-06-04
Posts: 4
Loc: Melbourne, Australia
OK, found a (temp) solution.

Use an undefined variable (ie a $xxxx that hasnt been used before) and it will write NULL into the database.

There must be some way to control this so that a variable can be used to enter the null.

Paul

Top
#200297 - 2010-10-19 08:06 AM Re: Writing NULL into an SQL database [Re: paulba]
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1896
Loc: Hilversum, The Netherlands
I use this to assign NULL to a variable:
 Code:
$nullstring = ""

And that works for me with SQL as well.

Top
#200298 - 2010-10-19 09:43 AM Re: Writing NULL into an SQL database [Re: paulba]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
Do you mean a zero length string or a null (undefined) variable? They are very different things.

Setting the variable to "NULL" is neither zero length or undefined, it is a string who's value is the letters N+U+L+L.

 Quote:
Use an undefined variable (ie a $xxxx that hasnt been used before) and it will write NULL into the database.

There must be some way to control this so that a variable can be used to enter the null.


What's wrong with that? I use it myself when I've needed a real NULL value. You can declare the variable as long as you never assign anything to it.

In this example I declare a variable called $NULL and use it wherever I need a NULL value:

 Code:
Global $NULL
Dim $FOO

"Settiing $$FOO..."+@CRLF
$FOO="bar"
"$$FOO now has a type of "+VarTypeName($FOO)+@CRLF

"Resetting $$FOO..."+@CRLF
$FOO=$NULL
"$$FOO now has a type of "+VarTypeName($FOO)+@CRLF

Top
#200361 - 2010-10-23 07:09 PM Re: Writing NULL into an SQL database [Re: Richard H.]
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11165
Loc: Boston, MA, USA
You could also create a string contianing the relevant SQL statement and then have one of the SQL-related UDFs execute the statement.
_________________________
There are two types of vessels, submarines and targets.

Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 739 anonymous users online.
Newest Members
M_Moore, BeeEm, min_seow, Audio, Hoschi
17883 Registered Users

Generated in 0.055 seconds in which 0.026 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org