Howdy, Stranger!

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

Categories

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.