Database Update Query

kgriffin · May 6th, 2005, 08:47 AM

I am trying to do a simple update to an access database and have used the WebMatrix query builder to get the following

Code:

    int doUpdates(int page_id, string page_title, string page_contents) {
        string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
        System.Data.IDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);

        string queryString = "UPDATE [Pages] SET [page_title]=@page_title, [page_contents]=@page_contents WHERE ([Pages].[page_id] = @page_id)";

        Response.Write(@page_id);
        Response.Write(@page_title);
        Response.Write(@page_contents);

        System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand();
        dbCommand.CommandText = queryString;
        dbCommand.Connection = dbConnection;

        System.Data.IDataParameter dbParam_page_id = new System.Data.OleDb.OleDbParameter();
        dbParam_page_id.ParameterName = "@page_id";
        dbParam_page_id.Value = page_id;
        dbParam_page_id.DbType = System.Data.DbType.Int32;
        dbCommand.Parameters.Add(dbParam_page_id);
        System.Data.IDataParameter dbParam_page_title = new System.Data.OleDb.OleDbParameter();
        dbParam_page_title.ParameterName = "@page_title";
        dbParam_page_title.Value = page_title;
        dbParam_page_title.DbType = System.Data.DbType.String;
        dbCommand.Parameters.Add(dbParam_page_title);
        System.Data.IDataParameter dbParam_page_contents = new System.Data.OleDb.OleDbParameter();
        dbParam_page_contents.ParameterName = "@page_contents";
        dbParam_page_contents.Value = page_contents;
        dbParam_page_contents.DbType = System.Data.DbType.String;
        dbCommand.Parameters.Add(dbParam_page_contents);

        int rowsAffected = 0;
        dbConnection.Open();
        try {
            rowsAffected = dbCommand.ExecuteNonQuery();
        }
        finally {
            dbConnection.Close();
        }
        return rowsAffected;

    }

which appears to work but rowsAffected comes back as 0 :/

I have checked that all the values are passed into the function,
and have run out of ideas. If someone could check it out for me it would be much appreciated :)

Cheers,
Kev

planoie · May 6th, 2005, 09:03 AM

I doubt that it matters much, but why are you using the IDataParameter interface as your variable type instead of the class OleDbParameter?

Also, this looks like it would be a compile error to me:

        Response.Write(@page_id);
        Response.Write(@page_title);
        Response.Write(@page_contents);

-Peter

kgriffin · May 6th, 2005, 09:15 AM

I have no idea why I am using the IDataParameter interface as my variable type instead of the class OleDbParameter, as, like I mentioned above it was generated by WebMatrix.

If you could explain why I should / shouldn't I would appreiciate it as I am new to C#.

Also I only put the Response lines in to ensure i was passing the variables properly, I'm not entirely sure what the '@' means but it was in the querystring so i thought I'd see if it made any difference when displaying the variable contents. It didn't :)

Cheers,
Kev

planoie · May 6th, 2005, 12:54 PM

Well, like I said, I don't know as it will make a difference if you use the interface or not, just something to try and rule out as a problem.

@ is the prefix for a variable in MS-SQL Transact SQL syntax. My question regarding those lines was that I would have expected the presense of @ in the .NET code to cause it to break. Apparently preceeding a variable with the @ symbol doesn't break the compile in C# (although I can't seem to find what it means when it's in front of a variable versus when it preceeds a string literal).

-Peter