Adding Rows to Data Table

[Tinbeard]

I have an application that I'm currently writing that takes the selected date from a calendar control and populates a datatable with bookings for that date from an access database. The problem I have is that there are not always bookings made so sometimes the datatable contains no rows, if I then try to add a row I get the following error.

System.NullReferenceException: Object reference not set to an instance of an object.

This I assume is because the datatable has a row count of 0. the question is how do I get around this problem, is there a way to add new rows to an empty data Table ?

The code for adding the row is as follows:

Dim objDataRow As DataRow
 
        objDataRow=objDataSet.Tables("Bookings").NewRow
        objDataRow.Item("Aircraft")=aircraft
        objDataRow.Item("Date")=myDate
        objDataRow.Item("Name")=txtName.Text
        objDataRow.Item("Duration")=txtDuration.Text
 
    objDataSet.Tables("Bookings").Rows.Add(objDataRow)
    objBookingDA.Update(objDataSet,"Bookings")

[sparrow1]

I search into the existing Forums and found one. See this Forum and you'll find an answer!

http://www.vbforums.com/showthread.p...rows+datatable

sparrow1

[Tinbeard]

Thanks, I know the mechanics of adding a datarow into an existing table, but when I try this with what is effectively an empty table it throws the error message.
The question is can I add a row using the code in the original post to an empty data table and if not (which I suspect is the case) how do I get around the problem when a date is selected that currently has no records ?

[jmcilhinney]

I'm going to make a bit of an educated guess and say that the problem is not that your DataTable contains no rows but that it contains no columns. If your query returns no rows then it may be that the table schema is not filled either. Check whether that is the case and, if so, you can call FillSchema just like you would call Fill to get the correct columns in the table:

Dim myTable As New DataTable
 
If myDataAdapter.Fill(myTable) = 0 Then
    myDataAdapter.FillSchema(myTable)
End If

[mar_zim]

I'm going to make a bit of an educated guess and say that the problem is not that your DataTable contains no rows but that it contains no columns. If your query returns no rows then it may be that the table schema is not filled either. Check whether that is the case and, if so, you can call FillSchema just like you would call Fill to get the correct columns in the table:

Dim myTable As New DataTable
 
If myDataAdapter.Fill(myTable) = 0 Then
    myDataAdapter.FillSchema(myTable)
End If

John If you do a select query w/ the tables that has no rows/data it STILL get the schema of it.

Tinbeard: Could you post the code on how you fill the dataset/datatable? Maybe you didn't fill the right datatable in which you use in adding a new row.

[jmcilhinney]

Bummer. Obviously my guesses need to go back to school.

[hoasua111]

Im my opinion, you should see the variant "myDate", which control give its value to "mydate". The error may be cause by that control.
Good luck!

[dogboydelta]

Here's a snippet of code I used to manually create a table in a dataset in one of my apps. It manually adds the table, then the columns. Maybe something similar can be done with rows.

ds is a DataSet
ds.Tables.Add(New System.Data.DataTable)
ds.Tables(0).Columns.Add("Date")
ds.Tables(0).Columns.Add("PhoneNumber")
ds.Tables(0).Columns.Add("Name")

[Asgorath]

Try this

Dim NewRow As System.Data.DataRow =ds.Tables(0).NewRow()
NewRow(0) = Date.Now
NewRow(1) = "35178945"
NewRow(2) = "name"
ds.Tables(0).Rows.Add(NewRow)

Regards
Jorge

[Tinbeard]

This is the code I've used to initially fill the dataset / datatable

Sub BindData()
    Dim strSelect as String
        strSelect="SELECT * FROM booking WHERE Date=" & myDate 
        objBookingDA= New OleDBDataAdapter(strSelect,objConnection)
        objBookingCB= New OleDBCommandBuilder(objBookingDA)
        objBookingDA.FillSchema(objDataSet, schemaType.Source,"booking")
        objBookingDA.Fill(objDataSet,"Bookings")
        dlstBookings.DataSource=objDataSet.Tables("Bookings")
        dlstBookings.DataBind
End Sub

If I remove the myDate variable as suggested I still get the error raised at the line.

objDataRow=objDataSet.Tables("Bookings").NewRow

(see original posting for location of this line)

[mar_zim]

Is this a web apps? You don't need to use the fillschema method becuase fill method already get the schema of the table.

Sub BindData()
    Dim strSelect as String
        strSelect="SELECT * FROM booking WHERE Date=" & myDate 
        objBookingDA= New OleDBDataAdapter(strSelect,objConnection)
        objBookingCB= New OleDBCommandBuilder(objBookingDA)
        objBookingDA.Fill(objDataSet,"Bookings")
        dlstBookings.DataSource=objDataSet.Tables("Bookings")
        dlstBookings.DataBind
End Sub

Question: When do you call the sub binddata? After you add a rows or before?
I guess you add first a row to the datatable before calling the sub binddata.

[Tinbeard]

The BindData() is called from the calendar selectionChanged so when someone clicks on a date the datalist is displayed showing all bookings for that date.

Only after this is the facility for adding a booking made visible (I want to make sure they select a date before making a booking).

The plan is that when they have made a booking BindData will be called again to re-populate the datalist with the new data, so the user can see that their booking has been added.

It is a web app, should this be moved to asp.net forum ?

Apologies if I've posted in the wrong forum, so used to having problems with windows app's it's second nature to post here