Dynamic SQL generation for the UpdateCommand is not supported

[spv4u]

Getting the following error when running my application.

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

On form load it reads the database to the textboxes for the user, they can then change their details.

On clicking the save button I am attempting to update the database with the information from a textbox but the above error is thrown and I'm not a bit lost.

Here is my code

    Private Sub WriteRecords()
        'Database Connection for save
        Dim Con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sqlcode As String


        Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\test.mdb; User ID=Admin; Password= ;"
        Con.Open()
        sqlcode = "SELECT * FROM [User]"
        da = New OleDb.OleDbDataAdapter(sqlcode, Con)
        da.Fill(ds, "RefUser")
        Con.Close()

        'Update dataset with user info

        ds.Tables("RefUser").Rows(0).Item(1) = tb_fname.Text

        'Update data
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        da.Update(ds, "RefUser")

        'Close the form
        Closeform()
    End Sub

Any help would be great Thanks

[tlhintoq]

TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.

[tlhintoq]

Until another expert with more experience in this can offer more targeted help, I can at least point you at these:

Database How-to parts 1 and 2
Database tutorial Part 1
Database tutorial Part 2

[CroCrew]

Hello spv4u,

The example below is just that an example. I would put all the database activity in a class and call functions from the class. But, for this example I put it all in the code behind of the form to keep it simple.

Elements on the form:
BookDropDownLis t – is a Drop Down List that get populated on the load of the page. Every time a book is selected from the drop down the page gets reloaded and the text boxes get populated with the selected books data.

UpdateBook – is a button on the form that when pressed the data from the text boxes then are used to update the corresponding data for the selected book.

xKeyID – an editable textbox populated with the selected book from the BookDropDownLis t.

xTitle – an editable textbox populated with the selected book from the BookDropDownLis t.

xAuthor – an editable textbox populated with the selected book from the BookDropDownLis t.

xPublisher – an editable textbox populated with the selected book from the BookDropDownLis t.


Anyways, I hope this helps you out,
CroCrew~

Imports System.Data
Imports System.Data.OleDb
Imports ADODB

Public Class Form1
    Private Function ConnectionString() As String
		Private ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.IO.Path.GetFullPath("Database\TheLibrary.mdb")
    End Function
	
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        BookDropDownList.DataSource = GetBooks()
        BookDropDownList.DataValueField = "KeyID"
        BookDropDownList.DataTextField = "Title"
        BookDropDownList.DataBind()
    End Sub	

	Protected Sub BookDropDownList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles BookDropDownList.SelectedIndexChanged
		Dim OpenBook As DataSet = GetBooks(BookDropDownList.SelectedValue)
		xKeyID.Text = OpenBook.Tables("mydata").Rows(0).Item("KeyID").ToString
		xTitle.Text = OpenBook.Tables("mydata").Rows(0).Item("Title").ToString
		xAuthor.Text = OpenBook.Tables("mydata").Rows(0).Item("Author").ToString
		xPublisher.Text = OpenBook.Tables("mydata").Rows(0).Item("Publisher").ToString
    End Sub	
	
	Protected Sub UpdateBook_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateBook.Click
		If (UpdateBook()) Then
			ErrorAndInformationDiv.InnerHtml = "Update completed.<br / >&nbsp;"
		Else
			ErrorAndInformationDiv.InnerHtml = "Error: 134.649.093<br / >&nbsp;"
		End If
    End Sub	
	
    Function GetBooks(Optional ByVal KeyID As Integer = 0) As DataSet
        Dim SQL As String = "SELECT * FROM Books"
		
		If (KeyID > 0) Then
			SQL &= " WHERE KeyID = " & KeyID
		End If

        Dim dbConnection As MySqlConnection = New MySqlConnection()
        dbConnection.ConnectionString = ConnectionString()

        Dim mylocalDataSet As DataSet = New DataSet
        Dim myDataAdapter As New MySqlDataAdapter(SQL, dbConnection)
        myDataAdapter.Fill(mylocalDataSet, "myData")

        If dbConnection.State = Data.ConnectionState.Closed Then
            dbConnection.Open()
        End If

        GetBooks = mylocalDataSet

        If dbConnection.State = Data.ConnectionState.Open Then
            dbConnection.Close()
            dbConnection.Dispose()
        End If
    End Function
	
    Function UpdateBook() As Boolean
       Try
            Using connection As New SqlConnection(ConnectionString())
                connection.Open()

                Dim commandText As String = "UPDATE Books SET Title=@Title, Author=@Author , Publisher=@Publisher WHERE KeyID = @KeyID"

                Using command As New SqlCommand(commandText, connection)
                    command.Parameters.AddWithValue("@KeyID", xKeyID.Text)
                    command.Parameters.AddWithValue("@Title", xTitle.Text)
                    command.Parameters.AddWithValue("@Author", xAuthor.Text)
                    command.Parameters.AddWithValue("@Publisher", xPublisher.Text)
                    command.CommandTimeout = 300
                    command.ExecuteNonQuery()
                End Using
            End Using
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function