C# Saving Integer Value to Access Database

[tawboiid]

I am trying to read a long integer value from an Access database, do some math to the number, and save the result back to the database. I've never had a problem doing this with VB6, but with C# it is throwing an error when it tries to Update the data adapter. Error is: "Syntax Error in Update Statement".

string sPreConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
	string sPostConnString = ";Jet OLEDB:Database Password=xxxxxxx;";
	string sMdbPath = @"\\CMS\AppCounter.mdb"; 
	string sDbCounter = sPreConnString + sMdbPath + sPostConnString;

         OleDbConnection conn = null;
	conn = new OleDbConnection(sDbCounter);
	conn.Open();
	
	string strSQL = "SELECT * FROM Data WHERE AppName = 'F4WDAcronym'";

	OleDbDataAdapter objDataAdapter = new OleDbDataAdapter (strSQL, conn);
	OleDbCommandBuilder objCommandBuilder = new OleDbCommandBuilder (objDataAdapter);
	DataTable objDataTable = new DataTable();
	objDataAdapter.Fill(objDataTable);
	
		DataRow objDataRow = objDataTable.Rows[0];
		string strCounter = objDataRow["Counter"].ToString();
		int intCounter = Int32.Parse(strCounter) + 1;
		
		objDataRow["Counter"] = intCounter;
	
	objDataAdapter.Update(objDataTable);
	objDataAdapter.Dispose();	
	
	conn.Close();

I think the problem relates to a mismatch in datatypes when trying to save to Access. I've tried all the integer data types but can't seem to resolve this. If I comment out the "objDataRow["Counter"] = intCounter;" line then the code runs without errors.

Anyone have experience saving integer values to Access databases?

[ddanbe]

What is the type of objDataRow["Counter"] ?
C# is very strict on types.

[tawboiid]

What is the type of objDataRow["Counter"] ?
C# is very strict on types.

In Access, the field is of data type "Number", and the Field Size property is set to "Long Integer". According to the Access documentation, this should be a 32 bit integer.

[Momerath]

From the documentation "The DbCommandBuilder must execute the SelectCommand in order to return the metadata necessary to construct the INSERT, UPDATE, and DELETE SQL commands. As a result, an extra trip to the data source is necessary, and this can hinder performance. To achieve optimal performance, specify your commands explicitly rather than using the DbCommandBuilder."

I don't see where you are doing that.

[tawboiid]

From the documentation "The DbCommandBuilder must execute the SelectCommand in order to return the metadata necessary to construct the INSERT, UPDATE, and DELETE SQL commands. As a result, an extra trip to the data source is necessary, and this can hinder performance. To achieve optimal performance, specify your commands explicitly rather than using the DbCommandBuilder."

I don't see where you are doing that.

Hmmmm...the code works perfectly if the only data being saved to the database is string data. I've used this same snippet in numerous other apps and ASP.Net web pages without problems, although in those cases the data was always string data.

I would be willing to try some other ADO methods if someone could give me some code snippets to try.

[abelLazm]

MOMERATH is right try his suggested way it will work ...Or change the integer datatype to double or try the following code

private static SqlCommand Command = new Command();
Command.Connection = DbConn;
Command.CommandText = "INSERT INTO tablname  (ColumnNames....) values(values)";
Command.CommandType = CommandType.Text;
Command.ExecuteNonQuery();

[tawboiid]

No good. The problem is not with the method of connecting or addressing the db connection, it is with the data type mismatch. As an example, if I take my original code and replace it with the following, it runs just fine with no problems (notice I commented out 3 lines and replaced them with three new lines which only processes string data:

OleDbConnection conn = null;
	conn = new OleDbConnection(sDbCounter);
	conn.Open();
	
	string strSQL = "SELECT * FROM Data WHERE AppName = 'F4WDAcronym'";

	OleDbDataAdapter objDataAdapter = new OleDbDataAdapter (strSQL, conn);
	OleDbCommandBuilder objCommandBuilder = new OleDbCommandBuilder (objDataAdapter);
	DataTable objDataTable = new DataTable();
	objDataAdapter.Fill(objDataTable);
	
		DataRow objDataRow = objDataTable.Rows[0];
		//string strCounter = objDataRow["Counter"].ToString();
		//int intCounter = Int32.Parse(strCounter) + 1;
		//objDataRow["Counter"] = intCounter;
	
		string strName = objDataRow["AppName"].ToString();
		strName = strName + "new";
		objDataRow["AppName"] = strName;
	
	objDataAdapter.Update(objDataTable);
	objDataAdapter.Dispose();	
	
	conn.Close();

This runs just fine and properly updates the Access database. The problem only shows up if I try to update an integer value. I've tried all the various integer types I could come up with but nothing works.

[tawboiid]

OK, I figured this out (and I'm a little embarrassed)....

The problem was that the field name "Counter" that I was using is a reserved word in Access. Once I changed the dB field name, my original code worked fine.

In my defense, the field name worked fine using similar code from VB6 code in classic ASP.