Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
407 views
in Technique[技术] by (71.8m points)

c# - SQLITE cuts off zeros, when updating database

Im working with a Sqlite database in C#, when I insert my date into it, everything works fine (in the format MM.YYYY). Nevertheless when I update the database, sqlite cuts off leading zeros, so 09.2019 appears as 9.2019. But it's important for further operations that the leading zero remains in it's place.

When creating the table "credits", I use TEXT for the date to store it.

I show you some code how I update the database:

SQLiteCommand command = new SQLiteCommand(databaseConnection);

command.CommandText = "Update credits Set lastDate = " + date +
     " WHERE ID=" + Convert.ToString(index);

command.ExecuteScalar();
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Try parametrizing your query; all you should provide is date and let RDBMS do its work (formats, representation etc. included)

// using - do not forget to Dispose (i.e. free resources)
using (SQLiteCommand command = new SQLiteCommand(databaseConnection)) {
  // Do not build query, but parametrize it
  command.CommandText = 
     @"update credits 
          set lastDate = @prm_Date
        where ID = @prm_ID";

  // Parameters instead hardcoding
  //TODO: better put command.Parameters.Add(Name, Value, Type)
  command.Parameters.AddWithValue("@prm_Date", date);
  command.Parameters.AddWithValue("@prm_ID", index);

  // Non query : we don't want to return even a single value, right?
  command.ExecuteNonQuery();
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...