Table does not appear to update after UPDATE statement

I am trying to update a table (asps) in MS access with the code below. However even though it complies and appears to execute, the table is not updated. Both ID and recID are double.

I am working in ASP .NET C#.

Any ideas

Thanks

private void Button1_Click(object sender, System.EventArgs e)

{

OleDbConnection conn = null;

conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\\Inetpub\\wwwroot\\WEB\\afterschoolprograms.mdb");

string strEditCommand = "UPDATE asps SET ";

strEditCommand += " NAME = ";

strEditCommand += " WHERE ID = ";

using (OleDbCommand cmd = new OleDbCommand(strEditCommand, conn))

{

cmd.Parameters.Add(new OleDbParameter("NAME", this.txtName.Text));

cmd.Parameters.Add(new OleDbParameter("ID", this.recID2));

cmd.Connection.Open();

cmd.ExecuteNonQuery();

}

Answer this question

Table does not appear to update after UPDATE statement

mrLarry1975

I did previously use Response.Write(cmd.ExecuteNonQuery(), which showed that one record was affected. But I think you may be on to something with the postback. I will check that out.

The value this.recID2 was passed froma previous page and stored in this variable.

Thanks

ssmallfish

try this:

private void Button1_Click(object sender, System.EventArgs e)
{

OleDbConnection conn = null;
conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\\Inetpub\\wwwroot\\WEB\\afterschoolprograms.mdb");
string strEditCommand = " UPDATE asps SET "
+ " [NAME] = "
+ (" WHERE [ID] = ");

using (OleDbCommand cmd = new OleDbCommand(strEditCommand, conn))
{

cmd.Parameters.Add(new OleDbParameter("@NAME", this.txtName.Text.Trim()));
cmd.Parameters.Add(new OleDbParameter("@ID", this.recID2));
try
{
cmd.Connection.Open();
Response.Write(strEditCommand);
Dim recordsAffected as Integer = cmd.ExecuteNonQuery();
Response.Write("<br>Records update: " + recordsAffected.ToString());
}

catch (OleDbException ex)
{
Response.Write(ex.Message);
}
finally

{
cmd.Connection.Close();
}

}

you should see record affected to be at least 1, if not, then something else is wrong. Be sure you are not overwriting the MS Access database file when running your application in the IDE. Also be sure that the ID number DOES exist in the database.

I am a bit concerned in this:

this.recID2

where is recID2 coming from

Remember, on the post back, all values are lost in ASP.NET since http/web is stateless. So you need a way of remembering this value either by adding it in session or perhaps writing it in a hidden field on the page some place and getting that value back

mliesmons

Ok. I think it has finally been solved. The post back issue raised by ahmedilyas got me thinking. The textboxes, on page load was populated from the database so that the user could then change the values and update.

Once I commented out the code which populated the textboxes the UPDATE statement worked.

Thanks guys, all the comments were very helpful.
Alvin Kuiper
Hi.

Add

cmd.CommandType = CommandType.Text

before

cmd.ExecuteNon......
RomanQ
have you tired:

strEditCommand += " NAME = @NAME";

strEditCommand += " WHERE ID = @ID ";

see
http://aspnet101.com/aspnet101/tutorials.aspx id=1
vkan

Please, try the following if you haven't tried that yet:

1. Put the

cmd.Connection.Open();

cmd.ExecuteNonQuery();

into try {...} block and catch and investigate the error (if any) returned by the command from the database.

2. Build your project in Debug configuration, put breakpoints in the button's event handler, attach a aspnet_wp.exe in the Debug\Processes menu, run, step through the code and make sure that parameters' values are OK as they are supposed to be before executing the command.
sofakng

Thanks for the help guys. I tried using @Name and @ID but the problem is still there. Including the error handling did help to remove a syntax error from the UPDATE statement. I no longer have an error but the table still does not update.However my other command (INSERT) works perfectly.

I am new to this but feel like I am missing something very simple and obvious. Don't not how to attach the aspnet.exe, but i will see what I can find out.

Response.Write(strEditCommand); produced: UPDATE asps SET NAME = WHERE ID =

private void Button1_Click(object sender, System.EventArgs e)

{

OleDbConnection conn = null;

conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\\Inetpub\\wwwroot\\WEB\\afterschoolprograms.mdb");

string strEditCommand = " UPDATE asps SET "

+ " NAME = "

+ (" WHERE ID = ");

using (OleDbCommand cmd = new OleDbCommand(strEditCommand, conn))

{

cmd.Parameters.Add(new OleDbParameter("NAME", this.txtName.Text.Trim()));

cmd.Parameters.Add(new OleDbParameter("ID", this.recID2));

try

{

cmd.Connection.Open();

Response.Write(strEditCommand);

cmd.ExecuteNonQuery();

}

catch (OleDbException ex)

{

Response.Write(ex.Message);

}

finally

{

cmd.Connection.Close();

}

}
Asday
no worries, its what we are here for :-)
Table does not appear to update after UPDATE statement

Answer this question