Insert SQL Database from datagridview

[clayton74]

Hi Everybody
Hope someone can help me, I am new to visual basic and need a bit of help.
I have imported some data from an excel sheet into a datagrid, what I am struggling to do is to pass this infomation into an sql database, I already have the table created called ImportInfo and the database is called mydb. The datagridview is called DataGridView1
Any help would be gratefully received

Thanks

 

[Golom]

A datagrid does not actually contain any data. It is simply a window through which you view the data in its recordsource.

If you want to copy an Excel spreadsheet to an Access (.mdb) table then you need to

- Link the Excel spreadsheet into the mdb

- Run some SQL (specifically an INSERT INTO statement) to copy the Excel table into the Access one.

- Drop the linked Excel table.

I have assumed that you are using an Access database. If it is something else then the process is conceptually the same although the individual steps may be somewhat more elaborate. Access and Excel, being MS products, are reasonably well integrated. That may not be the case with Excel and another DBMS.

 

[ousoonerjoe]

If using an SQL database:

Create an Insert stored procedure.

Create a For Next loop to iterate through each row.

CheckNum = 0
For CheckNum = 0 To (dgDetail.Rows.Count - 1) Step 1
	If dgDetail.Rows(CheckNum).Tag = True Then
		Pop.ResetCmd()
		Pop.ResetParams()
		ApDetailId = NotNull(dgDetail.Item("ApDetailId", CheckNum).Value, 0)
		If ApDetailId = 0 Then
			Pop.CommandString = "cst_Insert_ApTransDetail"
			Pop.AddParam("@ApTransId", ApTransId)
		Else
			Pop.CommandString = "cst_Update_ApTransDetail"
			Pop.AddParam("@ApTranDetailId", ApDetailId)
		End If
		Account = NotNull(dgDetail.Item("Account", CheckNum).Value)
		InvAmt = NotNull(dgDetail.Item("Amount", CheckNum).Value, 0.0)
		Desc = NotNull(dgDetail.Item("Description", CheckNum).Value)
		Pop.AddParam("@Company", Company)
		Pop.AddParam("@Account", Account)
		Pop.AddParam("@Amount", InvAmt)
		Pop.AddParam("@Desc", Desc)
		AupDt = Pop.OpenDataTable
		If AupDt Is Nothing Or Pop.Errs.Number <> 0 Then
			If Pop.Errs.Number <> 0 Then
				ErrorLog(Pop.Errs.Number, Pop.Errs.Description, Me.Name, Pop.Errs.Source, Pop.Errs.Line)
				StatBarEllipse(txtMsg, "Error: " & Pop.Errs.Description, True)
			Else
				StatBarEllipse(txtMsg, "Error: " & Err.Description, True)
			End If
			StatBarEllipse(txtMsg, "Error (Parameters): " & NotNull(AupDt.Rows(0).Item("ErrorMessage") & " NOTE: Your Invoice Details were not saved."), True)
			ErrorLog(NotNull(AupDt.Rows(0).Item("ErrorNumber"), -1), NotNull(AupDt.Rows(0).Item("ErrorMessage")), Me.Name, System.Reflection.MethodBase.GetCurrentMethod.Name, NotNull(AupDt.Rows(0).Item("ErrorLine")))
			dgDetail.Rows(CheckNum).Selected = True
		Else
			If NotNull(AupDt.Rows(0).Item("ErrorNumber"), 0) = 0 Then
				StatBarEllipse(txtMsg, "Line " & (CheckNum + 1) & " has been successfully committed to the system for Invoice Number (" & InvNum & ").", False)
			Else
				StatBarEllipse(txtMsg, "Error (Parameters): " & NotNull(AupDt.Rows(0).Item("ErrorMessage") & " NOTE: Your Invoice Details were not saved."), True)
				ErrorLog(NotNull(AupDt.Rows(0).Item("ErrorNumber"), -1), NotNull(AupDt.Rows(0).Item("ErrorMessage")), Me.Name, System.Reflection.MethodBase.GetCurrentMethod.Name, NotNull(AupDt.Rows(0).Item("ErrorLine")))
				dgDetail.Rows(CheckNum).Selected = True
				Exit For
			End If
		End If
	End If
Next CheckNum

It's not the prettiest way to get there, but gives a good idea about how you can proceed. (Note: Pop is a data class that wraps our connections. We just pass the stored procedure name and Parameters to it and check the return set).

Remember-- If you're porting to SQL Server, there is an Import Wizard for Spreadsheets to database. You can always port the excel file straight in and then from there, manipulate the data as needed.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------