Import data from Text file to Msaccess Database

[vks.gautam1]

Here im import data from Text file to Msaccess Database.
Data structure in Notepad is

22-Jan-09|919876165328|9878216613|

im storing this data in string & then split that & store to a array variable.
if i give cmd parameter
" .Add("@Date", OleDbType.date).Value = data(0)"
then this give error " Cannot convert string to date." so i do this
".Add("@Date", OleDbType.varchar).Value = data(0)"

Is there any better way to do this. Or this is ok

Dim file_name As String = "E:\hello.txt"
            Dim txtline, q, data(2) As String
            Dim cn As OleDbConnection
            Dim cmd As OleDbCommand
            If System.IO.File.Exists(file_name) = True Then
                Dim objreader As New System.IO.StreamReader(file_name)
                Do While objreader.Peek <> -1
' Storing line to string variable
                    txtline = objreader.ReadLine() & vbNewLine
'breaking the line & stroing to array variable
                    data = txtline.Split("|")
                    q = "insert into TAB1 values(@Date,@SNo,@MobNo)"
                    cn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=E:\dat.mdb;")
                    cn.Open()

                    cmd = New OleDbCommand(q, cn)
                    cmd.Connection = cn
                    With cmd.Parameters
                        .Add("@Date", OleDbType.VarChar).Value = data(0)
                        .Add("@SNo", OleDbType.VarChar).Value = data(1)
                        .Add("@MobNo", OleDbType.VarChar).Value = data(2)
                    End With
                    cmd.ExecuteNonQuery()
                Loop
            End If
           
            MessageBox.Show("Data Saved")

 

[jmcilhinney]

Imports System.IO

i used it. Now it is giving error on

cmd.Parameters("@date").Value = DateTime.ParseExact(spl(0), "dd-mmm-yy", CultureInfo.CurrentCulture)

Culturinfo = Name 'Culturinfo' is not declared.
Now wt should i import ?

There's never a need to ask a question like this. Just open your MSDN Library and use the index to find the class in question. The overview topic tells you which assembly the class is defined in and what namespace it's a member of. The same goes for every type in the .NET Framework.

 

[vks.gautam1]

I tried & getting the error of " an oledbparameter with parameter name"dat" is not contained by oledbParameterCollection.

q = "insert into TAB1 values(Dat,SNo,MobNo)"
cn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=E:\1.mdb;")
cn.Open()
cmd = New OleDbCommand(q, cn)

For Each line As String In File.ReadAllLines(file_name)
' for each line as String in
Dim spl() As String = line.Split("|")

' cmd.Parameters.AddWithValue("date", DateTime.ParseExact(spl(0), "dd-mmm-yy", CultureInfo.CurrentCulture))
' cmd.Parameters.AddWithValue("sno", spl(1))
' cmd.Parameters.AddWithValue("MobNo", spl(2))
cmd.Parameters("dat").Value = DateTime.ParseExact(spl(0), "dd-mmm-yy", CultureInfo.CurrentCulture)
cmd.Parameters("sno").Value = spl(1)
cmd.Parameters("mobno").Value = spl(2)
cmd.ExecuteNonQuery()
Next

if i try
cmd.Parameters.AddWithValue("dat",... then im getting error="String was not recognised as valid date & time "

 

[cjard]

I think youre coding by trial and error and not actually putting any thought into your coding

Look at my code.. OK it's a bit messy and there are some typos, but the order and the logic is sensible

Do I add to my parameters collection inside the loop? No. Why do you do that? Surely you can see that if your loop runs 100 times, your parameters colelction will have 300 items

Are my parameters adds commented out? No. Why are yours commented out? If you never add anything to the parameters collection, then of course youre going to get an error about not finding a parameter with name XXX


-

I can see you need help, but you also need to change your thinking before you can really get the help you need. If english isn't your first language, then try writing comments in FIRST, in your native language and then write code underneath them. This helps you in 2 ways:

a) your code is already commented
b) it's easier to think in a language youre used to, then translate into one that youre not

I want you to think about what's written here; I need to see you making some progress and attempting to solve problems on your own if I'm to continue helping you

'set up the database connection
q = "insert into TAB1 values(?,?,?)"
cn = New OleDbConnection("provider=microsoft.jet.oledb.4.0; data source=E:\1.mdb;")
cmd = New OleDbCommand(q, cn)

'build the parameters collection using fake data for now
cmd.Parameters.AddWithValue("date", DateTime.Now)
cmd.Parameters.AddWithValue("sno", "dummy")
cmd.Parameters.AddWithValue("mobno", "dummy")

'open the connection
cn.Open()

For Each line As String In File.ReadAllLines(file_name)

  'split the line
  Dim spl() As String = line.Split("|")

  'now assign some real values to our existing parameters

  'get the name right! "dat" and "date" are DIFFERENT
  cmd.Parameters("date").Value = DateTime.ParseExact(spl(0), "dd-mmm-yy", CultureInfo.CurrentCulture)
  cmd.Parameters("sno").Value = spl(1)
  cmd.Parameters("mobno").Value = spl(2)

  'do the insert
  cmd.ExecuteNonQuery()
Next

'dont forget to close the conenction

If it fails with a "String was not recognised" the use the debugger to find out WHAT the string actually is - it should then be obvious why it's not recognised. Probably it's a footer line or the date is blank

If you don't know what the debugger is, GOOGLE it!

Add some error handling!