INSERT INTO c# to Microsoft access

Question

I am trying to insert the text inside some text boxes into a database that I have in access. The code produces no errors but does not seem to add the items to the database.

The Database is called 'Database' the table is called 'TotalPlayerName' and the field is called 'Player Name'. There are other fields in the table.

for(int i = 0; i < numberOfPlayers; i++){

  using (OleDbConnection connection = new OleDbConnection(@"CONNECTION STRING"){
    using (OleDbCommand command = new OleDbCommand(@"INSERT INTO TotalPlayerName ([Player Name]) VALUES(@p1)", connection)){

      connection.Open();
      command.Parameters.Add("@p1", OleDbType.VarWChar).Value = Convert.ToString(textBox[i].Text);
      command.ExecuteNonQuery();

    }

  }

}

Answer 1

You might just need to declare @p1 because you call it in the INSERT statement, but it is never defined as a variable such as: varchar, int, ect, ect. This might work for what you are trying to do:

using (OleDbCommand command = new OleDbCommand(@"DECLARE @p1 VARCHAR(50) INSERT  INTO TotalPlayerName ([Player Name]) VALUES(@p1)", connection)){ 

Also if at all possible i would definitely make it a stored procedure if you can. This works with SQL not sure if it will work with MS Access, but i would imagine so. The other thing you might want to do is make sure that it's finding the correct DB.

Database.dbo.TotalPlayerName

But that is probably not the issue, probably just the lack of variable declaration.

Answer 2

While I don't see what's specifically wrong with your code, I can tell you your methodology is off a bit. Specifically, for every iteration of your loop you are:

1. Establishing a connection to the database
2. Creating the insert command, creating a parameter and assigning the value
3. Executing the insert

It would be better all around if you did steps 1 and part of 2 once and then executed the statement within the loop like this:

using (OleDbConnection conn = new OleDbConnection(
    @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\foo.accdb"))
{
    conn.Open();

    OleDbCommand command = new OleDbCommand(
        @"INSERT INTO TotalPlayerName ([Player Name]) VALUES (@p1)", conn);
    command.Parameters.Add(new OleDbParameter("@p1", OleDbType.VarChar));

    for (int i = 0; i < numberOfPlayers; i++)
    {
        command.Parameters[0].Value = textbox[i].Text;

        try
        {
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            // do something
        }
    }

    conn.Close();
}

I assume textbox is an array or list of actual Text Box controls. If that's the case, then textbox[i].Text is already a string, and you shouldn't need to do anything special to make OLE recognize it as such.

On a final note -- add that try/catch and put a breakpoint there. Are you SURE it's not failing? If you are running in debug mode, there is no guarantee that your program will halt -- it may just return back to the form without reporting any error. It may not be until you attempt to deploy the app that you see the actual error occurring.