OleDbCommandBuilder not working correctly

S

Samurai X

Member
Joined
Jul 9, 2012
Messages
10
Programming Experience
1-3
Hello,

I am having trouble with my OleDbCommandBuilder updating my datatable.

my code is as follows

VB.NET: 
        Dim DateAch As String = Date.Now.ToString("dd/MM/yyyy")        
        Dim Competence As Integer = cbComp.SelectedValue
        Dim OpNo As String = cbOpNo.SelectedValue
        Dim Ustage As String = cbStage.SelectedValue


        Dim findtab As String
        Dim findTC As TabControl
        Dim myDGV As String


        For Each Ctrl As Control In tcTest.SelectedTab.Controls


            If TypeOf Ctrl Is TabControl Then


                findTC = Ctrl
                findtab = findTC.SelectedTab.Name
                myDGV = "" & findtab & " " & EmpNumber & ""


                Dim NewRow As DataRow = ds.Tables("" & findtab & " " & EmpNumber & "").NewRow()
                ds.Tables(myDGV).Rows.Add(NewRow)
                NewRow.Item("EmpNo") = EmpNumber
                NewRow.Item("Prod") = tcTest.SelectedTab.Name
                NewRow.Item("SubAssy") = findtab
                NewRow.Item("OpNo") = cbOpNo.SelectedValue.
                NewRow.Item("Competence") = Competence
                NewRow.Item("DateAchieved") = DateAch
                NewRow.Item("Stage") = cbStage.SelectedValue


    
                Dim my_builder As OleDbCommandBuilder = New OleDbCommandBuilder(da)
         
                my_builder.GetUpdateCommand()
                da.Update(ds.Tables(myDGV))
                


            End If


        Next

The comboboxes cbOpNo and cbStage are populated by the following code, which is triggered on a selected index change


VB.NET: 
        Dim mytabcon As New TabControl        mytabcon = Sender
        sql = " Select * From " & mytabcon.SelectedTab.Name & ""
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "OpRoute")


        dgOpDesc.DataSource = ds.Tables("OpRoute")
        dgOpDesc.AutoResizeColumns()


        cbOpNo.ValueMember = ("OpNo")
        cbOpNo.DisplayMember = ("OpNo")
        cbOpNo.DataSource = ds.Tables("OpRoute")


        cbStage.ValueMember = ("Stage")
        cbStage.DisplayMember = ("Stage")
        cbStage.DataSource = ds.Tables("OpRoute")

when the lines
VB.NET: 
       Dim my_builder As OleDbCommandBuilder = New OleDbCommandBuilder(da)         
                my_builder.GetUpdateCommand()
                da.Update(ds.Tables(myDGV))

run i get the error Missing the DataColumn 'Description' in the DataTable 'BM401600 6055' for the SourceColumn 'Description'.

The column it is referring to is in the datatable "OpRoute"

if i set
VB.NET: 
NewRow.Item("OpNo") = TextBox1.text
NewRow.Item("Stage") = TextBox2.text
The data table is updated no problems

How can i get it to work as i want but without it trying to add to a column that isn't in that datatable(myDGV)

Thanks for any advice, this has been giving me a headache all day!
 
Sort by date Sort by votes
It seems there's an extra dot at the end of the line I've marked in red below. I don't know, but maybe it has something to do with your problem.

That not helping, i'd check what type of data comes into NewRow.Item("OpNo") and NewRow.Item("Stage") from cbOpNo.SelectedValue and cbStage.SelectedValue, since it works when you're sure you're assigning them Strings. Maybe you should set these fields with DBNull.Value instead of Nothing (in case .SelectedValue contains Nothing).

I'm not sure this solves your problem, but I hope it helps.

Good Luck!

Samurai X said:
Hello,

I am having trouble with my OleDbCommandBuilder updating my datatable.

my code is as follows

VB.NET: 
        Dim DateAch As String = Date.Now.ToString("dd/MM/yyyy")        
        Dim Competence As Integer = cbComp.SelectedValue
        Dim OpNo As String = cbOpNo.SelectedValue
        Dim Ustage As String = cbStage.SelectedValue


        Dim findtab As String
        Dim findTC As TabControl
        Dim myDGV As String


        For Each Ctrl As Control In tcTest.SelectedTab.Controls


            If TypeOf Ctrl Is TabControl Then


                findTC = Ctrl
                findtab = findTC.SelectedTab.Name
                myDGV = "" & findtab & " " & EmpNumber & ""


                Dim NewRow As DataRow = ds.Tables("" & findtab & " " & EmpNumber & "").NewRow()
                ds.Tables(myDGV).Rows.Add(NewRow)
                NewRow.Item("EmpNo") = EmpNumber
                NewRow.Item("Prod") = tcTest.SelectedTab.Name
                NewRow.Item("SubAssy") = findtab
[COLOR=#ff0000][U][B]                NewRow.Item("OpNo") = cbOpNo.SelectedValue.
[/B][/U][/COLOR]                NewRow.Item("Competence") = Competence
                NewRow.Item("DateAchieved") = DateAch
                NewRow.Item("Stage") = cbStage.SelectedValue


    
                Dim my_builder As OleDbCommandBuilder = New OleDbCommandBuilder(da)
         
                my_builder.GetUpdateCommand()
                da.Update(ds.Tables(myDGV))
                


            End If


        Next

The comboboxes cbOpNo and cbStage are populated by the following code, which is triggered on a selected index change


VB.NET: 
        Dim mytabcon As New TabControl        mytabcon = Sender
        sql = " Select * From " & mytabcon.SelectedTab.Name & ""
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "OpRoute")


        dgOpDesc.DataSource = ds.Tables("OpRoute")
        dgOpDesc.AutoResizeColumns()


        cbOpNo.ValueMember = ("OpNo")
        cbOpNo.DisplayMember = ("OpNo")
        cbOpNo.DataSource = ds.Tables("OpRoute")


        cbStage.ValueMember = ("Stage")
        cbStage.DisplayMember = ("Stage")
        cbStage.DataSource = ds.Tables("OpRoute")

when the lines
VB.NET: 
       Dim my_builder As OleDbCommandBuilder = New OleDbCommandBuilder(da)         
                my_builder.GetUpdateCommand()
                da.Update(ds.Tables(myDGV))

run i get the error Missing the DataColumn 'Description' in the DataTable 'BM401600 6055' for the SourceColumn 'Description'.

The column it is referring to is in the datatable "OpRoute"

if i set
VB.NET: 
NewRow.Item("OpNo") = TextBox1.text
NewRow.Item("Stage") = TextBox2.text
The data table is updated no problems

How can i get it to work as i want but without it trying to add to a column that isn't in that datatable(myDGV)

Thanks for any advice, this has been giving me a headache all day!
Click to expand...
 
Upvote 0 Downvote
Hello VBobCat


I'd noticed that dot, it was from a previous .tostring!


I have tied all my variables; opno, competence, stage etc to text boxes and they all check out fine.


I also deleted my relationship links in the access database just in case it was that, but it wasn't!


I'm thinking maybe if I create a holding table to insert the new row into then copy the row from the holding table into the dataset table that may work?!


Probably not the best way to do things but worth a try!

when I get to work tomorrow I'll post the table structure of myDGV and OpRoute
 
Upvote 0 Downvote
Hello the structure of my tables are as follows:

myDGV table

RecordNo (primary key)
EmpNo
Prod
SubAssy
OpNo
Competence
DateAchieved
Stage

OpRoute table

OpNo (primary key)
Stage
Description
SubAssy
Skillset
 
Upvote 0 Downvote
I'm not used to working with connected data sources, then I think I can help no more. But I'd check whether the field 'OpRoute.Description' is demanded by some index or some structure inside your DataSet.
Samurai X said:
Hello the structure of my tables are as follows: myDGV table RecordNo (primary key) EmpNo Prod SubAssy OpNo Competence DateAchieved Stage OpRoute table OpNo (primary key) Stage Description SubAssy Skillset
Click to expand...
 
Upvote 0 Downvote
Hello VBobCat,

i expoted the command text generated by the commandBuilder doing
rtb1.Text = my_builder.GetUpdateCommand.CommandText.ToString
UPDATE BM401600 SET OpNo = ?, Stage = ?, Description = ?, Subassy = ?, SkillSet = ? WHERE ((OpNo = ?) AND ((? = 1 AND Stage IS NULL) OR (Stage = ?)) AND ((? = 1 AND Description IS NULL) OR (Description = ?)) AND ((? = 1 AND Subassy IS NULL) OR (Subassy = ?)) AND ((? = 1 AND SkillSet IS NULL) OR (SkillSet = ?)))
from looking at it it seems that it is trying to update the OpRoute table and not the myDGV table
do you know how to tell it to update the correct table??
 
Upvote 0 Downvote
I've come to the same conclusion as you, but I can go no further.
My applications rely on disconnected databases, that is, I write my own SQL instructions in order to open the connection and execute the command whenever I need that.
I do not say that mine is a better approach, but it prevents me of further investigating your question, due to my lack of knowledge.
However, do take a better look on this part of your code, in order to see if you are passing the correct Table Name.
VB.NET: 
findTC = Ctrl
findtab = findTC.SelectedTab.Name
myDGV = "" & findtab & " " & EmpNumber & ""
Hope it helps. Good Luck!
 
Upvote 0 Downvote
Thanks for that Vbobcat!

I shall have a good look over it,

would you be able to suggest an article that i could look at to insert the data manually!
might be worth while trying that out!

thanks again for your time, i appreciate it
 
Upvote 0 Downvote
Samurai X said:
would you be able to suggest an article that i could look at to insert the data manually!
might be worth while trying that out!
Click to expand...

I doubt it's worth the effort.
You'd have to rewrite your entire application, whenever it deals with data.
All operations would have to be built with an eye on your database's structure.
Then you'd have to set a bunch of objects:
1. your data connection, which you'd have to open and close whenever you read or write data;
2. each one of your commands;
3. each one of your data readers.
I only do this because I'm so used to this approach that it is a real time-saver for me, especially because I wrote a class to save me most of the hassle.
It works with a Dictionary(of String, Object) as a container of data for each record I want to insert or update.
But it is an amateur stuff. Below you can take a look on it, if you want.
Good luck!

Imports System.Data.OleDb
Friend NotInheritable Class BD
    'Factory
    Friend Shared Function CreatesNew(ByVal my_path As String, ByVal recurse_path_upwards As Boolean, ByVal my_password As String) As BD
        If recurse_path_upwards Then my_path = RecursePathUpwards(my_path)
        If my_path = "" Then Return Nothing
        Dim source_connection As New OleDbConnection With {.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=@;".Replace("@", my_path) & IIf(my_password = "", "Persist Security Info=False;", "Jet OLEDB:Database Password=@;".Replace("@", my_password))}
        Try
            source_connection.Open()
            source_connection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
        Return New BD(source_connection)
    End Function
    Private Shared Function RecursePathUpwards(ByVal my_path As String) As String
        If IO.Path.GetDirectoryName(my_path) = "" Then my_path = My.Application.Info.DirectoryPath & "\" & my_path
        Do
            If IO.File.Exists(my_path) Then Exit Do
            If IO.Path.GetDirectoryName(my_path) = IO.Path.GetPathRoot(my_path) Then my_path = "" : Exit Do
            my_path = IO.Path.GetDirectoryName(IO.Path.GetDirectoryName(my_path)).TrimEnd("\"c) & "\" & IO.Path.GetFileName(my_path)
        Loop
        Return my_path
    End Function
    'Constructor
    Private Sub New(my_connection As OleDbConnection)
        _connection = my_connection : _timeout = New System.Timers.Timer(5000)
        StoredQueries = New Dictionary(Of String, DataTable)
        cmdStoredQueries = New Dictionary(Of String, String)
        parStoredQueries = New Dictionary(Of String, OleDbParameter())
    End Sub
    'Connection Commands
    Private WithEvents _connection As OleDbConnection, _timeout As System.Timers.Timer
    Private Sub Connects()
        _timeout.Stop() : _timeout.Start() : If _connection.State = ConnectionState.Closed Then _connection.Open()
        Do Until _connection.State = ConnectionState.Open : Application.DoEvents() : Loop
    End Sub
    Private Sub Disconnects() Handles _timeout.Elapsed
        If _connection.State <> ConnectionState.Closed Then _connection.Close()
    End Sub
    'Data Operations - Insertion
    Friend Function Inserts(ByVal table_name As String, ByVal data_dictionary As Dictionary(Of String, Object)) As Object
        Dim fields_list, values_list As New List(Of String), my_parameters As New List(Of OleDbParameter)
        For Each field_value_pair As KeyValuePair(Of String, Object) In data_dictionary
            fields_list.Add(field_value_pair.Key)
            values_list.Add("@" & field_value_pair.Key)
            If TypeOf field_value_pair.Value Is DateTime Then
                my_parameters.Add(New OleDbParameter("@" & field_value_pair.Key, OleDbType.Date) With {.Value = DirectCast(field_value_pair.Value, DateTime)})
            Else
                my_parameters.Add(New OleDbParameter("@" & field_value_pair.Key, field_value_pair.Value))
            End If
        Next
        Executes("INSERT INTO " & table_name & " (" & Join(fields_list.ToArray, ",") & ") VALUES (" & Join(values_list.ToArray, ",") & ");", my_parameters.ToArray)
        Return ReadsValue("SELECT @@IDENTITY")
    End Function
    'Data Operations - Updating
    Friend Function Updates(ByVal table_name As String, sql_where_conditions As String, ByVal data_dictionary As Dictionary(Of String, Object)) As Integer
        Dim sets_list As New List(Of String), my_parameters As New List(Of OleDbParameter)
        For Each field_value_pair As KeyValuePair(Of String, Object) In data_dictionary
            sets_list.Add(field_value_pair.Key & "=@" & field_value_pair.Key)
            Dim my_value As Object = DBNull.Value
            If field_value_pair.Value IsNot Nothing Then my_value = field_value_pair.Value
            If TypeOf field_value_pair.Value Is DateTime Then
                my_parameters.Add(New OleDbParameter("@" & field_value_pair.Key, OleDbType.Date) With {.Value = DirectCast(field_value_pair.Value, DateTime)})
            Else
                my_parameters.Add(New OleDbParameter("@" & field_value_pair.Key, field_value_pair.Value))
            End If
        Next
        Return Executes("UPDATE " & table_name & " SET " & Join(sets_list.ToArray, ",") & " WHERE " & sql_where_conditions & ";", my_parameters.ToArray)
    End Function
    ' Data Operations - Reading Table
    Friend Function ReadsQuery(ByVal sql_command As String, ParamArray my_parameters() As OleDbParameter) As DataTable
        Dim MyCommand As OleDbCommand = GeneratesCommand(sql_command, my_parameters), TimeOut = Now.AddSeconds(5), MyTable As New DataTable
        Do
            Try
                Connects()
                MyTable.Load(MyCommand.ExecuteReader)
                Return MyTable
            Catch ex As Exception
                If Now > TimeOut AndAlso MessageBox.Show("Houve um problema na conexão com o banco de dados" & vbCrLf & vbCrLf & ex.Message, "Falha na conexão", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) = DialogResult.Cancel Then
                    Stop
                    End
                End If
            End Try
        Loop
    End Function
    ' Data Operations - Reading Value
    Friend Function ReadsValue(ByVal comandoSQL As String, ParamArray parametros() As OleDbParameter) As Object
        Dim MyCommand As OleDbCommand = GeneratesCommand(comandoSQL, parametros), TimeOut = Now.AddSeconds(5)
        Do
            Try
                Connects()
                Return MyCommand.ExecuteScalar
            Catch ex As Exception
                If Now > TimeOut AndAlso MessageBox.Show("Houve um problema na conexão com o banco de dados", "Falha na conexão", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) = DialogResult.Cancel Then
                    Stop
                    End
                End If
            End Try
        Loop
    End Function
    ' Data Operations - Simple Execution
    Friend Function Executes(ByVal comandoSQL As String, ParamArray parametros() As OleDbParameter) As Integer
        Dim MyCommand As OleDbCommand = GeneratesCommand(comandoSQL, parametros), TimeOut = Now.AddSeconds(5)
        Do
            Try
                Connects()
                Return MyCommand.ExecuteNonQuery
            Catch ex As Exception
                If Now > TimeOut AndAlso MessageBox.Show("Houve um problema na conexão com o banco de dados", "Falha na conexão", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) = DialogResult.Cancel Then
                    Stop
                    End
                End If
            End Try
        Loop
    End Function
    ' Data Operations - Command Generation
    Private Function GeneratesCommand(ByVal comando_SQL As String, ByRef parametros() As OleDbParameter) As OleDbCommand
        Dim comando As New OleDbCommand With {.Connection = _connection, .CommandText = comando_SQL}
        For Each parametro As OleDbParameter In parametros
            If parametro IsNot Nothing AndAlso parametro.Value Is Nothing OrElse parametro.Value.Equals(String.Empty) Then parametro.Value = DBNull.Value
            comando.Parameters.Add(parametro)
        Next
        Return comando
    End Function
    'Stored Queries Operations
    Friend StoredQueries As Dictionary(Of String, DataTable)
    Private cmdStoredQueries As Dictionary(Of String, String)
    Private parStoredQueries As Dictionary(Of String, OleDbParameter())
    Friend Function AddsStoredQuery(ByVal table_name As String, ByVal sql_command As String, ParamArray my_parameters() As OleDbParameter) As Integer
        If cmdStoredQueries.ContainsKey(table_name) Then cmdStoredQueries(table_name) = sql_command Else cmdStoredQueries.Add(table_name, sql_command)
        If parStoredQueries.ContainsKey(table_name) Then parStoredQueries(table_name) = my_parameters Else parStoredQueries.Add(table_name, my_parameters)
        If StoredQueries.ContainsKey(table_name) Then StoredQueries(table_name) = ReadsQuery(sql_command, my_parameters) Else StoredQueries.Add(table_name, ReadsQuery(sql_command, my_parameters))
        Return StoredQueries(table_name).Rows.Count
    End Function
    Friend Function RefreshesQuery(ByVal table_name As String) As DataTable
        StoredQueries(table_name) = ReadsQuery(cmdStoredQueries(table_name), parStoredQueries(table_name))
        Return StoredQueries(table_name)
    End Function
End Class
 
Upvote 0 Downvote
Hello again......
if anyone is interested i solved my issue by getting ride of the commandBuilder and doing the insert manually.

VB.NET: 
Dim command As New OleDbCommand


                command.CommandText = "Insert into Skills (empno,prod,subassy,OpNo,Competence,DateAchieved,Stage) Values" _
                                      & "(" & EmpNumber & ",'" & product & "','" & findtab & "'," & OpNo & "," & Competence & "," _
                                      & "'" & DateAch & "','" & Ustage & "')"


                command.Connection = con
                con.Open()
                command.ExecuteNonQuery()
                con.Close()
 
Upvote 0 Downvote

Similar threads

Q
  • Question
why is this code not working?(database)
Replies
2
Views
2K
qaisqais
Q
B
  • Question
Question VB2010 with Access backend - changes do not persist first time db is set up
Replies
0
Views
1K
ballybeg
B
O
  • Question
data adapter insert statement not working
Replies
5
Views
3K
cjard
cjard
D
  • Question
Database update error
Replies
6
Views
2K
cjard
cjard
S
  • Question
But why? An update using the dataadapter inserts a new record
Replies
1
Views
2K
jmcilhinney
jmcilhinney

Share this page

Latest posts

Back
Top