da.update bool--> yes/no

[Stephen Plotnick]

In a data grid using Access as the data source..

The da.update is failing with
"object reference not set to an instance of anobject"

It looks like everything is ok and I was wondering if there is a problem
that I have a bool column mapping to a yes/no type.

__________________________________
Here is the code:

Private Sub PopulateProductGrid()

Dim conn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
source='C:\Program Files\MyProgramArea\Pricing\Pricing.mdb';Persist Security
Info=False")

Dim sSQL As String

sSQL = "select * from ProductNames"

conn.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter(sSQL, conn)

Dim cb As New OleDb.OleDbCommandBuilder(da)

Try

da.Fill(myDS, "ProductNames")

DataGrid1.DataSource = myDS

DataGrid1.DataMember = "ProductNames"

Dim irow As Integer

Dim icol As Integer

irow = 0

DataGrid1.Select(irow)

DataGrid1.SetDataBinding(myDS, "ProductNames")

RowCount = DataGrid1.BindingContext(myDS, "ProductNames").Count()

Catch ex As Exception

MessageBox.Show("Failed to connect to data source")

Finally

'conn.Close()

End Try

End Sub

Private Sub ProductTypeSelection_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load

PopulateProductGrid()

StoreNameOut.Text = StoreName

VolumeDiscountOut.Text = VolumeDiscount

'

' Create a Grid Table Style. Map it to the "Product Names" Table.

'

Dim aGridTableStyle As New DataGridTableStyle

aGridTableStyle.MappingName = "ProductNames"

aGridTableStyle.AlternatingBackColor = Color.Lavender

'

' Create GridColumnStyle objects for the grid columns

'

Dim aCol1 As New DataGridTextBoxColumn

Dim aCol2 As New DataGridTextBoxColumn

Dim aCol3 As New DataGridBoolColumn

'

With aCol1

..HeaderText = "Code"

..MappingName = "CODE_VALUE"

..Width = 50

..Alignment = HorizontalAlignment.Center

..TextBox.Enabled = False

End With

'

' Set column's caption, width and disable editing.

'

With aCol2

..MappingName = "CODE_MEANING"

..HeaderText = "Prodcut Type"

..Width = 515

..Alignment = HorizontalAlignment.Left

..TextBox.Enabled = False

End With

With aCol3

..MappingName = "CODE_Selected"

..HeaderText = "Select"

..Width = 50

..Alignment = HorizontalAlignment.Center

End With



'

' Add the GridColumnStyles to the DataGrid's Column Styles collection.

'

With aGridTableStyle.GridColumnStyles

..Add(aCol1)

..Add(aCol2)

..Add(aCol3)

End With

'

' Add the GridColumnStyles to the aGridTableStyle.

'

DataGrid1.TableStyles.Add(aGridTableStyle)

End Sub

Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As
System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate

End Sub

Private Sub NextButton_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles NextButton.Click

DataGrid1.DataSource = myDS

DataGrid1.DataMember = "ProductNames"

Try

da.Update(myDS, "ProductNames")

Catch ex As Exception

MsgBox(ex.Message)

End Try

PricingForm.StoreName = StoreName

PricingForm.VolumeDiscount = VolumeDiscount

PricingForm.Show()

End Sub

 

[Izzy]

da is only visible in the PopulateProductGrid() sub. If you want to use
it in other places in the class move it outside that sub.

 

[Stephen Plotnick]

I think I'm doing this correctly. This was already in my code.

Public Class ProductTypeSelection

Inherits System.Windows.Forms.Form

Private myDS As New DataSet

Dim PricingForm As New PricingForm

Dim RowCount As Integer

Public Shared StoreName As String

Public Shared VolumeDiscount As String

Private da As System.Data.OleDb.OleDbDataAdapter

Private cb As System.Data.OleDb.OleDbCommandBuilder



Thanks for you assistance,

Steve

 

[Stephen Plotnick]

I fixed this:
Dim da As New System.Data.OleDb.OleDbDataAdapter

Dim cb As System.Data.OleDb.OleDbCommandBuilder



WHen there is not a row change it does go through, when a row changes I get
this message:

Update requires a valid UpdateCommand when passed DataRow collection with
modified rows



Thanks,

Steve

 

[Stephen Plotnick]

I've resolved my problems.

I'm posting here in case anybody has the same problem.

Steve

If myDS.HasChanges Then

Dim conn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
source='C:\Program Files\MyArea\Pricing\Pricing.mdb';Persist Security
Info=False")

Dim uSql = "Select * from ProductNames"

da.SelectCommand = New OleDb.OleDbCommand(uSql, conn)

Dim cb As New OleDb.OleDbCommandBuilder(da)

Try

myDS.Tables("ProductNames").GetChanges()

da.Update(myDS.Tables("ProductNames"))

Catch ex As Exception

MessageBox.Show(ex.ToString)

End Try

End If