CODEWITHSUNDEEP
c#ms-accessado.net
John Smith
John Smith

Reputation: 283

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

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.

Upvotes: 1

Views: 1697

Answers (2)

Steve
Steve

Reputation: 216313

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.

Upvotes: 3

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

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.

Upvotes: 1

Related Questions