dimanche 6 septembre 2015

How to return @@Identity after Insert into SQLLite DB 3

i am trying to switch my code over to use SQL Lite 3 instead of MS Server 2014 so I can avoid the licening costs (and inital SQL setup on Custoomer PCs).

I insert a record into my DB and I want to get the identity back. The field IS an auto-increment type.

This is my code:

    public static DataTable AddAddress(string add1, string add2, string add3, string town, string county, string pcode)
    {
        var cmd = new SQLiteCommand();
        DataTable dt = new DataTable();
        string connectionString = ConfigurationManager.ConnectionStrings["LiveCN"].ConnectionString;
        using (var connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            cmd.Connection = connection;
            cmd.Parameters.AddWithValue("@Add1", add1);
            cmd.Parameters.AddWithValue("@Add2", add2);
            cmd.Parameters.AddWithValue("@Add3", add3);
            cmd.Parameters.AddWithValue("@Town", town);
            cmd.Parameters.AddWithValue("@County", county);
            cmd.Parameters.AddWithValue("@PCode", pcode);
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("INSERT INTO[Address] (Add1,Add2,Add3,County,PCode,Town) VALUES(@Add1, @Add2, @Add3, @County, @PCode, @Town);SELECT @@IDENTITY");
            cmd.CommandText = sb.ToString();
            cmd.CommandType = CommandType.Text;
            using (var da = new SQLiteDataAdapter(cmd))
            {
                da.Fill(dt);
            }
        }
        return dt;
    }

and the error I get is:

An exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll but was not handled in user code Additional information: SQL logic error or missing database unrecognized token: "@"

Aucun commentaire:

Enregistrer un commentaire