[RESOLVED] [2.0] Access Database not Writing

[RudiVisser]

Hi

I'm using the following code, and it adds the row.. in the application. However, it's not actually being saved to the database, even after 3 different AcceptChanges() calls, I will only do this once I was just clutching at straws as to why it wasn't saving.

Here's the code.

string databaseSource = "responses.mdb";
            string databaseCS = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + databaseSource + "";
            try
            {
                OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM responses", databaseCS);
                DataSet responsesTable = new DataSet("responses");
                dbAdapter.Fill(responsesTable, "responses");
                Console.WriteLine(responsesTable.Tables["responses"].Rows[0]["first_name"]);
                DataRow newRecord = responsesTable.Tables["responses"].NewRow();
                newRecord["first_name"] = "first name";
                newRecord["surname"] = "surname";
                newRecord["special"] = "special";
                newRecord["telephone"] = "telephone";
                newRecord["email"] = "email";
                newRecord["company_name"] = "company";
                newRecord["title"] = "title";
                responsesTable.Tables["responses"].Rows.Add(newRecord);
                responsesTable.Tables["responses"].AcceptChanges();
                responsesTable.AcceptChanges();
                dbAdapter.Update(responsesTable, "responses");
                Console.WriteLine(responsesTable.Tables["responses"].Rows[1]["first_name"]);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                Console.ReadLine();
            }

Any help as to why it might not be saving would be greatly appreciated.

The access database is very simple. Namely 1 table named responses with the following:
response_id - AutoNumber (Unique)
first_name - Text
surname - Text
special - Text
company_name - Text
title - Text
email - Text
telephone - Text

I'm sure whatever it is that's wrong is pretty simple, but I just can't pin it down!!

[RudiVisser]

UPDATE: Okay so I got it working in a console application but the actual need is for it to be in ASP.NET C#, and that's still not working.

I've tried the code above, the code from the console application and the following:

using(OleDbConnection conn = new OleDbConnection(databaseCS)) {
			conn.Open();
			using (OleDbCommand cmd = new OleDbCommand("INSERT INTO responses ( first_name, surname ) VALUES ( 'test' , 'test surname' )", conn)) {
				cmd.CommandType = CommandType.Text;
				cmd.ExecuteNonQuery();
			}
		}

This code gives the error: Operation must use an updateable query.

This work's pretty urgent so any help would be greatly appreciated.

[brucevde]

Regarding the code in your first post.

1) Don't call the AcceptChanges method. Basically, it sets the RowState property on any rows that were modified/added to "unchanged". The Update method would then have nothing to save. The Update method calls the AcceptChanges method after the row was saved to the database.

2) You need to ensure the DataAdapter's InsertCommand, UpdateCommand and DeleteCommand are also set. The Update method uses the appropriate command depending on the value of the RowState property.

This link might help you

http://msdn2.microsoft.com/en-us/lib...cz(VS.80).aspx

[RudiVisser]

Okay thanks I removed the AcceptChanges part and now I got a new error: "Update requires a valid InsertCommand when passed DataRow collection with new rows.".

So okay I'm reading this on insertCommand.

And I don't understand a bit, surely if the table has that new row in it, the update thing will just copy the table from the script into the database??

Or do I just have to write an INSERT INTO responses ( first_name, etc. etc. ) VALUES (what would go here?? ) as an insertCommand?

[brucevde]

You can automatically generate the Insert command using this code

Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(dbAdapter)
builder.GetInsertCommand()

or as you suggested create the InsertCommand manually

[RudiVisser]

Okay I got that but now it wants a SelectCommand too!

I tried: dbAdapter.SelectCommand = new OleDbCommand("SELECT * FROM responses");

Error: The DataAdapter.SelectCommand property needs to be initialized

I've Google'd about and played with various methods but nada!

[brucevde]

but now it wants a SelectCommand too!

Which code are you working with? The code from Post #1, creates the SelectCommand when the adapter is created.

OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM responses", databaseCS);

[RudiVisser]

Yes that's what I thought, but I still got that error! Here's the complete code:

string databaseSource = "responses.mdb";
            string databaseCS = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + databaseSource + "";
            try
            {
                OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM responses", databaseCS);
                DataSet responsesTable = new DataSet("responses");
                dbAdapter.Fill(responsesTable, "responses");
                //Console.WriteLine(responsesTable.Tables["responses"].Rows[0]["first_name"]);
                DataRow newRecord = responsesTable.Tables["responses"].NewRow();
                newRecord["first_name"] = "first name";
                newRecord["surname"] = "surname";
                newRecord["special"] = "special";
                newRecord["telephone"] = "telephone";
                newRecord["email"] = "email";
                newRecord["company_name"] = "company";
                newRecord["title"] = "title";
                responsesTable.Tables["responses"].Rows.Add(newRecord);
                OleDbCommandBuilder cB = new OleDbCommandBuilder();
                dbAdapter.InsertCommand = cB.GetInsertCommand();
                dbAdapter.Update(responsesTable, "responses");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                Console.ReadLine();
            }

[brucevde]

The CommandBuilder needs the DataAdapter that contains the SelectCommand.

OleDbCommandBuilder cB = new OleDbCommandBuilder(dbAdapter);

[RudiVisser]

THANK YOU!!!!!!!!!!

It's working, you don't know how much I would owe you if I knew you!!

Finished (And working!!!) code:

try {
		OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM responses", databaseCS);
		DataSet responsesTable = new DataSet("responses");
		dbAdapter.Fill(responsesTable, "responses");
		DataRow newRecord = responsesTable.Tables["responses"].NewRow();
		newRecord["first_name"] = Request.Form["first_name"];
		newRecord["surname"] = Request.Form["surname"];
		newRecord["special"] = Request.Form["special"];
		newRecord["telephone"] = Request.Form["telephone"];
		newRecord["email"] = Request.Form["email"];
		newRecord["company_name"] = Request.Form["company"];
		newRecord["title"] = Request.Form["title"];
		responsesTable.Tables["responses"].Rows.Add(newRecord);
		OleDbCommandBuilder InsertCommand = new OleDbCommandBuilder(dbAdapter);
        dbAdapter.InsertCommand = InsertCommand.GetInsertCommand();
		dbAdapter.Update(responsesTable, "responses");
	} catch (Exception e) {
		Response.Write("<!-- DATABASE ERROR: "+e.Message + " -->");
	}

Thanks again.