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 stackoverflow.com/questions/8922426/… — Marco
– Marco, Commented Jan 20, 2012 at 12:26

Marco · Accepted Answer · 2012-01-23 12:22:16Z

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, 2012 at 12:22

answered Jan 20, 2012 at 12:26

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Santhosh_ms3 Over a year ago

System.IndexOutOfRangeException was unhandled Message="Index was outside the bounds of the array."

Marco Over a year ago

@user1157902: this means that line.Split returned less than 4 parts, so your file is not the way you described I think....

Santhosh_ms3 Over a year ago

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.

Marco Over a year ago

@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?

Santhosh_ms3 Over a year ago

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

|

Collectives™ on Stack Overflow

Text File Handling with SQL Database - Visual Basic

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related