Saving Data to MS Access from a Windows Form Application

Question

Hello, I am a student in web programming and don't have too much experience with Windows Form Application programming. So I have a very simple application that reads a .txt file (which has data that needs to be placed in an MC Acces database), trims the content and places that into a textbox.

I now have to save that data to a MS Acces database, and I simply don't have a clue where to begin. Could annyone get me going or give me some tips on where to start?

Here is the function that reads the .txt file and places it in a textbox:

//LOAD FILE
            
            FileStream stream = File.OpenRead(@"C:\Documents and Settings\Owner\My Documents\Visual Studio 2008\Projects\WinFileReaderTest\WinFileReaderTest\file\DB20090926140705.txt");
            StreamReader s = new StreamReader(stream);
            string readedContent = s.ReadToEnd() ;
            Regex regex = new Regex("\n");
            string[] rows = regex.Split(readedContent);
            
            foreach (string row in rows)
            {
                string rowNew = row.Trim();
                Array columns = rowNew.Split('|');

                int i = 0;
                foreach (string column in columns)
                {
                    if(i%2 == 0)
                    {
                        this.txtResult.AppendText("HEADER: " + column + "\r\n");
                    }
                    else
                    {
                        this.txtResult.AppendText("VALUE: " + column + "\r\n");
                    }
                    i++;

                }

Answer 1

Hi,

w3schools is a good place to start.

http://www.w3schools.com/aspnet/aspnet_dbconnection.asp

You can also look at the following links to get some more insights

http://www.developerfusion.com/article/7869/vbnet-and-ms-access/

Answer 2

Hi ISten,

 

You can achieve it by the following step,

1)    Create a corresponding table in Access. If you want to create the table dynamically, you can run an OleDbCommand for creating table.

This MSDN explain how to write the SQL,

http://msdn.microsoft.com/en-us/library/aa140011(office.10).aspx

 

CREATE TABLE tblCustomers 

    (CustomerID INTEGER NOT NULL,

    [Last Name] TEXT(50) NOT NULL,

    [First Name] TEXT(50) NOT NULL,

    Phone TEXT(10),

    Email TEXT(50))

 

2)    After reading a row, insert it into Access table.

 

INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])

    VALUES (1, 'Kelly', 'Jill')

 

Use Oledb parameter will be better,

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx

 

 

Here is a similar sample code, (it is a little complex when deal with getting one row)

 

1.  string[] FieldArray = { "ID", "Username", "Password" };

2.     

3.      OleDb.OleDbConnection con = new OleDb.OleDbConnection();

4.      DataSet ds = new DataSet();

5.      OleDbDataAdapter da = default(OleDbDataAdapter);

6.      string Sql = null;

7.      string[] currentRow = null;

8.     

9.      con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\users.accdb";

10.     con.Open();

11.    

12.     //***Read Data from Text File******************************

13.    

14.     using (Microsoft.VisualBasic.FileIO.TextFieldParser Reader = new Microsoft.VisualBasic.FileIO.TextFieldParser("E:\\test.txt")) {

15.        

16.         Reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.FixedWidth;

17.         Reader.SetFieldWidths(1, 6, 3);

18.         var FieldCounter = 0;

19.        

20.         while (!Reader.EndOfData) {

21.             try {

22.                 string RowType = Reader.PeekChars(1);

23.                 currentRow = Reader.ReadFields();

24.                 DataRow dsNewRow = ds.Tables("COD_Data").NewRow();

25.                 string currentField = null;

26.                 FieldCounter = 0;

27.                 OleDbCommand comm = new OleDbCommand("Insert into UserList([ID], [Username], [Password]) values(ID,Username,Password)", con);

28.                 foreach (var currentField in currentRow) {

29.                     dsNewRow.Item(FieldArray(FieldCounter)) = currentField;

30.                     comm.Parameters.AddWithValue(FieldArray(FieldCounter), currentField);

31.                     FieldCounter = FieldCounter + 1;

32.                 }

33.                 int i = comm.ExecuteNonQuery();

34.                    

35.                 Interaction.MsgBox(i);

36.             }

37.             catch (Microsoft.VisualBasic.FileIO.MalformedLineException ex) {

38.                 //End If

39.                 Interaction.MsgBox("Line " + ex.Message + "is not valid and will be skipped.");

40.                

41.             }

42.         }

43.     }  

 

 

If you have any questions or concerns, please update the thread and we will have a further discussion.

 

Best Regards

Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Thanks so much! I'm gonna look into that the coming 2 days, hopefully it'll work. I'll let you know if it works.

Answer 4

I'm stuck with something. I need to test the value of HEADER to know in which column I have ti write a value.

If I try it with just a single line in my .txt file, this code (see below) works just fine.

But I need to test the value of HEADER in the if statement. I tried placing another if statement inside the first if statement, to see if the value of column was equal to "BOOK", if that was true, I placed the value of column into a string called "header".

But then in the else, where the actual value is, if I want to see if that string I declared in the if statement is equal to "BOOK", it doesn't recognise the string... Can somebody help out?

foreach (string column in columns)
                {
                    if(i%2 == 0)
                    {
                        this.txtResult.AppendText("HEADER: " + column +               "\r\n");
                    }
                    else
                    {
                        this.txtResult.AppendText("VALUE: " + column + "\r\n");
                        
                            OleDbCommand comm = new OleDbCommand("Insert into Books([BOOK]) values(BOOK)", conn);
                            comm.Parameters.AddWithValue("@BOOK", column);
                            comm.ExecuteNonQuery();
                    }
                    i++;
                }

Answer 5

Hi ISten,

 

The complexity of coding depends on the text file you are working with.

There are two options to simplify it:

1) Don’t have space in each filed.

For example,

ID        Name

1                  Monica

2                   Typot

 

Once you get a line, you can use split it to a string array,

                string s = "1  Monica";

                string[] r = s.Split(' ');

 

You can just get the name by r[1].

 

There is one tip, when you read the first line, you can store the columns’ name in an array or hash table.

                string[] colname = { "ID", "Name" };

                insert into tablename (" + colname[1] + ")Values(......

If you want to compare the column name, you can compare to the colname array and get the correct index for the column you want.

 

ID        Name

1                  Monica Feng

2                   Typot

 

If one filed has space, this method will not work properly.

 

2) Let each field start at the fixed position.

For example,

ID    Name

1     Monica

2     Typot

 

ID field always starts from position 1.

Name filed always starts form position 7.

Then you can use String.Substring and String.Trim to get certain records.

 

 

Best Regards

Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 6

Thanks, I got it solved!!

Answer 7

Hi ISten,

Could you share the solution here? It will benefit other members who have the similar problem.


Best Regards
Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.