473,382 Members | 1,436 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Updating multiple records from an import file

I have a winform app (VB 2005) that allows users to export data to
excel, make updates to the excel file and import the data from that
Excel file and update the database.

My question is: Is it best to do it this way, calling the update
stored procedure for every update? Or should I be loading this data
into a staging table, and if all goes well do the 'Real' Update. Or
put this into a data adapter and update from that? The application
will never update a huge amout of records, maybe 500 at the most. But
I would think this wouldn't scale when I have 5000 Records.

<<Snip>>
Using connection As New SqlConnection(g_sRCT_Conn)
'Get UserID
Dim uid As New SqlCommand("dbo.spoc_Get_UserID",
connection)
uid.CommandType = CommandType.StoredProcedure

'Open conn
connection.Open()

'Return Results
Dim uidResult As New SqlParameter("@UserID",
SqlDbType.Int)
uidResult.Direction = ParameterDirection.Output
uid.Parameters.Add(uidResult)

'Input parms
Dim sUID As String = g_sCurrUserDomain & "\" & g_sCurrUser
uid.Parameters.Add("@PrefID", SqlDbType.VarChar).Value =
sUID

'Exec and get user id
uid.ExecuteNonQuery()
Dim uidID As Integer = CInt(uidResult.Value)
uid.Dispose()

'Get total records to update
.Range("H2").Select()
.Selection.End(Excel.XlDirection.xlDown).Select()
Dim iTotalRecs As Integer = .ActiveCell.Row - 1
.Range("H2").Select()

'Loop thru RecordIDs and get notes
Dim dtUpdate As Date = Now()
Do While .ActiveCell.Text <""
If .ActiveCell.Offset(0, iNOTES_OFFSET).Text <""
Then
'Update records
Dim u As New SqlCommand
("dbo.spoc_ev_ImportUpdate", connection)
u.CommandType = CommandType.StoredProcedure

'Return Value
Dim uResult As New SqlParameter("@Result",
SqlDbType.Int)
uResult.Direction = ParameterDirection.Output
u.Parameters.Add(uResult)

'Input Parms
u.Parameters.Add("@ItemID", SqlDbType.Int).Value =
CInt(.ActiveCell.Text)
u.Parameters.Add("@UserID", SqlDbType.Int).Value =
uidID
u.Parameters.Add("@UpdateDate",
SqlDbType.DateTime).Value = dtUpdate
u.Parameters.Add("@Notes",
SqlDbType.VarChar).Value = .ActiveCell.Offset(0, iNOTES_OFFSET).Text

'Update
u.ExecuteNonQuery()
If Not CInt(uResult.Value) = 0 Then
MessageBox.Show("Error updating RecordID "
& .ActiveCell.Text, _
g_sApp_Name, MessageBoxButtons.OK,
MessageBoxIcon.Error)
iCount = -1
bError = True
Else
iCount += 1
Me.ssStatus.Text = "Updating ... " & iCount _
& " of " & iTotalRecs & " Records"
Application.DoEvents()
End If
End If
.ActiveCell.Offset(1, 0).Select()
Loop
End Using
<<Snip>>
Nov 19 '08 #1
5 2882
with all due respect - your operation of transferring data to excel from
your VB2005 app for updating and then returning that data back to your
DB (and I assume it is a sql server DB) seems a little redundant.

I would retrieve data from your DB using a sqlDataAdapter and populate a
local table within your app - display that data in a datagridview
control and perform the update(s) in the datagridview control - then
submit the updates back to the server. Or you could submit updates as
they occur in the datagridview.

Example:

'--Form level vars
dim da As New SqlDataAdapter, ds As New Dataset
dim conn as New SqlConnection
conn.ConnectionString = "Data
Source=yourServer;Database=yourDB;Integrated Security=True"
da.SelectCommand = New sqlCommand
da.SelectCommand.Connection = conn

da.Update = New SqlCommand
da.Update.Connection = conn

Private sub SelectData()
da.SelectCommand.CommandText = "Select * from tbl1 where something =
somethingelse"
da.Fill(ds,"localTbl")
datagridview1.datasource = ds.Tables("localtbl")
...
End Sub

Private Sub UpdateData()
da.UpdateCommand.Parameters.Add("@prmID",sqlDBType .Int, 4, ID)
da.UpdateCommand.parameters.Add(...)
...
da.UpdateComnmand.CommandText = "Update tbl1 Set fld1 = @prm1, fld2 =
@prm2,..., Where ID = @prmID"

da.Update(ds, "localtbl")
End Sub

Private Sub datagridview1_RowLeaveEvent(...) Handles...
'--may have to force updates on the local table "localTbl" with code
dim drF() as dataRow = ds.Tables("localTbl").Select("ID = " &
datagridview1.Rows(e.RowIndex).Cells("ID").Value.T oString
For Each row As DataRow in drF
row.BeginEdit
row("fld1") = datagridview1.Rows(e.rowIndex).Cells("fld1").Value
..
Row.EndEdit
Next

UpdateData()
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 19 '08 #2
The app does bring the data up in a data grid view, and can be updated
directly in the application. One of the requirements is the ability to
export to Excel and bring those updates back via this import. I fought
the feature, but I did not win.

Also, I have read that having SQL in the code is a no-no for
production applications. Rather it should all be in stored procedures.
And in our shop it is much better to have all that control in the
procedures as changes to a DB Server are much simpler than re-
deploying the application.

On Nov 19, 4:56*pm, Rich P <rpng...@aol.comwrote:
with all due respect - your operation of transferring data to excel from
your VB2005 app for updating and then returning that data back to your
DB (and I assume it is a sql server DB) seems a little redundant.

I would retrieve data from your DB using a sqlDataAdapter and populate a
local table within your app - display that data in a datagridview
control and perform the update(s) in the datagridview control - then
submit the updates back to the server. *Or you could submit updates as
they occur in the datagridview.

Example:

'--Form level vars
dim da As New SqlDataAdapter, ds As New Dataset
dim conn as New SqlConnection
conn.ConnectionString = "Data
Source=yourServer;Database=yourDB;Integrated Security=True"
da.SelectCommand = New sqlCommand
da.SelectCommand.Connection = conn

da.Update = New SqlCommand
da.Update.Connection = conn

Private sub SelectData()
da.SelectCommand.CommandText = "Select * from tbl1 where something =
somethingelse"
da.Fill(ds,"localTbl")
datagridview1.datasource = ds.Tables("localtbl")
..
End Sub

Private Sub UpdateData()
da.UpdateCommand.Parameters.Add("@prmID",sqlDBType .Int, 4, ID)
da.UpdateCommand.parameters.Add(...)
..
da.UpdateComnmand.CommandText = "Update tbl1 Set fld1 = @prm1, fld2 =
@prm2,..., Where ID = @prmID"

da.Update(ds, "localtbl")
End Sub

Private Sub datagridview1_RowLeaveEvent(...) Handles...
* *'--may have to force updates on the local table "localTbl" with code
* *dim drF() as dataRow = ds.Tables("localTbl").Select("ID = " &
datagridview1.Rows(e.RowIndex).Cells("ID").Value.T oString
* *For Each row As DataRow in drF
* * *row.BeginEdit
* * *row("fld1") = datagridview1.Rows(e.rowIndex).Cells("fld1").Value
* * *..
* * *Row.EndEdit
* *Next

* *UpdateData()
End Sub

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Nov 20 '08 #3
Not completely clear on your effort here. I have several apps which
write data to Excel or read data from Excel. And all of my apps use a
combination of stored procs and inline tsql code (meaning in the app
itself). The inline stuff is just easier to debug - or I have the app
write the tsql based on user input. As for deploying apps - 'Click
Once' is how I deploy all my apps. I can make up to the minute updates
(a user needs something little - turn around time - 1 minute) on an app
and deploy it. The user has the new feature within the minute (I
spoiled the people over at my place).

So - is your problme in writing data to excel from your app or reading
data from excel to your app? Or is it you read the data from excel and
need to push it back to the server? Let me know which of these, and I
am sure I could provide you with a sample how to do it.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 20 '08 #4
The battle over deploying new app versions is frustrating. IT won't
allow self updating (click once or roll your own). They want all
changes to go thru testing and sign-off (I can see there point, but it
is frustrating and can take months).

So I try as much as possible to stay away from inline sql in the app.

And I don't really have a problem, just a question if my way of
importing is as efficient as it can be. I am pushing it to the server
with a stored procedure, but calling that procedure for every record
that is read from the Excel file.

On Nov 20, 12:56*pm, Rich P <rpng...@aol.comwrote:
Not completely clear on your effort here. *I have several apps which
write data to Excel or read data from Excel. *And all of my apps use a
combination of stored procs and inline tsql code (meaning in the app
itself). *The inline stuff is just easier to debug - or I have the app
write the tsql based on user input. *As for deploying apps - 'Click
Once' is how I deploy all my apps. *I can make up to the minute updates
(a user needs something little - turn around time - 1 minute) on an app
and deploy it. *The user has the new feature within the minute (I
spoiled the people over at my place). *

So - is your problme in writing data to excel from your app or reading
data from excel to your app? *Or is it you read the data from excel and
need to push it back to the server? *Let me know which of these, and I
am sure I could provide you with a sample how to do it.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Nov 20 '08 #5
here is a technique I use for reading from Excel using ADO.Net and then
pushing the data to the Server

------------------------------------------------

Private Sub Button2_Click(...) Handles Button2.Click
Dim daOle As OleDbDataAdapter, conn As OleDbConnection
conn = New OleDbConnection

'--connection string to the Excel file

conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\somedir\Test1.xls;Extended Properties=""Excel 8.0;HDR=YES"""

daOle = New OleDbDataAdapter

daOle.SelectCommand = New OleDbCommand
daOle.SelectCommand.Connection = conn
'--put the Excel sheet name in brackets followed by $
daOle.SelectCommand.CommandText = "Select * From [Sheet1$]"
daOle.Fill(ds, "tblExcel")

'--da here is a sqlDataAdapter with a sqlConnection
'--tblEmails is a table on the Server DB
'--get the structure of tblEmails for the local table

Application.DoEvents()
da.SelectCommand.CommandText = "Select * From tblEmails"
da.Fill(ds, "tblEmails_Server")

'--set up the Insert command to push the data retrieved
'--from the Excel file to the Server table - tblEmails

da.InsertCommand.CommandText = "Insert Into tblEmails(RecordID, CoID,
SubscrID, Name) " _
& "Select @RecordID, @CoID, @SubscrID, @Name"
da.InsertCommand.Parameters.Clear()

'--add parameters for the Insert Statement

da.InsertCommand.Parameters.Add("@RecordID", SqlDbType.Int, 4,
"RecordID")
da.InsertCommand.Parameters.Add("@CoID", SqlDbType.VarChar, 50, "CoID")
da.InsertCommand.Parameters.Add("@SubscrID", SqlDbType.VarChar, 50,
"SubscrID")
da.InsertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name")

'--use a dataTableReader for the data push to the server
'--tblEmails_Server is the local app table that will be
'--the vehicle for pushing the data to the server

Dim reader As DataTableReader = ds.Tables("tblExcel").CreateDataReader
ds.Tables("tblEmails_Server").Load(reader, LoadOption.Upsert)

'--this is where the actual data push takes place
da.Update(ds, "tblEmails_Server")

dgrv1.DataSource = ds.Tables("tblExcel")
curMgr = CType(Me.BindingContext(ds.Tables("tblExcel")),
CurrencyManager)
tssL2.Text = (curMgr.Position + 1).ToString
tssL3.Text = curMgr.Count.ToString

End Sub

-------------------------------------------------

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 20 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Rudi Ahlers | last post by:
A different question though. Is it possible to delete duplicate entries, where the email address is the same, and only keep one? i.e.. I got say 4 DB entries, all with the same email address,...
1
by: Roy Adams | last post by:
Hi everyone I'm trying to build a shopping cart app using a db the part I'm stuck on is the fact that, if someone adds a product that they have previously added to the cart. I've got it set up to...
4
by: Indra | last post by:
Hi There, I am looking for information on how to import the txt or csv file to the multiple table in sql 2000. If you have any kind of inf. please let me know wheather we can do this and how. ...
1
by: ccr | last post by:
Please view in a text editor so that the columnar text lines up. I used Terminal 9pt font to compose this post and copied/pasted to my newsreader program. I am writing in the hope that one of...
1
by: ccr | last post by:
Reposted with a longer line length. Apologies if I did not cancel the 1st attempt before you got it. If necessary, please view in a text editor so the columnar text lines up. I used Terminal...
34
by: Jeff | last post by:
For years I have been using VBA extensively for updating data to tables after processing. By this I mean if I had to do some intensive processing that resulted in data in temp tables, I would have...
5
by: JimmyKoolPantz | last post by:
Situation: I am writing a program that opens up a data file (file ext .dbf), imports the information into a dataset. The program also, searches through the dataset for key words ("company...
10
by: chimambo | last post by:
Hi All, I have a little problem. I am retrieving records from a table and I want to update the records using checkboxes. I am able to display the database record quite alright and I have created...
2
tdw
by: tdw | last post by:
Hi all, I have several ideas on how to do this, but am having difficulty putting the ideas together and figuring out the most efficient way to do this. I have a database of survey coordinate...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.