How do I update a change using SubSonic and MySQL

Question

I was trying to TDD using SubSonic 2.1, MySQL and C# and when I got to testing updating an existing record using the code below,

public void Test2()
{
    User.Insert("jmarcus1", "jmarcus1", "jackass", "marcus", 3, false);
    User users = new User();
    int ulevel = 1;
    User.Update(2, "jmarcus1", "jmarcus1", "jackass", "marcus", ulevel, false);
    Assert.AreEqual(1, users.Ulevel);
}

with the following

------ Test started: Assembly: SalMan.dll ------

Starting the MbUnit Test Execution
Exploring SalMan, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
MbUnit 2.4.2.130 Addin
Found 3 tests
[success] TestFixture1.Test
[success] TestFixture1.Test1
[failure] TestFixture1.Test2
TestCase 'TestFixture1.Test2'
failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `userid` = 1; SELECT 1 AS id' at line 1
    MySql.Data.MySqlClient.MySqlException
    Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `userid` = 1; SELECT 1 AS id' at line 1
    Source: MySql.Data
    StackTrace:
    at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
    at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
    at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
    at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
    at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
    at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()
    C:\svn\subsonicproject\trunk\SubSonic\DataProviders\MySqlDataProvider.cs(280,0): at SubSonic.MySqlDataProvider.ExecuteScalar(QueryCommand qry)
    C:\svn\subsonicproject\trunk\SubSonic\DataProviders\DataService.cs(533,0): at SubSonic.DataService.ExecuteScalar(QueryCommand cmd)
    C:\svn\subsonicproject\trunk\SubSonic\ActiveRecord\ActiveRecord.cs(182,0): at SubSonic.ActiveRecord`1.Save(String userName)
    D:\My Documents\Visual Studio 2008\Projects\SalMan\SalMan\Generated\User.cs(352,0): at Salman.User.Update(Int32 varUserid, String varUsername, String varPassword, String varFname, String varLname, Int32 varUlevel, Boolean varStatus)
    D:\My Documents\Visual Studio 2008\Projects\SalMan\SalMan\Tests\TestFixture1.cs(40,0): at salman.TestFixture1.Test2()

[reports] generating HTML report
TestResults: file:///C:/Documents%20and%20Settings/*****************/Application%20Data/MbUnit/Reports/SalMan.Tests.html

2 passed, 1 failed, 0 skipped, took 7.66 seconds.

Does anyone have a workaround for this?

Answer 1

It looks to me like your args are out of order. The PK should be the first thing - are you sure they are lining up correctly?

Are you using ActiveRecord? If so - have you tried this:

User u=new User();
u.UserName("jmarcus1");
u.Name="jackass"
u.Ulevel=1;
...
u.Save();

User u=new User("jmarcus1");
u.UILevel=2;
u.Save();

Answer 2

apparently the solution was, following Robs excellent advice above, to

User u = User.FetchByID(2);
u.Ulevel = ulevel;
u.save();

i was not too sure that the snippet above would work for updates until you specify the record you wish to update.

my question to Rob however is, why does "u.Update(2, "jmarcus",..." give the error above?