C#: Input data from string array into MS Access DB Table

Question

I've searched as much as I can and can't find anything to help me. So what I have is a script that reads/splits and stores data from a .txt file into some arrays. (The one listed here is Vndnbr). What I'm having trouble with is how to go about inputting each entry in the array as an entry under a column in a MS Access table? This is what I have so far:

public void AddToDatabase()
{
    OleDbCommand command;
    OleDbConnection connection =
        new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source=filepath");
    foreach (string x in Vndnbr)
    {
        cmdstringVND[k] = "insert into Table1 (Vndnbr) Values (x)";
        k++;
        command = OleDbCommand(cmdstringVND[k],connection);
    }

    command.Parameters.AddWithValue("?", ReadFromFile("filepath"));
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

I'm not familiar with the Access library or what should be inserted in the first parameter of AddwithValue as I just copy pasted these lines after doing some research. If someone could help me with how to add all the data from an array into a table it would be greatly appreciated, thanks.

Steve · Accepted Answer · 2013-10-24 17:17:50Z

There are many errors in your code

In your loop you don't use a parameter to store the value to be inserted
You never creare the command. (Use new)

You try to execute only the last command because the ExecuteNonQuery is outside the loop

public void AddToDatabase()
{
    string cmdText = "insert into Table1 (Vndnbr) Values (?)";
    using(OleDbConnection connection = new OleDbConnection(.....))
    using(OleDbCommand command = new OleDbCommand(cmdText, connection))
    {
        connection.Open();
        command.Parameters.AddWithValue("@p1", "");
        foreach (string x in Vndnbr)
        {
             command.Parameters["@p1"].Value = x;
             command.ExecuteNonQuery();

        }
    }
}

I have changed you code to include the using statement to correctly close and dispose the connection and the command, then I have initialized the command outside the loop, passed a common string with as a parameter placeholder and initialized this parameter with a dummy value.

Inside the loop I have replaced the previous parameter value with the actual value obtained by your Vndnbr list and executed the command.

Mike Perrenoud · Accepted Answer · 2013-10-24 17:12:02Z

1

You'll want to change your SQL to this:

"insert into Table1 (Vndnbr) Values (@x)";

and then the AddWithValue is like this:

command.Parameters.AddWithValue("@x", ReadFromFile("filepath"));

All you're doing is saying, for this parameter name, I want this value assigned.

answered Oct 24, 2013 at 17:12

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

4 Comments

John Smith Over a year ago

ReadFromFile() isn't being recognized, do you have any reason why this might be?

Mike Perrenoud Over a year ago

@JohnSmith, what is ReadFromFile supposed to be? Is that a method you wrote? Is that something you had expected was readily available on the object you're currently in? I don't know what ReadFromFile is.

John Smith Over a year ago

I was researching how to properly make a connection and copy pasted that block of code. Looking at visual studios comment it says the second parameter is the value.. is @x not the value? Sorry for the confusion

Mike Perrenoud Over a year ago

@JohnSmith @x is the name of the parameter. You see that parameter in the modified insert statement I provided. The second parameter is in fact the value. But if you copied and pasted ReadFromFile from the internet, you need to get that method and add it to this class (or to a static class) so that it can be accessed.

Collectives™ on Stack Overflow

C#: Input data from string array into MS Access DB Table

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related