Hi All,

Im new to asp.net programming and Im trying to write a simple program to udpate a database using microsoft access. I have consulted different books, and tried different solutions, but I always encounter this error.. "OPERATION MUST USE UPDATABLE QUERY".

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
  "Data Source=C:\microcell.mdb"
  Dim dbConnection As New OleDbConnection(connectionString)

 dbConnection.Open()

       Dim commandString As String = "INSERT INTO Employees(FirstName, LastName) " & _
 "Values(@FirstName, @LastName)"

        Dim dbCommand As New OleDbCommand(commandString, dbConnection)
        Dim firstNameParam As New OleDbParameter("@FirstName", OleDbType.VarChar, 10)
        firstNameParam.Value = txtfirstname.Text
        dbCommand.Parameters.Add(firstNameParam)
        Dim lastNameParam As New OleDbParameter("@LastName", OleDbType.VarChar, 20)
        lastNameParam.Value = txtlastname.Text
        dbCommand.Parameters.Add(lastNameParam)

            dbCommand.ExecuteNonQuery()
            dbConnection.Close()

IMHO, I hate paramertized queries because you can only use them for a specific task, in this case inserting a firstname and lastname into a database. What I do in all of my applications is I write a wrapper class that handles all of my database connectivity for ANY sql statement that I pass in (usually I just call a stored procedure). In any case something like this may work for you.

private SqlDataAdapter daSql;
private SqlCommand sqlCmd;
private sqlDataReader sqlDataReader;
private SqlConnection sqlConn;

public void writeUpdate(string strSQLcmd)
{
 sqlConn = new SqlConnection();
 sqlConn.ConnectionString = <connection string>
 sqlConn.Open();
 sqlCmd = new SqlCommand(strSQLcmd, sqlConn);
 sqlCmd.CommandTimeout = 3800;
 sqlDataReader = sqlCmd.ExecuteReader();
 sqlDataReader.Close();
 sqlConn.Close();
 sqlDataReader = null;
 sqlConn = null;
}

That method is designed to handle queries that dont return a result set (eg UPDATE, DELETE or INSERT statements) what you would do in code is this

string sSQL;
sSQL = "INSERT INTO table(value1)values(somevalue)";
writeUpdate(sSQL)

Of course you will need to change your object to OLEDB objects but the general idea is still the same.

hth

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

... I am getting the same problem when executing a simple update query from Access VBA and am also struggling to locate the solution. In my code, the query is executed direct (and not parameterized).

UPDATE utility set appversion = '5.10.46'

Any ideas?

Regards,

Sean Anderson