none
Access\Change a cell in access databases in Visual Basic 2012

    Question

  • Hi, I want to Access\Assign Value of a Cell in Access Database in Visual Basic 2010 by it's index.

    Actually I am tired using Dastaset.Table(index).column_name

    I want to access it using it's column index like two dimensional Arrays.

    Is it any easier way to access cells (values) in <For> and <While>.

    Thanks In Advance.

    Saturday, August 25, 2012 6:12 PM

Answers

  • Be aware that with classic indexing it is slower (it has first to find the columnobject)

    However, why did you not try it.

    DataSet.TableName.Rows(x)(y)

    Be aware a datatable is not an array, it is an collection, and for sure not 2 dimensional.

    I don't see by the way why this makes it easier in a for and while, it seems that you have to read the documentation better.


    Success
    Cor

    • Marked as answer by Javid5555 Sunday, August 26, 2012 9:58 AM
    Sunday, August 26, 2012 5:09 AM
  • You can try this method.It may work.

    PublicFunction SDG(ByVal DG As DataGridView)
    Dim rows AsInteger = DG.RowCount
    Dim Coulumn AsInteger = DG.ColumnCount
    Dim Prudect AsString
    Dim N AsInteger
    Dim Price AsDouble
    Dim Total AsDouble
    Dim Final AsDouble
    Dim i AsInteger
    For i = 0To rows - 1
                Prudect = DG(0, i).Value
                N = DG(1, i).Value
                Price = DG(2, i).Value
                Total = DG(3, i).Value
                Final = DG(4, i).Value
    ''''''''''''''''''''''''
    Dim SavInto AsNew OleDb.OleDbCommand
    Dim ConStr AsString = _
    "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & _
                    Application.StartupPath & "\mydata.mdb"
    Dim Conn AsNew OleDbConnection(ConStr)

                SavInto.Connection = Conn
                SavInto.CommandType = CommandType.Text
                SavInto.CommandText = "INSERT INTO Report(Prudect,Number,Price,total,FinalPrice)" & _
    "values ('" & Price & "','" & N & "','" & Price & "','" & Total & "')"
                Conn.Open()
                SavInto.ExecuteNonQuery()
                Conn.Close()
    Next
    EndFunction

                                                                                                                                                                   
    • Proposed as answer by Radhakrishnan Iyer Sunday, August 26, 2012 10:50 AM
    • Marked as answer by Javid5555 Sunday, August 26, 2012 11:17 AM
    Sunday, August 26, 2012 2:28 AM

All replies

  • You can try this method.It may work.

    PublicFunction SDG(ByVal DG As DataGridView)
    Dim rows AsInteger = DG.RowCount
    Dim Coulumn AsInteger = DG.ColumnCount
    Dim Prudect AsString
    Dim N AsInteger
    Dim Price AsDouble
    Dim Total AsDouble
    Dim Final AsDouble
    Dim i AsInteger
    For i = 0To rows - 1
                Prudect = DG(0, i).Value
                N = DG(1, i).Value
                Price = DG(2, i).Value
                Total = DG(3, i).Value
                Final = DG(4, i).Value
    ''''''''''''''''''''''''
    Dim SavInto AsNew OleDb.OleDbCommand
    Dim ConStr AsString = _
    "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & _
                    Application.StartupPath & "\mydata.mdb"
    Dim Conn AsNew OleDbConnection(ConStr)

                SavInto.Connection = Conn
                SavInto.CommandType = CommandType.Text
                SavInto.CommandText = "INSERT INTO Report(Prudect,Number,Price,total,FinalPrice)" & _
    "values ('" & Price & "','" & N & "','" & Price & "','" & Total & "')"
                Conn.Open()
                SavInto.ExecuteNonQuery()
                Conn.Close()
    Next
    EndFunction

                                                                                                                                                                   
    • Proposed as answer by Radhakrishnan Iyer Sunday, August 26, 2012 10:50 AM
    • Marked as answer by Javid5555 Sunday, August 26, 2012 11:17 AM
    Sunday, August 26, 2012 2:28 AM
  • Be aware that with classic indexing it is slower (it has first to find the columnobject)

    However, why did you not try it.

    DataSet.TableName.Rows(x)(y)

    Be aware a datatable is not an array, it is an collection, and for sure not 2 dimensional.

    I don't see by the way why this makes it easier in a for and while, it seems that you have to read the documentation better.


    Success
    Cor

    • Marked as answer by Javid5555 Sunday, August 26, 2012 9:58 AM
    Sunday, August 26, 2012 5:09 AM
  • The following shows several ways to access data in a DataGridView but as it is all hand coded, no wizards to strongly type data you must create update, insert and remove SQL statements yourself. It appears you may be using a data wizard and if so you should still be able to gleem some tips from the code below.

    VS2010 VB.NET code

    ''' <summary>
    ''' Controls
    ''' BindingNavigator1       - BindingNavigator
    ''' DataGridview1           - DataGridView
    ''' cmdChecledRows          - Button
    ''' txtLastName_1           - TextBox
    ''' txtLastName_2           - TextBox
    ''' lblCurrentIdentifier    - Label
    ''' lblDataGridViewRowIndex - Label
    ''' </summary>
    ''' <remarks></remarks>
    Public Class Form1
        WithEvents bsPeople As New BindingSource
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
            ' Simulate loading from backend database
            Dim dt = GetMockedData()
            dt.Columns.Add(New DataColumn With {.DataType = GetType(Boolean), .ColumnName = "Process"})
            dt.Columns.Add(New DataColumn With
            {.DataType = GetType(String), .ColumnName = "Person", .Expression = " FirstName + ' ' + LastName"})
            dt.Columns("FirstName").ColumnMapping = MappingType.Hidden
            dt.Columns("LastName").ColumnMapping = MappingType.Hidden
            ' Position to first column is optional        
            dt.Columns("Process").SetOrdinal(0)
            ' give each row a value for the injected column
            For Each row As DataRow In dt.Rows
                row("Process") = False
            Next
            dt.AcceptChanges()
            bsPeople.DataSource = dt
            DataGridView1.DataSource = bsPeople
            BindingNavigator1.BindingSource = bsPeople
            txtLastName_1.DataBindings.Add("Text", bsPeople, "LastName")
            'Ensure we don't double trigger the event
            AddHandler DataGridView1.CellEnter, AddressOf DataGridView1_CellEnter
            ' Next 2 lines for showing row index in RowHeader of DataGridView
            DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToFirstHeader
            DataGridView1.EnableHeadersVisualStyles = False
        End Sub
        ''' <summary>
        ''' Simulate loading data from backend database since generally speaking
        ''' data from a database is loaded into a DataTable!
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function GetMockedData() As DataTable
            Dim dt As New DataTable()
            dt.Columns.Add("Identifier", GetType(Integer))
            dt.Columns.Add("FirstName", GetType(String))
            dt.Columns.Add("LastName", GetType(String))
            dt.Rows.Add(10, "John", "Smith")
            dt.Rows.Add(20, "Mary", "Willson")
            dt.Rows.Add(30, "Frank", "Mills")
            dt.Rows.Add(40, "Amy", "Jones")
            dt.Rows.Add(50, "Karen", "Starr")
            Return dt
        End Function
        ''' <summary>
        ''' Get rows that are checked
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub cmdChecledRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdChecledRows.Click
            Dim Rows =
            (From T In CType(bsPeople.DataSource, DataTable).AsEnumerable
             Where T.Field(Of Boolean)("Process") = True Select T).ToList
            If Rows.Count > 0 Then
                Dim sb As New System.Text.StringBuilder
                sb.AppendLine("Includes hidden columns.")
                For Each item In Rows
                    sb.AppendLine(String.Join("-", item.ItemArray))
                Next
                MessageBox.Show(sb.ToString)
            Else
                MessageBox.Show("Nothing checked")
            End If
        End Sub
        ''' <summary>
        ''' Current DataGridView row cell Identifier via the underlying datarowview
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub bsPeople_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsPeople.PositionChanged
            If bsPeople.Current IsNot Nothing Then
                lblCurrentIdentifier.Text = CType(bsPeople.Current, DataRowView).Item("Identifier").ToString
            End If
        End Sub
        ''' <summary>
        ''' Show LastName of underlying DataRow
        ''' Index of current row
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub DataGridView1_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
            If DataGridView1.CurrentRow IsNot Nothing Then
                txtLastName_2.Text = CType(bsPeople.DataSource, DataTable).Rows(DataGridView1.CurrentRow.Index)("LastName").ToString()
                lblDataGridViewRowIndex.Text = DataGridView1.CurrentRow.Index.ToString
            End If
        End Sub
        ''' <summary>
        ''' Show row index in row header cell of each row of the DataGridView
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub DataGridView1_CellFormatting(
            ByVal sender As Object,
            ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
            If Not DataGridView1.Rows(e.RowIndex).IsNewRow Then
                DataGridView1.Rows(e.RowIndex).HeaderCell.Value = e.RowIndex.ToString
            End If
        End Sub
    End Class


    KSG

    Sunday, August 26, 2012 6:16 AM
    Moderator
  • Be aware that with classic indexing it is slower (it has first to find the columnobject)

    However, why did you not try it.

    DataSet.TableName.Rows(x)(y)

    Be aware a datatable is not an array, it is an collection, and for sure not 2 dimensional.

    I don't see by the way why this makes it easier in a for and while, it seems that you have to read the documentation better.


    Success
    Cor

    The interesting point is we CAN NOT write: Dataset.tablename(X)(Y).value = "NewValue"

    We can just write: Dataset.tablename(X)(Y) = "NewValue"    with no  ".value"

    By the way Thanks for your immediate Answer
    • Edited by Javid5555 Sunday, August 26, 2012 10:05 AM
    Sunday, August 26, 2012 10:04 AM