Splitting strings in ASP.NET and SQL insert troubles

Associate
Joined
18 Oct 2002
Posts
972
Location
Derby
Again like my other post this is probably very simple as im not exactly a genius when it comes to coding. Here is the scenario:

I have a text file with a name and number for a series of students. The file looks like this:

Code:
0756363,Paul Smith,046252,Dave Smith

I want to insert this file into a database so it looks like the following

Student Number Student Name

0756363 Paul Smith
046252 Dave Smith

I used the string split function to split the string every time it sees a comma. This works fine.

What I need to do is loop through the array of split strings and insert the number and the name into my table until the file is read. I figured this would be pretty simple. However this is the closest I have got:

Student Number Student Name

0756363 0756363
Paul Smith Paul Smith
046252 046252
Dave Smith Dave Smith

If I use one variable such as the student number i can get all the student numbers in the file into their proper table but as soon as I try the name as well it all kicks off. I know this will be a simple error but if anyone can give me an idea i'd be most pleased. The method i've wrote to do this is below:

Code:
Sub readText(s As Object, E As Eventargs)
         objConn.Open()

         objStreamReader = File.OpenText("C:\Temp\names.txt")

         Dim strInput as String
         strInput = objStreamReader.ReadToEnd()

         Dim textDelimiter As String
         textDelimiter = ","
         Dim splitout = Split(strInput, textdelimiter)
         lblplaintext.text = strInput
         Dim i As Integer
         for i=0 to Ubound(splitout)

         'For testing purposes
         lblsplittext.Text &= "<b>Split </b>" & i+1 & ") " & splitout(i)& ""

         objCmd = New OleDbCommand("INSERT INTO NAME (studentNumber, name) VALUES (@studentNumber, @name)", objConn)
         objCmd.Parameters.Add("@studentNumber", splitout(i))
         objCmd.Parameters.Add("@Name", splitout(i))
         objCmd.ExecuteNonQuery()
         Next
         objStreamReader.Close()
         objConn.Close()
         End Sub

Thanks for your time
 
Each time you loop through the i variable, it is only getting incremented by 1. It needs to be incremented by 2 each time, so that it skips the student's name from getting worked upon in the next loop cycle.

Also, when you add the name sql parameter it is writing the student's number, not the name which should be the i+1 array item.

Hope this helps!
 
Back
Top Bottom