Write Unbound DGV to DB

Posted on 2013-01-02
Medium Priority
Last Modified: 2013-01-02
I have code calculating and displaying results to a DataGridView which I'd like to save to database. I'm finding solutions to update results that were populated via a DataAdapter, but this data wasn't queried.

I may have as many as 1000 rows, so I'd rather not Insert line by line (VPN connection can be terrible).  The only bulk method I know for SQL Server 2005 is to  
INSERT INTO MyTable (Field1, Field2)
SELECT Row1Val1, Row1Val2

I don't need to check for duplicates or any validation, just shove it all into a table.
Question by:Ryan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 13

Expert Comment

ID: 38737009
Since you're in SQL 2k5 you can't use table parameters (yet), so I think you're right.
Probably your best bet would be to dynamically create a string with your insert query.

First create the INSERT part, something like:

string strQuery = "INSERT INTO myTable(Field1, ... , FieldN)

Then loop through all the rows of your dgv and append to that string each and every one of your inserts.

Hope it helps.
LVL 13

Author Comment

ID: 38737052
I was hoping there was some way to utilize the DataAdapter.

From the lack of response, seems that StringBuilder probably is the best solution.
LVL 13

Accepted Solution

LIONKING earned 2000 total points
ID: 38737208
Check this out... It might give you another option:

LVL 13

Author Comment

ID: 38737417
That worked with some modifications. I'm hoping it generated a bulk insert command, I don't know how to test for that.  Here's the final code.

		Dim ProjectMain, ProjectSub As String
		ProjectMain = InputBox("Enter Main Project Name", "Main Project")
		If ProjectMain = "" Then Exit Sub
		ProjectSub = InputBox("Enter Sub Project Name", "Sub Project")
		If ProjectSub = "" Then Exit Sub

		Dim dr As DataRow
		Dim cnn As SqlConnection = Globals.cnnFACTS
		Using da As New SqlDataAdapter("SELECT * FROM tblTempUsed WHERE ID = -1", cnn)
			Using dt As New DataTable

				For Each r As DataGridViewRow In DataGridView1.Rows
					dr = dt.NewRow()
					With dr
						.Item("ProjectMain") = ProjectMain
						.Item("ProjectSub") = ProjectSub
						.Item("DiskNo") = r.Cells("DiskNo").Value
						.Item("BatchNo") = r.Cells("BatchNo").Value
					End With


				Dim sqlCB As New SqlCommandBuilder(da)

			End Using
		End Using

Open in new window

LVL 13

Expert Comment

ID: 38737504
Don't quote me on this, but I don't think it does a bulk insert.
What the data adapter does, is that it executes the InsertCommand that you specify for each row that is marked for insertion.

Glad it helped.

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question