insert null in to unique identifier

Hi i am trying to insert a null value in to a sql server
database.



My calling line is



%26lt;cfset VARIABLES.QRYTEST =
Application.dbbatchUpdate.test(variables.bupd.dsnC, '')%26gt;



and my function to unsert the value is



%26lt;cffunction name=''test'' access=''public'' output=''false''
returntype=''boolean''%26gt;

%26lt;cfargument name=''dsn'' type=''string'' required=''yes'' /%26gt;

%26lt;cfargument name=''test'' type=''guid'' required=''no'' /%26gt;

%26lt;cfset var local = structnew()%26gt;



%26lt;cfquery name=''local.test''
datasource=''#arguments.dsn#''%26gt;

update tb_problem

set itemid = %26lt;cfqueryparam cfsqltype=''cf_sql_idstamp''
value=''#arguments.test#''%26gt;

where problemnumber = 'CEN295'

%26lt;/cfquery%26gt;



%26lt;cfreturn true %26gt;

%26lt;/cffunction%26gt;



i keep getting the error ''element test passed to function is
not of type guid''.



I have also tried changing the argument type to any, and the
cf_sql_type to be a char but still no luck .



any ideas on how to solve this.



cheers



stewartinsert null in to unique identifier
Primary key fields can't be null. What are you trying to
accomplish by nullifying it?insert null in to unique identifier
Yes, you can't insert null to a primary key. I haven't tested
this, but have you tried defaulting your test argument?:



%26lt;cfargument name=''test'' type=''guid'' required=''no''
default=''#CreateUUID()#'' /%26gt;

stewart_smith1 wrote:

%26gt; %26lt;cfset VARIABLES.QRYTEST =
Application.dbbatchUpdate.test(variables.bupd.dsnC, '')%26gt;

%26gt; i keep getting the error ''element test passed to
function is not of type guid''.



Because an empty string '' is not a valid guid.





%26gt; also tried changing the argument type to any, and the
cf_sql_type to be a char but still no luck .



That would work for CF. But assuming your column type is
uniqueidentifier, MS SQL would throw an error because an empty
string '' is not a valid
uniqueidentifier





%26gt; %26lt;cfargument name=''test'' type=''guid'' required=''no''
/%26gt;

%26gt; %26lt;cfqueryparam cfsqltype=''cf_sql_idstamp''
value=''#arguments.test#''%26gt;



That will cause an error if nothing was passed in for
arguments.test





%26gt; i am trying to insert a null value in to a sql server
database



I'm assuming ''itemID'' is not your table's primary key since
you're trying to set the value to NULL. If it were the PK, that
would be troubling ... ;-)



Anyway, you need to read up on cfqueryparam's ''null''
attribute. If null=''true'' or null= ''yes'' a NULL value will be
passed to the database. If you want really want to pass a empty
string '''' to your function, you could skip the type checking and
use type=''any''. Then use ''null'' in your cfqueryparam



%26lt;cfargument name=''test'' type=''any'' required=''no''
default='''' /%26gt;

...

%26lt;cfqueryparam cfsqltype=''cf_sql_idstamp''
value=''#arguments.test#'' null=''#not len(trim(arguments.test))#''%26gt;



Or you could keep the type checking and make the argument
optional. Then inside the function, set a default value if the UUID
was not passed.