SQL CE statement to update or insert assets in ASP.Net

Question

I'm creating a web form to insert information into a database. I had no issue getting this set up to insert information that was typed, but I ran into trouble trying to reformat the command to UPDATE information if the entry already exists in the database.

I'm getting the following error when I try to submit the changes:

There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = Assets ]

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlServerCe.SqlCeException: There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = Assets ]

Source Error:

Line 39: INSERT (AssetNumber, SerialNumber, AssetType, Model, Location, DateModified, Comments), Line 40: VALUES (@0, @1, @2, @3, @4, @5, @6)"; Line 41: db.Execute(insertCommand, assetNumber, serialNumber, assetType, model, toCube, currentTime, comments); Line 42: Response.Redirect("~/AssetLookup"); Line 43: }

The code errors out at line 41. The only thing I changed in the code below is the SQL statement, so I'm sure there's something wrong with that. This is my first time trying to work with SQL; I did some digging around trying to figure out what is wrong with the statement, but in particular I'm having trouble with the USING ... ON part. I'm not really sure WHAT should go there, and I feel like that's probably the issue.

var assetNumber = "";
var serialNumber = "";
var assetType = "";
var model = "";
var toCube = "";
var comments = "";

if(IsPost && Validation.IsValid()){
    assetNumber = Request.Form["assetNumber"];
    serialNumber = Request.Form["serialNumber"];
    assetType = Request.Form["assetType"];
    model = Request.Form["model"];
    toCube = Request.Form["toCube"];
    var currentTime = DateTime.Now;
    comments = Request.Form["comments"];

    var db = Database.Open("Assets");
    var insertCommand = 
    @"MERGE Assets AS Target 
      USING (SELECT assetNumber, serialNumber FROM Assets) AS Source
      ON (Target.assetNumber = Source.assetNumber AND Target.serialNumber = Source.serialNumber)
        WHEN MATCHED THEN 
            UPDATE SET
                AssetNumber=@0,
                SerialNumber=@1,
                AssetType=@2,
                Model=@3,
                Location=@4,
                DateModified=@5,
                Comments=@6                                      
        WHEN NOT MATCHED THEN 
            INSERT (AssetNumber, SerialNumber, AssetType, Model, Location, DateModified, Comments), 
            VALUES (@0, @1, @2, @3, @4, @5, @6)";
    db.Execute(insertCommand, assetNumber, serialNumber, assetType, model, toCube, currentTime, comments);
    Response.Redirect("~/AssetLookup");
}

Like I said, I'm sure my SQL statement is messed up somewhere as I "kind of" understand it but am particularly confused on the USING...ON part. All table columns and variable names are set properly per the rest of the code/db. Can someone please offer some advice on the syntax I should use for this command?

UPDATE:

Based on Steve's comment, I have updated the question to reflect that this is SQL CE and not the standard version.

`UPDATE`s usually have a `WHERE` clause - however I'm not familiar with the `MATCHED` language, but a vanilla `UPDATE` without a `WHERE` would update every row. — RemarkLima, Nov 24 '14 at 21:36
MERGE is not supported by Sql Server Compact Edition http://stackoverflow.com/questions/14091092/does-sql-server-ce-supports-merge-statement — Steve, Nov 24 '14 at 21:41
@Steve I didn't even think to check something like that. Thanks for saving me a lot of trouble! I'll try to work out a reformatted version tomorrow leaving out MERGE. — levelonehuman, Nov 25 '14 at 03:06
Glad to be of help. [Here a full specs comparison](http://msdn.microsoft.com/en-us/library/bb896140.aspx) — Steve, Nov 25 '14 at 07:59
Thanks @DourHighArch, resubmitted my update as an answer to the question. — levelonehuman, Nov 25 '14 at 23:34

score 1 · Answer 1 · answered Nov 24 '14 at 21:44

It looks like you don't have anything tying your Target to your source. You're using your ON clause to compare the target table to itself effectively.

Maybe try something like this:

var assetNumber = "";
var serialNumber = "";
var assetType = "";
var model = "";
var toCube = "";
var comments = "";

if(IsPost && Validation.IsValid()){
    assetNumber = Request.Form["assetNumber"];
    serialNumber = Request.Form["serialNumber"];
    assetType = Request.Form["assetType"];
    model = Request.Form["model"];
    toCube = Request.Form["toCube"];
    var currentTime = DateTime.Now;
    comments = Request.Form["comments"];

    var db = Database.Open("Assets");
    var insertCommand = 
    @"MERGE Assets AS Target 
      USING (SELECT @0 as assetNumber, @1 as serialNumber) AS Source
      ON (Target.assetNumber = Source.assetNumber AND Target.serialNumber = Source.serialNumber)
        WHEN MATCHED THEN 
            UPDATE SET
                AssetNumber=@0,
                SerialNumber=@1,
                AssetType=@2,
                Model=@3,
                Location=@4,
                DateModified=@5,
                Comments=@6                                      
        WHEN NOT MATCHED THEN 
            INSERT (AssetNumber, SerialNumber, AssetType, Model, Location, DateModified, Comments), 
            VALUES (@0, @1, @2, @3, @4, @5, @6)";
    db.Execute(insertCommand, assetNumber, serialNumber, assetType, model, toCube, currentTime, comments);
    Response.Redirect("~/AssetLookup");
}

I'm not 100% on this one, as I'm not too well versed with Razor or running queries via the code (I usually stick to stored procs), but it sounds like you want to compare the incoming asset and serial number to what is actually in the table, and what you had before wouldn't do that.

This would be correct if this was Sql Server, but from the stack trace exception it is clearly **Sql Server Compact Edition** which doesn't support MERGE. — Steve, Nov 25 '14 at 08:00
Even though this didn't work for CE (my bad, wasn't aware that was an issue!) it's very helpful to know what was wrong with my MERGE statement in the first place. Thank you! — levelonehuman, Nov 25 '14 at 20:56

score 1 · Accepted Answer · answered Nov 25 '14 at 23:33

In a pretty basic way, I got this working by mixing some C# if/else logic (since SQL CE doesn't support that either) with basic SQL statements.

This code checks the form submission for asset and serial number match and updates if a match is found. If no record is returned, the asset is added to the database instead:

var db = Database.Open("Assets");
var query = "SELECT * FROM Assets WHERE AssetNumber = @0 AND SerialNumber = @1";
var record = db.QuerySingle(query, assetNumber, serialNumber);

if (record != null) {
    var updateCommand = 
    @"UPDATE Assets SET
        AssetNumber=@0,
        SerialNumber=@1,
        AssetType=@2,
        Model=@3,
        Location=@4,
        DateModified=@5,
        Comments=@6                                      
    WHERE AssetNumber=@0 AND SerialNumber=@1";
    db.Execute(updateCommand, assetNumber, serialNumber, assetType, model, toCube, currentTime, comments);
} else {
    var insertCommand = 
    @"INSERT INTO Assets (AssetNumber, SerialNumber, AssetType, Model, Location, DateModified, Comments) 
    VALUES (@0, @1, @2, @3, @4, @5, @6)";
    db.Execute(insertCommand, assetNumber, serialNumber, assetType, model, toCube, currentTime, comments);
}

This code is nested inside the if(IsPost...) method listed in the previous code block and replaces the SQL statement that was there before. It still requires a little work to put additional checks in place, but the basic logic does exactly what I wanted it to do.

Thanks @Steve for pointing me in the right direction!

SQL CE statement to update or insert assets in ASP.Net

2 Answers2