473,322 Members | 1,734 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Setting Null value in SQL update statement

I have a sub to update a record in a sql server 2000 table.
There's a field FK, which is defined to allow nulls in the table definition,
the field type is integer.
How do I write the sub's definition where the parameter FK is of type
integer but it's value can be NULL?
UpdateDB(ByVal ID as Integer,Optional Byval FK as integer = ? )
Any help would be greatly appreciated
Thanks
Bob
Oct 22 '06 #1
2 2627
Robert Dufour wrote:
I have a sub to update a record in a sql server 2000 table.
There's a field FK, which is defined to allow nulls in the table definition,
the field type is integer.
How do I write the sub's definition where the parameter FK is of type
integer but it's value can be NULL?
UpdateDB(ByVal ID as Integer,Optional Byval FK as integer = ? )
Any help would be greatly appreciated
Thanks
Bob
Hey Bob,

You don't mention which mechanism you are using to update/insert your
data. One way would be to set the default value of FK to some number
you would never expect, eg. -9999, then if FK has this value exclude it
from the insert. As the field allows NULLs you don't need to supply a
value for it.

I don't use Null values too often but I think you could also use
DBNull.Value, but you'll have to check on that.

Steve

Oct 22 '06 #2
Thanks
Bob
"Jester98x" <st**********@wdr.co.ukwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
Robert Dufour wrote:
>I have a sub to update a record in a sql server 2000 table.
There's a field FK, which is defined to allow nulls in the table
definition,
the field type is integer.
How do I write the sub's definition where the parameter FK is of type
integer but it's value can be NULL?
UpdateDB(ByVal ID as Integer,Optional Byval FK as integer = ? )
Any help would be greatly appreciated
Thanks
Bob

Hey Bob,

You don't mention which mechanism you are using to update/insert your
data. One way would be to set the default value of FK to some number
you would never expect, eg. -9999, then if FK has this value exclude it
from the insert. As the field allows NULLs you don't need to supply a
value for it.

I don't use Null values too often but I think you could also use
DBNull.Value, but you'll have to check on that.

Steve

Oct 23 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Sean Anderson | last post by:
ODBC under System DSN Setup Access Driver give it the DSN (Data Source Name) MSA Click on Select and point to the myfile.mdb (your database file)
5
by: Roger Withnell | last post by:
I'm updating a record by opening a recordset, setting the fields and the updating it with objRS.Update. I need to set an image datatype to NULL. objRS("field") = NULL works for datatypes int...
0
by: Steven Hilton | last post by:
I am experiencing odd behavior, and I'm hoping someone can explain why it is behaving this way, and how to get around it... When I update a row in a table with a field's data set to NULL, but...
1
by: Scott | last post by:
In the result of a SELECT statement, how can you change cell values from NULL to BLANK? The following does NOT do it: SET fieldname = ' ' WHERE fieldname IS NULL Also, for colums with a DATE...
2
by: WhiskyRomeo | last post by:
I have a bound textbox (bindings are set at design time) to a dataset column. Sometimes the value of this textbox must be set so that the underlying database field gets updated to null when using...
17
by: NuB | last post by:
I have a sql query that is doing an update of records, how can I add NULL to the field in the database if the field on my screen is blank? example: I have 5 textboxes, and a user can leave some...
3
by: TD | last post by:
I have an unbound form that adds and updates records to one table. I use an INSERT sql statment to add a new record and a UPDATE statement to update a record. I created a function named C2F to...
2
by: Brett | last post by:
My database has 2 tables: Table1 & Table2. If a field is not null on a record in table2, then the not null fields in table1 that correspond to the records in table1 needs to be updated to match the...
8
by: Andrus | last post by:
..NET 2 Winforms application. How to create new setting and set it default value in userSettings section of app.config file or overwrite existing setting value ? I found code below in this list...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.