Solved

how to update, add and delete records from the database table through a datagrid in a Win. App.

Posted on 2004-09-17
15
173 Views
Last Modified: 2010-04-23
I want to delete a record from a datagrid when selected. and be able to update/add records to the db using the datagrid.  Here is my code to populate the datagrid....thanks.


Private Sub PopulateGrid()
        'SQL string
        Dim strSQL As String = "SELECT  
        Option,LocationCode,LocationName,Period,Item,SalePrice,MaxQuantity,MinQuantity " & _
        "FROM TM_Vendor WHERE TM_Vendor.ScenarioName = '" & x & "'"
        Dim da As OleDbDataAdapter 'define dataAdapter's name. dataAdapter copies data from db to dataset
        Dim ds As DataSet 'define data  set name

     
        da = New OleDbDataAdapter(strSQL, strConn)
        ds = New DataSet  'define a new dataset to represent and store data from database


        Try
            da.Fill(ds, "Scenario")

            If ds.Tables(0).Rows.Count > 0 Then
                'use dataGrid to display data from dataset Scenario
                DataGrid1.DataSource = ds.Tables("Scenario")

                DataGrid1.SetDataBinding(ds, "Scenario")
            Else
                MsgBox("No Record!")
            End If

        Catch ex As Exception

        End Try
    End Sub
0
Comment
Question by:kate_y
  • 6
  • 2
  • 2
  • +2
15 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 12086698
Here is a sample app.  

Public Class Form1
    Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents bofDataGrid As System.Windows.Forms.GroupBox
    Friend WithEvents btnDataGridEdit As System.Windows.Forms.Button
    Friend WithEvents btnDataGridDel As System.Windows.Forms.Button
    Friend WithEvents btnDataGridPrevious As System.Windows.Forms.Button
    Friend WithEvents btnDataGridNext As System.Windows.Forms.Button
    Friend WithEvents btnDataGridUpdate As System.Windows.Forms.Button
    Friend WithEvents btnDataGridUse As System.Windows.Forms.Button
    Friend WithEvents dg As System.Windows.Forms.DataGrid
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.bofDataGrid = New System.Windows.Forms.GroupBox
        Me.btnDataGridEdit = New System.Windows.Forms.Button
        Me.btnDataGridDel = New System.Windows.Forms.Button
        Me.btnDataGridPrevious = New System.Windows.Forms.Button
        Me.btnDataGridNext = New System.Windows.Forms.Button
        Me.btnDataGridUpdate = New System.Windows.Forms.Button
        Me.btnDataGridUse = New System.Windows.Forms.Button
        Me.dg = New System.Windows.Forms.DataGrid
        Me.bofDataGrid.SuspendLayout()
        CType(Me.dg, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'bofDataGrid
        '
        Me.bofDataGrid.Controls.Add(Me.btnDataGridEdit)
        Me.bofDataGrid.Controls.Add(Me.btnDataGridDel)
        Me.bofDataGrid.Controls.Add(Me.btnDataGridPrevious)
        Me.bofDataGrid.Controls.Add(Me.btnDataGridNext)
        Me.bofDataGrid.Controls.Add(Me.btnDataGridUpdate)
        Me.bofDataGrid.Controls.Add(Me.btnDataGridUse)
        Me.bofDataGrid.Controls.Add(Me.dg)
        Me.bofDataGrid.Dock = System.Windows.Forms.DockStyle.Fill
        Me.bofDataGrid.Location = New System.Drawing.Point(0, 0)
        Me.bofDataGrid.Name = "bofDataGrid"
        Me.bofDataGrid.Size = New System.Drawing.Size(480, 225)
        Me.bofDataGrid.TabIndex = 2
        Me.bofDataGrid.TabStop = False
        Me.bofDataGrid.Text = "Windows Forms Data Grid Control"
        '
        'btnDataGridEdit
        '
        Me.btnDataGridEdit.Enabled = False
        Me.btnDataGridEdit.Font = New System.Drawing.Font("Arial", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnDataGridEdit.Location = New System.Drawing.Point(208, 192)
        Me.btnDataGridEdit.Name = "btnDataGridEdit"
        Me.btnDataGridEdit.Size = New System.Drawing.Size(40, 24)
        Me.btnDataGridEdit.TabIndex = 31
        Me.btnDataGridEdit.Text = "Edit"
        '
        'btnDataGridDel
        '
        Me.btnDataGridDel.Enabled = False
        Me.btnDataGridDel.Font = New System.Drawing.Font("Arial", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnDataGridDel.Location = New System.Drawing.Point(168, 192)
        Me.btnDataGridDel.Name = "btnDataGridDel"
        Me.btnDataGridDel.Size = New System.Drawing.Size(40, 24)
        Me.btnDataGridDel.TabIndex = 30
        Me.btnDataGridDel.Text = "Del"
        '
        'btnDataGridPrevious
        '
        Me.btnDataGridPrevious.Enabled = False
        Me.btnDataGridPrevious.Font = New System.Drawing.Font("Arial", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnDataGridPrevious.Location = New System.Drawing.Point(48, 192)
        Me.btnDataGridPrevious.Name = "btnDataGridPrevious"
        Me.btnDataGridPrevious.Size = New System.Drawing.Size(32, 24)
        Me.btnDataGridPrevious.TabIndex = 27
        Me.btnDataGridPrevious.Text = "<<"
        '
        'btnDataGridNext
        '
        Me.btnDataGridNext.Enabled = False
        Me.btnDataGridNext.Font = New System.Drawing.Font("Arial", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnDataGridNext.Location = New System.Drawing.Point(80, 192)
        Me.btnDataGridNext.Name = "btnDataGridNext"
        Me.btnDataGridNext.Size = New System.Drawing.Size(32, 24)
        Me.btnDataGridNext.TabIndex = 26
        Me.btnDataGridNext.Text = ">>"
        '
        'btnDataGridUpdate
        '
        Me.btnDataGridUpdate.Enabled = False
        Me.btnDataGridUpdate.Font = New System.Drawing.Font("Arial", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnDataGridUpdate.Location = New System.Drawing.Point(112, 192)
        Me.btnDataGridUpdate.Name = "btnDataGridUpdate"
        Me.btnDataGridUpdate.Size = New System.Drawing.Size(56, 24)
        Me.btnDataGridUpdate.TabIndex = 4
        Me.btnDataGridUpdate.Text = "Update"
        '
        'btnDataGridUse
        '
        Me.btnDataGridUse.Font = New System.Drawing.Font("Arial", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnDataGridUse.Location = New System.Drawing.Point(8, 192)
        Me.btnDataGridUse.Name = "btnDataGridUse"
        Me.btnDataGridUse.Size = New System.Drawing.Size(40, 24)
        Me.btnDataGridUse.TabIndex = 2
        Me.btnDataGridUse.Text = "Use"
        '
        'dg
        '
        Me.dg.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.dg.CaptionVisible = False
        Me.dg.DataMember = ""
        Me.dg.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.dg.Location = New System.Drawing.Point(8, 16)
        Me.dg.Name = "dg"
        Me.dg.ParentRowsVisible = False
        Me.dg.ReadOnly = True
        Me.dg.Size = New System.Drawing.Size(464, 168)
        Me.dg.TabIndex = 1
        Me.dg.Visible = False
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(480, 225)
        Me.Controls.Add(Me.bofDataGrid)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.bofDataGrid.ResumeLayout(False)
        CType(Me.dg, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region
    Public Sub CleanUpUses(ByVal intBof As Integer)

        '   Dim obj As Control

        ' Un-Use all bof's
        'For Each obj In bofManual.Controls
        '    If TypeOf obj Is TextBox Then obj.Text = ""
        'Next

        'For Each obj In bofWizardBoundControls.Controls
        '    If TypeOf obj Is TextBox Then obj.Text = ""
        'Next

        btnDataGridNext.Enabled = False
        btnDataGridPrevious.Enabled = False
        btnDataGridUpdate.Enabled = False
        dg.Hide()
        btnDataGridDel.Enabled = False

        ' Show
        If intBof = 2 Then
            btnDataGridNext.Enabled = True
            btnDataGridPrevious.Enabled = True
            btnDataGridUpdate.Enabled = True
            btnDataGridEdit.Enabled = True
            btnDataGridDel.Enabled = True
            dg.Show()
        End If
    End Sub
    Private Sub btnUse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataGridUse.Click

        Dim cb As System.Data.OleDb.OleDbCommandBuilder

        ' General init
        Dim strAppPath = System.IO.Directory.GetCurrentDirectory()
        CleanUpUses(2)


        ' Connection string (for simplicity and readability)
        Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAppPath & "\Book.mdb"

        ' OLEDB Init
        Dim objConn As New System.Data.OleDb.OleDbConnection(strConnectionString)

        ' Data Adapter (globally defined) - SelectCommand specified and ready for use
        myDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Contacts", objConn)

        ' Optional command builder which automates the process of building each command
        ' by hand (below). This only works if the SelectCommand issued to the data
        ' adapter contains a primary key. USE EITHER ONE, BUT NOT BOTH
        ' Use cb.GetUpdateCommand.CommandText to view auto-generated SQL statements
        'cb = New System.Data.OleDb.OleDbCommandBuilder(myDataAdapter)
        'Dim mystr As String = cb.GetUpdateCommand.CommandText
        'Dim val As Integer = cb.GetUpdateCommand.Parameters.Count

        ' InsertCommand available through the DataAdapter
        myDataAdapter.InsertCommand = New System.Data.OleDb.OleDbCommand("INSERT INTO Contacts (Name, OnHand, LastUser, LastChange) Values(?,?,?,?)", objConn)
        '       myDataAdapter.InsertCommand = New System.Data.OleDb.OleDbCommand("INSERT INTO Contacts (Name, OnHand) Values(?,?)", objConn)
        myDataAdapter.InsertCommand.CommandType = CommandType.Text
        myDataAdapter.InsertCommand.Parameters.Add("@Name", System.Data.OleDb.OleDbType.Char, 50, "Name")
        myDataAdapter.InsertCommand.Parameters.Add("@OnHand", System.Data.OleDb.OleDbType.Integer, 4, "OnHand")
        myDataAdapter.InsertCommand.Parameters.Add("@LastUser", System.Data.OleDb.OleDbType.Char, 50, "LastUser")
        myDataAdapter.InsertCommand.Parameters.Add("@LastChange", System.Data.OleDb.OleDbType.Char, 50, "LastChange")

        ' UpdateCommand available through the DataAdapter (note the parameter order!)
        ' myDataAdapter.UpdateCommand = New System.Data.OleDb.OleDbCommand("UPDATE Contacts SET Name = ?, OnHand = ?, LastUser = 'Me', LastChange =' #" & Now & "#' WHERE ContactID = ?", objConn)
        myDataAdapter.UpdateCommand = New System.Data.OleDb.OleDbCommand("UPDATE Contacts SET Name = ?, OnHand = ?, LastUser = ?, LastChange =? WHERE ContactID = ?", objConn)
        '  myDataAdapter.UpdateCommand = New System.Data.OleDb.OleDbCommand("UPDATE Contacts SET Name = ?, OnHand = ? WHERE ContactID = ?", objConn)
        myDataAdapter.UpdateCommand.CommandType = CommandType.Text
        myDataAdapter.UpdateCommand.Parameters.Add("@Name", System.Data.OleDb.OleDbType.Char, 50, "Name")
        myDataAdapter.UpdateCommand.Parameters.Add("@OnHand", System.Data.OleDb.OleDbType.Integer, 4, "OnHand")
        myDataAdapter.UpdateCommand.Parameters.Add("@LastUser", System.Data.OleDb.OleDbType.Char, 50, "LastUser")
        myDataAdapter.UpdateCommand.Parameters.Add("@LastChange", System.Data.OleDb.OleDbType.Char, 50, "LastChange")
        myDataAdapter.UpdateCommand.Parameters.Add("@ContactID", System.Data.OleDb.OleDbType.Integer, 4, "ContactID")

        ' DeleteCommand available through the DataAdapter (note parameter order and number of parameters!)
        myDataAdapter.DeleteCommand = New System.Data.OleDb.OleDbCommand("DELETE FROM Contacts WHERE ContactID = ?", objConn)
        myDataAdapter.DeleteCommand.CommandType = CommandType.Text
        myDataAdapter.DeleteCommand.Parameters.Add("@ContactID", System.Data.OleDb.OleDbType.Integer, 4, "ContactID")

        ' Connect to provider
        objConn.Open()

        ' Create and fill dataset
        ds = New DataSet("MS_Access_DataSet")
        myDataAdapter.Fill(ds, "MS_Access_DataSet")

        ' Bind DataGrid control to this dataset            
        dg.SetDataBinding(ds, "MS_Access_DataSet")

        ' Cleanup
        objConn.Close()

        dg.ReadOnly = False
        Me.BindingContext(ds, "MS_Access_DataSet").Position = ds.Tables(0).Rows.Count - 1
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' General init
        g_strAppPath = System.IO.Directory.GetCurrentDirectory()

    End Sub

    ' ********************************************************************************
    ' Pass changes made to the DataGrid back to the data source. Flow is as follows:
    ' IN:
    ' DataSource -> Adapter (SelectCommand) -> DataSet -> DataGrid
    ' OUT:
    ' DataGrid (any changes) -> DataSet -> Adapter (appropriate command) -> DataSource
    ' ********************************************************************************
    Private Sub btnDataGridUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataGridUpdate.Click
        Try
            myDataAdapter.Update(ds, "MS_Access_DataSet")
            dg.ReadOnly = True
            'lblInfo.Text = "Changes to the Windows Forms DataGrid have been saved. Please note that values you " & _
            '     "have entered for the ContactID (primary key) will be ignored by the system."
        Catch ex As SystemException
            MsgBox(ex.StackTrace & ex.Message, MsgBoxStyle.Critical, "General Error")
        End Try
        ' Create and fill dataset

        ds = New DataSet("MS_Access_DataSet")
        myDataAdapter.Fill(ds, "MS_Access_DataSet")

        ' Bind DataGrid control to this dataset            
        dg.SetDataBinding(ds, "MS_Access_DataSet")
        dg.Refresh()
    End Sub
    ' ********************************************************************************
    ' INSERT row into DataGrid
    ' ********************************************************************************
    Private Sub btnDataGridAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataGridEdit.Click

        ' There may be a way to turn edit on and jump to the last row for insert,
        ' but for this example we'll just toggle editing.
        dg.ReadOnly = False
        Me.BindingContext(ds, "MS_Access_DataSet").Position = ds.Tables(0).Rows.Count - 1
        'lblInfo.Text = "This bound control is attached to the DataSet with Update, Delete and Insert capabilities. " & _
        '     "To save your changes to the Windows Forms DataGrid, press 'update'."

        dg.Refresh()
    End Sub

    Private Sub btnDataGridPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataGridPrevious.Click
        BindingContext(ds, "MS_Access_DataSet").Position -= 1
    End Sub
    Private Sub btnDataGridNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataGridNext.Click
        Me.BindingContext(ds, "MS_Access_DataSet").Position += 1
    End Sub
    ' ********************************************************************************
    ' DELETE ROW FROM DATA BOUND DataGrid
    ' ********************************************************************************
    Private Sub btnDataGridDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataGridDel.Click

        ' This seems to be the easiest method for removing a row. Note the DataGrid
        ' is attached to the DataSet and changes to the DataSet are refelected in the
        ' grid. Also note you'd have to DataAdapter.Update() to save these changes.
        ds.Tables("MS_Access_DataSet").Rows(Me.BindingContext(ds, "MS_Access_DataSet").Position).Delete()

        'lblInfo.Text = "Current row removed from the DataSet bound to this DataGrid. To save your changes, " & _
        '     "you must click 'Update'."

    End Sub
    Private Sub dg_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles dg.Validated

    End Sub

    Private Sub dg_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles dg.Validating
        dg.CurrentCell.GetType()
    End Sub

    Private Sub dg_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dg.CurrentCellChanged
        Dim selectedCell As System.Windows.Forms.DataGridCell
        selectedCell = dg.CurrentCell
        Dim selectedItem As Object
        selectedItem = dg.Item(selectedCell.RowNumber, selectedCell.ColumnNumber)
        Dim cellValue As Integer
        cellValue = CInt(selectedItem)

    End Sub

    Private Sub dg_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles dg.KeyPress
        g_lngCurrentRow = Me.BindingContext(ds, "MS_Access_DataSet").Position
        ds.Tables("MS_Access_Dataset").Rows(g_lngCurrentRow).Item("LastUser") = "ME"
        ds.Tables("MS_Access_Dataset").Rows(g_lngCurrentRow).Item("LastChange") = Now

    End Sub
    Private Sub Dg_MouseDown(ByVal sender As Object, ByVal e As MouseEventArgs) Handles dg.MouseDown

        Dim selectedCell As System.Windows.Forms.DataGridCell
        selectedCell = Me.dg.CurrentCell

        Dim myGrid As DataGrid = CType(sender, DataGrid)
        Dim hti As System.Windows.Forms.DataGrid.HitTestInfo
        hti = myGrid.HitTest(e.X, e.Y)
        Select Case hti.Type
            Case System.Windows.Forms.DataGrid.HitTestType.None
                Console.WriteLine("You clicked the background.")
            Case System.Windows.Forms.DataGrid.HitTestType.Cell
                Console.WriteLine("You clicked cell at row " & hti.Row & ", col " & hti.Column)

            Case System.Windows.Forms.DataGrid.HitTestType.ColumnHeader
                Console.WriteLine("You clicked the column header for column " & hti.Column)
            Case System.Windows.Forms.DataGrid.HitTestType.RowHeader
                Console.WriteLine("You clicked the row header for row " & hti.Row)
            Case System.Windows.Forms.DataGrid.HitTestType.ColumnResize
                Console.WriteLine("You clicked the column resizer for column " & hti.Column)
            Case System.Windows.Forms.DataGrid.HitTestType.RowResize
                Console.WriteLine("You clicked the row resizer for row " & hti.Row)
            Case System.Windows.Forms.DataGrid.HitTestType.Caption
                Console.WriteLine("You clicked the caption")
            Case System.Windows.Forms.DataGrid.HitTestType.ParentRows
                Console.WriteLine("You clicked the parent row")
        End Select

        Console.WriteLine("Column " & hti.Column)
        Console.WriteLine("Row " & hti.Row)
        Console.WriteLine("Type " & hti.Type)
        Console.WriteLine("ToString " & hti.ToString)
        Console.WriteLine("Format " & hti.Type.ToString)
    End Sub
    Sub HideColumnOfDataSet()
        Dim points As New System.Data.DataTable("Points")
        points.Columns.Add(New DataColumn("X", GetType(Integer)))
        points.Columns.Add(New DataColumn("Y", GetType(Integer)))
        points.Rows.Add(New Object() {1, 2})
        points.Rows.Add(New Object() {3, 5})
        dg.DataSource = points

        Dim tableStyle As New DataGridTableStyle
        tableStyle.MappingName = "Points"
        dg.TableStyles.Add(tableStyle)
        dg.TableStyles("Points").GridColumnStyles("X").Width = 0
    End Sub
    Dim ts As DataGridTableStyle = New DataGridTableStyle

    Sub HideColumn()
        ' Set the DataGridTableStyle.MappingName property
        ' to the table in the data source to map to.
        ts.MappingName = dg.DataMember

        ' Add it to the datagrid's TableStyles collection
        dg.TableStyles.Add(ts)

        ' Hide the first column (index 0)
        dg.TableStyles(0).GridColumnStyles(0).Width = 0
    End Sub

End Class
0
 
LVL 5

Expert Comment

by:Jojo1771
ID: 12087297
Don't over complicate it. This is very simple. Just Simly make a SQLDATAadapter and asign it the select, insert, update, and delete stings on the adapter. Then generate the dataset,  and asign it to the grid.
Then use:
sqladapter1.fill(DS.table) -fills
sqladapter1.update(DS.table) -  does all update aka insert, delete, update, so anything that has changed in the datagrid.
0
 

Author Comment

by:kate_y
ID: 12087893
hey, thanks. I was still trying to understand the sample code. I am gonna try the simple one. let you guys know asap.
0
 

Author Comment

by:kate_y
ID: 12088150
Hi, I believe my update statement is wrong. Do we need to update the table column  by column? How to set the value dynamically from the datagrid?

Dim strSQL As String = "UPDATE TM_Vendor SET LocationCode = ?, Period = ?.....other columns" & _
               "WHERE TM_Vendor.ScenarioName = '" & x & "'"



tks.
0
 

Author Comment

by:kate_y
ID: 12088544
I used the ds.HasChages method to update database through the datagrid. but it showed error " no referrence ds.HasChanges()"

If ds.HasChanges() Then
              Dim strSQL As String = "SELECT            
              Option,LocationCode,LocationName,Period,Item,SalePrice,MaxQuantity,MinQuantity " & _
              "FROM TM_Vendor WHERE TM_Vendor.ScenarioName = '" & x & "'"

            Dim da As OleDbDataAdapter 'define dataAdapter's name. dataAdapter copies data from db to dataset
            Dim ds As DataSet 'define dataset name

            ' Create the DataAdapter.
            da = New OleDbDataAdapter(strSQL, strConn)

            da.TableMappings.Add("Table", "Vendor")

            command_builder = New OleDbCommandBuilder(da)

            da.Update(ds)
 End If
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12089692
You are declaring DS inside a loop that is using DS. Do you think it is possible ?
Comment this line..    "Dim ds As DataSet 'define dataset name "
-Baan
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 5

Expert Comment

by:Jojo1771
ID: 12090038
Your Update string looks right.
Just needs to be
UPDATE TABLENAME  SET STUFF = NEWSUFF WHERE (ID = OLDID) - ID being a primary key or what ever you identify off of, for a Datagrid you need to use some sort of unq key, as you only want to update that one row that was changed.

The best way to update is to call da.update(dataset.table) But how you determine when to call it is up to you, sometimes I use a button on the grids header and call it update. and when they push it I call the following 3 items

Sub Button 1

da.update(ds.table) ' Updates the database
    ds.Clear()  'clears dataset incase  data  in database has been changed from outer source, aka delete row from other source
        da.Fill(ds.table)  'reloads data so you get fresh source
end sub

FYI  da.update(ds.table) can all so be da.update(ds, "TableName")  ' same with fill
   
I all so use a timer in some applications, and run them on 10,000 msec clocks. So the table is updated even if the change is from another source. Though this requires more work as you have to code for other conditions.

Let me know if you need  anymore help.
-GL
0
 

Author Comment

by:kate_y
ID: 12099030
    I still don't understand. Here is my code. I used "?" as new stuff to assign to the fields. Is it right? The error message said "couldn't find table mapping or datatable "Vendor" "....:(
       

        Dim strSQL As String = "UPDATE TM_Vendor SET LocationCode = ?, Period = ?" & _
               "WHERE TM_Vendor.ScenarioName = '" & x & "'"

        ',Item,SalePrice,MaxQuantity,MinQuantity
        Dim da As OleDbDataAdapter 'define dataAdapter's name. dataAdapter copies data from db to dataset
        Dim ds As DataSet 'define dataset name

        da = New OleDbDataAdapter(strSQL, strConn) 'define a new data adapter copies data from database based on strSQL after strConn connedted db
        ds = New DataSet
        Try
            da.TableMappings.Add("Table", "Vendor")
            da.Update(ds, "Vendor")
            da.Fill(ds, "Vendor")
           
        Catch ex As SystemException
            MsgBox(ex.StackTrace & ex.Message, MsgBoxStyle.Critical, "General Error")
        End Try
0
 
LVL 2

Expert Comment

by:ahmadfuwad
ID: 12100406
remember table must have primary key then following code will run successfully
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''File The Datagrid''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  cn.ConnectionString = "Server=eteam04;Database=Test;user id=sa;password=password"
        cn.Open()
        cmd1.Connection = cn
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select * from Customer"
        oda1.SelectCommand = cmd1
        oda1.Fill(ods, "Customer")
        Me.DataGrid1.DataSource = ods.Tables("Customer")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Code for Update And Add Row''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim cm As SqlCommandBuilder = New SqlCommandBuilder(oda1)
        oda1.Update(ods, "Customer")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Code for Delete''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ods.Tables(0).Rows(Me.DataGrid1.CurrentRowIndex).Delete()
        Dim cm As SqlCommandBuilder = New SqlCommandBuilder(oda1)
        oda1.Update(ods, "Customer")


0
 

Author Comment

by:kate_y
ID: 12105197
So I don't need to use a SQL Update statement? Is it ok if my table use combined keys as the primary key? thanks.
0
 

Author Comment

by:kate_y
ID: 12105686
I don't know what i missed, it just doesn't work. It didn't give any error msg but just returned to the original state.

sub btnUpdate_click()
        Dim da As OleDbDataAdapter
        Dim ds As DataSet
        Dim strSQL As String = "SELECT LocationCode,LocationName,Period,Item,SalePrice,MaxQuantity,MinQuantity " & _
                 "FROM TM_Vendor WHERE TM_Vendor.ScenarioName = '" & x & "'"
        Dim cnn As OleDbConnection

        cnn = New OleDbConnection(strConn)
        cnn.Open()
     
        da = New OleDbDataAdapter(strSQL, strConn)
        ds = New DataSet  

       da.Fill(ds, "Vendor")
       DataGrid1.DataSource = ds.Tables("Vendor")
       Dim cm As OleDbCommandBuilder = New OleDbCommandBuilder(da)
       da.Update(ds, "Vendor")

End sub
0
 
LVL 2

Accepted Solution

by:
ahmadfuwad earned 500 total points
ID: 12120584
'remove your last two rows and add following code it will work fine
Dim oCommandBuilder As New OleDbCommandBuilder(da)
 da.Update(ds, "Vendor")
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now