Scott,
Thanks for your reply. I tried pasting your code into a new application. It
did not like the line DataGridView.DataBind(). It indicated that 'DataBind'
is not a member of 'System.Windows.Forms.DataGridView'.
Also, it did not like the line Me.myDA.Update() It claimed there are too few
parameters. I commented out the databind line and added my dataset to the
update line and I received the error: Object reference not set to an instance
of an object. on the
Me.myDA.Update(myDataSet.Tables("Default_Settings_Table")) line when I press
button1.
I know the data in the dataset is correct because I check it before the
update line executes. It seems like the CommandBuilder is not creating the
update command properly. Users will modify the Access tables (add and remove
columns) so I hesitate to create my own specific update command because every
time the table is modified, the update command will have to be modified as
well. The Access table does have a Primary Key set.
Any other advice would be welcome. I have done this with the SQL data
adapter with SQL Server many times without problems.
Thanks.
--
Scott H.
"Scott M." wrote:
> CommandBuilders are not well known for producing good commandText in all
> cases. You are better off writing the command yourself, but in the case of
> a DataAdapter, there is a CommandBuilder built-in. If you give it a proper
> SELECT command, it can sometimes extrapolate the others. Let's trim your
> code down (not everything you have there is necessary) and see what happens:
>
> Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
> Handles Me.Load
> Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=c:\AMCO\SNAP Support Files.mdb")
> Dim myDA = New OleDbDataAdapter("SELECT * FROM
> Default_Settings_Table", Cn)
> Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
> builder.RefreshSchema()
> myDataSet = New DataSet()
> myDA.Fill(myDataSet, "Default_Settings_Table")
> DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
> DataGridView.DataBind()
> End Sub
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> Me.Validate()
> Me.myDA.Update()
> Me.myDataSet.AcceptChanges()
> End Sub
>
> There is no need to open or close your connection when using a DataAdapter,
> as it will do this for you automatically. Also, there is no need to set
> your variables to Nothing in .NET, as this may actually cause your object to
> persist longer than it would have normally.
>
> -Scott
>
>
> "Scott H." <(E-Mail Removed)> wrote in message
> news:5D83E355-1A2E-4A76-886B-(E-Mail Removed)...
> > I'm trying to use an OleDbDataAdapter along with the OleDbCommandBuilder
> > to
> > update a Microsoft Access table from a DataGridView using vb.NET. THe
> > DataGridView is populated correctly. If I make a change to the
> > DataGridView
> > and click Button1 to update the table, the following error is generated:
> > Syntax error (missing operator) in query expression .....
> >
> > This works fine when I use the SQL version of the dataadapter and
> > commanbulder when updating a table in SQL Server. Any ideas as to why this
> > does not work with Access?
> >
> > Thanks.
> >
> > Private Sub Form1_Load(ByVal sender As Object, ByVal e As
> > System.EventArgs)
> > Handles Me.Load
> > Dim Cn As New
> > OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> >
> > Source=c:\AMCO\SNAP Support Files.mdb")
> > Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM
> > Default_Settings_Table", Cn)
> > Cn.Open()
> > myDA = New OleDbDataAdapter(cmd)
> > Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
> > builder.RefreshSchema()
> > myDataSet = New DataSet()
> > myDA.Fill(myDataSet, "MyTable")
> > DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
> > Cn.Close()
> > Cn = Nothing
> > End Sub
> >
> > Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> > System.EventArgs) Handles Button1.Click
> > Me.Validate()
> > Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
> > Me.myDataSet.AcceptChanges()
> > End Sub
> >
> > --
> > Scott H.
>
>
>
|
Similar Threads
