Access c# database OleDbException

Question

I tried writing codes to access database but there was OldDbException error at the line with the bold words. How should I change my words so that there will not be any errors at run time?

    private void submitbutton_Click(object sender, EventArgs e)
    {
        availabilitytabControl.SelectedTab = orderlisttabPage;

        OleDbConnection myAccessConn = myAccessConnection();
        OleDbCommand myAccessCommand = new OleDbCommand();
        DataSet myDataSet = new DataSet();

        try
        {
          int i;

            myAccessConn.Open();
            String insert ="insert into Particulars (Title,FirstName,LastName,Nationality,PassportNumber,PhoneNumber) VALUES(";


            for (i = 0; i < 100; i++)
            {
                myAccessCommand.CommandText = insert;
                String title = titlecomboBox.Items[i].ToString();
                String firstname = firstnametextBox.Text;
                String lastname = lastnametextBox.Text;
                String nationality = nationalitycomboBox.Items[i].ToString();
                String passportno = passporttextBox.Text;
                String phoneno = phonenotextBox.Text;

                myAccessCommand = new OleDbCommand(insert,myAccessConn);
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
                **myAccessCommand.ExecuteNonQuery();**

            }

        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
            return;
        }
        finally
        {
            myAccessConn.Close();
        }


    }

You have the insert query incomplete. Probably you need to re-read the rules for an INSERT query — Steve
– Steve, Commented Jul 29, 2015 at 12:33
Add a breakpoint just before myAccessCommand.ExecuteNonQuery(); and look at the myAccessCommand.CommandText. You'll notice that it's not a valid SQL syntax. And that's the problem. You've set the first half of the INSERT where you define the columns, then you started to define the VALUES but you never actually do anything with all those string variables in your for loop. Also, general convention is to use string for variables and String when utilizing the actual class. Such as String.IsNullOrWhiteSpace(). And in the future, include what ex.Message says in your post. — sab669
– sab669, Commented Jul 29, 2015 at 12:36

Soner Gönül · Accepted Answer · 2015-07-29 12:48:14Z

As far as I can see, you never add your values in your VALUES(..) part in your query.

My suggestion is;

Define your parameter in your VALUES part
Add your values with myAccessCommand.Parameters.Add in your for loop.
Execute your query.
Clear() your parameters just before your wanna insert values for next loop.
Your OleDbDataAdapter part is unnecessary since you try to put INSERT statement in it.

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. Also use using statement to dispose your database connections and commands.

using(var myAccessConn = myAccessConnection());
using(var myAccessCommand = myAccessConn.CreateCommand())
{
    myAccessCommand.CommandText = @"insert into Particulars (Title,FirstName,LastName,Nationality,PassportNumber,PhoneNumber) 
                                    VALUES(?, ?, ?, ?, ?, ?)";
    for (i = 0; i < 100; i++)
    {
        myAccessCommand.Parameters.Clear();

        myAccessCommand.Parameters.AddWithValue("?", titlecomboBox.Items[i].ToString());
        myAccessCommand.Parameters.AddWithValue("?", firstnametextBox.Text);
        myAccessCommand.Parameters.AddWithValue("?", lastnametextBox.Text);
        myAccessCommand.Parameters.AddWithValue("?", nationalitycomboBox.Items[i].ToString());
        myAccessCommand.Parameters.AddWithValue("?", passporttextBox.Text);
        myAccessCommand.Parameters.AddWithValue("?", phonenotextBox.Text); 

        myAccessConn.Open();
        myAccessCommand.ExecuteNonQuery();
    }
}

I used AddWithValue method as an example but you don't. This method may generate unexpected results sometimes. Use Add method overloads to specify your parameter type and it's size.

cnom · Accepted Answer · 2015-07-29 12:50:18Z

You probably need to modify your code like that:

(Let me note that this is only a TEST CODE to check that it solves your problem, you will have to construct your command by using parameter.add() for your code to be safe)

private void submitbutton_Click(object sender, EventArgs e){
    availabilitytabControl.SelectedTab = orderlisttabPage;

    OleDbConnection myAccessConn = myAccessConnection();
    OleDbCommand myAccessCommand = new OleDbCommand();
    DataSet myDataSet = new DataSet();

    try
    {
      int i;

        myAccessConn.Open();
        String insert ="insert into Particulars (Title,FirstName,LastName,Nationality,PassportNumber,PhoneNumber) VALUES(";


        for (i = 0; i < 100; i++)
        {

            String title = titlecomboBox.Items[i].ToString();
            String firstname = firstnametextBox.Text;
            String lastname = lastnametextBox.Text;
            String nationality = nationalitycomboBox.Items[i].ToString();
            String passportno = passporttextBox.Text;
            String phoneno = phonenotextBox.Text; 
            insert  += "'"+ firstname +"','"+ lastname+"','"+nationality + "','"+ passportno +"','"+ phoneno +"')";


            myAccessCommand = new OleDbCommand(insert,myAccessConn);
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
            **myAccessCommand.ExecuteNonQuery();**

        }

    }
    catch (Exception ex)
    {
        Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
        return;
    }
    finally
    {
        myAccessConn.Close();
    }
}

Collectives™ on Stack Overflow

Access c# database OleDbException

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related