473,503 Members | 12,425 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Updating a CSV file through OleDbConnection in vb.net

1 New Member
I've looked just about everywhere and pieced together as best as I can the code to update the tracking number in a CSV file for an order that has shipped out from our company so we can upload the file to our online shopping cart. I have 1 file with 3 columns that is an export from our shipping program, and the file that needs to be updated that we download from the shopping cart, insert the tracking numbers and re-upload.

File 1:
OrderNumber CustomerName TrackingNumber
280 PRISCILLA JOHNSON 1Z9R6V790355154706
271 SPENCER NEVEU 1Z9R6V790355180991
2208902 PATRICK KAUFMAN 1Z9R6V790355200423
286 DIANA DOETZEL 1Z9R6V790355454758
279 BARRY NUSSBAUM 1Z9R6V790355727294
257 JOHN LACY 1Z9R6V790356178288
270 MEGAN JOSE 1Z9R6V790356316235
202 BENJAMIN MACLENNAN 1Z9R6V790356624652
266 LEONARDO RODRIGUEZ 1Z9R6V790357024183
268 BRENT ROOZEN 1Z9R6V790357179016
287 KIM PIPPINGER 1Z9R6V790357311167
204 ANGELA EPSTEIN 1Z9R6V790357347049

File 2:
Order Number Customer Name Order Date Total Tracking Number Order Status
270 megan jose 7/7/2008 38.74 Shipped
239 WENDI SANDELL 7/6/2008 298.49 Shipped
240 Glenn Shane 7/6/2008 61.82 Shipped
268 Brent Roozen 7/7/2008 88.97 Shipped
269 william rohacik 7/7/2008 33.32 Shipped
242 magnus ekstrand 7/6/2008 227.25 Shipped
245 ALLISON CASTILLO 7/6/2008 104.27 Shipped
249 Kevin Lusinski 7/6/2008 86.06 Shipped
252 SEVAG KASARDJIAN 7/6/2008 77.94 Shipped
254 matthew kilmon 7/6/2008 63.92 Shipped
265 Filipe Azevedo 7/6/2008 83.44 Shipped
255 nabila wassef 7/6/2008 50.9 Shipped
256 Christopher Petro 7/6/2008 111.49 Shipped
257 John Lacy 7/6/2008 130.99 Shipped
258 Brandon Bletzacker 7/6/2008 77.49 Shipped
258 Shamae Stephens 7/6/2008 77.49 Shipped
259 Lisa A Washabaugh 7/6/2008 47.51 Shipped

Code:
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\CustomPrograms2\WebOrders1\output_files\ ;Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim strLine As String = ""
Dim trimChars() As Char = {ControlChars.Quote}
Dim iOrderNum As Integer = 0
Dim sTracking As String = ""
Dim inWebCSV As IO.StreamReader

Dim olecon As OleDb.OleDbConnection
Dim oleadpt As OleDb.OleDbDataAdapter
Dim olecomm As OleDb.OleDbCommand
Dim oleadptcomm As OleDb.OleDbCommand
Dim sqlQry As String = ""
Dim ra As Integer = 0
Dim ds As DataSet

'Try
olecon = New OleDb.OleDbConnection
olecon.ConnectionString = connstring
oleadptcomm = New OleDb.OleDbCommand
oleadptcomm.CommandText = "SELECT * FROM orders.csv"
oleadptcomm.Connection = olecon
ds = New DataSet
oleadpt = New OleDb.OleDbDataAdapter(oleadptcomm)
olecon.Open()
oleadpt.Fill(ds, "orders.csv")

With OpenFileDialog1.ShowDialog()
OpenFileDialog1.InitialDirectory = "c:\kona\data\dataport\ship\"
inWebCSV = New IO.StreamReader(OpenFileDialog1.FileName)
End With

'Read the first line of text from the web csv
'This is the header stuff
strLine = inWebCSV.ReadLine

'Read the first interesting line
strLine = inWebCSV.ReadLine

'Continue to end of the file.
Do While Not strLine Is Nothing
'Split the line into parts using the "," as the delimiter
Dim strArray1() As String = Split(strLine, ",", 3)

iOrderNum = strArray1(0).Trim(trimChars)
sTracking = strArray1(2).Trim(trimChars)
sqlQry = "UPDATE orders.csv SET `Tracking Number` = '" & sTracking & "' WHERE `Order Number` = '" & iOrderNum & "'"
olecomm = New OleDb.OleDbCommand(sqlQry, olecon)
oleadpt = New OleDb.OleDbDataAdapter(olecomm)
oleadpt.UpdateCommand = olecomm
ra = oleadpt.Update(ds, "orders.csv")
MessageBox.Show("Records affected: " & ra)
strLine = inWebCSV.ReadLine
Loop
MessageBox.Show("File Created", "Success", MessageBoxButtons.OK)
'Catch errorVariable As Exception
'MessageBox.Show(errorVariable.ToString(), "Error", MessageBoxButtons.OK)
'Finally
inWebCSV.Close()
olecon.Close()
'End Try
End Sub

I commented out the try-catch part temporarily for easier debugging. I'm always returning 0 rows updated and I would like to know why and how I can fix it.
Jul 10 '08 #1
0 1469

Sign in to post your reply or Sign up for a free account.

Similar topics

1
1684
by: Bryan Masephol | last post by:
Hi All I have a OleDbConnection as the "connection" below. I'm retriving a dataset from an access 2002 db and displaying it in a DataGrid. I'm making the connection to my access db file with...
3
24290
by: | last post by:
I am trying to Delete records from Fox Pro Free Tables (.DBF) using C#. I am able to insert and view data with no problems but when I try to delete - no luck. It appears that I need to run a...
0
3158
by: Andrew Dowding | last post by:
Hi Everybody, I have been looking at problems with my Windows Forms C# application and it's little Jet 4 (Access) database for the last few days. The Windows Forms app implements a facade and...
1
7805
by: Peter Cushing via .NET 247 | last post by:
I've an Excel file that I've been trying to make editable by Webusers via ASP.NET - however I just can't make the update syntaxto work. It's not an ASP problem, I've copied the code into anWindows...
1
3769
by: Harry Devine | last post by:
I have a DataGrid that is configured to use the Edit/Update/Cancel concept correctly. My grid shows values from 5 database fields. I only need to update that last 4 fields. The last field is a...
1
1524
by: jason | last post by:
The guts of the below asp.net vb code was pieced together from another thread - all due credit to it's original author. Thank you! I've modified it to maintain a small local Microsoft 2000...
5
1963
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...
1
2348
by: jonbartlam | last post by:
Hi There I'm not sure what exactly is going wrong here. I'm writing an application that retreives a table from a database (tbl_internalfaults) and updates it. (Actually, just the status column will...
5
2894
by: Bill Schanks | last post by:
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...
0
7098
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
7296
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
7364
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
1
7017
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
7470
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
5604
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
3186
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
0
1524
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
0
405
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

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.