How can we import a txt file into access database

Hi,
I am getting an error while importing a txt file into access database.Iam getting error as "Too few parameters ,Expected 1."

Here i have splitted the txt file into various fields and they r stored in variables like f1,f2...etc.

Can anyone ,Please Help Me.
Bye,
satya.


Dim filename As String
filename = Me.TextBox1.Text.Substring(3, Me.TextBox1.TextLength - 7)
Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim strSQL 'As String
Const DB_NAME = "C: asktracker_destdatabase asktracker_dest.mdb"
' Const DB_NAME = "C:Tasktracker_destdatabase asktracker_dest.mdb"
Const TBL_NAME = "tasks"
Dim DATA_SOURCE = "[Text;HDR=Yes;Database=C:]." & filename & "#txt"
Dim sr As StreamReader = New StreamReader("c:" & filename & ".txt")

oJet = CreateObject("DAO.DBEngine.36")
oDB = oJet.OpenDatabase(DB_NAME)
Dim line As String = ""
Dim finished As Boolean = False
Dim col As New Collection
Dim i As Integer, count As Integer
count = 0

While Not finished
line = sr.ReadLine()

If line Is Nothing Then
finished = True
Else
Dim strDelim As String = ","
Dim delimiter As Char() = strDelim.ToCharArray
Dim split As String() = Nothing
Dim fieldCount As Integer
Dim s As String
split = line.Split(delimiter)
fieldCount = 0
Dim f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15, f16, f17, f18 As String

f1 = split(0).Trim("""")
f2 = split(1).Trim("""")
f3 = split(2).Trim("""")
f4 = split(3).Trim("""")
f5 = split(4).Trim("""")
f6 = split(5).Trim("""")
f7 = split(6).Trim("""")
f8 = split(7).Trim("""")
f9 = split(8).Trim("""")
f10 = split(9).Trim("""")
f11 = split(10).Trim("""")
f12 = split(11).Trim("""")
f13 = split(12).Trim("""")
f14 = split(13).Trim("""")
f15 = split(14).Trim("""")
f16 = split(15).Trim("""")
f17 = split(16).Trim("""")
f18 = split(17).Trim("""")

sql = "select count(*) from project where projectid='" & f12 & "';"
cmd.CommandText = sql
Dim ct As Integer = cmd.ExecuteScalar

If ct = 0 Then
strSQL = "INSERT INTO project(trackerid,projectid,projectname,projecttype,startdate,act_enddt,exp_enddt,project_status) values(" & f11 & " ,'" & f12 & "','" & f13 & "','" & f14 & "','" & f15 & "','" & f16 & "' ,'" & f17 & "','" & f18 & "');"
Else
strSQL="Update project set projectname='" & f13 & "',startdate='" & f15 & "',act_ebddt='" & f16 & "' where projectid='" & f12 & "'
End If
oDB.Execute(strSQL)

sql = "select * from tasks where taskid='" & f1 & "'"
cmd.CommandText = sql
dr = cmd.ExecuteReader
If dr.Read = True Then
strSQL = "update " & TBL_NAME & " set task_name='" & f2 & "',projectid='" & f12 & "', projectname='" & f13 & "',catageoryid='" & f3 & "',project_type='" & f4 & "',startdate='" & f5 & "',duedate='" & f6 & "',prerequisites='" & f7 & "',comments='" & f8 & "' where taskid='" & f1 & "'"

Else
strSQL = "INSERT INTO " & TBL_NAME & " (taskid, task_name,projectid,projectname,catageoryid,project_type,startdate,duedate,prerequisites,comments,percentageofcompletion) values('" & f1 & "' ,'" & f2 & "' ,'" & f12 & "','" & f13 & "','" & f3 & "','" & f4 & "','" & f5 & "','" & f6 & "','" & f7 & "' ,'" & f8 & "'," & f9 & ");"

End If

oDB.Execute(strSQL)
dr.Close()

dr.Close()

End If
End While
oDB.close()
MsgBox("The text file was imported into the database successfully", MsgBoxStyle.Information, "Task Tracker")
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories