how to do update loop using excel as database

[jared.geli]

So far I can compute and update seperately a specific row when type it in a text box. What I want now is to read and update rows automatically up until the last record.

Imports System.Data.OleDb
Public Class Form1
    Dim MyConnection As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source=G:\Raw Data.xlsx; Extended Properties=Excel 12.0;")
    Dim DtSet As DataSet
    Dim myCommand As OleDbDataAdapter
    Dim myCommandsql As OleDbCommand
    Dim sql As String
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As String

        s = TextBox1.Text

        Dim i, cs, digit As Integer
        cs = 0 'checksum

        For i = 1 To 12
            digit = Mid(s, i, 1) - "0"
            If i Mod 2 = 0 Then
                cs = cs + digit * 3
            Else
                cs = cs + digit * 1
            End If
        Next i

        cs = (10 - (cs Mod 10)) Mod 10


        TextBox2.Text = s & cs
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Try
        MyConnection.Open()
        myCommand = New OleDbDataAdapter _
        ("select * from [Sheet1$]", MyConnection)
        'myCommand.TableMappings.Add("Table", "TestTable")
        DtSet = New DataSet
        myCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()
        'Catch ex As Exception
        'MsgBox(ex.ToString)
        'End Try
    End Sub


    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        'Try

        MyConnection.Open()
        sql = "Update [Sheet1$] set BARCODE = '" & TextBox2.Text & "' Where RAW = '" & TextBox1.Text & "'"
        myCommandsql = New OleDbCommand(sql, MyConnection)
        'myCommandsql.CommandText = sql
        myCommandsql.ExecuteNonQuery()
        myCommand = New OleDbDataAdapter _
       ("select * from [Sheet1$]", MyConnection)
        DtSet = New DataSet
        myCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()
        'Catch ex As Exception
        '   MsgBox(ex.ToString)
        'End Try
        MsgBox("Updated ")
    End Sub
End Class

Disregard the DataGrid. Thanks

[PerplexedB]

Let me see if I got your problem right. You have this sheet in an excel workbook that contains a column with numbers (Raw) and an empty column with title Barcode, that you need to fill.

In your button 1 you have a routine where you enter a number in textbox1, then you generate the barcode and you display it in textbox2.

First thing you should do is to created a method that returns a barcode based on an input.

Something like this (did not test it).

private function genBarcode(s as string) as string
        Dim i, cs, digit As Integer
        cs = 0 'checksum
        For i = 1 To 12
            digit = Mid(s, i, 1) - "0"
            If i Mod 2 = 0 Then
                cs = cs + digit * 3
            Else
                cs = cs + digit * 1
            End If
        Next i
        cs = (10 - (cs Mod 10)) Mod 10
        return s & cs
end function

Then in your button one you just need to me.textbox2.text = genBarcode(me.textbox1.text)

Might be come in handy to test.

Now in your Button3 you might want to read in the datatable very much as what you have done, the i.o. of displaying everything in the datagrid, you will want to loop:

    for each oRow as datarow in DtSet.Tables(0).rows
        dim sql as string
        sql = "update ... set barcode = " 
        sql &= genBarcode(oRow("Row")) where column1 = " & oRow("Raw")
        ' execute the sql
    next

There remains some work to be done by you, but if what you showed us work, you should be able to get what you need.

Good luck.

[jared.geli]

Thanks for the reply. I'll let you know if it works

[jared.geli]

I've tried your suggestion except for the private function and I'm getting this error:"Cannot find table 0."
Here's the code

    myCommand = New OleDbDataAdapter _
    ("select * from [Sheet1$]", MyConnection)

    DtSet = New DataSet


    For Each Row As DataRow In DtSet.Tables(0).Rows
        For Each Raw As DataColumn In DtSet.Tables(0).Columns
            Dim s As String = Row(Raw.ColumnName).ToString()
            Dim i, cs, digit As Integer
            cs = 0 'checksum

            For i = 1 To 12
                digit = Mid(s, i, 1) - "0"
                If i Mod 2 = 0 Then
                    cs = cs + digit * 3
                Else
                    cs = cs + digit * 1
                End If
            Next i

            cs = (10 - (cs Mod 10)) Mod 10

            Dim bc=s & cs



            sql = "Update [Sheet1$] set BARCODE = '" & bc & "' Where RAW = '" & s & "'"
            myCommandsql = New OleDbCommand(sql, MyConnection)
            myCommandsql.ExecuteNonQuery()

        Next
        Next

It's on button 1 and I'm want to get the values per row in column Raw after computation of value barcode it will update its row and and to the next up until to the last row and I'm disregarding the text boxes this time.

[PerplexedB]

You need myCommand.Fill(DtSet) after you define the dataset.

Why you loop through your columns is not clear to me. I thought you had one column that is called "raw" and another "barcode". After you For Each Row As DataRow In DtSet.Tables(0).Rows, you will find the values of raw in oRow("raw").

Hope this helps.

[jared.geli]

sorry did not notice that

here's my code as of now and I'm still getting error but this time it's:
"No value given for one or more required parameters."

myCommand = New OleDbDataAdapter _
             ("select * from [Sheet1$]", MyConnection)
        Dim DtSet As New DataSet
        myCommand.Fill(DtSet)
        MyConnection.Open()

        For Each Row As DataRow In DtSet.Tables(0).Rows
            For Each Coll As DataColumn In DtSet.Tables(0).Columns
                Dim r As String = Row(Coll.ColumnName).ToString()

                Dim bc = genBarcode(r)

                sql = "Update [Sheet1$] Set BARCODE = '" & bc & "' Where RAW = '" & r & "'"
                myCommandsql = New OleDbCommand(sql, MyConnection)
                myCommandsql.ExecuteNonQuery()
            Next
        Next
        MyConnection.Close()

I revised the code you gave me earlier because this code:

 sql &= genBarcode(oRow("Row")) where column1 = " & oRow("Raw")

gives me error

[PerplexedB]

replace

For Each Coll As DataColumn In DtSet.Tables(0).Columns
                Dim r As String = Row(Coll.ColumnName).ToString()
                Dim bc = genBarcode(r)
                sql = "Update [Sheet1$] Set BARCODE = '" & bc & "' Where RAW = '" & r & "'"
                myCommandsql = New OleDbCommand(sql, MyConnection)
                myCommandsql.ExecuteNonQuery()
            Next

with

                Dim r As String = Row("Raw")
                Dim bc = genBarcode(r)
                sql = "Update [Sheet1$] Set BARCODE = '" & bc & "' Where RAW = '" & r & "'"
                myCommandsql = New OleDbCommand(sql, MyConnection)
                myCommandsql.ExecuteNonQuery()

[jared.geli]

now I'm getting a "Column 'RAW' does not belong to table Table."
well atleast the table 0 error is gone thanks again I think I can fix this one
I'll let you know if I'm done thanks again

[PerplexedB]

well it depends on how your connection is set up uh and what the title of your column is. It might be case sensitive.

Alternatively, you might want to try row(0)

[jared.geli]

Thanks for your help the problem was my connection your code works