Use Regex?

[tsp1lrk72]

Hi- I'm trying to use a regex to parse a text file and then insert it into a sql db-- when I run this it just keeps adding null records to the db, something is wrong... is there a better way?

 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        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

any advice apppreciated
vb.net 2003-- i posted there too...

 

[ca8msm]

You could do this directly from sql server. Ask for more info in that forum if you want to go down that route.


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

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[jbenson001]

You'll have to trace through your code with the debugger to see where what you are expecting, is failing.

 

[tperri]

Isn't PEEK and POKE what you did to and AppleII?

 

[tsp1lrk72]

I read about the bulk insert, but we wanted to do this because we have to parse the fixed width file, that's why I was using the regex, but I'm having an issue getting this into a SQL table...Ugh!! Any ideas? or is this totally wrong?

Thanks!

 

[jbenson001]

Use the debugger to trace through your code.