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:
BookDropDownList – 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 BookDropDownList.

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

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

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


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