Why this database access code doesn't work? vb.net

Question

I have an Access database with columns titled Key and Username and I'm trying to set some data back to the Username column of the first row of the database. But I get an error:

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

The code:

 Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
        Dim connection_string As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.UserAppDataPath & "\DDL.mdb"
        Dim strSql As String = "SELECT Key,Username FROM table1"
        Dim dtb As New DataTable
        Dim command As New OleDbCommand
        'Dim dataset As DataSet
        Using cnn As New OleDbConnection(connection_string)
            cnn.Open()
            Using dad As New OleDbDataAdapter(strSql, cnn)
                dad.Fill(dtb)
                '/// send data from datatable to database
                Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(dad)


                command.Connection = cnn
                command.CommandText = "update * from table1"

                'set data
                dtb.Rows(0)("Username") = "PG"
                dad.Update(dtb)

            End Using
        End Using
    End Sub
End Class

EDIT: P.S: I did some edits to the code, still gets that error

It says Item isn't a property. Maybe you are looking for Rows? — the_lotus
– the_lotus, Commented Jul 24, 2018 at 15:33
If you have an MS Access database, why are you trying to use MS SQL Server provider objects? — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jul 24, 2018 at 15:52
@Plutonix: OMG, yes, why did I do that . it should be OleDbDataAdapter I guess .. it makes that entire code incorrect I guess. :/ and the connection is also wrong. :/ — Pretty_Girl3
– Pretty_Girl3, Commented Jul 24, 2018 at 16:39

Andrew Morton · Accepted Answer · 2018-07-25 09:00:10Z

1

You have a column named "Key", but is it actually the primary key for the table? That's what it wants.

There are two options:

(1) You don't have a primary key in the table. To solve that, add one or assign a current column to be the primary key. Add or change a table’s primary key in Access.

(2) If another column is the key, you can put that into the SELECT command and simply not display it/use it in this program.

The reason is that when it creates the UPDATE command it uses the primary key to identify which row to update. When it performs the SELECT it performs an extra query first to get information about the table, which will include which column is the primary key; it uses that information when creating the UPDATE command.

answered Jul 25, 2018 at 9:00

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pretty_Girl3 Over a year ago

I tried to set primary key via the : "ALTER TABLE " & table_name & " ADD PRIMARY KEY(" & "Key" & ")" this has a syntax error: Syntax error in CONSTRAINT clause. what is wrong?

Andrew Morton Over a year ago

You should only have to do it once, so I wonder why you're doing it in the program instead of in the Access program. However, I think it needs to be more like ALTER TABLE [tableName] ADD CONSTRAINT PK_Key PRIMARY KEY ([Key]);. It's going to be awkward because "Key" is a reserved word in Access.

Pretty_Girl3 Over a year ago

it works now. Thank you. The reason I'm doing it is, I'm programmatically making a access database, and then filling some data, retrieving data, changing data etc. So, it's easier for me to set the primary key through code after I make the file, so I don't have to do it manually. Thank you

Mary · Accepted Answer · 2018-07-24 17:15:56Z

0

You need to provide the DataAdapter with an Update command. You can do this manually by setting the .UpdateCommand property of the adapter.

dad.UpdateCommand = "Update table1 Set...

Or you can use the CommandBuilder method. This will generate the commands for you based on your Select query. (Insert, Update, Delete)

Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(dad)

answered Jul 24, 2018 at 17:15

Mary

15.1k3 gold badges20 silver badges27 bronze badges

3 Comments

Pretty_Girl3 Over a year ago

now it gives me a different error:

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

.. and I updated the code

Mary Over a year ago

Does your table in the database have a primary key? If it does add that column to your select statement.

Mary Over a year ago

If your table doesn't have one it probably needs one. You need to have a primary key to uniquely identify a record. Without a unique identifier the database can't Update or Delete a single record.

hakamairi · Accepted Answer · 2018-07-24 21:46:38Z

0

Try dtb.rows(0).item("Name") for the Name in the first row of the datatable

Use DirectCast(dtb.rows(0).item("Name"),String) to use the value as a string.

TextBox10.Text = DirectCast(dtb.rows(0).item("Name"),String)

Mind possible Null-Values in the Database. They will cause an exception in the example above. Check for Null-Values as follows:

If IsDBNull(dtb.rows(0).item("Name")) then
    TextBox10.Text = ""
Else
    TextBox10.Text = DirectCast(dtb.rows(0).item("Name"),String)
EndIf

edited Jul 24, 2018 at 21:46

hakamairi

4,7064 gold badges37 silver badges58 bronze badges

answered Jul 24, 2018 at 16:19

Dirk Struck

1

Collectives™ on Stack Overflow

Why this database access code doesn't work? vb.net

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related