[eworm]

Greetings!

I have an excel worksheet that has 20 columns of data, and thousands of rows. My goal is to take every row, and every column and insert it into a dataset.

Which I have successfully done here:

 'exporting the data from the excel workbook into a dataset
	Public Sub Export_From_Excel()
		Dim objApp As Excel.Application
		Dim objwb As Excel.Workbook
		Dim objws As Excel.Worksheet
		'---
		Dim strRef As String
		Dim x As Integer 'starting row in excel

		'progress window update
		frmProgress.lblStatus.Text = "Summarizing Excel Data..."
		Me.Enabled = False
		frmProgress.Show()
		frmProgress.Refresh()

		ds.Tables.Add("summary")

		With ds.Tables("summary").Columns  'excel column
			.Add("cost")				   'A
			.Add("uom")					'B
			.Add("carton_ct")			  'C
			.Add("sugg_retail")			'D
			.Add("upc")					'E
			.Add("vend_item_no")		   'F
			.Add("vend_no")				'G
			.Add("desc_1")				 'H
			.Add("desc_2")				 'I
			.Add("item_no")				'J
			.Add("self_serve_vendor_no")   'K
			.Add("dept_class")			 'L
			.Add("print_code")			 'M
			.Add("priority_code")		  'N
			.Add("special_packing_notes")  'O
			.Add("min")					'P
			.Add("max")					'Q
			.Add("order_multiple")		 'R
			.Add("po_min")				 'S
			.Add("why_pay")				'T
		End With

		'ignoring row 1 which contains column headers
		'storing first rows value to start loop with
		x = 2
		strRef = "A" & x

		'opening the excel worksheet
		objApp = New Excel.Application
		objwb = objApp.Workbooks.Open(Trim(txtWorksheetPath.Text))
		objws = objwb.Worksheets(1)

		'checking stored value for data
		Do While strRef <> ""

			'adding rows to dataset
			With ds.Tables("summary").Rows
				'adding each column in this row to the dataset from Excel
				.Add(objws.Range("A" & x).Value, objws.Range("B" & x).Value, objws.Range("C" & x).Value, _
					 objws.Range("D" & x).Value, objws.Range("E" & x).Value, objws.Range("F" & x).Value, _
					 objws.Range("G" & x).Value, objws.Range("H" & x).Value, objws.Range("I" & x).Value, _
					 objws.Range("J" & x).Value, objws.Range("K" & x).Value, objws.Range("L" & x).Value, _
					 objws.Range("M" & x).Value, objws.Range("N" & x).Value, objws.Range("O" & x).Value, _
					 objws.Range("P" & x).Value, objws.Range("Q" & x).Value, objws.Range("R" & x).Value, _
					 objws.Range("S" & x).Value, objws.Range("T" & x).Value)
			End With

			'move to next row
			x += 1
			intRecordsFromExcel += 1

			'store next rows value to determine if program should continue
			strRef = "" & objws.Range("A" & x).Value

		Loop

		'setting progress bar max to how many excel rows there were
		frmProgress.ProgressBar1.Maximum = ds.Tables("summary").Rows.Count

		objwb.Close()

Now with this data, I have to send it to 3 different tables. Each of these tables have anywhere from 80-120 fields to update. So what we've done is we had our client set up a DEFAULT record from each table, and this record is used as a template for updating the entire record. We figured that was easier to write it back rather than updating every single column of every single table, when there's only about 5-7 columns that need to be changed on each record.

I am reading in the default items to my dataset here:

 Public Sub Read_Default_Item(ByVal strDefaultItemNo As String, ByVal strDefaultLocation As String)
		Dim cn As SqlConnection
		Dim da As SqlDataAdapter
		Dim strSQL As String
		'---

		'progress window update
		frmProgress.lblStatus.Text = "Reading Default Item Number Fields... " & strDefaultItemNo
		frmProgress.Refresh()

		'*** Item Master ***
		Try
			cn = New SqlConnection(strConnectionString)

			strSQL = "SELECT * " & _
					 "FROM imitmidx_sql " & _
					 "WHERE item_no = '" & strDefaultItemNo & "'"

			'instantiated data adapter and dataset
			da = New SqlDataAdapter(strSQL, cn)
			ds.Tables.Add("default_imitmidx_sql")
			ds.Tables.Add("imitmidx_sql")

			'filling the dataset with a default item
			cn.Open()
			da.Fill(ds.Tables("default_imitmidx_sql"))
			da.Fill(ds.Tables("imitmidx_sql"))
			cn.Close()

			'clears default item from temp table, so it will not be included in import
			ds.Tables("imitmidx_sql").Rows.Clear()

			If ds.Tables("default_imitmidx_sql").Rows.Count < 1 Then
				MsgBox("Unable to locate default item """ & strDefaultItemNo & """ in imitmidx_sql table!", MsgBoxStyle.Information)
				blnErrors = True
			End If

		Catch ex As Exception
			LogError(ex.ToString, Err.Number, "Error reading default item from imitmidx_sql")
		End Try



		'*** Item Location ***
		Try
			cn = New SqlConnection(strConnectionString)

			strSQL = "SELECT * " & _
					 "FROM iminvloc_sql " & _
					 "WHERE item_no = '" & strDefaultItemNo & "' " & _
					 "  AND	 loc = '" & strDefaultLocation & "' "

			'instantiated data adapter and dataset
			da = New SqlDataAdapter(strSQL, cn)
			ds.Tables.Add("default_iminvloc_sql")
			ds.Tables.Add("iminvloc_sql")

			'filling the dataset with a default item
			cn.Open()
			da.Fill(ds.Tables("default_iminvloc_sql"))
			da.Fill(ds.Tables("iminvloc_sql"))
			cn.Close()

			'clears default item from temp table, so it will not be included in import
			ds.Tables("iminvloc_sql").Rows.Clear()

			If ds.Tables("default_iminvloc_sql").Rows.Count < 1 Then
				MsgBox("Unable to locate default item """ & strDefaultItemNo & _
					   """ at location """ & strDefaultLocation & """ in iminvloc_sql table!", MsgBoxStyle.Information)
				blnErrors = True
			End If

		Catch ex As Exception
			LogError(ex.ToString, Err.Number, "Error reading default item from iminvloc_sql")
		End Try


		'*** Vendor Item Master ***
		Try
			cn = New SqlConnection(strConnectionString)

			strSQL = "SELECT * " & _
					 "FROM poitmvnd_sql " & _
					 "WHERE item_no = '" & strDefaultItemNo & "' "

			'instantiated data adapter and dataset
			da = New SqlDataAdapter(strSQL, cn)
			ds.Tables.Add("default_poitmvnd_sql")
			ds.Tables.Add("poitmvnd_sql")

			'filling the dataset with a default item
			cn.Open()
			da.Fill(ds.Tables("default_poitmvnd_sql"))
			da.Fill(ds.Tables("poitmvnd_sql"))
			cn.Close()

			'clears default item from temp table, so it will not be included in import
			ds.Tables("poitmvnd_sql").Rows.Clear()

			If ds.Tables("default_poitmvnd_sql").Rows.Count < 1 Then
				MsgBox("Unable to locate default item """ & strDefaultItemNo & """ in poitmvnd_sql table!", MsgBoxStyle.Information)
				blnErrors = True
			End If

		Catch ex As Exception
			LogError(ex.ToString, Err.Number, "Error reading default item from poitmvnd_sql")
		End Try


	End Sub

Now this is where I take my ds.Tables("summary") from Excel and start creating my data in 3 new tables of my dataset.

 Public Sub Create_Records_In_Dataset()
		Dim cn As SqlConnection
		'---
		Dim dsRow As DataRow
		Dim dgRow As DataGridViewRow
		'---
		Dim x As Integer
		Dim y As Integer


		Try
			cn = New SqlConnection(strConnectionString)

			'looping through summary dataset table - inserting records into temporary imitmidx_sql, iminvloc_sql, and poitmvnd_sql tables
			For Each dsRow In ds.Tables("summary").Rows


				'***** creating imitmidx_sql record *****
				With ds.Tables("imitmidx_sql")

					'imports all columns from default item master
					.ImportRow(ds.Tables("default_imitmidx_sql").Rows(0))

					.Rows(x).Item("item_no") = dsRow("item_no").ToString

					.Rows(x).Item("price_uom") = dsRow("uom").ToString
					.Rows(x).Item("mfg_to_inv_ratio") = dsRow("carton_ct").ToString
					.Rows(x).Item("search_desc") = dsRow("desc_1").ToString
					.Rows(x).Item("item_desc_1") = dsRow("desc_1").ToString
					.Rows(x).Item("item_desc_2") = dsRow("desc_2").ToString
					.Rows(x).Item("user_def_fld_1") = dsRow("self_serve_vendor_no").ToString
					.Rows(x).Item("prod_cat") = dsRow("dept_class").ToString
					.Rows(x).Item("user_def_fld_2") = dsRow("print_code").ToString
					'.Rows(x).Item("ssl_ud3") = dsRow("priority_code").ToString
					.Rows(x).Item("note_5") = dsRow("special_packing_notes").ToString

				End With

				'***** creating poitmvnd_sql record *****
				With ds.Tables("poitmvnd_sql")

					'imports all columns from default vendor item master
					.ImportRow(ds.Tables("default_poitmvnd_sql").Rows(0))

					.Rows(x).Item("item_no") = dsRow("item_no").ToString

					.Rows(x).Item("vend_no") = dsRow("vend_no").ToString
					.Rows(x).Item("vend_item_no") = dsRow("vend_item_no").ToString

				End With


				'***** creating iminvloc_sql record for location if it's checked *****
				For Each dgRow In dgLocations.Rows
					If dgRow.Cells(2).Value = True Then

						With ds.Tables("iminvloc_sql")
							'imports all columns from default item location
							.ImportRow(ds.Tables("default_iminvloc_sql").Rows(0))

							.Rows(y).Item("item_no") = dsRow("item_no").ToString
							.Rows(y).Item("loc") = dgRow.Cells(0).Value.ToString

							.Rows(y).Item("avg_cost") = dsRow("cost")
							.Rows(y).Item("last_cost") = dsRow("cost")
							.Rows(y).Item("std_cost") = dsRow("cost")

							.Rows(y).Item("price") = dsRow("sugg_retail")
							.Rows(y).Item("reorder_lvl") = dsRow("min")
							.Rows(y).Item("ord_up_to_lvl") = dsRow("max")
							'.Rows(y).Item("mfg_to_inv_ratio") = dsRow("order_multiple")
							.Rows(y).Item("po_min") = dsRow("po_min")
							.Rows(y).Item("user_def_fld_5") = dsRow("why_pay")

						End With

						'record increment for item location
						y += 1
						intItemLocationRecords += 1
					End If



				Next

				'progress window update
				frmProgress.lblStatus.Text = "Importing Item " & dsRow.Item(0).ToString
				frmProgress.ProgressBar1.Value += 1
				frmProgress.Refresh()

				'record increment for item master and vendor item master
				x += 1
				intItemMasterRecords += 1
				intVendorItemRecords += 1
			Next

			'progress window update
			frmProgress.lblStatus.Text = "Process Complete"
			frmProgress.Refresh()

			frmProgress.ProgressBar1.Value = 0
			frmProgress.lblStatus.Text = ""
			Me.Enabled = True
			frmProgress.Close()


		Catch ex As Exception
			LogError(ex.ToString, Err.Number, "Error Importing")
		End Try

	End Sub

So in Summary:

I have:
ds.Tables("summary") 'from Excel
ds.Tables("default_imitmidx_sql") '1 record which stores default item
ds.Tables("default_iminvloc_sql") '1 record which stores default item
ds.Tables("default_poitmvnd_sql") '1 record which stores default item
ds.Tables("imitmidx_sql") 'thousands of records from excel which I want to put to database
ds.Tables("iminvloc_sql") 'thousands of records from excel which I want to put to database
ds.Tables("poitmvnd_sql") 'thousands of records from excel which I want to put to database

As you can imagine, there already exists thousands of records in the database for those bottom 3 tables. I want to INSERT my brand new records into that same database without modifying any of the existing data.

I've googled many dataadapter solutions, but none of them seem to be working for me. Because they all seem to want to delete all of my existing data and not just insert my new data.

I've Tried:

Dim cn As SqlConnection
Dim da As SqlDataAdapter

cn = New SqlConnection(strConnectionString)
da = New SqlDataAdapter("SELECT * FROM imitmidx_sql", cn)

da.Update(ds.Tables("imitmidx_sql"))

And I received an error that said "0 Rows Updated where there were expected to be 1"..
And after looking at my database I could see that the only thing that happened..was that statement deleted my default record that was set up.

Any suggestions? Thanks!

This post has been edited by eworm: 09 December 2009 - 03:07 PM

[FlashM]

First of all I don't think it's a very good idea to load thousands of rows into a dataset because this data is then stored in your computer memory so you could quite easily get your memory filled. Is it really necessary to load everything?

This post has been edited by FlashM: 09 December 2009 - 05:41 PM

[eworm]

FlashM, on 9 Dec, 2009 - 04:40 PM, said:

First of all I don't think it's a very good idea to load thousands of rows into a dataset because this data is then stored in your computer memory so you could quite easily get your memory filled. Is it really necessary to load everything?

Is that any different than having thousands of rows in an Excel Worksheet? And also if it helps any, I don't mean thousands as in 900,000 rows, it's probably more like 2,000-3,000 rows.