Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 6th, 2005, 08:47 AM
Registered User
 
Join Date: May 2005
Location: Leeds, , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kgriffin
Default Database Update Query

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
  #2 (permalink)  
Old May 6th, 2005, 09:03 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
  #3 (permalink)  
Old May 6th, 2005, 09:15 AM
Registered User
 
Join Date: May 2005
Location: Leeds, , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kgriffin
Default

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
  #4 (permalink)  
Old May 6th, 2005, 12:54 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Query rsm42 ASP.NET 1.0 and 1.1 Basics 0 April 9th, 2007 09:32 AM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
update query leo_vinay SQL Server 2000 1 February 28th, 2006 09:55 AM
update query? robb Access 4 May 19th, 2004 04:41 PM
Update query how to? vickriz Access 1 August 26th, 2003 09:44 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.