[RESOLVED] A weird Excel problem

[tommygrayson]

Help!!! Help!!! Help!!! I always have this error whenever I transfer Excel data to SQL.

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in adodb.dll

Every time I run this code

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ExcelApp As Microsoft.Office.Interop.Excel.Application = GetObject("", "Excel.Application")
        Dim ExcelWorkbook As Microsoft.Office.Interop.Excel.Workbook
        Dim ExcelWorksheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim ExcelRange As Microsoft.Office.Interop.Excel.Range
        Dim ColumnData As String
        Dim ADOConn As New ADODB.Connection
        Dim ADOComm As New ADODB.Command
        Dim ADOParam As New ADODB.Parameter
        Dim ADORec As New ADODB.Recordset
        Dim EmployeeCounter As Integer
        Dim EmployeeMaxCounter As Integer
        Label1.Visible = True
        EmployeeCounter = 1
        EmployeeMaxCounter = 42
        ExcelWorkbook = ExcelApp.Workbooks.Open("C:\Transglobal Knowledge\Employ Pro 0.0.1\as of January 2006.xls")
        ExcelWorksheet = ExcelWorkbook.Worksheets("Sheet1")
        ADOConn.ConnectionString = "Provider=SQLOLEDB.1;Password=cons0l;Persist Security Info=True;User ID=sa;Initial Catalog=Employees;Data Source=MNLBS001"
        ADOConn.Open()
        For EmployeeCounter = 1 To EmployeeMaxCounter
            ADOParam = ADOComm.CreateParameter("@Fullname", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 50)
            ADOComm.CommandText = "[NewConvEmp]"
            ADOComm.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
            ADOComm.ActiveConnection = ADOConn
            ExcelRange = ExcelWorksheet.Cells(16 + EmployeeCounter, 2)
            ColumnData = ExcelRange.Text
            ADOParam.Value = ColumnData
            ADOComm.Parameters.Append(ADOParam)
            ADORec = ADOComm.Execute()
            ProgressBar1.Increment(100 / (EmployeeMaxCounter - (EmployeeCounter + 1)))
            ADOComm.Parameters.Refresh()
        Next
        ADOConn.Close()
        ExcelWorkbook.Close()
        ExcelApp.Quit()
End Sub

[RobDog888]

Looks like your using the ADODB instead of ADO.NET for starters. Then you should use a Try Catch to trap the error being generated from the data access code.

[tommygrayson]

Looks like your using the ADODB instead of ADO.NET for starters. Then you should use a Try Catch to trap the error being generated from the data access code.

Hmmm... good point. I will "try" that.

I have another question. Is ADO .NET better than ADODB when in comes to transferring Excel data to SQL?

Does data adapter objects use ADO .NET?

[RobDog888]

Yes, ADO.NET is better then using the old ADO in general.

A data adapter is part of ado.net.

[tommygrayson]

Now I'm getting somewhere. I will not use ADODB at this state. I will try to use the Data Adapter.

I always love ADODB but in this case I have no choice but to change it to ADO .NET.

[tommygrayson]

Looks like your using the ADODB instead of ADO.NET for starters. Then you should use a Try Catch to trap the error being generated from the data access code.

Now I'm getting somewhere. The error has generated a more clear message.

Muti-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work has done.

What does the error message mean?

[tommygrayson]

How do I use ADO .NET to connect to an Excel worksheet?

[tommygrayson]

Can anyone help me here?

[tommygrayson]

Anyone?

[RobDog888]

There are several causes for this error. Did you search the hlpe file for the error message?

Step through your code and check your values and see what its actually doing.

[jmcilhinney]

What code are you using now?

[tommygrayson]

Yes, ADO.NET is better then using the old ADO in general.

A data adapter is part of ado.net.

I did followed your advice and it worked. Using the data adapter is much more easier than what I use. Thanks.

I'll rate your post for that and Jmchilhinney's for responding.

[jmcilhinney]

I did followed your advice and it worked. Using the data adapter is much more easier than what I use. Thanks.

I'll rate your post for that and Jmchilhinney's for responding.

I don't mean to sound ungrateful but I'd suggest not rating posts just for responding. By all means rate posts that are genuinely helpful, but any old idiot can respond, as evidenced by the fact that I responded to this thread.

[RobDog888]

Maybe he was wanting to give a -ve rating Jk