hi i doin a project using Vb and SQL server .
for the testing purpose i use access as database with VB
i use vb to read the txt file and save into database.
i able to do the program.but now the problem is whn i convert the access to sql server i got problem to save all the information.
i only able to save the last line.while the other line do not save into sql server database.

here the code

the main form

Option Explicit

Private Sub cmdStart_Click()
Dim InFile As Integer
Dim InLine, InLine2 As String
Dim Data() As String
Dim Fields As Integer
Dim i, n As Integer
Dim strdate As String

'Database variables
Dim db As Database
Dim SQLInsertStatement As String
n = 1
InFile = FreeFile
ConnectDB
strdate = Now()

lbl1.Caption = strdate
Open App.Path & "\EOW-3.txt" For Input As #InFile

'Open the database
'Set db = DBEngine.OpenDatabase(App.Path & "\db1.mdb")
'Start the real data processing

While Not EOF(InFile)
Line Input #InFile, InLine
If n > 5 Then
'RemSpaceComma (InLine)
'Data() = Split(InLine)
InLine2 = RemSpaceComma(InLine)
Data() = Split(InLine2)
Fields = UBound(Data())

SQLInsertStatement = "INSERT INTO shift(Split_Skill,ACD_Calls,Avg_Speed_Ans,Avg_ACD_Time,Avg_ACW_Time,Aban_Calls,Abancalls,A vg_Aban_Time,Extn_Out_Calls,Avg_Extn_Out_Time,Avg_Pos_Staff,Avg_ACD_Per_Hour,Max_ACD_Per_H our,datetime1) " & _
"VALUES ('" & Data(0) & "', " & _
"'" & Data(1) & "', " & _
"'" & Data(2) & "', " & _
"'" & Data(3) & "', " & _
"'" & Data(4) & "', " & _
"'" & Data(5) & "', " & _
"'" & Data(6) & "', " & _
"'" & Data(7) & "', " & _
"'" & Data(8) & "', " & _
"'" & Data(9) & "', " & _
"'" & Data(10) & "', " & _
"'" & Data(11) & "', " & _
"'" & Data(12) & "', " & _
"' " & lbl1.Caption & "') "
End If
n = n + 1
Wend

Close #InFile

cn.Execute SQLInsertStatement

CloseDB
End Sub

the module code(remove the comma)

Public Function RemSpaceComma(ByVal TextLine As String) As String

Lenoftext = Len(TextLine) + 1
n = 1
While n <> Lenoftext
'Loop while the n counter is
'not eq with the length of whole line
SingleChar = Mid(TextLine, n, 1)
If SingleChar <> Chr(44) And SingleChar <> Chr(32) Then
'if a single comma was found ignore it
'and put replace a space in between each character
CursorLine = CursorLine & SingleChar
ElseIf SingleChar = Chr(44) Then
'chr 44 = comma sign
CursorLine = CursorLine & Chr(32)

End If
n = n + 1
Wend
RemSpaceComma = CursorLine
End Function

the module(connect sql server)

Public cn As Object
Public cs As String

Public Sub ConnectDB()
Set cn = CreateObject("ADODB.Connection")

cs = "Provider=sqloledb;Data Source=VSIVELIG-DESK;User Id=cms;Password=cms;Initial Catalog=test"
cn.Open cs
End Sub

Public Sub CloseDB()
cn.Close
End Sub

Total,374,9.93850231171,393.053466797,306.794128418,33,8.1,45.424243927,528,123.06628418,4 .1,2.8,0
English,285,10.40350914,391.277191162,369.466674805,14,4.7,71.3571395874,191,162.931930542 ,4.6,4.3,14.0
Mandarin,89,8.44943809509,398.741577148,106.101119995,19,17.6,26.3157901764,337,100.471809 387,3.7,1.3,8.0

i only able to save mandarin line.while total and english data is not update into database.
i really don't no.plz help me bz i still a student. :(