Solved

update creating dups

Posted on 2012-04-11
4
247 Views
Last Modified: 2012-04-18
When this runs it doesn't update the rows it inserts them.                    


Dim data_adapter As New OleDb.OleDbDataAdapter("SELECT * FROM thierorderguide WHERE Company = '" & lbCompanies.SelectedItem.ToString & "'", mainform.dbconnection)
                    Dim command_builder As New OleDb.OleDbCommandBuilder(data_adapter)
                    data_adapter.Update(DataGridViewToDataTable(dgadditem))
0
Comment
Question by:Millkind
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37835289
Its because you recreate the adapter. Declare the adapter at class level and use the same instance to populate and update the datatable.
0
 

Author Comment

by:Millkind
ID: 37837103
I switched up and am using the dataadapter from the loaddatagrid sub, it is still inserting and not updating

Here is the code for loading the datagrid:
    Sub loaddatagrid(ByVal sql As String)
        ''fill the datagrid with all items in the order guide by the company selected
        dsadditems = New DataSet
        dvdataview = New DataView
        daeditdeleteorderguide = New OleDb.OleDbDataAdapter(sql, mainform.dbconnection)
        daeditdeleteorderguide.Fill(dsadditems)
        dgadditem.DataSource = dsadditems.Tables(0).DefaultView.ToTable(True)
        dvdataview = New DataView(dgadditem.DataSource)
        dgadditem.DataSource = dvdataview
    End Sub

Open in new window


Here is the code when saved is clicked:
                Try
                    Dim command_builder As New OleDb.OleDbCommandBuilder(daeditdeleteorderguide)
                    daeditdeleteorderguide.Update(DataGridViewToDataTable(dgadditem))
                    mainform.runcommand("insert into actionszebra (actionzebra,userzebra,datezebra) values ('Made Changes to Data','" & mainform.curruser & "','" & Date.Now & "')")
                    MsgBox("Edits Saved")
                    loaddatagrid("SELECT * FROM thierorderguide WHERE Company = '" & lbCompanies.SelectedItem.ToString & "'")
                Catch ex As Exception
                    mainform.errorwrite(ex.ToString & " saved edits to company ordre guide")
                End Try

Open in new window


Here is the code for taking a datagridview to a table:
    Public Shared Function DataGridViewToDataTable(ByVal dtg As DataGridView) As DataTable
        Try
            Dim dt As New DataTable
            Dim row As DataRow
            Dim TotalDatagridviewColumns As Integer = dtg.ColumnCount - 1
            'Add Datacolumn
            For Each c As DataGridViewColumn In dtg.Columns
                Dim idColumn As DataColumn = New DataColumn()
                idColumn.ColumnName = c.Name
                dt.Columns.Add(idColumn)
            Next
            'Now Iterate thru Datagrid and create the data row
            For Each dr As DataGridViewRow In dtg.Rows
                'Iterate thru datagrid
                row = dt.NewRow 'Create new row
                'Iterate thru Column 1 up to the total number of columns
                For cn As Integer = 0 To TotalDatagridviewColumns
                    row.Item(cn) = IfNullObj(dr.Cells(cn).Value) ' This Will handle error datagridviewcell on NULL Values
                Next
                'Now add the row to Datarow Collection
                dt.Rows.Add(row)
            Next
            'Now return the data table
            Return dt
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

Open in new window

0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 37853663
Why are you doing this bit in 3rd code snippet? Off course you are creating new rows which would be inserted instead of updated.
0
 

Author Comment

by:Millkind
ID: 37855691
Okay this is what i feared.  Since im using a datagridview and not a datagrid there is not table bound from the database to the source.  Is that why i can't update?
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question