VB.NET reading excel file and update database via query

I am writing a small application that would read an excel file and then update a SQL database.  The excel file has only 2 columns A and B.  A has the Item_ID and B has the Cost.  What I wanted to do is read this excel file and then update the database table if it matches the Item_ID.

Example:
ColumnA              ColumnB
ABC                       15.00
DEF                       20.00
GHI                       18.00

Below I started out reading the excel file, but I am running into an error that the "Microsoft Jet Database Engine could not find the object.  Make sure the object exists and that you spell its name and the path name correctly.  Here's the query that I want to update with.

UPDATE PARTS
SET PRICE = strPrice
WHERE ITEM_ID = strItem


Being a novice at VB.NET, I am not sure what I need to do.
Try
            Dim strConn As String
            Dim strSQL As String
 
            strConn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=(0);Extended Properties='Excel 8.0;HDR=Yes'", "C:\Output.xls")
 
            strSQL = "SELECT * FROM [PRICE]"
 
 
            Dim cnn As New OleDb.OleDbConnection(strConn)
            Dim cmd As New OleDb.OleDbCommand(strSQL, cnn)
 
            Dim oleReader As OleDb.OleDbDataReader
            Dim dtbExcelData As New Data.DataTable
 
            Call cnn.Open()
            oleReader = cmd.ExecuteReader
 
            dtbExcelData.Load(oleReader)
 
            Call oleReader.Close()
            Call cnn.Close()
 
            oExcel = CreateObject("Excel.Application")
            Dim wbk As Object
 
            wbk = oExcel.Workbooks.Open(FileName:=txbFile.Text, UpdateLinks:=False, ReadOnly:=False)
 
            Dim i As Integer
 
            numrow = 2
 
            For i = 0 To dtbExcelData.Rows.Count - 1
                With wbk.ActiveSheet
                    If .Range("A" & numrow).Value <> "" Then
                        strItem = .Range("A" & numrow).value
                        numrow = numrow + 1
                    Else
                        numrow = numrow + 1
                    End If
                End With
            Next
 
            wbk = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
            oExcel = Nothing
            GC.Collect()
 
        Catch ex As Exception
            MsgBox("Error with excel file: " & ex.Message)
        End Try

Open in new window

holemaniaAsked:
Who is Participating?
 
holemaniaAuthor Commented:
The code below works well and very fast.  Transfer about 1500 records in seconds.  Basically dump selected field and dump into a table.
'Connection string
        Dim excelCon As String
        Dim conString As String = GetConnectionString()
        Dim file As String = "C:\Parts.xls"
 
        excelCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
 
        Dim excelstrCon As New OleDb.OleDbConnection(excelCon)
        excelstrCon.Open()
 
        'Create objects and get data
        Dim cmd As New OleDb.OleDbCommand("SELECT [PART ID], [TOTAL COST] FROM [PART$]", excelstrCon)
        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
 
        'open the destination data
        Dim dbCon As SqlConnection = New SqlConnection(conString)
        dbCon.Open()
        Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(dbCon)
 
        bulkCopy.DestinationTableName = "PART_COST"
        bulkCopy.WriteToServer(reader)
        reader.Close()

Open in new window

0
 
ThatDeadDudeCommented:
I think that as it stands the issue is with your connection on line 5.  It appears that you are opening an Excel file as if it were a database, and using the Jet provider.  Is there a reason for this?
0
 
holemaniaAuthor Commented:
There's no reason for this.  Is there another way of opening the excel file and reading those 2 columns?

Basically I just want to be able to read the excel file, count/read number of rows with data and than loop around my update query to update the SQL database with the pricing.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ThatDeadDudeCommented:
My issue was with the initial connection at the top of your code.  It's unnecessary given that you're creating a reference to the Excel application in the CreateObject line.  Thus, I think the connection string at the top of the page is where the problem is occurring.  I've not used .NET, but given that a lot of the syntax is similar to old-school VB I'll try give you something of a correction.  You may have to fix some syntax errors, and you'll have to play with the connection string to get the connection to your SQL Server working properly.
Try
            Dim strConn As String
            Dim strSQL As String
 
            strConn = "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=dBName; User Id=myUserName; Password=myPassword"
 
            Dim cnn As New OleDb.OleDbConnection(strConn)
            Dim cmd As OleDb.OleDbCommand
 
            oExcel = CreateObject("Excel.Application")
            Dim wbk As Object
 
            wbk = oExcel.Workbooks.Open(FileName:=txbFile.Text, UpdateLinks:=False, ReadOnly:=False)
 
            Dim i As Integer
 
            numrow = 2
 
            For i = 0 To wbk.ActiveSheet.UsedRange.Rows.Count - 1
                With wbk.ActiveSheet
                    If .Cells(numrow, 1).Value <> "" Then
                        strItem = .Range("A" & numrow).value
                        strSQL = "UPDATE PARTS SET PRICE = " & .Cells(numrow, 2).Value & " WHERE ITEM_ID = " & .Cells(numrow, 1).Value
                        cmd = new OleDb.OleDbCommand(strSQL, cnn)
                        cmd.ExecuteNonQuery
                        cmd = Nothing
                        numrow = numrow + 1
                    Else
                        numrow = numrow + 1
                    End If
                End With
            Next
 
            wbk = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
            oExcel = Nothing
            GC.Collect()
 
        Catch ex As Exception
            MsgBox("Error with excel file: " & ex.Message)
End Try

Open in new window

0
 
silemoneCommented:
are you using excel 2007?  or 2003?  if 2007, then you're using wrong connect string...
0
 
holemaniaAuthor Commented:
I am using excel 2003.
0
 
holemaniaAuthor Commented:
Thinking this over, I think my approach is probably not the best way of updating a SQL database via reading it off an excel spreadsheet.  Would it be more efficient to read the excel file into a dataset then create another procedure where it would read from the dataset and then update the SQL database?  Anyone might have sample code of reading excel into a dataset and then update a table in SQL Server base on the Item_ID?

Example:
ColumnA              ColumnB
ABC                       15.00
DEF                       20.00
GHI                       18.00

Once that is read into a dataset, create another function to update a table called PARTS in SQL if the item_id matches?
UPDATE PARTS
SET PRICE = strPrice
WHERE ITEM_ID = strItem

0
 
silemoneCommented:
I'm doing something similar and I do think it probably would be more efficient to do one procedure...and once that procedure has been completed complete the next...maybe not super quick, but I wrote my code where it reads and then updates database and its locking the computer up and using up to 45% of computer resources...
0
 
holemaniaAuthor Commented:
Are you reading off an excel and than updating the database or of a dataset?  Originally my request was to read of an excel file looking for the item id, and compare it to the databse.  If it matches than only update that item with the updated pricing.

Is it possible you post your code?
0
 
silemoneCommented:
ok...

Here's what's goin on here...I open an excel file that has 3 different data collections...I find first empty line...count it...it there is another, that means end of file..otherwise i reset counter back to 0 because it means we have found our second table (separated by empty line) -- this code  while (flag != 2){....} checks to see if to empty lines...i called it flag...should have more meaningful name like Emptylines...

I skip header files also as seen  
                              if (RecordType == "RECORD_TYPE")
                              {
                                   index++;
                                   rowIndex = index;
                                   continue;
                              }

Everything else is just Microsoft...Excel library

Oops...i do have a Find directory for file...which locates directory for the file....i.e.  user can't just type any filename/filepath to the EXCEL file...they have to use browse function and it opens a "Find File" Dialog that only looks for .xls, .xlsx. and all files

my beginning simple interface looks like

EnterFileNameLabel:     filePathNameTextBox<--txtFilePath        BrowseButton <------btnBrowse

                                         ImportButton  <--- btnImport

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.IO;
 
 
namespace WindowsApplication1
{
     public partial class Form1 : Form
     {
          Microsoft.Office.Interop.Excel.ApplicationClass excel;
          Microsoft.Office.Interop.Excel.Workbook excelWorkBook;
          Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet;
          string excelFile = @"C:\DESTINY.xls";
 
 
          public Form1()
          {
               InitializeComponent();
               try
               {
 
               }
               catch (Exception ex)
               {
                    MessageBox.Show(ex.StackTrace.ToString());
               }
          }
 
 
 
 
 
          public void loadExcelWorkSheets()
          {
               excelFile = txtFilePath.Text.ToString();
               excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
               excelWorkBook = excel.Workbooks.Open(excelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
               excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.ActiveSheet;
          }
 
 
          private void LoadExcelData()
          {
 
               //Variables Declaration
               OleDbConnection objOleConn = null;
               OleDbCommand objOleComm = null;
               OleDbDataAdapter SQLAdapter = null;               
               string strConnectionString = String.Empty;
               string strDB = String.Empty;
               
              
               //Declaration of Variables
               strDB = @"C:\Documents and Settings\sospina\My Documents\JimmyProjectDB.accdb";
               loadExcelWorkSheets();
               int index = 1;
               object rowIndex = 1;
               string RecordType = String.Empty;
               int flag = 0;
               try
               {
 
                    if ((strDB.Trim().EndsWith(".accdb")))
                    {
                         strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;", strDB);
                    }
                    else
                         if ((strDB.Trim().EndsWith(".mdb")))
                         {
                              strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin;Password=;", strDB);
                         }
                    objOleConn = new System.Data.OleDb.OleDbConnection(strConnectionString);
                    objOleConn.Open();
                    //Delete all Data from first Table before populating
                    SQLAdapter = new OleDbDataAdapter();
                    string sqlStr = "Delete From GroupBookingData";
                    objOleComm = new OleDbCommand(sqlStr, objOleConn);
                    SQLAdapter.DeleteCommand = objOleComm;
                    objOleComm.ExecuteNonQuery();
 
                    //Delete all Data from second Table before populating
                    SQLAdapter = new OleDbDataAdapter();
                    sqlStr = "Delete From PassengerData";
                    objOleComm = new OleDbCommand(sqlStr, objOleConn);
                    SQLAdapter.DeleteCommand = objOleComm;
                    objOleComm.ExecuteNonQuery();
 
                    //Delete all Data from third Table before populating
                    SQLAdapter = new OleDbDataAdapter();
                    sqlStr = "Delete From MealsRequestsCreditsData";
                    objOleComm = new OleDbCommand(sqlStr, objOleConn);
                    SQLAdapter.DeleteCommand = objOleComm;
                    objOleComm.ExecuteNonQuery();
                    objOleConn.Close();                    
                    
                    RecordType = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 1]).Value2.ToString();
 
 
 
                    while (flag != 2)
                    {
                         if (((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 1]).Value2 != null)
                         {
                              flag = 0;
                              // Read the Cells to get the required value.                          
                              RecordType = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 1]).Value2.ToString();
                              if (RecordType == "RECORD_TYPE")
                              {
                                   index++;
                                   rowIndex = index;
                                   continue;
                              }
                              if (RecordType == "01")
                              {
                                   string BookingID = convertLastDigits(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 2]).Value2.ToString());
                                   string SailingDate = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 3]).Value2.ToString();
                                   Int32 CruiseLength = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 4]).Value2.ToString());
                                   string CabinCategory = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 5]).Value2.ToString();
                                   string Cabin = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 6]).Value2.ToString();
                                   Int32 PAX = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 7]).Value2.ToString());
                                   string ArrivalGate = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 8]).Value2.ToString();
                                   string DepartureGate = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 9]).Value2.ToString();
                                   string Prebooked = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 10]).Value2.ToString();
 
 
 
 
                                   SQLAdapter = new OleDbDataAdapter();
                                   objOleComm.Connection.Open();
 
                                   //sqlStr = "Insert Into GroupBookingData(RecordType, BookingID)Values('1', @BookingID)";
 
 
                                   /*sqlStr = "Insert Into GroupBookingData"
                                   + "([RecordType],[BookingID], [SailingDate], [CruiseLength], [CabinCategory], [CabinNumber], [Pax], [Arrival], [Departure], [Prebooked])"
                                   + "Values(@RecordType,@BookingID, '12/2/2009', @CruiseLength, @CabinCategory, @CabinNumber, @Pax, @Arrival, @Departure, @Prebooked)";
                                   */
                                   sqlStr = "Insert Into GroupBookingData"
                                   + "([RecordType],[BookingID], [SailingDate], [CruiseLength], [CabinCategory], [CabinNumber],[Pax], [Arrival], [Departure], [Prebooked])"
                                   + "Values(@RecordType,@BookingID, '12/12/2009', @CruiseLength, @CabinCategory, @CabinNumber, @Pax, @Arrival, @Departure, @Prebooked) ";
                                   objOleComm = new OleDbCommand(sqlStr, objOleConn);
                                   objOleComm.Parameters.AddWithValue("@RecordType", Convert.ToInt32(RecordType));
                                   objOleComm.Parameters.AddWithValue("@BookingID", BookingID);
                                   //objOleComm.Parameters.AddWithValue("@SailingDate", SailingDate);
                                   objOleComm.Parameters.AddWithValue("@CruiseLength", CruiseLength);
                                   objOleComm.Parameters.AddWithValue("@CabinCategory", CabinCategory);
                                   objOleComm.Parameters.AddWithValue("@CabinNumber", Cabin);
                                   objOleComm.Parameters.AddWithValue("@Pax", PAX);
                                   objOleComm.Parameters.AddWithValue("@Arrival", ArrivalGate);
                                   objOleComm.Parameters.AddWithValue("@Departure", DepartureGate);
                                   objOleComm.Parameters.AddWithValue("@Prebooked", convertPreBoooked(Prebooked));                                   
                                   SQLAdapter.SelectCommand = objOleComm;
                                   objOleComm.ExecuteNonQuery();
                                   objOleComm.Connection.Close();
                                   index++;
                                   rowIndex = index;
                                   continue;
                              }
                              else
                                   if (RecordType == "02")
                                   {
 
                                        string BookingID = convertLastDigits(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 2]).Value2.ToString());
                                        string LastName = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 3]).Value2.ToString();
                                        string FirstName = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 4]).Value2.ToString();
                                        string Greeting = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 5]).Value2.ToString();
                                        string Sex = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 6]).Value2.ToString();
                                        string CruiseType = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 7]).Value2.ToString();
                                        string Age = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 8]).Value2.ToString();
                                        string DiningRequest = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 9]).Value2.ToString();
                                        string AllInclusive = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 10]).Value2.ToString();
                                        Int32 OnboardCredit = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 11]).Value2.ToString());
 
                                        SQLAdapter = new OleDbDataAdapter();
                                        objOleComm.Connection.Open();
 
                                        //sqlStr = "Insert Into PassengerData(RecordType)Values('2')";
 
 
                                        sqlStr = "Insert Into GroupBookingData"
                                        +       "([RecordType],[BookingID],  [FirstName], [Greeting], [Sex], [Type], [DiningRequest], [AllInclusive], [OnboardCredit])"
                                        + "Values(@RecordType,@BookingID, @FirstName, @Greeting, @Sex, @Type, @DiningRequest, @AllInclusive, @OnboardCredit)";
                                        objOleComm = new OleDbCommand(sqlStr, objOleConn);
                                        objOleComm.Parameters.AddWithValue("@RecordType", Convert.ToInt32(RecordType));
                                        objOleComm.Parameters.AddWithValue("@BookingID", BookingID);
                                        //objOleComm.Parameters.AddWithValue("@LastName", LastName);
                                        objOleComm.Parameters.AddWithValue("@FirstName", FirstName);
                                        objOleComm.Parameters.AddWithValue("@Greeting", Greeting);
                                        objOleComm.Parameters.AddWithValue("@Sex", Sex);
                                        objOleComm.Parameters.AddWithValue("@Type", CruiseType);
                                        objOleComm.Parameters.AddWithValue("@Arrival", Age);
                                        objOleComm.Parameters.AddWithValue("@DiningRequest", DiningRequest);
                                        objOleComm.Parameters.AddWithValue("@AllInclusive", AllInclusive);
                                        objOleComm.Parameters.AddWithValue("@OnboardCredit", OnboardCredit);
                                        SQLAdapter.SelectCommand = objOleComm;
                                        objOleComm.ExecuteNonQuery();
                                        objOleComm.Connection.Close();
                                        index++;
                                        rowIndex = index;
                                        continue;
                                   }
                                   else
                                        if (RecordType == "03")
                                        {
                                             string BookingID = convertLastDigits(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 2]).Value2.ToString());
                                             string Comments = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 3]).Value2.ToString();
 
                                             SQLAdapter = new OleDbDataAdapter();
                                             objOleComm.Connection.Open();
 
                                             //sqlStr = "Insert Into MealsRequestsCreditsData(RecordType)Values('3')";
                                             sqlStr = "Insert Into GroupBookingData"
                                             +       "([RecordType],[BookingID], [Comments])"
                                             + "Values(@RecordType,@BookingID, @Comments)";
                                             objOleComm = new OleDbCommand(sqlStr, objOleConn);
                                             objOleComm.Parameters.AddWithValue("@RecordType", Convert.ToInt32(RecordType));
                                             objOleComm.Parameters.AddWithValue("@BookingID", BookingID);
                                             objOleComm.Parameters.AddWithValue("@Comments", Comments);                                                                         
                                             SQLAdapter.SelectCommand = objOleComm;
                                             objOleComm.ExecuteNonQuery();
                                             objOleComm.Connection.Close();
                                             index++;
                                             rowIndex = index;
                                             continue;
                                        }
 
 
                              //Console.WriteLine("Name : {0},{1} ", RecordType, BookingID);
                              index++;
                              rowIndex = index;
                         }
                         else
                         {
                              flag++;
                              index++;
                              rowIndex = index;
                              continue;
                         }
 
                    }
                    
               }
               catch (Exception ex)
               {
                    // Log the exception and quit...
 
                    MessageBox.Show(ex.StackTrace.ToString());
 
               }
               finally
               {
                    if (objOleConn.State == ConnectionState.Open)                    
                         objOleConn.Close();                    
                    objOleConn.Dispose();
                    objOleComm.Dispose();
                    SQLAdapter.Dispose();                   
                    excel = null;
                    excelWorkBook = null;
                    excelWorkSheet = null;                                      
 
               }
          }
         
 
          private void btnImport_Click(object sender, EventArgs e)
          {
               if (txtFilePath.Text == String.Empty)
               {
                    lblError.Text = "You must enter a file location.";
               }
               else
               {
                    
                    LoadExcelData();
               }
          }
 
          private void btnBrowse_Click(object sender, EventArgs e)         
          {
              OpenFileDialog openFileDialog1 = new OpenFileDialog();              
              openFileDialog1.InitialDirectory = "c:\\" ;
              openFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel 2007 (*.xlsx)|*.xlsx|All files (*.*)|*.*" ;              
              openFileDialog1.FilterIndex = 1 ;
              openFileDialog1.Title = "Find File";
             
              openFileDialog1.RestoreDirectory = true ;
 
              if(openFileDialog1.ShowDialog() == DialogResult.OK)
              {
                  txtFilePath.Text = openFileDialog1.FileName;                  
                  
                   lblError.Text = String.Empty;
              }
          }
 
 
          //DB only wants 9 characters...getting file with 10, so converting last digit to alphabet
          public string convertLastDigits(string x)
          {
               string prefix = x.Substring(0, x.Length-2);
               
               x = x.Trim().Substring(x.Length - 2, 2);
 
               switch (x)
               {
                    case "01": x = "a"; break;
                    case "02": x = "b"; break;
                    case "03": x = "c"; break;
                    case "04": x = "d"; break;
                    case "05": x = "e"; break;
                    case "06": x = "f"; break;
                    case "07": x = "g"; break;
                    case "08": x = "h"; break;
                    case "09": x = "i"; break;
                    case "10": x = "j"; break;
                    case "11": x = "l"; break;
                    case "12": x = "m"; break;
                    case "13": x = "n"; break;
                    case "14": x = "o"; break;
                    default: x = "p"; break;
               }
               return prefix + x;
              
          }
 
          public string recoverLastDigits(string x)
          {
               string prefix = x.Substring(0, x.Length - 1);
               x = x.Trim().Substring(x.Length - 1, 1);
 
               switch (x)
               {
                    case "a": x = "01"; break;
                    case "b": x = "02" ; break;
                    case "c": x = "03" ; break;
                    case "d": x = "04" ; break;
                    case "e": x = "05" ; break;
                    case "f": x = "06" ; break;
                    case "g": x = "07" ; break;
                    case "h": x = "08" ; break;
                    case "i": x = "09" ; break;
                    case "j": x = "10" ; break;
                    case "l": x = "11" ; break;
                    case "m": x = "12" ; break;
                    case "n": x = "13" ; break;
                    case "o": x = "14" ; break;
                    default: x = "15"; break;
 
 
               }
               return prefix + x;
          }
          
 
          //using yes/no field in access database...file comes with Y and N so converting to digits
          public int convertPreBoooked(string x)
          {
               if (x.Trim().ToUpper() == "Y")
                    return -1;
               else
                    return 0;
          }
     }
}

Open in new window

0
 
silemoneCommented:
and yes, there are definite flaws in my code like 1, i should used stored queries (SPROCs for MSSQL) but I don't see them being as powerful as SPROCS...but I will eventually inject them...
0
 
holemaniaAuthor Commented:
Thanks silemone.  I will analyze your code and see if that'll work for me.
0
 
holemaniaAuthor Commented:
After doing some research I found that if all I wanted to do is dump data from Excel into SQL database, it be more efficient to just use SQLBulkCopy to accomplish this.

Below is what I am trying to work with, but I am getting an error at excelstrCon.Open() in regard to ISAM not being installed.  Looking into why at the moment.
 	'Connection string
        Dim excelCon As String
        Dim conString As String = GetConnectionString()
        Dim file As String = "C:\Parts.xls"
        Dim wrkSht As String = "PART"
 
        'Dim excelCon As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; ", _
        '       "Data Source={0};Extended Properties={1};", file, """Excel 8.0;HDR=YES;IMEX=1""")
 
        excelCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=8.0;"
 
        Dim excelstrCon As New OleDb.OleDbConnection(excelCon)
        excelstrCon.Open()
 
        'Create objects and get data
        Dim cmd As New OleDb.OleDbCommand("SELECT [COLUMN2], [COLUMN23] FROM " & wrkSht, excelstrCon)
        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
 
        'open the destination data
        Dim dbCon As SqlConnection = New SqlConnection(conString)
        dbCon.Open()
        Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(dbCon)
 
        bulkCopy.DestinationTableName = "PART_COST"
        bulkCopy.WriteToServer(reader)
        reader.Close()

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.