[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Write Unbound DGV to DB

Posted on 2013-01-02
5
Medium Priority
?
313 Views
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
UNION ALL SELECT Row2Val1, Row2Val2
UNION ALL SELECT Row3Val1, Row3Val2

I don't need to check for duplicates or any validation, just shove it all into a table.
0
Comment
Question by:Ryan
[X]
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
5 Comments
 
LVL 13

Expert Comment

by:LIONKING
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.
0
 
LVL 13

Author Comment

by:Ryan
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.
0
 
LVL 13

Accepted Solution

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

http://stackoverflow.com/questions/1631054/using-sqldataadapter-to-insert-a-row
0
 
LVL 13

Author Comment

by:Ryan
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
				da.Fill(dt)

				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

					dt.Rows.Add(dr)
				Next

				Dim sqlCB As New SqlCommandBuilder(da)

				da.Update(dt)
			End Using
			cnn.Close()
		End Using

Open in new window

0
 
LVL 13

Expert Comment

by:LIONKING
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.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

649 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