Hi All,   I am trying to, first upload and then import excel spreadsheet into my DB.  Most of it works except that when I run the import only the first 20 item codes are imported.  The rest are blank.  It seems to be something to do with the Item Code itself.  The app imports the items code that are similar to 53074, i.e. all start with 53, but once the item codes change to 003A90002, everything there after is ignored. I have included the ability to view the uploaded Excel Document, but even that does not display the offending item codes,  I have checked that the uploaded sheet has got the item codes in and it does.Please help ASAP.Thank you 

1    Imports System.Data
2    Imports System.Data.OleDb
3    Imports System.Data.SqlClient
4    Partial Class Admin_Imports_importItems
5        Inherits System.Web.UI.Page
6    
7        Dim objConn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=fm_suite;Integrated Security=True")
8        Dim transactionImportItems As SqlTransaction
9    
10       Dim strSQLCountItems As String
11       Dim strAction As String
12   
13       Protected Function ExcelConnection() As OleDbCommand
14           Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/ExcelImportItems.xls") & ";Extended Properties='Excel 8.0;hdr=Yes;FMT=CSVDelimited'"
15   
16           Dim objXConn As New OleDbConnection(xConnStr)
17           objXConn.Open()
18   
19           Dim objCommand As New OleDbCommand("SELECT * FROM [Sheet1$]", objXConn)
20           Return objCommand
21   
22       End Function
23   
24       Protected Sub ButtonView_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonView.Click
25           pnl_Upload.Visible = False
26           pnl_View.Visible = True
27           pnl_Import.Visible = False
28   
29           Dim objDataAdapter As New OleDbDataAdapter()
30   
31           objDataAdapter.SelectCommand = ExcelConnection()
32   
33           Dim objDataSet As New DataSet()
34           objDataAdapter.Fill(objDataSet)
35   
36           gv_UploadedData.DataSource = objDataSet.Tables(0).DefaultView
37           gv_UploadedData.DataBind()
38   
39       End Sub
40   
41       Protected Sub ButtonImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonImport.Click
42           Dim strSQLImportItems As String
43           Dim objCommand As New OleDbCommand()
44           Dim reader As OleDbDataReader
45           Dim categoryID As Integer
46           Dim supplierID As Integer
47           Dim PurchaseUOMID As Integer
48           Dim SellingUOMID As Integer
49           Dim StockUOMID As Integer
50           Dim manufacturerID As Integer
51   
52           pnl_Upload.Visible = False
53           pnl_View.Visible = False
54           pnl_Import.Visible = True
55           LabelImport.Text = ""
56   
57           objCommand = ExcelConnection()
58           reader = objCommand.ExecuteReader()
59   
60           While reader.Read()
61               Dim ItemCode As String = Convert.ToString(reader("ItemCode"))
62               Dim ItemName As String = Convert.ToString(reader("ItemName"))
63               Dim ItemDescription As String = Convert.ToString(reader("ItemDescription"))
64               Dim Category As String = Convert.ToString(reader("Category"))
65               Dim Supplier As String = Convert.ToString(reader("Supplier"))
66               Dim Manufacturer As String = Convert.ToString(reader("Manufacturer"))
67               Dim PurchaseUOM As String = Convert.ToString(reader("PurchaseUOM"))
68               Dim SellingUOM As String = Convert.ToString(reader("SellingUOM"))
69               Dim StockUOM As String = Convert.ToString(reader("StockUOM"))
70               Dim MarkUp As Decimal = Convert.ToDecimal(reader("MarkUp"))
71               Dim SellingPrice As Decimal
72               Dim OnHand As Integer
73               Dim Deleted As Integer
74   
75               Try
76                   objConn.Close()
77                   objConn.Open()
78                   transactionImportItems = objConn.BeginTransaction
79   
80                   'Here we are trying to establish if the itme is already in the database or not
81                   strSQLCountItems = ""
82                   strSQLCountItems = "SELECT ItemCode FROM Items WHERE ItemCode = N'" & ItemCode & "'"
83   
84                   Dim CountItemsCommand As New SqlCommand(strSQLCountItems, objConn, transactionImportItems)
85                   Dim CountItemsReader As SqlDataReader
86   
87                   CountItemsReader = CountItemsCommand.ExecuteReader
88                   CountItemsReader.Read()
89   
90                   If (CountItemsReader.HasRows = False) Then
91                       strAction = "Import"
92                   Else
93                       strAction = "Update"
94                   End If
95   
96                   CountItemsReader.Close()
97   
98                   GetCategoryID(Category, categoryID)
99                   GetSupplierID(Supplier, supplierID)
100                  GetManufacturerID(Manufacturer, manufacturerID)
101                  GetUnitOfMeasure(PurchaseUOM, SellingUOM, StockUOM, PurchaseUOMID, SellingUOMID, SellingUOMID)
102  
103                  If strAction = "Import" Then
104                      strSQLImportItems = "INSERT INTO Items "
105                      strSQLImportItems &= "(ItemCode, ItemName, ItemDescription, categoryID, supplierID, manufacturerID,"
106                      strSQLImportItems &= "PurchaseUOM, SellingUOM, StockUOM, LastCostPrice, AverageCostPrice, MarkUp,"
107                      strSQLImportItems &= " SellingPrice, OnHand, Deleted) "
108                      strSQLImportItems &= "VALUES"
109                      strSQLImportItems &= "(N'" & ItemCode & "', "
110                      strSQLImportItems &= "N'" & ItemName & "', "
111                      strSQLImportItems &= "N'" & ItemDescription & "', "
112                      strSQLImportItems &= categoryID & ","
113                      strSQLImportItems &= supplierID & ","
114                      strSQLImportItems &= manufacturerID & ", "
115                      strSQLImportItems &= PurchaseUOMID & ", "
116                      strSQLImportItems &= SellingUOMID & ", "
117                      strSQLImportItems &= StockUOMID & ", "
118                      strSQLImportItems &= 0 & ", "
119                      strSQLImportItems &= 0 & ", "
120                      strSQLImportItems &= MarkUp & ", "
121                      strSQLImportItems &= 0 & ", "
122                      strSQLImportItems &= 0 & ", "
123                      strSQLImportItems &= 0 & ")"
124  
125                      Dim InsertItemCommand As New SqlCommand(strSQLImportItems, objConn, transactionImportItems)
126                      InsertItemCommand.ExecuteNonQuery()
127  
128                      transactionImportItems.Commit()
129  
130                      InsertItemCommand.Dispose()
131  
132                  Else
133                      strSQLImportItems = "UPDATE Items SET "
134                      strSQLImportItems &= "ItemName = N'" & ItemName & "', "
135                      strSQLImportItems &= "categoryID = " & categoryID & ", "
136                      strSQLImportItems &= "supplierID = " & supplierID & ", "
137                      strSQLImportItems &= "manufacturerID = " & manufacturerID & ", "
138                      strSQLImportItems &= "PurchaseUOM = " & PurchaseUOMID & ", "
139                      strSQLImportItems &= "SellingUOM = " & SellingUOMID & ", "
140                      strSQLImportItems &= "StockUOM = " & StockUOMID & ", "
141                      strSQLImportItems &= "MarkUp = " & MarkUp & ", "
142                      strSQLImportItems &= "SellingPrice = " & SellingPrice & ", "
143                      strSQLImportItems &= "OnHand = " & OnHand & ", "
144                      strSQLImportItems &= "Deleted = " & Deleted
145                      strSQLImportItems &= " WHERE ItemCode = N'" & ItemCode & "'"
146  
147                      Dim UpdateItemCommand As New SqlCommand(strSQLImportItems, objConn, transactionImportItems)
148                      UpdateItemCommand.ExecuteNonQuery()
149  
150                      transactionImportItems.Commit()
151  
152                      UpdateItemCommand.Dispose()
153                  End If
154              Catch ex As Exception
155                  Me.LabelImport.Text = strSQLImportItems & " " & ex.Message
156              Finally
157                  transactionImportItems.Dispose()
158              End Try
159          End While
160  
161          reader.Close()
162  
163      End Sub
164  
165      Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonUploadFile.Click
166          If FileUploadExcel.HasFile Then
167              Try
168                  FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImportItems.xls"))
169                  LabelUpload.Text = "Upload File Name: " & _
170                       FileUploadExcel.PostedFile.FileName & "<br>" & _
171                       "Type: " & _
172                       FileUploadExcel.PostedFile.ContentType & _
173                       " File Size: " & _
174                       FileUploadExcel.PostedFile.ContentLength & " kb<br>"
175              Catch ex As Exception
176                  LabelUpload.Text = "Error: " & ex.Message.ToString
177              End Try
178          Else
179              LabelUpload.Text = "Please select a file to upload."
180          End If
181  
182      End Sub
183      Private Sub GetCategoryID(ByVal Category As String, ByRef categoryID As Integer)
184          Dim strSQLCategoryID As String
185  
186          strSQLCategoryID = "SELECT categoryID FROM Categories WHERE Category = N'" & Category & "'"
187  
188          Dim CategoryIDCommand As New SqlCommand(strSQLCategoryID, objConn, transactionImportItems)
189          Dim CategoryIDReader As SqlDataReader
190  
191          CategoryIDReader = CategoryIDCommand.ExecuteReader
192          CategoryIDReader.Read()
193  
194          If (CategoryIDReader.HasRows = True) Then
195              categoryID = CInt(CategoryIDReader("categoryID").ToString)
196          Else
197              categoryID = -1
198          End If
199  
200          CategoryIDReader.Close()
201      End Sub
202  
203      Private Sub GetSupplierID(ByVal Supplier As String, ByRef supplierID As Integer)
204          Dim strSQLSupplierID As String
205          strSQLSupplierID = "SELECT supplierID FROM Suppliers WHERE SupplierName =  N'" & Supplier & "'"
206  
207          Dim SupplierIDCommand As New SqlCommand(strSQLSupplierID, objConn, transactionImportItems)
208          Dim SupplierIDReader As SqlDataReader
209  
210          SupplierIDReader = SupplierIDCommand.ExecuteReader
211          SupplierIDReader.Read()
212  
213          If (SupplierIDReader.HasRows = True) Then
214              supplierID = CInt(SupplierIDReader("supplierID").ToString)
215          Else
216              supplierID = -1
217          End If
218  
219          SupplierIDReader.Close()
220  
221      End Sub
222  
223      Private Sub GetManufacturerID(ByVal Manufacturer As String, ByRef manufacturerID As Integer)
224          Dim strSQLManufacturerID As String
225  
226          strSQLManufacturerID = "SELECT manufacturerID FROM Manufacturers WHERE ManufacturerName = N'" & Manufacturer & "'"
227  
228          Dim ManufactrureIDCommand As New SqlCommand(strSQLManufacturerID, objConn, transactionImportItems)
229          Dim ManufactrureIDReader As SqlDataReader
230  
231          ManufactrureIDReader = ManufactrureIDCommand.ExecuteReader
232          ManufactrureIDReader.Read()
233  
234          If (ManufactrureIDReader.HasRows = True) Then
235              manufacturerID = CInt(ManufactrureIDReader("manufacturerID").ToString)
236          Else
237              manufacturerID = -1
238          End If
239  
240          ManufactrureIDReader.Close()
241  
242      End Sub
243      Private Sub GetUnitOfMeasure(ByVal PurchaseUOM As String, ByVal SellingUOM As String, ByVal StockUOM As String, _
244                                   ByRef PurchaseUOMID As Integer, ByRef SellingUOMID As Integer, ByRef StockUOMID As Integer)
245          Dim strSQlUnitOfMeasurePurchaseUOMID, strSQlUnitOfMeasureSellingUOMID, strSQlUnitOfMeasureStockUOMID As String
246  
247          strSQlUnitOfMeasurePurchaseUOMID = "SELECT UnitOfMeasureID FROM UnitOfMeasure WHERE UnitOfMeasure =N'" & PurchaseUOM & "'"
248          strSQlUnitOfMeasureSellingUOMID = "SELECT UnitOfMeasureID FROM UnitOfMeasure WHERE UnitOfMeasure =N'" & SellingUOM & "'"
249          strSQlUnitOfMeasureStockUOMID = "SELECT UnitOfMeasureID FROM UnitOfMeasure WHERE UnitOfMeasure =N'" & StockUOM & "'"
250  
251          Dim UnitOfMeasurePurchaseUOMIDCommand As New SqlCommand(strSQlUnitOfMeasurePurchaseUOMID, objConn, transactionImportItems)
252          Dim UnitOfMeasureSellingUOMIDCommand As New SqlCommand(strSQlUnitOfMeasureSellingUOMID, objConn, transactionImportItems)
253          Dim UnitOfMeasureStockUOMIDCommand As New SqlCommand(strSQlUnitOfMeasureStockUOMID, objConn, transactionImportItems)
254  
255          Dim UnitOfMeasurePurchaseUOMIDReader As SqlDataReader
256          Dim UnitOfMeasureSellingUOMIDReader As SqlDataReader
257          Dim UnitOfMeasureStockUOMIDReader As SqlDataReader
258  
259          UnitOfMeasurePurchaseUOMIDReader = UnitOfMeasurePurchaseUOMIDCommand.ExecuteReader
260          UnitOfMeasurePurchaseUOMIDReader.Read()
261  
262          If UnitOfMeasurePurchaseUOMIDReader.HasRows = True Then
263              PurchaseUOMID = CInt(UnitOfMeasurePurchaseUOMIDReader("UnitOfMeasureID"))
264          Else
265              PurchaseUOMID = -1
266          End If
267  
268          UnitOfMeasurePurchaseUOMIDReader.Close()
269  
270          UnitOfMeasureSellingUOMIDReader = UnitOfMeasureSellingUOMIDCommand.ExecuteReader
271          UnitOfMeasureSellingUOMIDReader.Read()
272  
273          If UnitOfMeasureSellingUOMIDReader.HasRows = True Then
274              SellingUOMID = CInt(UnitOfMeasureSellingUOMIDReader("UnitOfMeasureID"))
275          Else
276              SellingUOMID = -1
277          End If
278  
279          UnitOfMeasureSellingUOMIDReader.Close()
280  
281          UnitOfMeasureStockUOMIDReader = UnitOfMeasureStockUOMIDCommand.ExecuteReader
282          UnitOfMeasureStockUOMIDReader.Read()
283  
284          If UnitOfMeasureStockUOMIDReader.HasRows = True Then
285              StockUOMID = CInt(UnitOfMeasureStockUOMIDReader("UnitOfMeasureID"))
286          Else
287              StockUOMID = -1
288          End If
289  
290          UnitOfMeasureStockUOMIDReader.Close()
291      End Sub
292  End Class
 
A user needs the administrative password like nitro-glycerine needs a good shake

Hey , go to the link below, you will get the whole code with description
 http://shaikhnizam.blogspot.com/2009/02/how-to-import-ms-excell-data-to-sql.html
If this reply is answer to your question then mark this reply as Answer 


Get more solutions in SQL Server, C#.Net 3.5/2.0/1.1, SharePoint

-Mohammad Nizamuddin 

Thank you for your quick repsonse Nizam, but that answer is not addressing the issue of why is some of the data being igored.

Thanx
A user needs the administrative password like nitro-glycerine needs a good shake