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 recordsets?

When retrieving a recordset with more than 100.000 records, it needs some time to fill a datagrid.
How can the grid be filled quickly?
I read something about caching, but that doesn't seem to work.
MauriceI think you should use asynchronous query that means you have to open the recordset whith the adSyncExecute and adSyncFetch parameters.
You will get synchronously a first cache of records (so you can populate the visible part of your grid) and then asynchronously the rest of records. To do this you must trap the event FetchProgress on your recordset.

I've got the same problem but I don't know how to detect when the query returns no records. If you have an idea about this, think to me

Pascale
Pascale,

Can you provide me with some source code execute your suggestions?

Maurice.
This method is not very simple to use in C++ but this is a good sample from MSDN

Pascale

********************************************

ADO Events Model Example (VC++)


The Visual C++ section of ADO Event Instantiation by Language gives a general description of how to instantiate the ADO event model. The following is a specific example of instantiating the event model within the environment created by the #import directive.

The general description uses adoint.h as a reference for method signatures. However, a few details in the general description change slightly as a result of using the #import directive:

The #import directive resolves typedef's, and method signature data types and modifiers to their fundamental forms.

The pure virtual methods that must be overwritten are all prefixed by "raw_".
Some of the code simply reflects coding style.

The pointer to IUnknown used by the Advise method is obtained explicitly with a call to QueryInterface.

You don't need to explicitly code a destructor in the class definitions.

You may want to code more robust implementations of QueryInterface, AddRef, and Release.

The __uuidof() directive is used extensively to obtain interface IDs.
Finally, the example contains some working code.

The example is written as a console application.

You should insert your own code under the comment, "// Do some work".

All the event handlers default to doing nothing, and canceling further notifications. You should insert the appropriate code for your application, and allow notifications if required.
// eventmodel.cpp : Defines the entry point for the console application.
//

#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \
no_namespace rename("EOF", "EndOfFile")
#include
#include

//--The Connection events--------------

class CConnEvent : public ConnectionEventsVt
{
private:
ULONG m_cRef;
public:
CConnEvent() { m_cRef = 0; };
~CConnEvent() {};

STDMETHODIMP QueryInterface(REFIID riid, void ** ppv);
STDMETHODIMP_(ULONG) AddRef(void);
STDMETHODIMP_(ULONG) Release(void);

STDMETHODIMP raw_InfoMessage(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_BeginTransComplete(
LONG TransactionLevel,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_CommitTransComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_RollbackTransComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_WillExecute(
BSTR *Source,
CursorTypeEnum *CursorType,
LockTypeEnum *LockType,
long *Options,
EventStatusEnum *adStatus,
struct _Command *pCommand,
struct _Recordset *pRecordset,
struct _Connection *pConnection);

STDMETHODIMP raw_ExecuteComplete(
LONG RecordsAffected,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Command *pCommand,
struct _Recordset *pRecordset,
struct _Connection *pConnection);

STDMETHODIMP raw_WillConnect(
BSTR *ConnectionString,
BSTR *UserID,
BSTR *Password,
long *Options,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_ConnectComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_Disconnect(
EventStatusEnum *adStatus,
struct _Connection *pConnection);
};

//--The Recordset events--------------

class CRstEvent : public RecordsetEventsVt
{
private:
ULONG m_cRef;
public:
CRstEvent() { m_cRef = 0; };
~CRstEvent() {};

STDMETHODIMP QueryInterface(REFIID riid, void ** ppv);
STDMETHODIMP_(ULONG) AddRef(void);
STDMETHODIMP_(ULONG) Release(void);

STDMETHODIMP raw_WillChangeField(
LONG cFields,
VARIANT Fields,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_FieldChangeComplete(
LONG cFields,
VARIANT Fields,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_WillChangeRecord(
EventReasonEnum adReason,
LONG cRecords,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_RecordChangeComplete(
EventReasonEnum adReason,
LONG cRecords,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_WillChangeRecordset(
EventReasonEnum adReason,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_RecordsetChangeComplete(
EventReasonEnum adReason,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_WillMove(
EventReasonEnum adReason,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_MoveComplete(
EventReasonEnum adReason,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_EndOfRecordset(
VARIANT_BOOL *fMoreData,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_FetchProgress(
long Progress,
long MaxProgress,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_FetchComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);
};

//--Implement each connection method-----------

STDMETHODIMP CConnEvent::raw_InfoMessage(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_BeginTransComplete(
LONG TransactionLevel,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_CommitTransComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_RollbackTransComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_WillExecute(
BSTR *Source,
CursorTypeEnum *CursorType,
LockTypeEnum *LockType,
long *Options,
EventStatusEnum *adStatus,
struct _Command *pCommand,
struct _Recordset *pRecordset,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_ExecuteComplete(
LONG RecordsAffected,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Command *pCommand,
struct _Recordset *pRecordset,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_WillConnect(
BSTR *ConnectionString,
BSTR *UserID,
BSTR *Password,
long *Options,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_ConnectComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_Disconnect(
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

//--Implement each recordset method-----------

STDMETHODIMP CRstEvent::raw_WillChangeField(
LONG cFields,
VARIANT Fields,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_FieldChangeComplete(
LONG cFields,
VARIANT Fields,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_WillChangeRecord(
EventReasonEnum adReason,
LONG cRecords,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_RecordChangeComplete(
EventReasonEnum adReason,
LONG cRecords,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_WillChangeRecordset(
EventReasonEnum adReason,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_RecordsetChangeComplete(
EventReasonEnum adReason,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_WillMove(
EventReasonEnum adReason,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_MoveComplete(
EventReasonEnum adReason,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_EndOfRecordset(
VARIANT_BOOL *fMoreData,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_FetchProgress(
long Progress,
long MaxProgress,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_FetchComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

//--Implement QueryInterface, AddRef, and Release-------

STDMETHODIMP CRstEvent::QueryInterface(REFIID riid, void ** ppv)
{
*ppv = NULL;
if (riid == __uuidof(IUnknown) ||
riid == __uuidof(RecordsetEventsVt)) *ppv = this;
if (*ppv == NULL)
return ResultFromScode(E_NOINTERFACE);
AddRef();
return NOERROR;
}
STDMETHODIMP_(ULONG) CRstEvent::AddRef(void) { return ++m_cRef; };
STDMETHODIMP_(ULONG) CRstEvent::Release()
{
if (0 != --m_cRef) return m_cRef;
delete this;
return 0;
}

STDMETHODIMP CConnEvent::QueryInterface(REFIID riid, void ** ppv)

{
*ppv = NULL;
if (riid == __uuidof(IUnknown) ||
riid == __uuidof(ConnectionEventsVt)) *ppv = this;
if (*ppv == NULL)
return ResultFromScode(E_NOINTERFACE);
AddRef();
return NOERROR;
}
STDMETHODIMP_(ULONG) CConnEvent::AddRef() { return ++m_cRef; };
STDMETHODIMP_(ULONG) CConnEvent::Release()
{
if (0 != --m_cRef) return m_cRef;
delete this;
return 0;
}

//--Write your main block of code------------

int main(int argc, char* argv[])
{
HRESULT hr;
DWORD dwConnEvt;
DWORD dwRstEvt;
IConnectionPointContainer *pCPC = NULL;
IConnectionPoint *pCP = NULL;
IUnknown *pUnk = NULL;
CRstEvent *pRstEvent = NULL;
CConnEvent *pConnEvent= NULL;
int rc = 0;
_RecordsetPtr pRst;
_ConnectionPtr pConn;

::CoInitialize(NULL);

hr = pConn.CreateInstance(__uuidof(Connection));
if (FAILED(hr)) return rc;

hr = pRst.CreateInstance(__uuidof(Recordset));
if (FAILED(hr)) return rc;

// Start using the Connection events

hr = pConn->QueryInterface(__uuidof(IConnectionPointContainer),
(void **)&pCPC);
if (FAILED(hr)) return rc;
hr = pCPC->FindConnectionPoint(__uuidof(ConnectionEvents), &pCP);
pCPC->Release();
if (FAILED(hr)) return rc;

pConnEvent = new CConnEvent();
hr = pConnEvent->QueryInterface(__uuidof(IUnknown), (void **) &pUnk);
if (FAILED(hr)) return rc;
hr = pCP->Advise(pUnk, &dwConnEvt);
pCP->Release();
if (FAILED(hr)) return rc;

// Start using the Recordset events

hr = pRst->QueryInterface(__uuidof(IConnectionPointContainer),
(void **)&pCPC);
if (FAILED(hr)) return rc;
hr = pCPC->FindConnectionPoint(__uuidof(RecordsetEvents), &pCP);
pCPC->Release();
if (FAILED(hr)) return rc;

pRstEvent = new CRstEvent();
hr = pRstEvent->QueryInterface(__uuidof(IUnknown), (void **) &pUnk);
if (FAILED(hr)) return rc;
hr = pCP->Advise(pUnk, &dwRstEvt);
pCP->Release();
if (FAILED(hr)) return rc;

// Do some work

pConn->Open("dsn=Pubs;", "sa", "", adConnectUnspecified);
pRst->Open("SELECT * FROM authors", (IDispatch *) pConn,
adOpenStatic, adLockReadOnly, adCmdText);
pRst->MoveFirst();
while (pRst->EndOfFile == FALSE)
{
wprintf(L"Name = '%s'\n", (wchar_t*)
((_bstr_t) pRst->Fields->GetItem("au_lname")->Value));
pRst->MoveNext();
}

pRst->Close();
pConn->Close();

// Stop using the Connection events

hr = pConn->QueryInterface(__uuidof(IConnectionPointContainer),
(void **) &pCPC);
if (FAILED(hr)) return rc;
hr = pCPC->FindConnectionPoint(__uuidof(ConnectionEvents), &pCP);
pCPC->Release();
if (FAILED(hr)) return rc;
hr = pCP->Unadvise( dwConnEvt );
pCP->Release();
if (FAILED(hr)) return rc;

// Stop using the Recordset events
hr = pRst->QueryInterface(__uuidof(IConnectionPointContainer),
(void **) &pCPC);
if (FAILED(hr)) return rc;
hr = pCPC->FindConnectionPoint(__uuidof(RecordsetEvents), &pCP);
pCPC->Release();
if (FAILED(hr)) return rc;
hr = pCP->Unadvise( dwRstEvt );
pCP->Release();
if (FAILED(hr)) return rc;

CoUninitialize();
return 1;
}
Pascale,

Is is easier to handle in Visual Basic?

Maurice
It's more easier in VB. here is a sample in VB. In your case you should detect the FetchProgress event to populate the grid before getting the FetchComplete event. Look also for the following property
'rst.Properties("Initial Fetch Size") = 1
I hope it will help you

Pascale

Private WithEvents rst As ADODB.Recordset

Private Sub Form_Load()

Dim strCnn As String

strCnn = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=Pubs;User ID=;Password="

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient

'Uncomment the next line to workaround the problem
'rst.Properties("Initial Fetch Size") = 1
' For dual processor machines uncomment the next line also
' rst.properties("Background Fetch Size") = 1

rst.Open "select * from publishers", strCnn, adOpenKeyset, adLockOptimistic, adAsyncFetch
rst.MoveLast

End Sub

Private Sub rst_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

MsgBox "FetchComplete executed"

End Sub
Pascale,

Thanks again for your help.

I still have two questions.

I did the following:

Private WithEvents mrstDummy As ADODB.Recordset

Private Sub Form_Load()

Set mrstDummy = New ADODB.Recordset

mrstDummy.Open "select * from
publishers",dnvJFM.ConJfm, adOpenStatic,
adLockReadOnly, adCmdText + adAsyncFetch

Set DataGrid.DataSource = mrstDummy

End sub

The events fetchprogress and fetchcomplete are not fired. Why not?

Why do I need these events to populate the datagrid?
I assign the datagrid.datasource to the recordset.

Maurice
Try this, it works on my computer (VB6)

Dim WithEvents rstPG As ADODB.Recordset

Dim cnn As New ADODB.Connection
Set cnn = New ADODB.Connection

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "Q:\test.mdb"
End With
Set rstPG = New ADODB.Recordset
rstPG.CursorType = adOpenStatic
rstPG.CursorLocation = adUseClient
rstPG.LockType = adLockBatchOptimistic
rstPG.Properties("Initial Fetch Size") = 1

rstPG.Open "select * from tb3", cnn, adOpenStatic, adLockBatchOptimistic, adCmdText + adAsyncExecute + adAsyncFetch

Private Sub rstPG_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

Debug.Print "fetch progress"
End Sub

Private Sub rstPG_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

Debug.Print "fetch complete"

End Sub
Pascale,

Yes offcourse you are right.
I made a mistake.

But still, do I need to catch those events, because I assign my datagrid.datasource to the recordset? The grid will populate itself.

Maurice

how to handle datagrid item command in C#?

Can anyone share a code snippet to handle the link button with Update command in C#? The link button is a template column inside a datagrid.

ASPX code:
<asp:TemplateColumn HeaderText="Update">
<ItemTemplate>
<asp:LinkButton Runat="server" Text="Update" CommandName="Update" ID="Linkbutton1" NAME="Linkbutton1"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateColumn
I tried the following C# code:

private void grdOrderItems_ItemCommand(Object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
if (e.CommandName == "Update")
{
...
}
}

But the handler did not get called when the link button was clicked.

Thanks.Hello, i recommend you check this pageAdding Button Columns to a DataGrid Control.

Good Luck.

How to handle buton event inside datagrid

Hi All

In Datagrid with the help of template i am able to place a button like this

<asp:TemplateColumn HeaderText="Patient_id">
<ItemTemplate>
<asp:Label id=Label1 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Patient_id") %>'>
</asp:Label>
<asp:Button id="Button1" runat="server" Text="Button" OnClick="redir" ></asp:Button>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id=TextBox1 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Patient_id") %>'>
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateColumn>

i added redir in aspx page like this

protected sub redir( sender as object, e as eventargs)

//code

end sub

when i m invoking that with Onclick its generating the error

Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@dotnet.itags.org. Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.

Help Needed

Thank u

Baba

You cant use event handlers directly like that when handling events of a nested control. The nested control bubbles up its event and you can handle them as shown in this article:

http://blogs.msdn.com/paraga/archive/2005/11/26/497120.aspx

Hope this helps,

Vivek

how to handle addhandler to the dynamically added control in datagrid

Hello friends,
I have a datagrid which contain the conditional checkbox added in the second column of the
datagrid.It is done in the
datagrid's itemdatabound event... It is working fine.
But I want to handle the checkbox's checkchanged event...
As the checkbox is added dynamically, i am trying to use the

AddHandler mychkbox.CheckedChanged,AddressOfMe.mychkbox_CheckedChanged

But at any case, this event is not firing ...
In the following condition,
i.e. I am binding the datagrid in the page_load event.
within the condition as

If (Page.IsPostBack =False)Then

bindgrid()
end if
If i removed the condition above & fire the bindgrid() in the page_load event directly,
then it is firing the event:
AddHandler mychkbox.CheckedChanged,AddressOfMe.mychkbox_CheckedChanged
But for my anothere use i have to fire the binggrid event within the Page.IsPostBack =False
Plz tell me the solution how to fire the
AddHandler mychkbox.CheckedChanged,AddressOfMe.mychkbox_CheckedChanged

Thanks & Regards,
Sandeep.

master_sandy wrote:

Hello friends,
I have a datagrid which contain the conditional checkbox added in the second column of the
datagrid.It is done in the
datagrid's itemdatabound event... It is working fine.

Did you mean that the checkbox would be added to your second column conditionally?? If that is the case you may not be able to use the CheckChanged event handler for your checkbox control at all because any events related to the controls within a DataGrid Control needs to be added within the ItemCreated Event of the DataGrid but this event occurs before the ItemDataBound event and hence if you create your Checkboxes conditionally based on some value coming from the Database in your ItemDataBound event, you would not be able to add the Check Changed event handler to you Control as this event handler along with the Control Creation needs to be done in the ItemCreated event which is before ItemDataBound event chronologically. Write back, how exactly you want to create this checkbox control and I could help you further.
hth