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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |