How to handle NULL values in SQL Server

I'm building an ASP.NET application using VB.NET and SQL Sever 2000.

I'm returning data from a table to my datagrid that contains a datetime field. The date values for some of the records are null in the database table. My strategy (be it good or bad) is to update every field in a record during the datagrid's UpdateCommand event, regardless of whether each field's data has changed.

When I update a record whose date was null and has not been modified, SQL Server places a 1/1/1900 value in the date field. I was expecting the datetime field to remain null. Would someone be able to enlighten me on how best to handle this?

My update code looks like this:

Private Sub dgrdEaTask_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgrdEaTask.UpdateCommand
Dim Success As Boolean
Dim intEaId As Integer
Dim txtEaTask As TextBox
Dim txtDueDate As TextBox

txtEaTask = e.Item.Cells(2).Controls(0)
txtDueDate = e.Item.Cells(11).Controls(0)
intEaId = dgrdEaTask.DataKeys(e.Item.ItemIndex)

'Call the update method in the oEaTasks class
Success = oEaTasks.Update(txtEaId.Text, txtEaTask.Text, txtDueDate.Text)

If Success Then
dgrdEaTask.EditItemIndex = -1
BindGrid()
Else
'There was a problem updating the data
End If

End Sub

Thanks!use dbnull.value incase of null...

hth
Thanks!

I'll research it and give it try.

How to handle large result sets in Windows Forms

A typical szenario in a database-centered application is that a user
(accidentally or on purpose) requests data that would result in a very
large result set, may be millions of rows.
I really like the way how MS Access handles that problem. The first
records are displayed as soon as they are available. When the user
scrolls down he or she experiences the display of the further records
as if they were available locally. Jumping to record number 1,000,000
also as little delay, the same applies for jumping to the end.
Is it possible to achieve the same or a similar behavior in ADO.NET
and Windows Forms? Can I still rely on data binding?
My approach would be:
- Using a background thread for the query to keep the application
responsive and give the user an opportunity to abort the action.
- Using a DataReader to fill a DataTable with the first n records.
- Data bind the DataTable.
- Handle scrolling down or jumping to a specified record by reading
forward from the DataReader into the DataTable. That can't be done
in the background thread, I suppose.
The problem is that I need to keep open the connection all the time
(or until the user scrolles to the end) which has a strong impact on
scalability.
I have a basic idea of how to handle paging in ADO.NET.
How do I support sorting in the DataGrid?
I suppose that is a frequent requirement. There should be some kind of
a design pattern - or even a framework or third-party tool that
supports such an implementation. Does anyone know more?
Thank you in advance.
Daniel FaensenHi Dan,
This is a continual issue. I looked into it extensively about 1 and a half
years ago, and I came up with certain workarounds, but nothing too great.
First, a datareader is not the way to go - it's a fire hose cursor and
doesn't make it possible to do very much very effectively, even searching.
Second, you can always use ado through interop services to bind to a
datasource CONNECTED, not disconnected, as ado .net provides using a
datatable. But you lose a lot of the benefits of ado .net, especially it's
disconnected, non-clashing nature, and it's enormous speed when data is in
memory.
But I think the best solution may be a third party grid, like C1, which I
use (many of the others are probably fine also). C1 also has a data access
class that can do what you described re MS Access, but I haven't chosen to
use it, because my users may want to filter rows while they are in the grid
and that requires gathering the full table and manipulating it, perhaps
again and again during the same session.
Even with the third party grid, I try to get my users to select data
filtered first (both re data and/or re columns to be returned), so that is
all they have in the grid at one time - they usually can do this because
they know what they're after. If they want the whole table - my largest is
about 25 cols wide, 1.5 million rows - they wait for the load - which isn't
terrible (approx 75 seconds to load).
I long ago spoke to MS about adding a connected element to ado .net, and
they said they would, but I don't believe it will happen.
HTH,
Bernie Yaeger
"Daniel Faensen" <faensen@.inf.fu-berlin.de> wrote in message
news:e9099bc4.0411050227.40853353@.posting.google.com...
>A typical szenario in a database-centered application is that a user
> (accidentally or on purpose) requests data that would result in a very
> large result set, may be millions of rows.
> I really like the way how MS Access handles that problem. The first
> records are displayed as soon as they are available. When the user
> scrolls down he or she experiences the display of the further records
> as if they were available locally. Jumping to record number 1,000,000
> also as little delay, the same applies for jumping to the end.
> Is it possible to achieve the same or a similar behavior in ADO.NET
> and Windows Forms? Can I still rely on data binding?
> My approach would be:
> - Using a background thread for the query to keep the application
> responsive and give the user an opportunity to abort the action.
> - Using a DataReader to fill a DataTable with the first n records.
> - Data bind the DataTable.
> - Handle scrolling down or jumping to a specified record by reading
> forward from the DataReader into the DataTable. That can't be done
> in the background thread, I suppose.
> The problem is that I need to keep open the connection all the time
> (or until the user scrolles to the end) which has a strong impact on
> scalability.
> I have a basic idea of how to handle paging in ADO.NET.
> How do I support sorting in the DataGrid?
> I suppose that is a frequent requirement. There should be some kind of
> a design pattern - or even a framework or third-party tool that
> supports such an implementation. Does anyone know more?
> Thank you in advance.
> Daniel Faensen

How to handle data in different tables?

I was wondering if someone could point me to a tutorial that might help me?

I have an application that I'm developing that will display a tabbed form which needs to contain data from different tables. For example, my database has a contact table with typicall information and then it also has a lead table which contains information if the contact is a sales lead. They are linked together by a common contactID column. There are a few other normalized tables also like an AlternateAddress table.

My first problem was that I could only get the binding source to connect to a particular table. This was a problem cause the data that I need to modify is normalized across a few different tables. My solution to that problem was to create a view and join all of my data from the different tables into a 'virtual table'.

My new problem now is I can't generate an update command for a view.

How do I get a binding navigator to use a binding source to navigate through my data for each contact and also be able to update changes to the datasource?

I've read some other posts about this (there's not many) and here's what I think might work. Can someone tell me if there is an easier way?

I would have my dataset with a table for each table in the database (ContactInfo, Lead, and Alternate Address) Each would have a table adapter, but the Lead and Alternate Address tables would have an additional table adapter to fillByContactID. I would have my binding source tied to my contactInfo table, and I would have to catch the position changed event and then re-fill the Lead and Alternate Address tables by the current contactID.

Would it be easier to write my own update function for using a VIEW? Someone please help. Thank you.

create a view in sql server

allow the application to update insert delete from the view

in sql server configure an instead of update,insert and delete trigger

that updates individual table behind the scene

From what I understand, I can't update to a view without writing my own update SQL command correct?

yes you can write your update statement for a view

but inside sql server it can be the instead of triggers

that update the individual base tables behind the scene

most views are not updateable. VS perceive the view as updatable

Adam,

ADO.NET 2.0 is not very apt at dealing with scenarios involving multiple tables. I know it sounds silly, but this is unfortunately the truth. LINQ to SQL and ADO.NET vNext deal with this problem out of the box.

So obviously the question is, "What can you do to deal with this problem today".

The best answer I can give you is to do some further reading into ADO.NET and try and wean yourself away from drag-drop solutions for this problem. A handwritten solution, which is definitely possible in ADO.NET 2.0 (my book covers it well in Chap 10), is your best bet in this scenario.

The "virtual table" scenario you mention - may just be a good shortcut, but you will probably have to hand-specify your own commands.

- Sahil Malik
http://blah.winsmarts.com

I found the answer I was looking for. Basically I followed this example which showed me that the way to have multiple tables on the same form is to have multiple binding sources and then hooking the Data Source of the child tables up the Parent table binding source. Also, hook the Data Member of the child tables up to the Foriegn Key in the parent table's binding source. Anyone else looking for help with this, follow this example and then look at the binding sources.

http://msdn2.microsoft.com/en-us/library/4esb49b4.aspx

how to handle bulk data in gridview

HI

I am having a gridview in a web application. for a search my stored procedure returns more than 30 thousands of records sometimes.

I am not using any direct connection from gridview to databse. i am fill the dataset by executing the stored procedure. If it's a direct connection it will bring few records to fill the first few pages and once we navigate , it will bring another set of data. (my page size is 1000).

how can i handle this? is there anyway to bring few data to dataset and once we navigate to another page we can bring next few 1000s records?

Regards

K.Anvar sadath

Hi Anvar,

You need to use custom paging in Gridview. Please check out this article:

http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

Hope this helps.