Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Write null to database

Posted on 2005-05-02
8
Medium Priority
?
372 Views
Last Modified: 2008-02-01
Hi,

     If I have a row from the database in my dataset and I want to write one of the values in that row to null, how do I do it?  Do I just write an empty string "", or does that just give me an empty string?  I want to actually write a null back to the database.

JP
0
Comment
Question by:gleznov
  • 4
  • 3
8 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13909223
You need to use System.DBNull.Value. However, types objects cannot be set to dbnull.value, so you need to either use variables of type object, or set you parameter values using dbnull.value.

For example,

Dim myParam as sqlparameter = mySQLCommand.Parameter.Add("@myParam",sqldbtype.int)
if isnothing(param) then
  myparam.value = system.dbnull.value
else
  myparam.value = param
end if

(Keep in mind that this is pseudo-code only, and requires that you import system.data.sqlclient, declare the connection and command objects, etc)
0
 

Author Comment

by:gleznov
ID: 13909490
OK help me out.  Here's what I have:

                    Dim dr As DataRow
                    dr = DS_Cont1.Tables(0).Rows(a)
                    dr.Item("SetKey1") = "" 

                    Try
                        ' When you call the update ADO.net will go through all the record that have a rowstate  'Modified' and will update that record
                        SqlDataAdapter1.Update(DS_Cont1)
                    Catch ex As Exception
                        'MsgBox(ex.Message)
                    End Try

                    DS_Cont1.AcceptChanges()

This is supposed to update that row's SetKey1 column to null.  How would I change this code to do the same thing?

JP
0
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 800 total points
ID: 13909625
I usually end up making a direct call to the database to do this.

However, you can try:
Dim dr As DataRow
dr = DS_Cont1.Tables(0).Rows(a)
dr.Item("SetKey1") = System.DBNull.Value

Depending on your table, you may get an "invalid cast" error, which is why I make the direct call (either to a stored procedure, or using a SQL string). The latter approach looks like this:
(** This is pseudo-code, and you need to replace myTable, myKey and myKeyValue with the appropriate information
Dim conn as SQLConnection = New SQLConnection
' TODO set your connection string and open your connection
Dim strSQL as String = "UPDATE myTable SET SetKey1=Null WHERE myKey=" & myKeyValue
Dim cmd as SQLCommand = New SQLCommand(strSQL, conn)
cmd.ExecuteNonQuery
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:b1xml2
ID: 13909775
1. There cannot be an invalid cast error as far as DBNull.Value and setting it on the datarow.
2. If the column does not allow nulls, then a constraintexception will be thrown, not an invalidcastexception.
Using the data adapter's Update method is highly recommended as you will begin to understand how to use ADO.NET properly, with DataSets and SqlCommands, you can automate the back-end process rather neatly.

0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13909830
b1xml2,
  I was pretty sure I remembered getting an InvalidCast exception when trying to set an integer field in a datarow to dbnull. However, I could well be wrong.
  On your statement about using the DataAdapter.... we'll have to agree to disagree. The ADO .Net objects have some pretty serious limitations as far as using update and delete methods. In particular, if you have a relatively complex database structure, it becomes tedious to try to use these objects (you end up having to explicitly code the update and delete commands, which is unpleasant if you're acting on the same tables from multiple pages/forms). In addition, you lose efficiency using ADO .Net update and delete commands instead of stored procedures -- especially if you have found code that uses CodeDOM to generate these stored procedures automatically.
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13909936
Chaosian,
>>
 I was pretty sure I remembered getting an InvalidCast exception when trying to set an integer field in a datarow to dbnull.
<<
The invalid cast can only occur on Typed DataSets. In these datasets,
row.Set<column_name>Null() has to be called. whereas row[column_name] = DBNull.Value would work.


1. You do realise that you can use stored procedures together with Data Adapters when using the Update method. You can also create stored procedures on the fly thru the wizard tho I would not recommend it.
>>
In addition, you lose efficiency using ADO .Net update and delete commands instead of stored procedures -- especially if you have found code that uses CodeDOM to generate these stored procedures automatically.
<<

2. You can create a component class, drag and drop stored procedures to them and hook them up to DataAdapters.
>>
if you have a relatively complex database structure, it becomes tedious to try to use these objects
<<
Important: If it is relatively complex, then handcrafting stored procedures is commonly practiced.
2a. As far as complexity is concerned, if necessary you can update multiple tables within the UpdateCommand.
2b. Good for inserts,updates and deletes since it is using the DataRowState value in the Row's RowState (In ADO.NET 2.0, we can directly set this value)

0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13910249
b1xml2,
Would you also use the same data adapter approach in ASP .Net?

And, no, I didn't realize that you could use stored procedures with the data adapter. Probably the biggest reason that I have avoided heavy use of the data adapter thus far is that I have a DB with ~90 tables. I have been able to create a method that will allow me to update ANY of these tables using a simple, overloaded method. This, in turn, has allowed me to create a single form that, depending on the arguments passed to it, allows me to edit the data in any one of these tables. Said form includes validator controls, combo boxes, check boxes, etc all created dynamically. Seems like it would be cumbersome to do this if I had to keep track of the dataadapter as well.
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13914537
1. The data adapter is widely applied in ASP.NET and for good reasons.

2. Before you go and degrade the Update method, it would be a good idea to be better acquainted with ADO.NET before making spurious assertions.
>>And, no, I didn't realize that you could use stored procedures with the data adapter<<

The FIRST THING a serious practitioner of ADO.NET would realise is that stored procedures can be used left, right and centre with ADO.NET. That's the whoie raison d'etre why ADO.NET was created and why the methodology is widely practised, more so in ASP.NET than WinForms (because of the disconnected model).

In ASP.NET, contrary to the many examples of immediate updates in DataGrids, in the real commercial world, it is a lazy update. Only when the user clicks on the Save Button does all the changes in the DataGrid flow through to the Database. In such an event, the amount of code to write manually is wasteful and the Update method comes in very handily.

Next, although we use the UpdateCommand, InsertCommand and DeleteCommand, we can use different processes in the back-end. ADO.NET provides a layer of abstraction. For instance, if a delete is actually a de-activation of the data, then the DeleteCommand would fire off the stored procedure that only updates the data like so

create proc spDeactivateRecord
@Id id
update tbl_record
set active = 0
where id = @Id

I am not saying that the Update method must always be used. However I am saying unless there is a compelling reason, one should always try to stick to the Adapter Update method.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

580 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