Text File Handling with SQL Database - Visual Basic

Question

I have a text file contains delimited records.

1243;jhhf';982u4k;9u2349;huf8
kij;9238u;98ur23;jfwf;03i24

I need to replace the value of 4th part of every record with the value returned from SQL database (Select X from T where C='4Th part from the flatfile').

Regards,
SAnthosh.

Note that part of my answer is taken from my previous answer on OP post http://stackoverflow.com/questions/8922426/text-file-handling-in-visual-basic/8922498#8922498 — Marco, Jan 20 '12 at 12:26

Marco · Accepted Answer · 2012-01-23T12:22:16.880

1

Try this:

Dim newLines As List(Of String) = New List(Of String)
Dim sqlConn As New SqlConnection(connectionString)
Dim SQLCmd As New SqlCommand()
SQLCmd.Connection = sqlConn
Dim lines As String() = File.ReadAllLines(filename)
sqlConn.Open()
For Each line As String In lines
    Dim parts As String() = line.Split(";")
    SQLCmd.CommandText = "Select X from T where C=""" & parts(3) & """"
    Dim dr As SqlDataReader = SQLCmd.ExecuteReader
    While dr.Read()
        parts(3) = dr("X")
    End While
    newLines.Add(String.Join(";", parts))
Next
File.WriteAllLines(filename, newLines.ToArray())
sqlConn.Close()

edited Jan 23 '12 at 12:22

answered Jan 20 '12 at 12:26

Marco

56,740
14
129
152

System.IndexOutOfRangeException was unhandled Message="Index was outside the bounds of the array." – Santhosh_ms3 Jan 23 '12 at 10:53
@user1157902: this means that `line.Split` returned less than 4 parts, so your file is not the way you described I think.... – Marco Jan 23 '12 at 10:54
A;B;C;160;D /n X;Y;Z;161;W /n P;Q;R;162;S/n A;B;C;163;D/n (4 lines ) This is the content. i'm just testing before implementing into the actual. – Santhosh_ms3 Jan 23 '12 at 11:06
@Santhosh_ms3: I tried my code and it works for me; I edited it because datareader name was wrong, but I can't reproduce your error. Are you sure about "/n"? Is that a newline? – Marco Jan 23 '12 at 12:25
yes its(\n) a newline. Actually i got an error {"There is already an open DataReader associated with this Command which must be closed first."} so closd the Sqldr as SQLdr.close() . after that geting the exception "Index was outside the bounds of the array – Santhosh_ms3 Jan 23 '12 at 13:05
@Santhosh_ms3: hey, you have to debug your app!! Put a breakpoint on `SQLCmd.CommandText = ...` line and check (for every line in your file) that parts.Length is really five as it should. You can do lot of things to find the problem... it is called debugging! :) – Marco Jan 23 '12 at 13:12
THanks marco. THat was the issue. THere were spaces in 5th and 6th line. so added a condition if If parts.Length > 1. Can you suggest any books for rookies ? – Santhosh_ms3 Jan 23 '12 at 13:43
@Santhosh_ms3: sorry, I've started coding almost 30 years ago, so I'm not familiar with books for newbies sold today. I saw you stripped "accepted" away from my answer: is there a particular reason? – Marco Jan 23 '12 at 14:08
@Santhosh_ms3: no, you did not accept neither this, nor the other. Are you sure you did? See [http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Marco Jan 23 '12 at 14:40

Text File Handling with SQL Database - Visual Basic

1 Answers1

Linked