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; 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?