How to handle Numeric or Date Null Data or COlumn when creating query to insert into excel sheet

I am creating Excel Sheet using Microsoft.Jet.OLEDB.4.0 Create Table Query. It works well and I can transfer my data to Excel Sheet.

The problem is whenever I have some numeric or date data and it is null then it passes nothing in query so there is error of Insert Into.

Whenever I have stateid is null then it forms query like Insert Into City (CityID,City,StateID) Values (1,'Mumbai',).

How to handle Numeric or Date Null Data or COlumn when creating query to insert into excel sheet?

Excel is not true structured database. In a case if columns inside of the spreadsheet contain mixed data, provider will start to return and store NULL values if it cannot detect type of the cell. For example, assuming spreadsheet has column A1 where some values are strings and other one dates. First provider scans first N rows and detects datatype of the column. If it detects that column contains strings, then it will work with the dates as with NULLs and vice versa. The only thing you could do with Jet 4.0 is to force it to treat all the values as strings, if you add IMEX=1 to the Extended Properties of the connection string. But in this case you will lose data types completely. I faced this issue long time ago and I decided to go my own way of creating component for it. Another way is to use Office Tools from Microsoft, but they still COM based and require a lot of the resources during run-time

how to handle nulls in a RadioButtonList

My webform RadioButtonList is bound to a column from a Sql Server dataSource. The radioButtonList is in a formView which starts up in edit mode. My question is how to handle null values because the only way I could get the page to come up when the columnn value is null is to have an item in the radio list which is setup for nulls such as, "Unknown" in the Text property and -1 in the value property. I understand that the RadioButtonList control is supposed to default to nulls which means that nothing in the radio list is selected - this is the behavior that I want, but I get the error below complaining that null (-1) is not valid because it does not exist as a valid value in the radio list: I have tried -1, '', etc. with no success.

'RadioButtonList2' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

How should I handle nulls at page startup?

Thanks.

While setting SelectedValue = null will normally allow no default selection for the RadioButtonList control, trying to set it to null in binding (using either Eval or Bind) doesn't work and throws an exception. Going through a method, however, seems to work as follows:

[ASPX]

<ItemTemplate>

<asp:RadioButtonListID="RadioButtonList1"runat="server"SelectedValue='<%# myFunc(Eval("NumValue").ToString()) %>'>

<asp:ListItemValue="1"Text="Item 001"></asp:ListItem>

<asp:ListItemValue="2"Text="Item 002"></asp:ListItem>

<asp:ListItemValue="3"Text="Item 003"></asp:ListItem>

<asp:ListItemValue="4"Text="Item 004"></asp:ListItem>

<asp:ListItemValue="5"Text="Item 005"></asp:ListItem>

</asp:RadioButtonList>

</ItemTemplate>

[code-behind: C#]

publicstring myFunc(string val)

{

if (val =="")

returnnull;

else

return val;

}

Thank you, the myFunc worked great for handling nulls, But it caused another problem with the update statement. My update statement no longer works because it has lost the identity of the bound variable somehow.

The default SelectedValue works fine:

aspx:

SelectedValue='<%# Bind("myField") %>'

UpdateCommand="update myTable setmyField=@.myField

But with your custom SelectedValue code I get the error at the bottom:

SelectedValue='<%# myFunc(Eval("r1").ToString()) %>'

UpdateCommand="update myTable setmyField=@.myField

here is the error:

Must declare the variable'@.myField'.

[SqlException (0x80131904): Must declare the variable'@.myField'.]

The customization to SelectedValue caused this, any idea why?

Thanks.

I can see why this would be an issue when trying to update since it deviates from the binding expected by the framework. Interesting situation you have here. I'll have to look into this before giving you an answer. I wouldn't be surprised if someone already faced such situation and resolved it.
Thanks. I am appreciative of your help.
I tried to see if there's a workaround for this issue, but I'm afraid there isn't as far as I can see. I thought that there might be a way to bind a NULL value into SelectedValue property (which can be done if a direct null value is assigned), but when binding to a table column, it rejects it for some reason. This problem/behavior may be related to the fact that RadioButtonList isn't meant to provide an option for "no selection" (i.e. once a selection is made, you cannot go back to a no-selection state), although the starting point allows no selection state. Perhaps the DropDownList control serves your purpose better since you can have a default value that represents no selection. Another option might be to add another item for your RadioButtonList that represents no selection and default what would have been null value to that choice. Sorry I don't have a better news for you.

Here's another way to handle this. Create a user control with your RadioButtonList in it, and create a property in it called SelectedValue such as following:

public string SelectedValue
{
get
{
return RadioButtonList1.SelectedValue;
}
set
{
if (value != "")
RadioButtonList1.SelectedVAlue = value;
}
}

This way you conditionally avoid setting the RadioButtonList value when it's null. Then you can happily use the SelectedValue='<%# Bind("FieldName") %>' syntax in your user control tag declaration.

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

How to handle a integer null value in a (.xsd) dataset

Hi.

I want to pass a null value to an integer in a (stored) dataset. The column in the database allows null values, but the dataset throws an error. (VS2005)

Goos van Beek

If column in DataTable allows NULLs, you could assing value using DBNull.Value, not Null

Thanks for responding, Val.

How can I pass a DBNull value to the dataset? The user deletes an integer value in a bound textfield and the result is a frozen application.

Of course I can use unbound fields, which I usually do, but I have chosen for the easy way this time :-)

Goos van Beek.

In a case of binding, I do not think you have much control, but I believe application should not freeze in this case anyway. Do you get any exception? Do you trap any exceptons but do not handle them? In a case of unbound controls, you could assign value using next code, but you have to know index of the row that require the change

MyDataTable.Rows(IndexOfTheRowHere).Items("MyColumn)=DBNull.Value

Actually the application doesn't freeze, but the cursor can't leave the field until there is a integer value typed in the field. And that's the problem when the value should be null (or dbnull)

I know the record index, but i can't update a single value of that row.

this.MyDataSet.Tables[0].Rows[idx].Items["MyFieldName"] = DBNull.Value; doesn't work, because the there is no defenition for 'Items'

I can update the value in the underlaying database table, but not in the dataset.

Goos van Beek.

uhmm... (a little idea...i don't now how it fits your needs)...

you can set in design time a default value for your column...this value is never used by your application (e.g. invoice total value cannot be negative and you can use -1).

In runtime when the user clean the column you can set somthing like this

this.MyDataSet.Tables[0].Rows[idx].Items["MyFieldName"] = myDataset.MyTable.MyColumn.DefaultValue

Thanks for responding, Bob.

Sometimes you don't want a default value, although not in the userform.

I changed the datatype in the dataset from System.Int32 to System.String. This allows me to enter a null value in the field. The value in the table is updated by a SqlCommand. Only this is not what I want...

this.MyDataSet.Tables[0].Rows[idx].Items["MyFieldName"] = myDataset.MyTable.MyColumn.DefaultValue; doesn't work, because the there is no defenition for 'Items'

Isn't there a way to adapt the default behaviour of the dataset to handle null values for an integer?