Dataadapter.update throws error; datacollumn rwuser in datatable localdb for sourcecollumn rwuser doesn't excist

Question

0.00/5 (No votes)

See more:

Dear all,

I am trying to update a dataset with multiple tables. However evertime I want to update, the first table that I want to update throws an error, that collumn doesn't excist in that table. This is indeed thru, but the collumn only excist in the last table I want to update. I did try to swap (put last first and all) but still it is keep giving me the same error with the same collumn name.

Always it is missing collumn rwUser..except in the table with the rwUser inside this will be done correctly.

Below my code I have and also all the tables and rows I have.

The Database I created with Access 2016 and is an aacdb. And I am using Visual Basic 2017

The Database;

Words starting with tb are the Tables.

The Tables / rows I have;
•	tbAuthorizations
o	AuthorizationID
o	rwAuthorization (this to be used as sourch for table tbUsersAuthorizations / rwSetUser, rwSetData, rwSetSettings, rwSetReports
•	tbLocalDataFolder
o	LocalDataBaseID
o	rwDatabase
o	rwLocation
•	tbServerDataFolder
o	ServerDatabaseID
o	rwDatabase
o	rwLocation
•	tbUserAuthorizations
o	UserAuthorizationID
o	rwAuthUser = Lookup / relation of tbUsers – rwUserID
o	rwSetUser = Lookup / relation of tbAuthorizations - AuthorizationID
o	rwSetData = Lookup / relation of tbAuthorizations - AuthorizationID
o	rwIncludeFixData
o	rwSetSettings = Lookup / relation of tbAuthorizations - AuthorizationID
o	rwSetReports = Lookup / relation of tbAuthorizations – AuthorizationID
•	tbUsers
o	UserID
o	rwUser (this to be used as sourch for table tbUsersAuthorizations – rwUser)
o	rwInlogName
o	rwPassword
o	rwSercretQuestion
o	rwSecretAnswer
o	rwEmail
o	rwComputerName
o	rwWindowsUser
o	rwPasswordLogin
o	rwHoldOnStart
o	rwRememberName
o	rwRememberPassword

What I have tried:

Public Class Form1


    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim dbFolder As String
    Dim dbName As String

    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        dbProvider = "PROVIDER=MICROSOFT.ACE.OLEDB.12.0;"
        dbName = "D:\SystemSettings.accdb"
        dbSource = "DATA SOURCE = " & dbName
        con.ConnectionString = dbProvider & dbSource
        con.Open()

        da = New OleDb.OleDbDataAdapter("SELECT * FROM tbAuthorizations", con)
        da.Fill(ds, "AuthDB")
        da.SelectCommand.CommandText = "SELECT * FROM tbLocalDataFolder"
        da.Fill(ds, "LocalDB")
        da.SelectCommand.CommandText = "SELECT * FROM tbServerDataFolder"
        da.Fill(ds, "ServerDB")
        da.SelectCommand.CommandText = "SELECT * FROM tbUserAuthorizations"
        da.Fill(ds, "UserAuthDB")
        da.SelectCommand.CommandText = "SELECT * FROM tbUsers"
        da.Fill(ds, "UsersDB")

        con.Close()

    End Sub
    Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        Try
            dsNewRow = ds.Tables("UsersDB").NewRow()
            dsNewRow.Item("rwUser") = txtNaam.Text
            dsNewRow.Item("rwInlogName") = TxtGeboorteplaats.Text
            ds.Tables("UsersDB").Rows.Add(dsNewRow)
            da.Update(ds, "UsersDB")

            dsNewRow = ds.Tables("LocalDB").NewRow()
            dsNewRow.Item("rwDatabase") = txtNaam.Text
            dsNewRow.Item("rwLocation") = TxtGeboorteplaats.Text
            ds.Tables("LocalDB").Rows.Add(dsNewRow)
            da.Update(ds, "LocalDB") ''exception error; DataCollumn rwUser in DataTable LocalDB for SourceCollumn rwUser doesn't excist

            dsNewRow = ds.Tables("ServerDB").NewRow()
            dsNewRow.Item("rwDatabase") = txtNaam.Text
            dsNewRow.Item("rwLocation") = TxtGeboorteplaats.Text
            ds.Tables("ServerDB").Rows.Add(dsNewRow)
            da.Update(ds, "ServerDB") ''exception error; DataCollumn rwUser in DataTable ServerDB for SourceCollumn rwUser doesn't excist

            dsNewRow = ds.Tables("UserAuthDB").NewRow()
            dsNewRow.Item("rwAuthUser") = txtNaam.Text
            dsNewRow.Item("rwSetUser") = TxtGeboorteplaats.Text
            ds.Tables("UserAuthDB").Rows.Add(dsNewRow)
            da.Update(ds, "UserAuthDB") ''exception error; DataCollumn rwUser in DataTable UserAuthDB for SourceCollumn rwUser doesn't excist

        Catch ex As Exception
            Dim s As String
            s = ex.StackTrace
        End Try

    End Sub

End Class

Posted 1-Dec-18 0:39am

Member 13039327

Updated 2-Dec-18 3:54am

v5

Add a Solution

Comments

Richard MacCutchan 1-Dec-18 7:03am

That does not make sense. Why are you trying to use a column name that does not exist?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 13039327 · Answer 1 · 2018-12-01T01:44:00

Solution 1

Hello Richard

That is the problem. Why is VB calling this row? I know it is saying column in the exception, but I assume it is addressing the row.But inside the code I am not addressing this row, only in the update for tbUser where there is such row.

I am not using this column, never had. The only table that has a row called rwUser is the table tbUsers. And if I only use tbUsers to update, there is no problem. So why indeed, is VB give me the error in the other tables, which indeed don't have rwUser inside.

And if there is something wrong in the code, then please let me know, where I am trying to use that column in all the tables except the tbUsers.

But VB is saying that this doesn't excist in table LocalDB, tbServerDB and tbUserAuthorizations.

Regards,
Hans

Posted 1-Dec-18 1:44am

Member 13039327

Comments

Richard MacCutchan 1-Dec-18 8:01am

            dsNewRow = ds.Tables("UserAuthDB").NewRow()
            dsNewRow.Item("rwUser") = txtNaam.Text

rwUser does not exist in this table.

Member 13039327 1-Dec-18 8:22am

Hello Richard,

Sorry but this was my mistake. I did try to change the row in access to rwAuthUser and changed this also in the VB. But forget to adjust in the above code. But it still doesn't change the problem.

I even did try only to update tbLocalDataFolder (LocalDB) and tbServerDataFolder (ServerDB) but I keep getting the error. Also when I only try to update tbLocalDataFolder (LocalDB) the error is being thrown. This also when I am trying to update an other table. With the exception of tbUsers (UsersDB). For with tbUsers it will update indeed the DB
It seems like somewere it is mixing the rows or something like that. Or that it is checking all the rows and wants to update all of them in table

Richard MacCutchan 1-Dec-18 9:03am

Sorry but you will need to use your debugger to inspect the actual code as it runs. It is impossible to guess what is happening.

Member 13039327 · Answer 2 · 2018-12-01T03:58:00

Hello Richard,

I am using the debuger and everytime when it hits the da.update it is going in that error.

If I remove the tbUsers int he da/ds then the error will be that rwAuthUser is not in the table. But then when I only update tbUserAuthorizations, it is going okay.

And the above code is all I have. So it seems that the da.update comand is not correct for multiple table database or I need to have something extra inside.

But I will try to see. Thanks anyway for your replies

Member 13039327 · Answer 3 · 2018-12-02T03:55:00

Dear all,

I found a way to make it work, but not sure if in the end it will not creating problems, like difference of collumn / row between DS and the Database. If then the Dataset, Dataadapter and Database are in sync.

I now have combined ds.tables with a query for insert. ds.tables is/was already updated in the above code. But I run in problem with da.update and for this da.update I want to use a query, so I have then;

Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    Dim dsNewRow As DataRow
    Dim query As String
    Dim i As Integer
    Try

        dsNewRow = ds.Tables("UsersDB").NewRow()
        dsNewRow.Item("rwUser") = txtNaam.Text
        dsNewRow.Item("rwInlogName") = TxtGeboorteplaats.Text
        ds.Tables("UsersDB").Rows.Add(dsNewRow)
        query = "INSERT INTO tbLocalDataFolder (rwDatabase,rwLocation) VALUES ('" & txtNaam.Text & "','" & TxtGeboorteplaats.Text & "')"
        executquery(query, con)

        dsNewRow.Item("rwDatabase") = txtNaam.Text
        dsNewRow.Item("rwLocation") = TxtGeboorteplaats.Text
        ds.Tables("LocalDB").Rows.Add(dsNewRow)
        query = "INSERT INTO tbLocalDataFolder (rwDatabase,rwLocation) VALUES ('" & txtNaam.Text & "','" & TxtGeboorteplaats.Text & "')"
        executquery(query, con)

    Catch ex As Exception
        Dim s As String
        s = ex.StackTrace
    End Try

End Sub
Public Sub executquery(query As String, connec As OleDb.OleDbConnection)
    Dim commandOleDb As New OleDb.OleDbCommand(query, connec)
    con.Open()
    commandOleDb.ExecuteNonQuery()
    con.Close()
End Sub

Dataadapter.update throws error; datacollumn rwuser in datatable localdb for sourcecollumn rwuser doesn't excist

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0