Problem updating a database from a dataset using OLEDB command builder 
Author Message
 Problem updating a database from a dataset using OLEDB command builder

I have been playing around with ADO.Net and have run across a problem that I
haven't been able to solve. Apparently this is not a problem when using the
SQL data adapter only the OLE DB data adapter. When I run the following code
and try to update the datasource it returns an error saying:

An unhandled exception of type 'System.InvalidOperationException' occurred

Additional information: Dynamic SQL generation is not supported against a
SelectCommand that does not return any base table information.

Apparently the command builder is not creating the insert, update, and
delete commands because their value is nothing. I have even tried the
Refresh Schema but it did not help. Any suggestions?


Tim Shelton

Landau Uniforms

Imports System.Data

Imports System.Data.OleDb

Imports System.Text

Public Class Form1

Inherits System.Windows.Forms.Form

Dim strConn As String = "Provider=HiTOLEDB400;Password=ODBC;User
ID=ODBC;Data Source=;Libraries=ABCDATA;Init Timeout=90"

Dim cn As New OleDbConnection(strConn)

Dim da As New OleDbDataAdapter("", cn)

Dim ds As New DataSet()

#Region " Windows Form Designer generated code "

Public Sub New()


'This call is required by the Windows Form Designer.


'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then


End If

End If


End Sub

'Required by the Windows Form Designer

Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer

'It can be modified using the Windows Form Designer.

'Do not modify it using the code editor.

Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid

Friend WithEvents Button1 As System.Windows.Forms.Button

Friend WithEvents txtStnd As System.Windows.Forms.TextBox

Friend WithEvents btnUpdate As System.Windows.Forms.Button

<System.Diagnostics.De{*filter*}StepThrough()> Private Sub InitializeComponent()

Me.DataGrid1 = New System.Windows.Forms.DataGrid()

Me.Button1 = New System.Windows.Forms.Button()

Me.txtStnd = New System.Windows.Forms.TextBox()

Me.btnUpdate = New System.Windows.Forms.Button()

CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()





Me.DataGrid1.DataMember = ""

Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText

Me.DataGrid1.Location = New System.Drawing.Point(8, 72)

Me.DataGrid1.Name = "DataGrid1"

Me.DataGrid1.RowHeadersVisible = False

Me.DataGrid1.Size = New System.Drawing.Size(720, 288)

Me.DataGrid1.TabIndex = 0




Me.Button1.Location = New System.Drawing.Point(160, 40)

Me.Button1.Name = "Button1"

Me.Button1.TabIndex = 1

Me.Button1.Text = "Search"




Me.txtStnd.Location = New System.Drawing.Point(16, 40)

Me.txtStnd.Name = "txtStnd"

Me.txtStnd.TabIndex = 2

Me.txtStnd.Text = ""




Me.btnUpdate.Location = New System.Drawing.Point(648, 368)

Me.btnUpdate.Name = "btnUpdate"

Me.btnUpdate.TabIndex = 3

Me.btnUpdate.Text = "Update"




Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

Me.ClientSize = New System.Drawing.Size(744, 397)

Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.btnUpdate,
Me.txtStnd, Me.Button1, Me.DataGrid1})

Me.Name = "Form1"

Me.Text = "Form1"

CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()


End Sub

#End Region

Private Sub OleDbConnection1_InfoMessage(ByVal sender As System.Object,
ByVal e As System.Data.OleDb.OleDbInfoMessageEventArgs)

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.Even
tArgs) Handles MyBase.Load

da.SelectCommand.CommandText = "Select * from zpstnd"


da.Fill(ds, "Standards")

DataGrid1.DataSource() = ds.Tables("Standards").DefaultView

End Sub

Private Sub OleDbConnection1_InfoMessage_1(ByVal sender As System.Object,
ByVal e As System.Data.OleDb.OleDbInfoMessageEventArgs)

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 Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim strText As New StringBuilder()

strText.Append("Select * from zpstnd where XSTND = ")




da.SelectCommand.CommandText = strText.ToString


da.Fill(ds, "Standards")

DataGrid1.DataSource() = ds.Tables("Standards").DefaultView

End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

Dim cb As New OleDbCommandBuilder(da)

da.Update(ds, "Standards")

End Sub

End Class

Wed, 04 Aug 2004 06:25:09 GMT  
 Problem updating a database from a dataset using OLEDB command builder


My name is Jian-Wei Yu. Thank you for using the Microsoft VB Newsgroups.

Can you create a Connection in the Server Explorer and attempt to drag a
table onto the form to create a DataAdapter?

According to your description, this OLEDB Provider does not work with
OleDbCommandBuilder. To workaround, code the SelectCommand, UpdateCommand,
DeleteCommand, and InsertCommand properties of the DataAdapter manually.

Hope it helps. If you have any questions, please reply to this post.

Best Regards,  

Jian-Wei Yu
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Fri, 06 Aug 2004 10:12:23 GMT  
 [ 2 post ] 

 Relevant Pages 

1. Updating the database using the DataSet object

2. update sybase database using dataset api

3. dataset problems - cant update sql after editing dataset

4. Problem with Updating Dataset contents back to db when using data binding

5. oledb update command not inserting recently added row...

6. Problems using SHAPE command when building Database connection string using UDL File

7. DB2 update using command.excute/ Null problem solution...

8. updating a dataset to the SQL Database

9. Updating a dataset to a different database

10. Update database from a dataset

11. Updating Database/Dataset

12. Updating child recordsets using the Microsoft.Jet.OLEDB.3.51 provider


Powered by phpBB® Forum Software