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 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 massive amounts of data in a data table

Hi,

I have over 200,000 records in a database and need the user to be able to view the data and edit some records. I have an editable data grid setup but when I query stuff to try to show around 30,000 in a table it just crashes.

Thanks

Why not page it with like 100 per page or do they need to see all 30,000 at once?

200,000 records in 1 page... in one go.. yea that will mess up your computer.

This is due to the raw amounts of data being passed to the clients computer from your webpage, and the viewstate is probably massive as well.

What you need to do is bring in paging for your DataGrid, as well as filtering (Date Filtering would be the easiest if you have date logs for the data your returning).

You also might want to turn EnableViewState on your DataGrid to false. This will force you to REBIND after everypostback, but it will not store the datasource in the viewstate (this is a big issue for large recordsets).

Also, look into paging.

----

If you wanted to play around to get more information on this, change your select statement to only select the top 1,000 rows. Then render it and view the properties of the page (right click->properties). Look at the size of the page.

Then increase it to 5,000 rows, 10,000 rows, 20,000 rows etc. and you will see how much data is being transfered to the client. As well, think about what the browser needs to do in order to render that data.

thanks ... I have decided that it is best to only allow them to see the records week by week.

How to handle insert and ListBox from a GRID??

Please help me, Is it possible to do this from a grid?

Functionallity:
------
The grid will be populated from the database table TIME_JOURNAL.

The last line should be an empty line for adding new rows. Should I place this in the grid footer?.

The listbox (dropdown-list) should be populated from the database table TRANSACTION_TYPES.
So the user can change the transaction type. How should I do to populate the listbox?

To edit a cell the user should just click on it with the mouse pointer. How should I solve this?
I dont want an EDIT button on each line. Just click the cell to edit.

Delete button: Will delete current row.

Finish button:
Will take TIME_JOURNAL id for current row and start a stored procedure.
How do I reference the id? After this button is clicked the row should be green.
Can I reference every cell in a grid??

Is there any good examples?

Database tables:
TIME_JOURNAL
# IDNUMBER
# TYPE_OF_TRANS NUMBER (fk to TRANSACTION_TYPES ID)
# AMOUNT NUMBER
# DESCRIPTIONCHAR

TRANSACTION_TYPES
# ID NUMBER
# DESCRIPTION CHAR

Design
---------------------
| Listbox | TextBox | TextBox | Delete button | Finish button |
---------------------
| Listbox | TextBox | TextBox | Delete button | Finish button |
---------------------
| Listbox | TextBox | TextBox | Delete button | Finish button |
---------------------
| Listbox | TextBox | TextBox | Delete button | Finish button |
---------------------
| Listbox | TextBox | TextBox | Add button | (empty line for adding rows)
----------------

Regards,

MartinHi,

DataGrid & DropDownList Live demo :
Specifying a DataBound DropDownList in the EditItemTemplate

Adding a New Record to the DataGrid

To make empty line for adding new rows in the footer :

<asp:TemplateColumn HeaderText="DESCRIPTION"
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "DESCRIPTION") %>
</ItemTemplate
<EditItemTemplate>
 <asp:TextBox ID="DESCRIPTION" Text='<%# Container.DataItem("DESCRIPTION") %>' Runat="server" />
</EditItemTemplate
<FooterTemplate>
 <asp:TextBox ID="add_DESCRIPTION" Runat="Server" />
</FooterTemplate
</asp:TemplateColumn>

HTH
Thank you I think, I get it to work.

/m

How to handle foreign keys when using DataObjectTypeName

If you have a table that has a foreign key, is it good practice to have a property in your class for that foreign key? I ask this because right now I haven't, and so I have declared a method to insert a record like this:

public static Insert(OrderItem orderItem,int orderId) { }

And I was thinking I could hook this up to an ObjectDataSource that looks like this:

<asp:ObjectDataSource ID="OrderitemDataSource" runat="server" TypeName="OrderItemManager" DataObjectTypeName="OrderItem"> <InsertParameters> <asp:QueryStringParameter name="orderId" QueryStringField="orderId" /> </InsertParameters></asp:ObjectDataSource>

But that will not work, because it seems that, as soon as I specify a DataObjectTypeName, the ObjectDataSource will ignore any extra parameters. It will look for:

public static void Insert(OrderItem orderItem) { }

That means that, in order to insert the foreign key value, it need to be a property of OrderItem. Is there another way around this, or is this the way you'd solve it too? The reason I am not sure, is because in OOP, it's more naturla to have a property of type List<OrderItem> on the Order class.

Thanks for your time!

Hi,

If you have a DataObjectTypeName set, the ObjectDataSource will create an object for this type and set property value for each of these parameters.

I think if you're having a reference to another object in this type, it is fine. It works like a foreign key.

Yes, I know. But how do you feel about this from an OOP perspective? Don't you usually have it the other way around (object Order has a List<OrderLine>, object OrderLine does not have an Order). Foreign key is a database term, OOP should not be coupled to the database.

How to handle concurrency problem!

HI ALL,

I have taken the employee table to dataset and updated the designation for the employee 111 and the same time another user deleted the 111 record from the database.while update my dataset what woudl happen whether my changes are stored or exception thrown.?

This is very basic question.Any one please let me know how can i check the database with values of the dataset or is there way to handle?

its wise to give me url of concepts to solve the concurrency problem.

.

Please visit following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcatchingconcurrencyerror.asp

http://msdn.microsoft.com/msdnmag/issues/03/04/DataConcurrency/

Regards

Kuldeep Deokule