[fanglign]

Imports System.ComponentModel
Imports System.Data.OleDb
Imports System.IO



Public Class Form2

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ''Connections
        Dim OLEcmd As New OleDbCommand

        Dim OLEconnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\111411H\My Documents\data.accdb;")
        'Dim OLECmd As New OleDbCommand("SELECT * INTO [StudentsData] FROM [Text;Database=J:\FYP;].[SEG1.txt]", OLEconnection)
        OLEcmd.Connection = OLEconnection

        Dim i, j As Integer
        Dim mystring As String
        Dim arrdata(5) As String
        mystring = "School" & vbTab & "Campus" & vbTab & "AdminNo" & vbTab & "ModuleCode" & vbTab & "ModuleGrp"
        arrdata = mystring.Split(vbTab)


        arrdata(0) = "School"
        arrdata(1) = "Campus"
        arrdata(2) = "AdminNo"
        arrdata(3) = "ModuleCode"
        arrdata(4) = "moduleGrp"

        'dgv columns
        DataGridView1.ColumnCount = 5
        DataGridView1.Columns(0).Name = "School"
        DataGridView1.Columns(1).Name = "Campus"
        DataGridView1.Columns(2).Name = "AdminNo"
        DataGridView1.Columns(3).Name = "ModuleCode"
        DataGridView1.Columns(4).Name = "ModuleGrp"

       
   



        For Each line As String In System.IO.File.ReadAllLines("J:\FYP\SEG1.txt")
            j = 0
            For i = 0 To j
                OLEcmd.CommandText = "Insert into Table1(School, Campus, AdminNo, ModuleCode, ModuleGrp) values ('" & mystring(0) & "', '" & mystring(1) & "', '" & mystring(2) & "', '" & mystring(3) & "', '" & mystring(4) & "')"

            Next
            'OLEcmd.CommandText = "Insert into Table1(School, Campus, AdminNo, ModuleCode, ModuleGrp) values ('" & mystring(0) & "', '" & mystring(1) & "', '" & mystring(2) & "', '" & mystring(3) & "', '" & mystring(4) & "')"
            ReDim Preserve arrdata(i)


        Next

        OLEconnection.Open()
        OLEcmd.ExecuteNonQuery()
        OLEconnection.Close()

Hi, i have gotten this codes. Have alr read text file. my text file has information as follows:

School Campus AdminNo ModuleCode ModuleGrp
SEG AMK 111411h EG201 EG201-G4
.
.
.

i want to break the columns up into array, loop through the text file lines, and insert into the Access database using SQL command. I have also the datagridview, which i populate my datas and my access database having the datas too.

Thanks in advance!

This post has been edited by Atli: 24 June 2013 - 02:22 AM
Reason for edit:: Use [code] tags when posting code.

[sela007]

at first glance, your code looks fine. Do you have any bugs?

[fanglign]

sela007, on 24 June 2013 - 08:51 AM, said:

at first glance, your code looks fine. Do you have any bugs?

Hi thanks for you reply. yea, i do have errors.
for my arrdata(0), (1), (2),(3), (4)has an error saying variable used before it has been assigned a value.
and for the INSERT command, i want the data to go into my database instead of "School" .
Please help thanks, kinda urgent for this.

[fanglign]

sela007, on 24 June 2013 - 08:51 AM, said:

at first glance, your code looks fine. Do you have any bugs?

i have change my code a little, could you help me check if theres any other errors apart from the error im getting which is "index outside bounds of array", how do i solve that? with the codes i've done, am i doing the right coding to achieve what i want?

Thanks!!

fanglign, on 24 June 2013 - 06:18 PM, said:

sela007, on 24 June 2013 - 08:51 AM, said:

at first glance, your code looks fine. Do you have any bugs?

i have change my code a little, could you help me check if theres any other errors apart from the error im getting which is "index outside bounds of array", how do i solve that? with the codes i've done, am i doing the right coding to achieve what i want?

Thanks!!

here's my code:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Imports System.ComponentModel
Imports System.Data.OleDb
Imports System.IO



Public Class Form2

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ''Connections
        Dim OLEcmd As New OleDbCommand

        Dim OLEconnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\111411H\My Documents\data.accdb;")
        'Dim OLECmd As New OleDbCommand("SELECT * INTO [StudentsData] FROM [Text;Database=J:\FYP;].[SEG1.txt]", OLEconnection)
        OLEcmd.Connection = OLEconnection

        Dim i, j As Integer
        Dim mystring As String

        Dim arrdata(4) As String
        
        j = 0
        For i = 0 To j
            ReDim Preserve arrdata(i)

            For Each line As String In System.IO.File.ReadAllLines("J:\FYP\SEG1.txt")

                mystring = "School,Campus, AdminNo,ModuleCode, ModuleGrp"
                arrdata = mystring.Split(vbTab)

                Dim School As String = arrdata(0)
                Dim Campus As String = arrdata(1)
                Dim AdminNo As String = arrdata(2)
                Dim ModuleCode As String = arrdata(3)
                Dim Modulegrp As String = arrdata(4)


            Next

        Next

        OLEcmd.CommandText = "Insert into Table1(School, Campus, AdminNo, ModuleCode, ModuleGrp) values ('" & arrdata(0) & "', '" & arrdata(1) & "', '" & arrdata(2) & "', '" & arrdata(3) & "', '" & arrdata(4) & "')"

This post has been edited by AdamSpeight2008: 24 June 2013 - 06:52 PM
Reason for edit:: Please learn to use the code tags.

[AdamSpeight2008]

you may be interested in the TextFieldParser Tutorial

[fanglign]

AdamSpeight2008, on 24 June 2013 - 06:54 PM, said:

you may be interested in the TextFieldParser Tutorial

thanks for that. but i really don't understand it. dont even know what's code tags.
im a very beginner in vb.net 2008/2010

[sela007]

if you take the sample text line from your code and split by vbTab you will get only one line.

Dim str As String = "SEG AMK 111411h EG201 EG201-G4"
Dim arrdata(5) As String 
arrdata = str.Split(vbTab)
MsgBox(arrdata.Count)' one arrdata 

arrdata(0) = "SEG AMK 111411h EG201 EG201-G4"
arrdata(1) = nothing
arrdata(2) = nothing
arrdata(3) = nothing
arrdata(4) = nothing

so, only arrdata(0) is assigned. And if you use any other arrdata(>0) like

Dim Campus As String = arrdata(1)

you will get error "index outside bounds of array".


if you split text line by empty space (" ") you will get 5 parts.

Dim str As String = "SEG AMK 111411h EG201 EG201-G4"
Dim arrdata(5) As String 
arrdata = str.Split(" ")
MsgBox(arrdata.Count)' 5 

arrdata(0) = "SEG"
arrdata(1) = "AMK"
arrdata(2) = "111411h"
arrdata(3) = "EG201"
arrdata(4) = "EG201-G4"

I think this is the solution to your problem.