CODEWITHSUNDEEP
c#asp.netms-accessinsert
user2211486
user2211486

Reputation: 237

Store values in the Access Database

I basically want to store some values into the database.
I keep getting the error "Number of query values and destination fields are not the same." pointing towards the dr = cmd.ExecuteReader();

Can someone please help me? :)
I am kinda new to the whole thing and don't really know whats happening.
Bellow is the code am using.

public partial class Registeration_experiment : System.Web.UI.Page
{
    static OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\New folder\Project 1.0\WebSite1\New Microsoft Office Access 2007 Database.accdb");
    OleDbDataAdapter ada = new OleDbDataAdapter();
    OleDbCommand cmd = new OleDbCommand();
    OleDbDataReader dr;

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string str = "insert into Registeration_Test (Name1, address, emailaddress)" +
          "values (?, ?, ?)";
        con.Open();
        cmd = new OleDbCommand(str, con);
        cmd.Parameters.AddWithValue("@p1", TextBox1.Text);
        cmd.Parameters.AddWithValue("@p2", TextBox2.Text);
        cmd.Parameters.AddWithValue("@p4", TextBox4.Text);
        cmd.ExecuteNonQuery(); 
    }
}

Thank you :)

Upvotes: 0

Views: 313

Answers (1)

Steve
Steve

Reputation: 216313

You are writing to the database, not reading from it.
No sense to use ExecuteReader, you need a ExecuteNonQuery for that

EDIT Seeing now the structure of the table. It is composed of 5 fields.
In this case, you have two choices. Provide the name of the fields that you want to update in the insert statement or add a parameter for every field

First choice, you don't give the name of the fields (pass a parameter for every field except the ID because I suppose it is an AutoIncrement field, meaning that the database manages itself the value for that field)

// No name provided for fields, need to pass a parameter for each field
string str = "insert into Registeration_Test values (?, ?, ?, ?)";
con.Open();
cmd = new OleDbCommand(str, con);
cmd.Parameters.AddWithValue("@p1", Textbox1.Text);
cmd.Parameters.AddWithValue("@p2", Textbox2.Text);
cmd.Parameters.AddWithValue("@p3", Textbox3.Text);
cmd.Parameters.AddWithValue("@p4", Textbox4.Text);
cmd.ExecuteNonQuery();

(Note, the TextBox1... etc are the names you have provided in you example above, I don't know the exact content of these textboxes where the user types the data to insert in the database, nor I don't know if they really exists in your page/form)

Second choice, you declare the fields that should be updated in the database

// Just three named fields updated
string str = "insert into Registeration_Test (Name1, address, emailaddress)" + 
             "values (?, ?, ?)";
con.Open();
cmd = new OleDbCommand(str, con);
cmd.Parameters.AddWithValue("@p1", Textbox1.Text);
cmd.Parameters.AddWithValue("@p2", Textbox2.Text);
cmd.Parameters.AddWithValue("@p4", Textbox4.Text);
cmd.ExecuteNonQuery();

Please note also that I have changed your sql command to not use string concatenation.
It is a very bad practice that leads to numerous problem. The worst one is Sql Injection, not to mention problems with string parsing when text contains single quotes or other problematic characters

A parameter placeholder is represented (in OleDb) by the ? in the command text. This placeholder will be replaced by the actual value added in the parameter collection of the OleDbCommand (in the exact order in which the placeholders appears). Using Parameters (Parametrized query) allows the framework to examine the values passed and take appropriate actions if these values are invalid.

EDIT The problem with connection arises from a previous command that has left the connection open. This is another bad practice. Every connection should be: created, opened, used, closed. The pseudocode to follow is this

// Create the connection
using(OleDbConnection con = GetOleDbConnection())
{
    con.Open();

    ... // use the connection with insert/delete/update/select commands

}
// Exiting from the using block close and destroy (dispose) the connection

Upvotes: 3

Related Questions