Split text file-insert into DB

[tsp1lrk]

Hello--
I'm stuck! I'm trying to parse a text file into an array and then insert it into a SQL table- now It looks like it's inserting only one field into the DB and doesn't do anything else-- something is wrong!!! Not that great with arrays so here is what I tried to use:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'File to string vars
Dim objSRFile As StreamReader
Dim strInput, arrStrInput() As String
Dim intCurrPos As Integer
Dim DocumentDate As String
Dim ISAID As String
Dim SetID As String
Dim ControlNumber As String
Dim GroupControlNumber As String
Dim GroupType As String
Dim InvoiceNbr As String
Dim Comments As String
Dim RecFlag As String
Dim TranslationStatus As Char
'DB vars
Dim strSQL As String
Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim objCmd As SqlCommand

objSRFile = File.OpenText("C:\sundaytest.txt")
While objSRFile.Peek <> -1
strInput = objSRFile.ReadLine
Dim textdelimiter As String
textdelimiter = " "
arrStrInput = Split(strInput, textdelimiter)
For intCurrPos = 0 To arrStrInput.Length - 1
Select Case intCurrPos
Case 0
ISAID = arrStrInput(intCurrPos)
Case 1
InvoiceNbr = arrStrInput(intCurrPos)
Case 2
TranslationStatus = arrStrInput(intCurrPos)
Case 3
GroupControlNumber = arrStrInput(intCurrPos)
Case 4
DocumentDate = arrStrInput(intCurrPos)
Case 5
ControlNumber = arrStrInput(intCurrPos)
End Select
Next
strSQL = "insert into InovisInt_Lisa (ISAID, InvoiceNbr, TranslationStatus, GroupControlNumber, DocumentDate, ControlNumber, Comments, RecFlag) values ('" & ISAID & "', '" & InvoiceNbr & "','" & TranslationStatus & "','" & GroupControlNumber & "','" & DocumentDate & "','" & ControlNumber & "','No Comments','N')"
objCmd = New SqlCommand(strSQL, conn)
conn.Open()
objCmd.ExecuteNonQuery()
conn.Close()
End While
objSRFile.Close()

Any advice appreciated...
Thanks,
Lisa

 

[tsp1lrk]

The text file is not comma delimited, it's either tabbed or spaced (fixed length). I've tried several combinations for the delimiter, but that seems to be causing my issue or the array...

 

[cjard]

Please read this about CODE tags:
http://www.vbdotnetforums.com/misc.php?do=bbcode

Then read the PQ link in my signature about doing proper data access in situations like this..

Incidentally, ReadLine returns Nothing when the end of the stream is reached. This means you can:

Dim line As String = reader.ReadLine()
While line IsNot Nothing
  .. do code

  line = reader.ReadLine()
End While

No need for peek

 

[tsp1lrk]

What about the array?

 

[cjard]

Dimming all your vars at the start of the method is old school.. We now dim inline, close to first use to promote:

No need for dimming varts we never use (return statement)
Variables dimmed near use and within small blocks means we can see what type the var is
.NET is smart enough not to repeatedly Dim a variable that you dim in a loop.. DIm in the loop, the compiler dims out of the loop for you --> better readability, better performance


What about what array? Can I be blunt? That code's a mess and i dont really wanna read it. Nothing in your reply tells me that you actually bothered to read the two advices I posted initially.. help me help you; tidy your post and your SQL up!

 

[tsp1lrk]

I'm sorry that my code is a mess, I'm new and I only came here for some help... thanks.

 

[cjard]

Hmm, yeah.. and you still havent read the first two advices.. or you have, but didnt act on them.. If you dont put any effort in in order to receive free advice, is it reasonable to expect me to put that effort in and then also put effort into giving you the advice? Normally that's the kinda context where you pay for the advice, see..

I'm not annoyed, just trying to teach some basic programming forum etiquette: Dont post code that's an unindented mess when facilities exist to preserve the readability.. I'm sure you'll eventually take some time to go for this notion, the longer the issue remains unresolved..

Welcome to the forums, by the way

 

[tsp1lrk]

fair enough... how about this:

Dim strSQL As String
        Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
        Dim objCmd As SqlCommand
        Dim sr As New System.IO.StreamReader("c:\Sundaytest.txt")
        Dim pattern As String = "^(?<first>.{35})(?<second>.{30})(?<third>.{15})(?<fourth>.{11})(?<fifth>.{35})(?<sixth>.{14})(?<seventh>.+)$"
        Dim re As New System.Text.RegularExpressions.Regex(pattern)
        Dim ma As System.Text.RegularExpressions.Match = re.Match(sr.ReadLine())

        Do While sr.Peek <> -1
            strSQL = "insert into InovisInt_Lisa (ISAID, TPName)values(@ISAID, @TPName)"
            objCmd = New SqlCommand(strSQL, conn)
            objCmd.Parameters.Add("@ISAID", ma.Groups("first").Value.TrimEnd())
            objCmd.Parameters.Add("@TPName", ma.Groups("second").Value.TrimEnd())
            conn.Open()
            objCmd.ExecuteNonQuery()
            conn.Close()
        Loop
        sr.Close()
    End Sub

doesn't insert anything, seems to keep loading the SQL table with nulls... or nothing at all.

 

[cjard]

Wow, named capturing groups in regexes.. that's some serious progress..

As far as I can see, there's a bit of a logical problem in the sense that, you declare a streamreader but you never read from it, so Peek will always be true, so you have an infinite loop
Added to this you are appending 2 new parameters to the SQL every time you loop, which is infinite, which will basically crash your program out very quickly with lack of memory .. AND.. if your sql only contains 2 parameters, then endlessly appending more to the collection (i.e. after you read 1000 lines, your parameters colelction has 2000 parameters in it) ISNT the right way to go about it..

It is, however, nearly there in the sense that you say it is appending nulls to the table.. I guess that's because the regex groups dont contain anything on the first line.. You do actually read ONCE from the reader, i notice.


Now for advices:

Take a look at the System.IO.File.ReadAllLines
It returns you an array, so you can say:

For Each line As String in File.ReadAllLines("c:\sundaytest.txt")
and the loop will run for every line in the file


DONT recreate your sql command in a loop, DONT create parameters in the loop, DONT keep opening and closing the conenction..

Here is some pseudocode. If youre a newbie programmer, you should do this with a pencil and paper or better, WRITE COMMENTS FIRST, then put code to them after.
All the pro programmers do this with hard algorithms because a) you think in english. not vb.net and b) commenting code is cool, and writing the comments first to logic it out means you write code that works and is commented! wow

I cant stress how important it is when youre new, to think in english, write comments in english, then translate the comments to code -> you stay focused and on track and the logic works. You may remember from uni, youre told "when writing an essay, write a plan first; it stops you rambling on and getting lost in the detail" and also a wisdom from french class: "when you start learning french, you think in english, form the english sentence in the order the french would say, then translate the words to french" -> do the same in code! think in the language you know, not the language youre learning!

'make a new sql commmand using some SQL and a connection string
'add the parameters to the parameters collection ONCE, we set the values repeatedly later
'set up the regular expression to match the lines ONCE, and we'll repeatedly ask it to match later

'open the database connection once, use it repeatedly;we'll close it later

'heres the repeated part: for every line in the file
'match the regex to the line
'set the parameter values
'execute the query
'loop round

'close the connection
'dispose of the stuff

Have fun..