[Weeman001]

What I'm trying to do is load an Excel file and see through each row; in each row I want each cell in that row to be put into an array which would loop through for the next row.

Through finding the row cells I want to update my database with this information. At the moment I am assigning the excel file to a datagridview because I do not have the knowledge to find another solution after looking across the internet I still ain't come up with a suitable solution.

Upon running my code below; after one minute it crashes with the following error:

"The CLR has been unable to transition from COM context 0x94cc80 to COM context 0x94cdf0 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations."

The code below is what I'm using atm (which crashes) but I want to be able to actually get rid of the datagridview as this application must not contain a GUI (ment to be a fully automated system, with no actions taken from the end user other than running the application.


Any help would be appreciated as I've been working on this for quite some time now can cannot get it to work.

Thank you in advanced x

Email me if need be

       Try

            'declare a oledb connection to connect to the excel file
            Dim MyConnection As System.Data.OleDb.OleDbConnection

            'declare a dataset
            Dim DtSet As System.Data.DataSet

            'declare a oledb command
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

            'give the connection its string value
            MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\AvailabilityImports\Fisher\Availability.xls';Extended Properties=Excel 8.0;")

            'give the command its SQL query
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Availability$]", MyConnection)

            '*** need the values in an array???

            MyCommand.TableMappings.Add("Table", "TestTable")

            DtSet = New System.Data.DataSet

            MyCommand.Fill(DtSet)

            DataGridView1.DataSource = DtSet.Tables(0)

            MyConnection.Close()

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

        '*** Import captured data into SQL Database

        Try
            'Connection String
            ConnectDB.ConnectionString = "Data Source=wd-00126;Initial Catalog=NewTredzWeb;Integrated Security=True"

            'Show progress bar & assign its value
            ProgressBar1.Visible = True
            ProgressBar1.Maximum = DataGridView1.Rows.Count - 1

            'loop through the datagridview
            For m = 0 To DataGridView1.Rows.Count - 1

                'Update as new command
                Dim updateSQL As New SqlCommand
                'Update command String
                updateSQL.CommandText = ("UPDATE tblproductsku SET SuppAvail = '" & DataGridView1.Rows(m).Cells(17).Value & "', RRP = '" & DataGridView1.Rows(m).Cells(7).Value & "', SkuTrade = '" & DataGridView1.Rows(m).Cells(9).Value & "', SuppAvailLastUpdate = GETDATE() WHERE productref in (SELECT productref FROM tblproductdetail WHERE Supplier LIKE '" & "Fisher".ToLower.Trim & "') and ManRef = '" & DataGridView1.Rows(m).Cells(2).Value & "')")

                'Open connection
                updateSQL.Connection = ConnectDB
                ConnectDB.Open()
                'Execute 
                updateSQL.ExecuteNonQuery()
                'Close Connection
                ConnectDB.Close()
                'Progress bar - new value
                ProgressBar1.Value = m
                'loop again until end of rows
            Next m


        Catch ex As Exception
            MessageBox.Show(ex.Message + " Update Stock for Supplier: 'Fisher' Failed")
            ConnectDB.Close()
        Finally
            MessageBox.Show("Successfully Inserted .XLS file into the Database")
        End Try

    End Sub

[CharlieMay]

To get rid of the datagridview would be simple. You bring the sheet into a dataset right? Why not just loop through the dataset and put it back into a database (same as you're doing with the dgv). You SHOULD be able to update the database based on that dataset with a quick and easy update via a commandbuilder but I would have to test that to be sure.

Either way, the dataset can be viewed as an Invisible Datagrid view and can be looped through much like you're doing with the DGV.

If I get time later, I'll play around with the dataset and see if this is something simple as it seems to be in my head but in practice, I may be overlooking something.

[Weeman001]

Brilliant I've literally tried my best to get this sorted; and using this forum as my last resort! I just cant seem to find a suitable solution but hopefully now ill get it done tomorrow with the guidance you've provided.

Thank you plus also thank you again in advanced it you going to have a play around with it

[m_wylie85]

Hey i was looking at what CharlieMay said so i try it my self this is just very basic/ cut down version way of doing what he was saying (i Think): I wrote this in a console app so NO GUI / datagridview

Module Module1

    'declare a oledb connection to connect to the excel file
    Dim MyConnection As System.Data.OleDb.OleDbConnection

    'declare a dataset
    Dim DtSet As System.Data.DataSet

    'declare a oledb command
    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    Sub Main()
        CreateSupplier()
    End Sub



    Sub CreateSupplier()
        Dim ds As DataSet = Get_Excel()
        Dim dr As DataRow = Nothing
        Dim dt As DataTable = ds.Tables(0)

        For Each dr In dt.Rows
            Console.WriteLine(dr)
        Next

        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & CStr(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\ExcelToAccess.mdb")
        Dim DBCon As New OleDb.OleDbConnection(ConnString)
        DBCon.Open()
        Dim cm As New OleDb.OleDbCommand(" insert into Table1 (CustAcctNum) VALUES  ('" & dr.Item(0) & "')", DBCon)

        Try
            cm.ExecuteNonQuery()

            DBCon.Close()
        Catch ex As Exception

        Finally

        End Try
    End Sub

    Private Function Get_Excel()

        Try
            'give the connection its string value
            MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & CStr(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\Availability.xls; Extended Properties=Excel 12.0"))
            'give the command its SQL query
            MyConnection.Open()
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

            '*** need the values in an array???

            DtSet = New System.Data.DataSet

            MyCommand.Fill(DtSet)
            MyConnection.Close()

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
        Return DtSet
    End Function
End Module

This post has been edited by m_wylie85: 31 May 2012 - 01:52 PM

[Weeman001]

Thank you very much for your help guys, I'm going to be trying this tomorrow morning, when I have a set of fresh eyes and head as been on this all day. Will post back tomorrow stating the progress/results

Just one question: Will your code allow me to get the data in a dataset which then can be inserted/updated into an SQL database? It looks good and far more reasonable compared to my code.

Again Thank you very much for your quick reply I will let you know how it goes and maybe then I can flag this post as solved

[m_wylie85]

Hey can you stop using that font please

[Weeman001]

Sorry Mate

The reason for my code crashing is because I had no Multi-threading functions available. I have not yet done this but I'm currently implementing this. Plus got rid of the datagridview due to your help Many Thanks Guys!

I'll post the fully working code when the multi-threading has been completed, However got loads of coding to do as if for 102 suppliers

Thank you both very much x